How to give non-DBA user kill session privileges?

[newbie5]

I need to give a non-DBA user kill session privileges. Obviously I do not want him to have ALTER SYSTEM system privilege.

Is there any way out such as a execute privileges on a package such as dbms_session or dbms_system for example?

[tomcat]

I did something similar with password resets. Create a procedure owned by sys that does what you want; grant execute on that procedure to the user.

I would add some check in the procedure, so they can't kill just any session - like only if the username is in a certain list/table, or user profile, etc.

Just curious, why does someone need to kill sessions? Sounds drastic...

[newbie5]

This is needed for our remote site operators so that they do not wake us up at 3 am to kill a session.

[davey23uk]

a proc owned by sys which you give execute permission on will do then

[sambavan]

I would second the tomcat's suggestion. But here I would create a webpage and based on the operator sites, I would first list the session information corresponding to the site and user. Then through the web front end would gather the inforamation from the operator and then invoke the database package to delete the session using alter system and then also log the information on the session to a log table with the time of delete and the person who deleted it. This way it would keep track of things and also would help you to answer some questions if one were to be raise to you.

[tomcat]

I'm all for avoiding late night calls.

I've seen problems before with the unix process hanging out there, with the Oracle process in a KILLED status in v$session. If you've been doing this for them, and haven't had any problems - then it's probably fine.

I would put someway in the procedure to limit what sessions can be killed, you need someway for the operator to identify the session. You wouldn't want them getting the wrong session...