Re: Roles

Solomon.Yakobson_at_entex.com wrote:
>
> No offense, but it would be nice to test your suggestions before
> posting a reply. ORACLE documentation clearly states that SET ROLE
> statement can not be called from stored procedure. It makes no
> difference how you call it: via SQL or dynamic SQL or any other
> way (e.g. DBMS_SESSION.SET_ROLE).
> But even if it would be allowed via DBMS_SQL package, your stored
> procedure would not work. You assumed that SET ROLE is a DDL
> statement. It is not. SET ROLE is session control statement.
> Therefore DBMS_SQL.PARSE just parses it (not parse+execute like it
> is done for DDL statements). You need explicit DBMS_SQL.EXECUTE.
> And if you will add it, compile and execute your stored procedure
> it will tell you:
>
> SQL> CREATE OR REPLACE PROCEDURE set_role(p_role_name IN VARCHAR2) IS
> 2 cursor_handle INTEGER;
> 3 rows_processed INTEGER;
> 4 BEGIN
> 5 cursor_handle := DBMS_SQL.OPEN_CURSOR;
> 6 DBMS_SQL.PARSE(cursor_handle, 'set role ' ||
> 7 p_role_name, DBMS_SQL.V7);
> 8 rows_processed := DBMS_SQL.EXECUTE(cursor_handle);
> 9 END;
> 10 /
>
> Procedure created.
>
> SQL> exec set_role('DUMMY');
> begin set_role('DUMMY'); end;
>
> *
> ERROR at line 1:
> ORA-06565: cannot execute SET ROLE from within stored procedure
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 348
> ORA-06512: at "SYS.DBMS_SQL", line 134
> ORA-06512: at "OPERATIONS.SET_ROLE", line 8
> ORA-06512: at line 1
>
> Solomon.Yakobson_at_entex.com
>

The following is cut directly from the Oracle 7.1.6 AIX versions dbmsutil.sql file. It may not work but it sure looks like it is supposed to work. Note the words "from, stored procedures". I don't currently need this functionality, so I'm not going to test it but I will share this information with the person that originally needed and he/she may do with it what they with. Do you still believe this does not work. And if so why not.

John Hough

create or replace package dbms_session is

• OVERVIEW --
• This package provides access to SQL "alter session" statements, and
• other session information from, stored procedures.

  ---

• PROCEDURES AND FUNCTIONS -- procedure set_role(role_cmd varchar2);
• Equivalent to SQL "SET ROLE ...".
• Input arguments:
• role_cmd
• This text is appended to "set role " and then executed as SQL.