RE: Disconnecting session from an on logon trigger
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 13 Oct 2007 13:00:13 +0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bobak, Mark
Sent: Saturday, October 13, 2007 05:47
To: Jay.Miller@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> mark.bobak@xxxxxxxxxxxxxxx
<http://www.proquest.com> www.proquest.com
<http://www.csa.com> www.csa.com
ProQuest...Start here.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Friday, October 12, 2007 4:42 PM
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
