Kamran Agayev's Oracle Blog

Oracle Certified Master

User access to database within limits of time

Posted by Kamran Agayev A. on July 28th, 2009

Today, my Turkish friend Emre Baransel asked a question in OTN Forum related to limiting access of a user to a database based on time interval. He want to prevent a specific user to access to a database between 08 and 22.  I’ve created a logon script and showed how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kam identified by kam;
 
User created.
 
SQL> grant connect, resource to kam;
 
Grant succeeded.
 
SQL> conn kam/kam
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAM' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kam/kam
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kam/kam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAM! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>

10 Responses to “User access to database within limits of time”

  1. Emre Baransel Says:

    Hi Kamran;
    Thanks for the reply on the forum and also for this post… See you on other topics :)

  2. Kamran Agayev A. Says:

    Hi Emre! You’re welcome. Thanks for comment. See you :)

  3. Mohamed Azar Says:

    SQL> conn kam/kam@orcl;
    ERROR:
    ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation

    Warning: You are no longer connected to ORACLE.
    SQL>

    May i know what is the error.

  4. Kamran Agayev A. Says:

    The trigger you’ve created contains error and should be corrected and recompiled
    After creation of the trigger type
    show error

    and see what error you get

  5. Mohamed Azar Says:

    Thanks for your reply

    i go to check this error..

    Regards

    azar

  6. Mohamed Azar Says:

    sql>create user kam identified by kam;
    sql>grant connect, resource to kam;

    SQL> create or replace trigger limit_connection
    2 after logon on database
    3 begin
    4 if user =’KAM’ THEN
    5 if to_number(TO_CHAR(SYSDATE, ‘hh24’)) BETWEEN 6 AND 10
    6 THEN
    7 RAISE_APPLICATION_ERROR(-20998,’DEAR USER ‘||USER||’! YOU CAN’T LOGIN BETWE
    EN 6 AND 10′);
    8 END IF
    9 END IF;
    10 END limit_connection;
    11 /

    Warning: Trigger created with compilation errors.

    After i connect the above user the folloe error occured.

    SQL> conn kam/kam@orcl;
    ERROR:
    ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation

    Warning: You are no longer connected to ORACLE.
    SQL>

    and then

    I created another user ‘aza’ but i am not written trigger for aza user though i cannot connect aza user.. the above error occured

    sql>create user aza identified by aza;
    sql>grant connect,resource to aza;
    sql>conn aza/aza@orcl;
    ERROR:
    ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation

    Warning: You are no longer connected to ORACLE.

    May i know , What i did mistaken?

  7. Mohamed Azar Says:

    the trigger working..

    Thanks

    S.Azar.

  8. Kamran Agayev A. Says:

    You’re welcome Azar!

  9. George Says:

    hello im trying to make the trigger but when i’m trying to connect to the >sqlplus “/as sysdba” i’m getting this error message on the startup

    ERROR:
    ORA-01031: insufficient privileges

    also i’m having this error message when im trying this command create user george identified by george;

    ERROR at line 1:
    ORA-01031: insufficient privileges

    how i can solve it? im working on mac

  10. Kamran Agayev A. Says:

    Make sure you’ve logged in to OS with an oracle user

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>