Oracle Database Discussions

Hello Everyone

We are trying to place in a trigger that depending on the OSUSER logging in (Independent of the DB USER) would set different roles
--Please note that schemanames (except SYSTEM) and procedures were changed before this post. Same as setting DBA Role.

Our code as below:

CREATE OR REPLACE PROCEDURE NKOSEC.ROLEGRANT AUTHID CURRENT_USER
AS
BEGIN
DBMS_SESSION.SET_ROLE('DBA');
END;
/


CREATE OR REPLACE TRIGGER SYSTEM.LOGON_ROLE
AFTER LOGON ON DATABASE
DECLARE p_session_user varchar2(64);
pragma autonomous_transaction;
BEGIN
SELECT UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) INTO p_session_user FROM DUAL;
CASE p_session_user
when 'NKOSEC' THEN BEGIN
NKOSEC.ROLEGRANT;
END;
-- Else
-- nada;
END CASE;
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;
/


SQL> CONN NKOSEC
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at line 15


Could you please advise on how to work around this issue?

Thanks.

System info:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Edited by: user9030445 on 28-mar-2012 13:33