Hi,
I need to write a trigger to audit logon and logoff activities of specific users 'SYS' and 'SYSTEM'.
I have one trigger but its not inserting records into the table mentioned in the trigger.
Please, help me to fix the issue.Your help will be appreciated,thanks in advance.
CREATE TABLE newlogontab1
(
user_id varchar2(30),
sess_id number(10),
logon_time date,
client_ip varchar2(20),
client_name varchar2(150),
client_machine varchar2(150),
v_program varchar2(100)
);
create or replace trigger newlogontrig
AFTER LOGON on database
begin
insert into newlogontab1
(
user_id,sess_id,logon_time,client_ip,
client_name,client_machine,v_program
)
select username,sid,sysdate,sys_context('USERENV','IP_ADDRESS'),
osuser,machine,program
from v$session
where username in('SYS','SYSTEM');
end;
Presuambly you want audit the login of the current session. Which means you need to extend the where clause to include this filter!
Do this and it works fine for me:
conn chris/chris
CREATE TABLE newlogontab1
(
user_id varchar2(30),
sess_id number(10),
logon_time date,
client_ip varchar2(20),
client_name varchar2(150),
client_machine varchar2(150),
v_program varchar2(100)
);
create or replace trigger newlogontrig
AFTER LOGON on database
begin
insert into newlogontab1
(
user_id,sess_id,logon_time,client_ip,
client_name,client_machine,v_program
)
select username,sid,sysdate,sys_context('USERENV','IP_ADDRESS'),
osuser,machine,program
from v$session
where username in ('SYS','SYSTEM')
and sid = sys_context('USERENV', 'SID');
end;
/
conn / as sysdba
select user_id, sess_id, logon_time, client_name
from chris.newlogontab1;
USER_ID SESS_ID LOGON_TIME CLIENT_NAME
SYS 51 29-JAN-2018 08:48:56 csaxon But remember: sys has privileges to do anything in the database! Such as disabling this trigger. Or deleting the log...
Anyone who's up to no good probably knows this, so will be able to cover their tracks. A better approach is to log to a file system owned by root. You can do this by setting audit_sys_operations to true.
Uwe Hesse discusses further at:
https://uhesse.com/2010/02/02/how-to-audit-sys-into-an-os-file-owned-by-root/