RE: Disconnecting session from an on logon trigger
- From: <Jay.Miller@xxxxxxxxxxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Oct 2007 11:14:49 -0400
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@xxxxxxxxxxxxxxx]
Sent: Friday, October 12, 2007 5:47 PM
To: Miller, Jay; 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
mark.bobak@xxxxxxxxxxxxxxx <mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com <http://www.proquest.com>
www.csa.com <http://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:
