Restrict User access to database within certain limit of time


We need to create a trigger for this purpose.

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL> create user sam identified by sam;

User created.

SQL> grant connect , resource to sam;

Grant succeeded.

SQL> conn sam/sam
Connected.
SQL> exit

[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> CREATE OR REPLACE TRIGGER limit_connection
         AFTER LOGON ON DATABASE
       BEGIN
          IF USER = 'SAM' THEN
             IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 20 AND 22
             THEN
                RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 20hrs to 22hrs');
             END IF;
          END IF;
      END limit_connection;
      /

Trigger created.

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

TO
--
21

SQL> conn sam/sam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user SAM! You can't login between 20hrs to 22hrs
ORA-06512: at line 5
Warning: You are no longer connected to ORACLE.

Done...

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...