allowing a user to kill his own connections

#21

On May 6, 11:55*pm, m... (AT) pixar (DOT) com wrote:

Quote:

Ana C. Dent <anaced... (AT) hotmail (DOT) com> wrote:

m... (AT) pixar (DOT) com wrote innews:NsKTj.1754$ah4.1745 (AT) flpi148 (DOT) 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

#22

On May 7, 12:55*am, m... (AT) pixar (DOT) com wrote:

Quote:

Ana C. Dent <anaced... (AT) hotmail (DOT) com> wrote:

m... (AT) pixar (DOT) com wrote innews:NsKTj.1754$ah4.1745 (AT) flpi148 (DOT) 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 --

#23

On May 7, 12:55*am, m... (AT) pixar (DOT) com wrote:

Quote:

Ana C. Dent <anaced... (AT) hotmail (DOT) com> wrote:

m... (AT) pixar (DOT) com wrote innews:NsKTj.1754$ah4.1745 (AT) flpi148 (DOT) 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 --

#24

On May 7, 12:55*am, m... (AT) pixar (DOT) com wrote:

Quote:

Ana C. Dent <anaced... (AT) hotmail (DOT) com> wrote:

m... (AT) pixar (DOT) com wrote innews:NsKTj.1754$ah4.1745 (AT) flpi148 (DOT) 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 --

#25

On May 7, 12:55*am, m... (AT) pixar (DOT) com wrote:

Quote:

Ana C. Dent <anaced... (AT) hotmail (DOT) com> wrote:

m... (AT) pixar (DOT) com wrote innews:NsKTj.1754$ah4.1745 (AT) flpi148 (DOT) 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 --

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

allowing a user to kill his own connections

comp.databases.oracle.misc comp.databases.oracle.misc

Re: allowing a user to kill his own connections - 05-07-2008 , 07:30 AM

Re: allowing a user to kill his own connections - 05-07-2008 , 10:47 AM

Re: allowing a user to kill his own connections - 05-07-2008 , 10:47 AM

Re: allowing a user to kill his own connections - 05-07-2008 , 10:47 AM

Re: allowing a user to kill his own connections - 05-07-2008 , 10:47 AM