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?

TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

mh@pixar.com wrote in news:NsKTj.1754$ah4.1745@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.

On May 5, 3:53*pm, m...@pixar.com wrote:
> 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?
>
> TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

I agree with the stored procedure route, as that's the only way you
could restrict the action to sessions associated with the invoking
user.


David Fitzjarrell

On May 5, 11:00*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> m...@pixar.com wrote innews:NsKTj.1754$ah4.1745@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.

I also agree with the stored procedure route; however, the owner does
not need to be SYS and probably should not be SYS. I created a DBA
privileged account to own special purpose routines like this. Using a
non-SYS owner makes keeping track of the special purpose routines,
database event triggers, and like easier.

IMHO -- Mark D Powell --

Ana C. Dent <anacedent@hotmail.com> wrote:
> mh@pixar.com wrote in news:NsKTj.1754$ah4.1745@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

On May 6, 11:55*pm, m...@pixar.com wrote:
> Ana C. Dent <anaced...@hotmail.com> wrote:
>
> > m...@pixar.com wrote innews:NsKTj.1754$ah4.1745@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 are correct.


David Fitzjarrell

On May 7, 12:55*am, m...@pixar.com wrote:
> Ana C. Dent <anaced...@hotmail.com> wrote:
>
> > m...@pixar.com wrote innews:NsKTj.1754$ah4.1745@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 --