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.
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.
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 ?
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.
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.