How to give non-DBA user kill session privileges?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to give non-DBA user kill session privileges?

Hybrid View

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    How to give non-DBA user kill session privileges?

    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?

  2. #2
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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...
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  3. #3
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    This is needed for our remote site operators so that they do not wake us up at 3 am to kill a session.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    a proc owned by sys which you give execute permission on will do then

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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.
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    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...
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

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