Skip to Main Content
  • Questions
  • ORA-06565: SET ROLE in LOGON TRIGGER

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 27, 2017 - 10:32 pm UTC

Last updated: December 07, 2023 - 6:24 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

We have a trigger

CREATE OR REPLACE TRIGGER TRG_AFTER_LOGON
AFTER LOGON ON DATABASE
BEGIN
LogonCheck;
END;
/

with

CREATE OR REPLACE PROCEDURE LogonCheck
AUTHID current_user
AS
BEGIN
EXECUTE IMMEDIATE 'SET ROLE NONE';
END;
/

This always results in error ORA-06565, if LogonCheck is called out of the trigger.
LogonCheck succeeds, if called directly.
Obviously the trigger acts as DR unit, which is not allowed in the call stack, if SET ROLE is invoked.

Is there any way to call SET ROLE NONE from out of a trigger?

------------
version
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

and Connor said...

SQL> exec dbms_session.set_role('NONE')

PL/SQL procedure successfully completed.



but take a look here at using it from a trigger.

https://asktom.oracle.com/pls/asktom/asktom.search?tag=procedures-roles-and-grants


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_SESSION.set_role is not allowed to be called from a trigger

A reader, December 07, 2023 - 2:03 am UTC

Hi,

I was trying to set some roles dynamically using a logon trigger but it seems it does not work or rather is not allowed.

"AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled (Doc ID 106140.1)"

Hope it helps others who are attempting to do the same  :o)

Cheers


Connor McDonald
December 07, 2023 - 6:24 am UTC

Good input. Thanks for contributing.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.