Prevent users from login into a database within defined period.

Description:

It shows how to set up the database security so that users cannot log into the database outside defined time-window. Here we are using Event Triggers.

Steps to be followed :

Step 1 : Create a user (or existing user) and check, whether is able to connect.

SQL> connect username/*****
Connected.

Now we able to connect, afterthat login as sys user and create a trigger .

Step 2 : Create an event trigger that allow the user to login the database only on Monday to Friday included 9 AM to 6 PM.

Except DBA Privilege user, remaining users connection occurs only within the time frame.

SQL> create or replace trigger prevent_logon_database_trigger after logon on database
begin
if (to_char(sysdate,'D') not between '2' and '6') ------(2 for monday and 6 for friday)
or (to_char(sysdate, 'HH24')  between '09' and '18') 

-----or (to_char(sysdate, 'HH24') not between '09' and '18') 

then RAISE_APPLICATION_ERROR(-20001, 'Please login on your given date');
end if;
end;
/

Trigger created.

OR

SQL>create or replace trigger prevent_logon_database_trigger after logon on database 
declare
current_hour pls_integer;
restricted_user boolean;
restricted_time boolean;
begin
restricted_user := user = 'USERNAME';
current_hour    := extract(hour from systimestamp);
restricted_time := (current_hour < 18 or current_hour >= 9);
if restricted_user and restricted_time 

then raise_application_error(-20001, 'You can''t come in!');
end if;
end ;
/

Trigger created.

Step 3 : Now check whether you are able to connect to the database.

Example : It’s Saturday normal user cannot be able to log into the database. We have created trigger for all the users to login into the database on Monday and Friday included 9AM to 6PM. So the error throws .

SQL> connect username/*****

ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to log into database now.
ORA-06512: at line 3
Warning: You are no longer connected to ORACLE.

Step 4 : Disable trigger.

SQL>Drop trigger prevent_logon_database_trigger;

Trigger dropped.