Logon trigger

We have a requirement to create logon trigger.Purpose of the trigger is to redirect all the read only connections to standby database using read only service.Logon trigger is expected to kick out any read only users trying to access Primary database.Below is the trigger code which we have

CREATE OR REPLACE TRIGGER AUTOSYS_INSTALL.GLOBAL_LOGON_TRG
AFTER LOGON
ON DATABASE DISABLE
DECLARE
p_session_user varchar2(64);
p_database_role VARCHAR2(16);
BEGIN
SELECT UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) INTO p_session_user FROM DUAL;
SELECT UPPER(SYS_CONTEXT('USERENV', 'DATABASE_ROLE')) INTO p_database_role FROM DUAL;

DBMS_SESSION.SET_IDENTIFIER(p_session_user || '-' || p_database_role);

IF ((p_session_user NOT IN ('xyz','abc')) AND (p_database_role='PRIMARY')) THEN
DBMS_SESSION.SET_IDENTIFIER('about to raise app_error..');
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;

END;
/


The Problem is every time i want to add or restrict a user the trigger will have to be changed...Is there a way for me to use a table which can maintain metadata of what users are authenticated to login?

Are the read only users assigned a special role? If so just check to see if they have that role.
You can also check to see if a user has a role that grants it write privileges. Depending on
how users get rights either way could work.

Hopefully you don't manually assign select privileges to each user. :rolleyes:

There is no sys context which would allow me to check whether user has been granted a role or not..Any thoughts?

Quote:

---

Originally Posted by mohith2009

There is no sys context which would allow me to check whether user has been granted a role or not..Any thoughts?

---

You need to query for the role based on the user that is logged in. Here is an example. You can encapsulate this in a
function that will return true or false depending on whether or not the current user can login to the current database.
I used XDBADMIN for an example you would use the read only role that read only users get.

Code:

---

SQL > select GRANTEE from DBA_ROLE_PRIVS where GRANTEE='SYS' and GRANTED_ROLE='XDBADMIN';

GRANTEE
------------------------------------------------------------------------------------------
SYS

Elapsed: 00:00:00.00

---