| test trigger [message #248765] |
Sun, 01 July 2007 14:25  |
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
|
Member |
|
|
It's not raising the error. if i tried to login with the different operating user id.
[CREATE OR REPLACE TRIGGER LogIn_Audit_Trig
AFTER LogOn ON DATABASE
DECLARE
v_cnt NUMBER;
BEGIN
SELECT COUNT(* )
INTO v_cnt
FROM stg_Test_Tab a
WHERE a.UserId = Ora_LogIn_User
AND a.osUser_Id IN (SELECT osUser
FROM v$Session
WHERE audsId = Userenv('sessionid'));
IF v_cnt > 0 THEN
INSERT INTO lc_Monitor.LogIn_Audit
(User_Id,
User_sId,
User_Serial,
LogIn_dte,
osUserId,
MachineName,
Program)
SELECT USER,
sId,
Serial#,
osUser,
Machine,
Program,
audsId
FROM v$Session
WHERE audsId = Userenv('sessionid')
AND USER NOT IN ('DBSNMP',
'SYS',
'PERFSTAT');
ELSE
Raise_Application_Error(- 20501,'Error: login using '
||Ora_LogIn_User
||' prohibited');
END IF;
END;
[Updated on: Sun, 01 July 2007 16:53] Report message to a moderator |
|
|
|
|
|
| Re: test trigger [message #248767 is a reply to message #248766] |
Sun, 01 July 2007 16:54  |
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
|
Member |
|
|
if i login into the database with the different osuserid, although the osuserid does not exists in the stg_test_tab, but it still logged me in.
oracle version 9.2.6.0
Thanks in advance
|
|
|
|
|
|
|
|
| Re: test trigger [message #248867 is a reply to message #248855] |
Mon, 02 July 2007 09:06 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SYSTEM has DBA role which has ADMINISTER DATABASE TRIGGER privilege which ignores exceptions in logon trigger.
Use a normal user to make your test.
Regards
Michel
[Updated on: Mon, 02 July 2007 10:32] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
