SET ROLE from within stored procedure 2

[hayo]

Hello,

I am using Oracle 8i on a server, and I want to submit a SET ROLE ... IDENTIFIED BY ... command from a stored procedure.

What happens ?
I start an SQL*Plus session, submit SET ROLE NONE to elimiate all roles. Then I check my session roles:
SELECT * FROM SESSION_ROLES; and I have none (thats ok).
Now I enter the command SET ROLE ... directly within SQL*Plus. When I then check again for my session roles, the new role is active !

Now comes the question:
I want the role to be activated thru a stored procedure. I created the stored procedure with AUTHID CURRENT_USER:

CREATE OR REPLACE PROCEDURE SP_SET_ROLE (...)
AUTHID CURRENT_USER
AS
...

When now executing this stored procedure from SQL*Plus, the trace shows that the SET ROLE command is submitted, but when the procedure ends, the role is NOT active.

Can somebody help me ?

Hayo

Thanks
Hayo

 

[Dagon]

Seems to work OK for me:

SQL> create or replace procedure sp_set_role
2 authid current_user
3 as
4 begin
5 dbms_session.set_role('JUNK identified by fred');
6 end;
7 /

Procedure created.

SQL>
SQL> drop role junk;
drop role junk
*
ERROR at line 1:
ORA-01919: role 'JUNK' does not exist


SQL>
SQL> create role junk identified by fred;

Role created.

SQL>
SQL> set role none;

Role set.

SQL>
SQL> select * from session_roles;

no rows selected

SQL>
SQL> exec sp_set_role

PL/SQL procedure successfully completed.

SQL>
SQL> select * from session_roles;

ROLE
------------------------------
JUNK

 

[sem]

Do you catch others exceptions in your procedure? I suppose the the user executing this procedure is not granted the role he's trying to set, but the procedure just dismisses it. Can you provide full code of your procedure?

Regards, Dima

 

[hayo]

Hi Dima:

Yes, I catch others in my procedure. And, Yes, the role is already granted to the user.

Usually I am using packages, which are called from within the procedure. Here I provide a reduced version of my stored proc:

CREATE OR REPLACE procedure SP_TEST_INVDB2 	
    ( i_Userid IN VARCHAR2 
      ,o_Result OUT INTEGER
      ,o_Msg OUT VARCHAR2 )  
AUTHID CURRENT_USER
IS
    Success     BOOLEAN;
	emsg        VARCHAR2(200);
	Action_Failed   EXCEPTION;
	cmd				varchar2(100);
	cur				integer;
	rc				integer;

BEGIN
	o_Msg := NULL;
	
	/*
	|| prepare the command
	*/
	cmd := 'set role r_cudadmread identified by gold';
	 
    DBMS_OUTPUT.PUT_LINE( cmd );

    cur := dbms_sql.open_cursor;

    dbms_sql.parse( cur, Cmd, dbms_sql.v7 );

    dbms_sql.close_cursor( cur );
	
	o_Result := 0;
	DBMS_OUTPUT.PUT_LINE( 'SP_TEST_INVDB beendet.' );

	RETURN;

	EXCEPTION
			 
		WHEN Action_Failed THEN
            o_result := 1;
            o_Msg := eMsg;
			eMsg := substr('"SP_TEST_INVDB2"'||'; '||eMsg,1,200);
            RAISE_APPLICATION_ERROR( -20001, eMsg );
		WHEN OTHERS THEN
	        eMsg := substr('"SP_TEST_INVDB2"'||'; '||sqlerrm,1,200);
	        RAISE_APPLICATION_ERROR( -20001, eMsg );

END SP_TEST_INVDB2;
/

Thanks
Hayo

 

[sem]

SET ROLE is session control, not DDL statement, so it requires EXECUTING, not only PARSING. Add o_Result:= dbms_sql.execute( cur); to your code or, if you're on 8i+, replace the hole command set by EXECUTE IMMEDIATE

Regards, Dima

 

[hayo]

Great !

Thanks to Dagon for his version !

And thanks to Dima for the useful hint to include the EXECUTE statement. Now everything works fine.


Thanks
Hayo