Re: allowing a user to kill his own connections

On May 7, 12:55�am, m..._at_pixar.com wrote:
> Ana C. Dent <anaced..._at_hotmail.com> wrote:
>
> > m..._at_pixar.com wrote innews:NsKTj.1754$ah4.1745_at_flpi148.ffdc.sbc.com:
>
> > > I would like to allow developers to kill their own sessions, e.g.
>
> > > � � alter system kill session '$sid,$serial#'
>
> > > but only for sessions which are theirs.
>
> > > Is there a grant which can handle this? �If not, what's the
> > > best way to handle this?
>
> > You can write a procedure owned by SYS which can issue the ALTER SYSTEM;
> > using owner's rights not invoker's rights.
>
> And if I want to make sure that you can't kill someone else's
> session, that should be handled by comparing the current
> user with the user of the $sid, is that right?
>
> In other words, there's not an automatic ownership/protection
> mechanism a la unix processes and kill.
>
> Thanks All!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

You want to build in as many safeguards or features as necessary for your environment. Besides allowing users with unique Oracle usernames to kill sessions that he or she owns you might in the case of a shared Oracle username be able to key off of v$session.osuser as an example. Or maybe like us you have an application where distributers enter information and can if the task is not completed hold a lock for hours. If production batch is waiting on the non-existent user you might approve the kill based on the time since the session last issued an SQL statement and the v$session.program being executed then log the action.

Requirements for what you want to allow and for keeping a history of who killed who will vary by sites.

This same technique is also good for allowing userA to truncate userB objects. But if you are going to allow truncate it is probably wise to require OK to truncate tables to be input into a table that records this fact and check against it least someone truncate a table that should not be truncated.

HTH -- Mark D Powell -- Received on Wed May 07 2008 - 10:47:10 CDT