Q
Problem solve Get help with specific problems with your technologies, process and projects.

Creating an AFTER LOGON trigger

My question is how to create an AFTER LOGON trigger based on your answer to a previous question about restricting logon times.

To create an AFTER LOGON trigger, you can use code similar to the following:

CREATE OR REPLACE TRIGGER my_after_logon_trig
AFTER LOGON
   currtime DATE;
   sid V$SESSION.SID%TYPE;
   serial V$SESSION.SERIAL#%TYPE;
   killcmd VARCHAR2(100);
BEGIN
   -- Get the current time
   SELECT sysdate INTO currtime FROM dual;
   -- Is the current time outside of 09:00 to 17:00?
   IF (TO_CHAR(currtime,'HH24MI') < '0900' ||
       TO_CHAR(currtime,'HH24MI') > '1700') THEN
      -- Get user's SID and SERIAL# and kill their
session
      SELECT sid,serial# INTO sid,serial 
      FROM v$session WHERE
audsid=USERENV('sessionid');
      killcmd:='ALTER SYSTEM KILL SESSION ''';
      killcmd:=killcmd||sid||','||serial||''';
      EXECUTE IMMEDIATE killcmd;
   END IF;
END;
/
This should give you a start. You can modify the above trigger definition if it doesn't suit your exact needs.

For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close