| logon trigger creates 2 records [message #469786] |
Thu, 05 August 2010 15:43  |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Hi Experts,
Please help me figure out why my logon trigger always creates
2 records with different timestamp. Also is this the proper way of excepting records to insert?
Script.
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then -- don't want to insert this
if user <> 'SYS' then -- don't want to insert this
insert into AUD_SESSION_TBL
(
OS_USERNAME,
USERNAME,
USERHOST,
LOGON_TIMESTAMP,
LOGOFF_TIMESTAMP,
ACTION_NAME,
COMMENT_TEXT,
RETURN_CODE
)
VALUES
(
sys_context('USERENV','OS_USER'),
USER,
sys_context('USERENV','HOST'),
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
NULL,
'LOGON',
SYS_CONTEXT('USERENV','IP_ADDRESS'),
0
);
end if;
end if;
END;
/
Result when select.
OS_USERNAME LOGON_TIMESTAMP
----------- ----------------
GAN 2010-08-05 14:27:52
GAN 2010-08-05 14:27:55
Thank you,
George
|
|
|
|
|
|
| Re: logon trigger creates 2 records [message #469796 is a reply to message #469786] |
Thu, 05 August 2010 17:01  |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then
if user <> 'SYS' then
insert into AUD_SESSION_TBL
(
OS_USERNAME,
USERNAME,
USERHOST,
LOGON_TIMESTAMP,
LOGOFF_TIMESTAMP,
ACTION_NAME,
COMMENT_TEXT,
RETURN_CODE
)
VALUES
(
sys_context('USERENV','OS_USER'),
USER,
sys_context('USERENV','HOST'),
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
NULL,
'LOGON',
SYS_CONTEXT('USERENV','IP_ADDRESS'),
0
);
end if;
end if;
END;
/
Hi BlackSwan,
Please see reformat of my code.
Thank you
|
|
|
|
|
|
| Re: logon trigger creates 2 records [message #469798 is a reply to message #469797] |
Thu, 05 August 2010 17:13 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The main question here is how are you logging on?
What tool/language are you using?
Because I really can't see any way that trigger can give you 2 records 3 seconds apart unless you actually logged in twice.
|
|
|
|
| Re: logon trigger creates 2 records [message #469816 is a reply to message #469798] |
Thu, 05 August 2010 22:26 |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
|
Our database is 10g R2 & we're using Toad DBA tools. The Trigger was created in my account (Not GAN) w/c is currently on and inserted the records into a local table in the same account named AUD_SESSION_TBL table. For testing, I logon using other account in this case "GAN" and when I viewed the table it appears two records created. Actually, I noticed that the 2 records has also different SESSIONID (during investigation. not part on the code supplied) aside from the 3 seconds different on LOGON_TIMESTAMP.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: logon trigger creates 2 records [message #470720 is a reply to message #469971] |
Tue, 10 August 2010 09:30 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
GAN2009 wrote on Fri, 06 August 2010 18:24Confirmed.
Toad did that intentionally with the option to turn it off.
Thank you!
You don't want to turn this off, otherwise a query in one tab will block all further activity.
Simply don't bother about the audit contents.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
