This discussion is archived
12 Replies Latest reply: Jan 9, 2013 4:51 AM by JohnWatson RSS

Logon Trigger

user10978624 Newbie
Currently Being Moderated
We have requirement where we need to restrict some user from accessing the schema.So for testing i was creating below trigger to stop user 'rajvardhan' from connecting to a schema.But even after i created this trigger user was able to login to the schema? Can you please help what i am missing here,i don't have dba privelleges on this schema


CREATE OR REPLACE TRIGGER logon_audit_trigger1
AFTER LOGON
ON SCHEMA
DECLARE
OSUSER VARCHAR2 (100);
v_error VARCHAR2 (2000);
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'OS_USER') INTO OSUSER FROM DUAL;

IF SYS_CONTEXT ('USERENV', 'OS_USER') = 'rajvardhan'
THEN
raise_application_error (-101, 'ACCESS DENIED');
END IF;
END;
  • 1. Re: Logon Trigger
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    as you have created trigger after logon it will allow user to enter into the scheme.
    you need to create trigger before logon or
    revoke the privileges from the scheme user to access the scheme that you dont want to allow by that user.
  • 2. Re: Logon Trigger
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Why you just check the user privileges , and if you want to prevent user from connecting :
    revoke connect from <USER>
  • 3. Re: Logon Trigger
    JohnWatson Guru
    Currently Being Moderated
    First, I think you need to use an AFTER LOGON ON DATABASE trigger, rather than AFTER LOGON ON SCHEMA?
    Second, if rajvardhan has access to an account that has been granted DBA, the trigger will not prevent the logon. This is hard wired into the DBA role.
  • 4. Re: Logon Trigger
    user10978624 Newbie
    Currently Being Moderated
    rajvardhan dint not have dba privelleges.
  • 5. Re: Logon Trigger
    JohnWatson Guru
    Currently Being Moderated
    Is rajvardhan an OS username or a database username?
  • 6. Re: Logon Trigger
    Bawer Journeyer
    Currently Being Moderated
    user10978624 wrote:
    We have requirement where we need to restrict some user from accessing the schema.So for testing i was creating below trigger to stop user 'rajvardhan' from connecting to a schema.But even after i created this trigger user was able to login to the schema? Can you please help what i am missing here,i don't have dba privelleges on this schema
    you don't block the database user. you will block an OS-User account.
    If user rajvardhan is a database user, it won't work.
    If user rajvardhan is a os user, than it will work but this is not secure (only connections of this os account will be blocked, if user tries with another account, he will get connected).
  • 7. Re: Logon Trigger
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    user10978624 wrote:
    rajvardhan dint not have dba privelleges.
    You said you want to prevent user there is lot of option to do that :
    -Revoke like i post earlier
    -Use denied list in Sqlnet.ora.
  • 8. Re: Logon Trigger
    user10978624 Newbie
    Currently Being Moderated
    rajvardhan is an OS username, can you please tell how to stop the user from connecting to schema?

    AFTER LOGON ON SCHEMA is not working to stop the connection for this os_user
  • 9. Re: Logon Trigger
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Look at the option osama suggested.
  • 10. Re: Logon Trigger
    JohnWatson Guru
    Currently Being Moderated
    I've already told you: you need to use an AFTER LOGON ON DATABASE trigger, not an AFTER LOGON ON SCHEMA trigger.

    But (as I also said) if he knows the Oracle username/password for a DBA account, the trigger will not prevent the logon.


    (by the way, you might want to say "thank you")
  • 11. Re: Logon Trigger
    Bawer Journeyer
    Currently Being Moderated
    Osama_mustafa wrote:
    -Use denied list in Sqlnet.ora.
    How do you deny an os user / db user in sqlnet.ora ?
  • 12. Re: Logon Trigger
    JohnWatson Guru
    Currently Being Moderated
    This topic is driving me nuts. OK, here is the complete solution:
    orcl> connect / as sysdba
    Connected.
    
    Session altered.
    
    orcl>   CREATE OR REPLACE TRIGGER logon_audit_trigger1
      2  AFTER LOGON
      3  ON database
      4  DECLARE
      5  OSUSER VARCHAR2 (100);
      6  v_error VARCHAR2 (2000);
      7  BEGIN
      8  SELECT user INTO OSUSER FROM DUAL;
      9  IF SYS_CONTEXT ('USERENV', 'OS_USER') = 'jwdell\john'
     10  THEN
     11  raise_application_error (-101, 'ACCESS DENIED');
     12  END IF;
     13  END;
     14  /
    
    Trigger created.
    
    orcl> grant dba to scott;
    
    Grant succeeded.
    
    orcl> conn scott/tiger
    Connected.
    
    Session altered.
    
    orcl> conn / as sysdba
    Connected.
    
    Session altered.
    
    orcl> revoke dba from scott;
    
    Revoke succeeded.
    
    orcl> conn scott/tiger
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-21000: error number argument to raise_application_error of -101 is out of range
    ORA-06512: at line 8
    
    
    Warning: You are no longer connected to ORACLE.
    
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points