Logon trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Logon trigger

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Posts
    15

    Question 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?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

  3. #3
    Join Date
    Oct 2008
    Posts
    15
    There is no sys context which would allow me to check whether user has been granted a role or not..Any thoughts?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Quote Originally Posted by mohith2009 View Post
    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
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width