RE: Disconnecting session from an on logon trigger

Thanks!  

The kill suggestion came from an Oracle consultant that we had on site. I hadn't actually tested it yet as I was sure there was a cleaner solution :).      

Jay Miller
Sr. Oracle DBA
201.369.8355  

From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Friday, October 12, 2007 5:47 PM
To: Miller, Jay; 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 mark.bobak_at_il.proquest.com <mailto:mark.bobak_at_il.proquest.com> www.proquest.com <http://www.proquest.com> www.csa.com <http://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 Tue Oct 16 2007 - 10:14:49 CDT