Description:
We are restricting the logon time for a particular database user so the user can only access the database within defined periods.
If the user attempts to logon during a non-defined period his logon attempt will fail.
Users with ADMINISTER DATABASE TRIGGER system privilege can log into
the database any time.
Steps to be followed :.
Step 1 :
SQL> CREATE OR REPLACE TRIGGER logon_trigger after logon on User1.schema
declare
temp varchar2(50);
v_time varchar2(50);
begin
temp := ‘select to_char(sysdate,”HH24:MI”) from dual’;
EXECUTE IMMEDIATE temp into v_time;
if (to_date(v_time,’HH24:MI’) < to_date(’09:00′,’HH24:MI’)) then
raise_application_error (-20001,’User1 access is denied. You have to logon database in your defined time period(09:00 – 18:00). The current time is ‘||v_time,true);
end if;
if (to_date(v_time,’HH24:MI’) > to_date(’17:59′,’HH24:MI’)) then
raise_application_error (-20001,’User1 access is denied. You have to logon database in your defined time period(09:00 – 18:00). The current time is ‘||v_time,true);
end if;
end;
/
Trigger created.
Drop trigger :
Drop trigger logon_trigger;
