Home » RDBMS Server » Server Administration » sysman audit question
sysman audit question [message #229063] |
Thu, 05 April 2007 04:26 |
kiel44
Messages: 10 Registered: September 2006
|
Junior Member |
|
|
hi all,
i have one question.
I used this trigger to audit users logon's.
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
But every minute this script catch SYSMAN account logon. Is that normal or somthing was wrongly configurated?
USER_ID SESSION_ID HOST LAST_PROGRAM LAST_ACTION LAST_MODULE LOGON_DAY LOGON_TIME LOGOFF_DAY LOGOFF_TIME ELAPSED_MINUTES
SYSMAN 0 SERWER 05.04.07 11:56:30 11:56:30
SYSMAN 0 SERWER 05.04.07 11:57:35 11:57:35
SYSMAN 0 SERWER 05.04.07 11:58:40 11:58:40
|
|
|
|
|
|
|
Re: sysman audit question [message #229094 is a reply to message #229080] |
Thu, 05 April 2007 06:05 |
kiel44
Messages: 10 Registered: September 2006
|
Junior Member |
|
|
True i know that but easier for me is to check it in table than in file. This script doesn't record user SYS.
I have another question with select * from dba_users; i can see all user and time when they were created. But without enabled audit can i somewhere see who created them?
|
|
|
|
|
Re: sysman audit question [message #229110 is a reply to message #229096] |
Thu, 05 April 2007 07:23 |
kiel44
Messages: 10 Registered: September 2006
|
Junior Member |
|
|
Thx for info.
But i have another problem.
I found this script on many oracle portals but i have problem because when i use it bdump generate me errors.
There were no info about this error.
ORA-00604: error occured at recursive SQL level 1
ORA-01427: single-row subquered returns more than one row
....
create table
stats$user_log
(
user_id varchar2(30),
session_id number(,
host varchar2(30),
osuser varchar2(30),
type varchar2(10),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(
)
;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update os user
-- ***************************************************
update
stats$user_log
set
osuser = (select osuser from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update session type
-- ***************************************************
update
stats$user_log
set
typ = (select type from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/
|
|
|
Re: sysman audit question [message #229142 is a reply to message #229110] |
Thu, 05 April 2007 09:18 |
|
Michel Cadot
Messages: 68686 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
"select <something> from v$session where sys_context('USERENV','SESSIONID') = audsid" can return several rows, above all with SYS user as audsid is always 0 for SYS.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Sep 20 01:48:18 CDT 2024
|