Killing the session through an AFTER LOGON trigger

This is related to your answer on AFTER LOGON trigger. One can not kill its own session! Right. If this is so, then how could you kill a session through an AFTER LOGON trigger? The trigger is executed by the current session. So you cannot kill the same session through AFTER LOGON trigger.

    Requires Free Membership to View

    Login

You are correct, to a point. One cannot kill its own session. But the effect is the same. I created the following trigger: 

CREATE OR REPLACE TRIGGER my_after_logon 
AFTER LOGON ON DATABASE 
DECLARE 
   usernm VARCHAR2(50); 
   sid    NUMBER; 
   serial NUMBER; 
BEGIN 
   -- Get username 
   SELECT user INTO usernm FROM dual; 
   -- Get SID and SERIAL# of this current session 
   SELECT sid,serial# INTO sid,serial 
      FROM v$session WHERE 
audsid=SYS_CONTEXT('userenv','sessionid'); 
   -- Do we drop this user? 
   IF usernm='DROPME' THEN 
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION 
'''||sid||','||serial||''''; 
   END IF; 
END; 
/
If all goes well, then when DROPME signs on, their session will be terminated. To prove the concept, see the following: 
ORA9I SQL> connect dropme/dropme 
ERROR: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-00027: cannot kill current session 
ORA-06512: at line 13 

Warning: You are no longer connected to ORACLE. 
ORA9I SQL> select * from all_users; 
SP2-0640: Not connected
This trigger attempted to kill the current session. We know that this is not allowed. This can be verified by the ORA-00027 error message. But the AFTER LOGON trigger did not complete succesfully. So the logon attempt was denied as can be verified by the SP2-0640 error message.

I personally don't like resorting to this AFTER LOGON trigger that I created. So I modified it slightly. This time, if the DROPME user connects to the database, then an application error is raised, indicating that this user cannot connect. The modified trigger appears below: 

CREATE OR REPLACE TRIGGER my_after_logon 
AFTER LOGON ON DATABASE 
DECLARE 
   usernm VARCHAR2(50); 
   sid    NUMBER; 
   serial NUMBER; 
   e_nologon EXCEPTION; 
BEGIN 
   -- Get username 
   SELECT user INTO usernm FROM dual; 
   -- Get SID and SERIAL# of this current session 
   SELECT sid,serial# INTO sid,serial 
      FROM v$session WHERE 
audsid=SYS_CONTEXT('userenv','sessionid'); 
   -- Do we drop this user? 
   IF usernm='DROPME' THEN 
      RAISE e_nologon; 
   END IF; 
EXCEPTION 
   WHEN e_nologon THEN 
      RAISE_APPLICATION_ERROR(-20001,'This user not 
allowed to login'); 
END; 
/
Now, we attempt to connect as the DROPME user: 
ORA9I SQL> connect dropme/dropme 
ERROR: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-20001: This user not allowed to login 
ORA-06512: at line 18 

Warning: You are no longer connected to ORACLE.
Notice this time, the ORA-20001 error is raised, with the appropriate error message. This may be more of what you are looking for.

But personally, I wouldn't even dream of using a trigger to control who can and cannot logon to the database. That is precisely what the CREATE SESSION privilige is for. Revoke this privilege from the users you don't want to access the database and they will see the following: 

ORA9I SQL> connect dropme/dropme 
ERROR: 
ORA-01045: user DROPME lacks CREATE SESSION privilege; 
logon denied 

Warning: You are no longer connected to ORACLE.
One can also LOCK the account in question.


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top