Home » RDBMS Server » Server Utilities » test trigger
test trigger [message #248765] Sun, 01 July 2007 14:25 Go to next message
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 #248766 is a reply to message #248765] Sun, 01 July 2007 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Then, with which user it does not raise an error?

Regards
Michel
Re: test trigger [message #248767 is a reply to message #248766] Sun, 01 July 2007 16:54 Go to previous messageGo to next message
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 #248782 is a reply to message #248767] Sun, 01 July 2007 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which ORACLE user?

Regards
Michel
Re: test trigger [message #248855 is a reply to message #248782] Mon, 02 July 2007 08:22 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

with system oracle user...


Thanks in advance
Re: test trigger [message #248867 is a reply to message #248855] Mon, 02 July 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: test trigger [message #248878 is a reply to message #248867] Mon, 02 July 2007 09:41 Go to previous message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Thanks and rgd
Previous Topic: sql loader script
Next Topic: "create" and "export schema" merged
Goto Forum:
  


Current Time: Thu Dec 08 04:26:59 CST 2016

Total time taken to generate the page: 0.06493 seconds