Re: URGENT: Killing Users using dynamic SQL

On Wed, 12 Jan 2000 13:28:04 +0100, "Martin" <mmarhold_at_yahoo.com> wrote:

>I´m trying to kill sessions using dynamic SQL. The code that should do this
>is:
>
>aFetchCursor := SYS.DBMS_SQL.OPEN_CURSOR;
>aKillCursor := SYS.DBMS_SQL.OPEN_CURSOR;
>aSqlCmd := 'SELECT sid, serial# FROM Temp_Online_Users';
>SYS.DBMS_SQL.PARSE(aFetchCursor, aSqlCmd, DBMS_SQL.V7);
>SYS.DBMS_SQL.DEFINE_COLUMN(aFetchCursor, 1, aUserSid);
>SYS.DBMS_SQL.DEFINE_COLUMN(aFetchCursor, 2, aUserSerial#);
>aRetCode := SYS.DBMS_SQL.EXECUTE(aFetchCursor);
>LOOP
> IF DBMS_SQL.FETCH_ROWS(aFetchCursor) = 0 THEN
> EXIT;
> END IF;
> DBMS_SQL.COLUMN_VALUE(aFetchCursor, 1, aUserSid);
> DBMS_SQL.COLUMN_VALUE(aFetchCursor, 2, aUserSerial#);
> aSqlCmd := 'ALTER SYSTEM KILL SESSION
>'''||aUserSid||','||aUserSerial#||'''';
> SYS.DBMS_SQL.PARSE(aKillCursor, aSqlCmd, DBMS_SQL.V7);
> aRetCode := SYS.DBMS_SQL.EXECUTE(aKillCursor);
>END LOOP;
>SYS.DBMS_SQL.CLOSE_CURSOR(aKillCursor);
>SYS.DBMS_SQL.CLOSE_CURSOR(aFetchCursor);
>
>Although I´m connected as User SYS (who has the privilege to kill sessions)
>the code above returns the folowing error:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
>ORA-06512: at "SYS.DBMS_SQL", line 328
>
>Does anyone have a solution for this?
>
>Thanks in advance, Martin
>
>

You have alter system privilege through a role, and roles are not enabled in stored procedures. You need to grant privilege directly.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Jan 12 2000 - 07:04:29 CST