Re: Kill Session question

From: Alexey Sergeyev <a.sergeev_at_ibsp.ru>
Date: Fri, 24 Jan 2003 15:15:48 +0300
Message-ID: <b0rebq$9dt$1@slim.sovintel.ru>

Jan Gelbrich wrote:
> Hello,
>
> I want to write a stored procedure that - if tested - should run
> by dbms_job every 5 minutes to detect and eventually kill blocking
sessions
> automatically.
>
> Now I am standing closed to the goal, and this is the part still making
> trouble:
>
> PROCEDURE blocker_killer

> But when I activate EXECUTE ... cKiller, I see
>
> ORA-01031: UNSUFFICIENT PRIVILIGES
> ORA-06512: in "MASTERMIND.BLOCKER_KILLER", Zeile 156 (where EXECUTE .. is
> on)
> ORA-06512: in Zeile 1
>
> Then I connected as SYS and re-granted ALTER SESSION to MASTERMIND -
nothing
> doing.
> (Did not make sense anyway, but I had the same effect at selecting from
v$*
> in a SP ...)
> Strange.
>
> But when I am MASTERMIND in SQL*Plus, I *can* kill sessions just as I

> How come ? AFAIK, DBA-Role is the most powerful role of all in a schema.
> What am I missing or doing wrong ?

You must create your procedure with AUTHID CURRENT_USER clause to activate role priveleges, when the procedure execute.

Alexey Sergeyev. Received on Fri Jan 24 2003 - 06:15:48 CST