Insufficient Privileges

[tmnath]

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.

[lesstjm]

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.

[tmnath]

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.

[akkerend]

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.

[tmnath]

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 ?

[akkerend]

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.

[tmnath]

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.

[nirasha]

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

[akkerend]

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.

[tmnath]

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