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>