DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Insufficient Privileges

  1. #1
    Join Date
    Jan 2001
    Posts
    28
    I am logged in as a db user and when I run the query
    ALTER SYSTEM KILL SESSION 'sid, serial#'
    from the SQL prompt, it kills the session, but when I try running the same inside a PL/SQL procedure using dynamic sql, it says 'insufficeint privileges'. Can some throw some light on this.
    - Mayur.

  2. #2
    Join Date
    Jan 2001
    Posts
    515
    who ownes the procedure? Does the procedure have dba priveledges? If it doesn't they need to be granted the right to kill a session.

  3. #3
    Join Date
    Jan 2001
    Posts
    28
    Well, the procedure is owned by the DBA user and how do I check if the procedure has DBA privileges. And whom do I need to grant the alter kill session to ? Because, the user who owns the procedure, when runs the alter command from the SQL prompt, it kills the session.
    - Mayur.

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    Does the owner of the procedure has ALTER SYSTEM privilege granted to her/him directly and not through a role. Check USER_SYS_PRIVS.
    Roles are not visible in stored procedures.

  5. #5
    Join Date
    Jan 2001
    Posts
    28
    Now this is a little confusing. I checked in the table and I don't have any ALTER privileges at all. But then how can I execute the command from the SQL prompt with the same user and it doesn't crib stating Insufficient Privileges. You get what I mean. Do you mean that if a Role is assigned which has the ALTER privilege, I can run from SQL but not from a procedure ?
    - Mayur.

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    Yes.
    Priviliges can be granted directly and through a role. In stored procedures, packages etc roles are invisible/disabled, that's why you need privileges granted directly if you need them in a stored procedure.

  7. #7
    Join Date
    Jan 2001
    Posts
    28
    Thanks man... I got it now. The user was given the DBA role. I granted ALTER SYSTEM to the user and tried running the procedure and it works. Thanks a lot, was something I didn't know.
    - Mayur.

  8. #8
    Join Date
    Nov 2000
    Posts
    205
    Is there some way to just give them permission to alter system kill session, instead of alter system?

    I just feel that that is an awful lot of rights to give to a person just to able to kill his own session.

    Also can you help me as to how I would write a proc/package to do this?

    Thanks,
    Nirasha

  9. #9
    Join Date
    Jul 2000
    Posts
    296
    If a user has already DBA role, she or he can do a lot more then just kill sessions. If you want a user to be able to kill her or his own sessions, you can create a stored procedure to kill a session, if it is a session of this user. You can grant execute on the procedure to public, so everybody can kill just their own sessions. Only the owner of the procedure needs ALTER SYSTEM privilege, not the user who executes the procedure.

  10. #10
    Join Date
    Jan 2001
    Posts
    28
    Nirasha,
    Give me your mail id and I shall send across the procedure that I have written. Or mail me at mayur_nath@hotmail.com
    - Mayur.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width