User access to database within limits of time
Posted by Kamran Agayev A. on July 28th, 2009
Today, my Turkish friend Emre Baransel asked a question in OTN Forum related to limiting access of a user to a database based on time interval. He want to prevent a specific user to access to a database between 08 and 22. I’ve created a logon script and showed how it’s working:
C:\Documents and Settings\Administrator>sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create user kam identified by kam; User created. SQL> grant connect, resource to kam; Grant succeeded. SQL> conn kam/kam 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 / as sysdba Connected. SQL> CREATE OR REPLACE TRIGGER limit_connection 2 AFTER LOGON ON DATABASE 3 BEGIN 4 IF USER = 'KAM' THEN 5 IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22 6 THEN 7 RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22'); 8 END IF; 9 END IF; 10 END limit_connection; 11 / Trigger created. SQL> select to_char(sysdate,'hh24') from dual; TO -- 23 SQL> conn kam/kam Connected. SQL> select to_char(sysdate,'hh24') from dual; TO -- 18 SQL> conn kam/kam ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-20998: Dear user KAM! You can't login between 08 and 22 ORA-06512: at line 5 Warning: You are no longer connected to ORACLE. SQL>
July 28th, 2009 at 6:57 pm
Hi Kamran;
Thanks for the reply on the forum and also for this post… See you on other topics
July 29th, 2009 at 8:48 am
Hi Emre! You’re welcome. Thanks for comment. See you
October 6th, 2009 at 6:32 pm
SQL> conn kam/kam@orcl;
ERROR:
ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation
Warning: You are no longer connected to ORACLE.
SQL>
May i know what is the error.
October 6th, 2009 at 6:36 pm
The trigger you’ve created contains error and should be corrected and recompiled
After creation of the trigger type
show errorand see what error you get
October 6th, 2009 at 6:40 pm
Thanks for your reply
i go to check this error..
Regards
azar
October 6th, 2009 at 6:54 pm
sql>create user kam identified by kam;
sql>grant connect, resource to kam;
SQL> create or replace trigger limit_connection
2 after logon on database
3 begin
4 if user =’KAM’ THEN
5 if to_number(TO_CHAR(SYSDATE, ‘hh24’)) BETWEEN 6 AND 10
6 THEN
7 RAISE_APPLICATION_ERROR(-20998,’DEAR USER ‘||USER||’! YOU CAN’T LOGIN BETWE
EN 6 AND 10′);
8 END IF
9 END IF;
10 END limit_connection;
11 /
Warning: Trigger created with compilation errors.
After i connect the above user the folloe error occured.
SQL> conn kam/kam@orcl;
ERROR:
ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation
Warning: You are no longer connected to ORACLE.
SQL>
and then
I created another user ‘aza’ but i am not written trigger for aza user though i cannot connect aza user.. the above error occured
sql>create user aza identified by aza;
sql>grant connect,resource to aza;
sql>conn aza/aza@orcl;
ERROR:
ORA-04098: trigger ‘SYS.LIMIT_CONNECTION’ is invalid and failed re-validation
Warning: You are no longer connected to ORACLE.
May i know , What i did mistaken?
October 7th, 2009 at 4:33 pm
the trigger working..
Thanks
S.Azar.
October 7th, 2009 at 4:34 pm
You’re welcome Azar!
March 26th, 2012 at 12:23 pm
hello im trying to make the trigger but when i’m trying to connect to the >sqlplus “/as sysdba” i’m getting this error message on the startup
ERROR:
ORA-01031: insufficient privileges
also i’m having this error message when im trying this command create user george identified by george;
ERROR at line 1:
ORA-01031: insufficient privileges
how i can solve it? im working on mac
March 27th, 2012 at 9:42 am
Make sure you’ve logged in to OS with an oracle user