Re: Set Role From a Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/29
Message-ID: <33b7d7bd.28072916@newshost>#1/1

On Fri, 27 Jun 97 17:00:09 +0400, "Victor L. Artchakov" <VICTOR_at_rias.khv.ru> wrote:

>> roles are never enabled in a stored procedure.. setting a role in a stored
>> procedure won't work. In the supplied example, the parse succeeded, but the
>> execute of the statement (only DDL is implicitly execute with dbms_sql, set role
>> is not ddl) never took place. Modify the routine to be:
>>

[snip]

>What about my spool file?
>
>CREATE OR REPLACE PROCEDURE set_role
> 2 IS
> 3 cursor_handle INTEGER;
> 4 rc number;
> 5 BEGIN
> 6 cursor_handle := DBMS_SQL.OPEN_CURSOR;
> 7 DBMS_SQL.PARSE(cursor_handle,'SET ROLE ALL',DBMS_SQL.V7);
> 8 rc := dbms_sql.execute( cursor_handle );
> 9 END;
> 10 /
>
>Procedure created.
>
>SQL> exec set_role;
>
>PL/SQL procedure successfully completed.
>

You must have 7.1, apparently that version didn't raise an error -- set role is invoked (but the set role does nothing in spite of the lack of an error message, roles do not exist in a procedures execution space). Try this in 7.1 to verify that the set role command in a stored procedure is a big no-op... (x$kzsro is an internal list of roles that are active, your userid and the userid for 'PUBLIC' are always in there so when NO roles are active, this will have 2 rows):

SQL*DBA: Release 7.1.6.2.0 - Production on Sun Jun 29 17:17:42 1997

Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.1.6.2.0 - Production

SQLDBA> connect internal
Connected.

SQLDBA> create view enabled_roles as select * from x$kzsro; Statement processed.

SQLDBA> grant select on enabled_roles to scott; Statement processed.

SQLDBA> connect scott/tiger;
Connected.

SQLDBA> select count(*) from sys.enabled_roles; COUNT(*)

4
1 row selected.

SQLDBA> set role none;
Statement processed.

SQLDBA> select count(*) from sys.enabled_roles; COUNT(*)

2
1 row selected.

SQLDBA> set role all;
Statement processed.

SQLDBA> CREATE OR REPLACE PROCEDURE set_role

      cursor_handle INTEGER;
      rc                       number;
      n                        number;
    BEGIN
          select count(*) into n from sys.enabled_roles;
          dbms_output.put_line( n );
          cursor_handle := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cursor_handle,'SET ROLE ALL',DBMS_SQL.V7);
          rc := dbms_sql.execute( cursor_handle );
          select count(*) into n from sys.enabled_roles;
          dbms_output.put_line( n );
    END;
/     2>      3>      4>      5>      6>      7>      8>      9>     10>     11>
     12>     13>     14>     15>

Statement processed.

SQLDBA> set serveroutput on

SQLDBA> execute set_role;
2
2
Statement processed.
SQLDBA> As you can see, before and after the set role all call, the number of 'roles' is 2, the rows in the enabled_roles will map to the user_id of the currently logged in user and the user_id of the user 'PUBLIC'....

In 7.2, the dbms_sql call begins to raise the error we reported previously (SET ROLE not able to be used in procedures)....

ERROR at line 1:
ORA-06565: cannot execute SET ROLE from within stored procedure

>
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 29 1997 - 00:00:00 CDT