Re: Kill session from PL/SQL

A copy of this was sent to Norman Dunbar <NDunbar_at_Tenhill.co.uk> (if that email address didn't require changing) On Tue, 27 Jul 1999 16:43:39 +0100, you wrote:

>We have an application which runs under a non-dba user.
>We cannot assume that the customer will grant dba or alter system privs
>to the user when the application is installed - in fact we know that
>some of them won't.
>Our application runs a day end which must kill off any users still
>connected using a sys owned kill_user procedure (see below).
>
>The procedure gets any serial# values from v$session for the passed sid
>and tries to do an alter system kill session for each one found.
>
>Oracle 7.3.4 and 8.0.5.1 are involved.
>
>
>The problems are as follows :
>
>1. It won't actually run even in user SYS (which owns it) - it gives the
>following output/errors :
>
>SQL> execute kill_user(14);
>'14,195'
>begin kill_user(14); end;
>
>*
>ERROR at line 1:
>ORA-00911: invalid character
>ORA-06512: at "SYS.KILL_USER", line 11
>ORA-06512: at line 1
>

lose the semi-colon. the procedure should have:

        DBMS_SQL.PARSE(C3, 'alter system kill session '|| USERID,
                       DBMS_SQL.NATIVE);
        RESULT := DBMS_SQL.EXECUTE(C3);

the owner of the procedure will need the ALTER SYSTEM privelege granted to them directly.

>(Line 11 is the execute line.)
>
>
>2. I suspect that I may yet encounter privilege problerms anyway - what
>is the rule for running a stored procedure from another user - I have
>read the manual and it is not clear as to whether it will run as SYS or
>as our non-dba user.
>
>Any help would be appreciated as I am about to tear all my fingernails
>out, just for fun !
>
>Norman Dunbar (mailto://NDunbar@Tenhill.co.uk)
>
>
>The code is as follows :
>
>create or replace procedure kill_user(sid_in IN number) as
> C3 BINARY_INTEGER;
> RESULT BINARY_INTEGER;
> USERID VARCHAR2(250);
>begin
> C3 := DBMS_SQL.OPEN_CURSOR;
> for C2 in (select serial# from v$session where sid = sid_in) loop
> USERID := '''' || sid_in || ',' || C2.serial# || '''' ;
> DBMS_OUTPUT.PUT_LINE(USERID);
> DBMS_SQL.PARSE(C3, 'alter system kill session '|| USERID || ';'
>, DBMS_SQL.NATIVE);
> RESULT := DBMS_SQL.EXECUTE(C3);
> end loop;
>
> DBMS_SQL.CLOSE_CURSOR(C3);
>end;
>/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 27 1999 - 11:10:41 CDT