Kill session from PL/SQL

From: Norman Dunbar <NDunbar_at_Tenhill.co.uk>
Date: Tue, 27 Jul 1999 16:43:39 +0100
Message-ID: <9419728F7734D311B0490008C7DBDFFF044042@teapps.tenhill.co.uk>

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

(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;
/ Received on Tue Jul 27 1999 - 10:43:39 CDT