12 Replies Latest reply: Jan 9, 2013 6:51 AM by JohnWatson RSS

    Logon Trigger

    user10978624
      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
          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
            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
              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
                rajvardhan dint not have dba privelleges.
                • 5. Re: Logon Trigger
                  JohnWatson
                  Is rajvardhan an OS username or a database username?
                  • 6. Re: Logon Trigger
                    Bawer
                    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
                      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
                        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
                          Hi,

                          Look at the option osama suggested.
                          • 10. Re: Logon Trigger
                            JohnWatson
                            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
                              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
                                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.