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 and check, whether is able to connect.
SQL> connect user1/*****
Connected.
We can able to connect, now login as sys user and create a trigger .
Step 2 :
Create an event trigger that allow the user to log in the database only on Monday and Friday included 9AM to 6PM.
Except DBA Privilege user, remaining users connection occurs only within the time frame.
SQL> create or replace trigger logon_database_trigger after logon on database
begin
if (to_char(sysdate,’D’) not between ‘2’ and ‘6’)
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.
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 user1/*****
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.
SQL>
Disable trigger :
Drop trigger logon_database_trigger;
