Re: Roles

We encountered this situation on my current project. We got around it by using a 3 step process:
1) Client requests its roles and provides a password to the server.
2)Server checked the password and if it was correct, provided the roles and their passwords to the client. 3) The client then called dbms_session.set_role command through an anonymous PL/SQL block.

It would be better, if Oracle removed the restriction and allowed the server to perform the dbms_session.set_role for the client.

Does anyone know why Oracle explicity prevents that session control statement from being used by a stored procedure?

Dave Podnar
dave.podnar_at_daytonoh.ncr.com

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
>
  Received on Fri Jan 31 1997 - 00:00:00 CET