RE: Disconnecting session from an on logon trigger

Yep, go with Mark's suggestion.  

Make sure that your users don't have ADMINISTER DATABASE TRIGGER priv (or any role which contains it), otherwise they can ignore the exception generated by logon trigger and still log on.  

--

Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/>  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Saturday, October 13, 2007 05:47
To: Jay.Miller_at_tdameritrade.com; oracle-l_at_freelists.org Subject: RE: Disconnecting session from an on logon trigger

Jay,  

Try something like:

  1 create or replace trigger you_may_not_login

  2 after logon on database

  3 begin

  4 if sys_context('USERENV','SESSION_USER')='TESTME' then

  5 raise_application_error(-20001,'Denied! You are not allowed to logon the database');

  6 end if;

  7* end;

SQL> /   Trigger created.  

SQL> conn testme/testme

ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Denied! You are not allowed to logon the database

ORA-06512: at line 3    

BTW, have you actually tried killing your own session? Can't be done.....

SQL> conn mbobak

Enter password:

Connected.

SQL> select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);  

       SID SERIAL#

• ----------

       539 63485  

SQL> alter system kill session '539,63485';

alter system kill session '539,63485'

*

ERROR at line 1:

ORA-00027: cannot kill current session    

-Mark
 

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059

 <mailto:mark.bobak_at_il.proquest.com> mark.bobak_at_il.proquest.com
 <http://www.proquest.com> www.proquest.com
 <http://www.csa.com> www.csa.com

ProQuest...Start here.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jay.Miller_at_tdameritrade.com Sent: Friday, October 12, 2007 4:42 PM
To: oracle-l_at_freelists.org
Subject: Disconnecting session from an on logon trigger  

I'd like to create an on logon trigger that will prevent the user from connecting in certain circumstances. I have one way of doing it which would be to issue

execute immediate ' alter system kill session 'session_id', 'serial#''  

but I can't help but wonder if there's a cleaner way to do it from within oracle instead of killing the session?      

Oracle 9.2.0.7      

Thanks,
Jay Miller      

--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 13 2007 - 00:00:13 CDT