MULTIPLE USERS ACCESS TO DATABASE WITHIN LIMITS OF TIME

SQL> create user shahid identified by shahid;

User created.

SQL> grant connect, resource to shahid;

Grant succeeded.

SQL> conn shahid/shahid
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 sys / as sysdba
Password: xxxxxxxx
Connected.


 CREATE OR REPLACE TRIGGER limit_connection
  AFTER LOGON ON DATABASE
  BEGIN
     IF USER = 'SHAHID' OR USER = 'SCOTT' THEN
       IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
       THEN
          RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
 END IF;
 END IF;
END limit_connection;
 /


Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
23

SQL> conn shahid/shahid
Connected.
SQL> select to_char(sysdate,'hh24') from dual;

TO
--
18

SQL> conn shahid/shahid

ERROR:ORA-00604: error occurred at recursive SQL level 1ORA-20998: Dear user SHAHID! You can't login between 08 and 22ORA-06512: at line 5 Warning: You are no longer connected to ORACLE.SQL>



DISABLE TRIGGER


SQL>  ALTER TRIGGER limit_connection DISABLE;


ENABLE TRIGGER


SQL>ALTER TRIGGER limit_connection ENABLE;