1 2 Previous Next 22 Replies Latest reply: Jan 12, 2011 11:36 AM by Hannaloulou Go to original post RSS
      • 15. Re: simple trigger to allow users to log on at certain times
        Hannaloulou
        No, the user does not have DBA role.
        Trigger is created, NPQ can still log in, and the same error is in the alert log after log in.


        SQL> CREATE OR REPLACE TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ AFTER LOGON ON DATABASE
        2 BEGIN
        3 if TO_NUMBER (to_char(sysdate,'hh24')) between 16 and 18 AND USER='NPQ' THEN
        4 raise_application_error(-20001,'Not allowed to logon database during this time');
        5 end if;
        6 end;
        7 /


        As user NPQ...
        (TO_CHAR(SYSDATE,'HH24'))
        -------------------------
        16
        1 row selected.
        • 16. Re: simple trigger to allow users to log on at certain times
          JustinCave
          Does the NPQ user have the ADMINISTER DATABASE TRIGGER privilege?

          Justin
          • 17. Re: simple trigger to allow users to log on at certain times
            Hannaloulou
            Why would the npq user need administer trigger. I am setting this up as sys.
            The npq is just a regular user that I don't want logging in at certain times.
            • 18. Re: simple trigger to allow users to log on at certain times
              JustinCave
              NPQ should not have the ADMINISTER DATABASE TRIGGER privilege. If it does have that privilege, however, it would explain the behavior you're seeing.

              Justin
              • 19. Re: simple trigger to allow users to log on at certain times
                Hannaloulou
                It has create session privileged and that is it.
                • 20. Re: simple trigger to allow users to log on at certain times
                  EdStevens
                  >

                  I think Justin is on to your root problem, but let me point up a few things to make your work easier, and certainly to make getting help here more productive.
                  yes, I would love to all by command line...
                  I hear a "but ..." there. As in "I would love to all by command line, but ...."
                  Bere in mind I change to to test...

                  SQL> CREATE OR REPLACE TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ AFTER
                  2 LOGON ON DATABASE
                  3 BEGIN
                  4 if to_char(sysdate,'HH24') between '14' and '16' AND USER='NPQ' THEN
                  As I pointed up in an earlier post, comparing character strings here can produce unintended results .. especially if one of your boundary points is midnight. Go back and review the posts in this thread that deal with converting to a number for comparison. Also note in those messages that Oracle doesn't like the time 2400. Midnight is 0000. Oracle's sense of time runs from 00:00:00 to 23:59:59.
                  5 raise_application_error(-20001,'Not allowed to logon database during this time');
                  6 end if;
                  7 end;
                  8 /

                  Trigger created.

                  SQL> exit
                  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  No reason to exit sqlplus here. Did you know that you can issue a CONNECT statement within a running (and connected) sqlplus session, and it will establish an entire new session? Exiting like this isn't necessarily wrong, it's just a few extra and unnecessary steps.
                  [oracle@kali ~]$ sqlplus

                  SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 10 14:41:02 2011

                  Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

                  Enter user-name: npq
                  Enter password:

                  Connected to:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options


                  Alert log triggered errors:
                  Mon Jan 10 14:41:10 2011
                  Errors in file /u01/app/oracle/admin/NASARPT/udump/nasarpt_ora_17508.trc:
                  ORA-00604: error occurred at recursive SQL level 1
                  ORA-20001: Not allowed to logon database during this time
                  ORA-06512: at line 3


                  /u01/app/oracle/admin/NASARPT/udump/nasarpt_ora_17508.trc
                  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
                  System name: Linux
                  Node name: kali.mtsallstream.com
                  Release: 2.6.18-164.6.1.el5
                  Version: #1 SMP Tue Oct 27 11:28:30 EDT 2009
                  Machine: x86_64
                  Instance name: NASARPT
                  Redo thread mounted by this instance: 1
                  Oracle process number: 31
                  Unix process pid: 17508, image: oracle@kali.mtsallstream.com (TNS V1-V3)

                  *** ACTION NAME:() 2011-01-10 14:41:10.293
                  *** MODULE NAME:(sqlplus@kali.mtsallstream.com (TNS V1-V3)) 2011-01-10 14:41:10.293
                  *** SERVICE NAME:(SYS$USERS) 2011-01-10 14:41:10.293
                  *** SESSION ID:(54.49496) 2011-01-10 14:41:10.293
                  Skipped error 604 during the execution of SYS.LOGON_TIME_RESTRICTION_NPQ
                  *** 2011-01-10 14:41:10.293
                  ksedmp: internal or fatal error
                  ORA-00604: error occurred at recursive SQL level 1
                  ORA-20001: Not allowed to logon database during this time
                  ORA-06512: at line 3


                  I also tried the setting for the to_num but it would not even compile:
                  SQL> CREATE OR REPLACE TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ AFTER LOGON ON DATABASE
                  2 BEGIN
                  3 if to_num(to_char(sysdate,'hh24miss')) between '000001' and '040000' AND USER='NPQ' then
                  4 raise_application_error(-20001,'Not allowed to logon database during this time');
                  5 end if;
                  6 end;
                  7 /

                  Warning: Trigger created with compilation errors.
                  Ok, it compiled with an error. (Yes, the function is to_number, not to_num). At the point you got the "Warning" you could have issued a "show error" and it would have shown you what it didn't like;
                  SQL> CREATE OR REPLACE TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ AFTER LOGON ON DATABASE
                    2  BEGIN
                    3  if to_num(to_char(sysdate,'hh24miss')) between '000001' and '040000' AND USER='NPQ' then
                    4   raise_application_error(-20001,'Not allowed to logon database during this time');
                    5  end if;
                    6  end;
                    7  /
                  
                  Warning: Trigger created with compilation errors.
                  
                  SQL> show error
                  Errors for TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ:
                  
                  LINE/COL ERROR
                  -------- -----------------------------------------------------------------
                  2/1 PL/SQL: Statement ignored
                  2/4 PLS-00201: identifier 'TO_NUM' must be declared
                  SQL> 
                  >
                  SQL>
                  Also, notice how my example above is set off to preserve formatting, making it easier to read? Use the
                   tags on a line by themselves to bracket any posted code.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                  • 21. Re: simple trigger to allow users to log on at certain times
                    Hannaloulou
                    I appreciate all the help. I am all for doing things from the command line, and I prefer it. I try to take something that may be easy the GUI, and convert to command, or do research to do it by command line. Once I have that down I store it away for safe keeping... So there is no buts, just learning something new, as you see, love command line much better! :)

                    SQL> CREATE OR REPLACE TRIGGER "SYS".LOGON_TIME_RESTRICTION_NPQ AFTER
                      2  LOGON ON DATABASE
                      3  BEGIN
                      4  if to_number(to_char(sysdate,'hh24miss')) between '210000' and '230000' AND USER='NPQ' then
                      5  raise_application_error(-20001,'Not allowed to logon database during this time');
                      6  end if;
                      7  end;
                      8  /
                    
                    Trigger created.
                    
                    SQL> show errors
                    No errors.
                    SQL> select trigger_name, status from dba_triggers where owner ='SYS';
                    
                    TRIGGER_NAME                   STATUS
                    ------------------------------ --------
                    AW_DROP_TRG                    ENABLED
                    AURORA$SERVER$STARTUP          DISABLED
                    AURORA$SERVER$SHUTDOWN         DISABLED
                    CDC_ALTER_CTABLE_BEFORE        DISABLED
                    CDC_CREATE_CTABLE_AFTER        DISABLED
                    CDC_CREATE_CTABLE_BEFORE       DISABLED
                    CDC_DROP_CTABLE_BEFORE         DISABLED
                    OLAPISTARTUPTRIGGER            ENABLED
                    OLAPISHUTDOWNTRIGGER           ENABLED
                    LOGON_TIME_RESTRICTION_NPQ     ENABLED
                    
                    10 rows selected.
                    
                    SQL> connect
                    Enter user-name: npq
                    Enter password: 
                    Connected.
                    On logon:
                    Errors in file /u01/app/oracle/admin/NASARPT/udump/nasarpt_ora_1034.trc:
                    ORA-00604: error occurred at recursive SQL level 1
                    ORA-20001: Not allowed to logon database during this time
                    ORA-06512: at line 3

                    ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
                    System name: Linux
                    Node name: kali.mtsallstream.com
                    Release: 2.6.18-164.6.1.el5
                    Version: #1 SMP Tue Oct 27 11:28:30 EDT 2009
                    Machine: x86_64
                    Instance name: NASARPT
                    Redo thread mounted by this instance: 1
                    Oracle process number: 20
                    Unix process pid: 1034, image: oracle@kali.mtsallstream.com (TNS V1-V3)

                    *** ACTION NAME:() 2011-01-10 21:36:14.485
                    *** MODULE NAME:(sqlplus@kali.mtsallstream.com (TNS V1-V3)) 2011-01-10 21:36:14.485
                    *** SERVICE NAME:(SYS$USERS) 2011-01-10 21:36:14.485
                    *** SESSION ID:(87.48885) 2011-01-10 21:36:14.485
                    Skipped error 604 during the execution of SYS.LOGON_TIME_RESTRICTION_NPQ
                    *** 2011-01-10 21:36:14.500
                    ksedmp: internal or fatal error
                    ORA-00604: error occurred at recursive SQL level 1
                    ORA-20001: Not allowed to logon database during this time
                    ORA-06512: at line 3
                    • 22. Re: simple trigger to allow users to log on at certain times
                      Hannaloulou
                      I thank all who have helped. Indeed the account had the trigger privie that I could not find at first.
                      1 2 Previous Next