Oracle special system-level triggers, which can be created on system events that are supported on DATABASE and SCHEMA. These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers. The user logon/logoff triggers will accurately tell you the time of the user logon and logoff.
Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1007895
Here we are trying to design a single table for auditing user logon and logoff activities.
create table myuser_audit
(
sessionid number,
username varchar2(30),
osuser varchar2(30),
process varchar2(24),
machine varchar2(64),
port number,
terminal varchar2(16),
module varchar2(64),
action varchar2(64),
client_info varchar2(64),
program varchar2(64),
host varchar2(30),
ip_address varchar2(64),
logon_date date,
logoff_date date
);
Following is the login trigger, which will create a row in myuser_audit table when a user gets login on database, it will record sid, username, host, ip_address and logon_date
create trigger user_logon_audit_trg
after logon on database
begin
insert into myuser_audit
(
sessionid,
username,
host,
osuser,
ip_address,
logon_date
)
values
(
sys_context('userenv','sessionid'),
sys_context('userenv', 'session_user'),
sys_context('userenv', 'host'),
sys_context('userenv', 'os_user'),
sys_context('userenv', 'ip_address'),
sysdate
);
end;
/
After a login trigger, we need a trigger on logoff event, which will simply update the information from v$session to our auditing table by using sessionid when user is getting logged off.
create trigger logoff_audit_trigger
before logoff on database
begin
update
myuser_audit
set
(
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
logoff_date
)
=
(
select
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
sysdate
from
v$session
where
audsid = sys_context('userenv','sessionid')
)
where
sessionid = sys_context('userenv','sessionid')
and logoff_date is null;
end;
/
Now a various kind of reports/informaction about user login and logoff activities can be retrived from myuser_audit table.
Related Post:
ORA-00604: error occurred at recursive SQL level string
http://nimishgarg.blogspot.in/2013/02/ora-00604-error-occurred-at-recursive.html
Hey guys.....
ReplyDeleteEach time i am executing any of the trigger in my database it is giving me the same error either i am executing the DDL TRIGGER or LOGON TRIGGER....
The code is written bellow..
-------------------------------
SQL> create table DDL_LOG
2 (username varchar2(20),
3 change_date date,
4 object_type VARCHAR2(20),
5 object_owner VARCHAR2(30),
6 database varchar2(20));
Table created.
SQL> CREATE OR REPLACE TRIGGER
2 DDL_LOG_TRIG
3 AFTER DDL ON DATABASE
4 BEGIN
5 INSERT INTO DDL_LOG
6 (username ),
7 change_date,
8 object_type,
9 object_owner,
10 database
11 )
12 VALUES
13 (ora_login_user,
14 sysdate,
15 ora-dict_obj_type,
16 ora_dict_obj_owner,
17 ora_daabase_name)
18 END;
19 /
Warning: Trigger created with compilation errors.
SQL> conn arvind/arvind
Connected.
SQL> create table xyz
2 (name varchar2(10));
create table xyz
*
ERROR at line 1:
ORA-04098: trigger 'SYS.DDL_LOG_TRIG' is invalid and failed re-validation
SQL> conn / as sysdba
Connected.
SQL> ALTER TRIGGER DDL_LOG_TRIG compile;
Warning: Trigger altered with compilation errors.
SQL> show errors trigger DDL_LOG-TRIG;
No errors.
SQL>
--------------------------------------------
Please help...
Your trigger has compilation errors.
Deleteuse following
CREATE OR REPLACE TRIGGER DDL_LOG_TRIG AFTER DDL ON DATABASE
BEGIN
INSERT INTO DDL_LOG (username ,change_date,object_type,object_owner,database)
VALUES
(ora_login_user, sysdate, ora_dict_obj_type, ora_dict_obj_owner, ora_database_name);
END;
/
Hey....
DeleteThanks a lot Nimish for correcting my silly mistakes...
Hey guys Can anyone help me in fine grained auditing...
ReplyDeleteI am trying to create this policy but getting error.
-----------------------------------------------------
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY (
3 object_schema => 'SCOTT',
4
5 object_name => 'FGA_TEST',
6
7 policy_name => 'FGA_TEST_POLICY',
8
9 audit_condition => NULL,
10
11 audit_column => 'ENAME,SAL',
12
13 handler_schema => 'FGA_HANDLER',
14
15 handler_module => 'sp_audit',
16
17 enable => true,
18
19 statement_types => `SELECT,INSERT,UPDATE,DELETE' );
20
21 end;
22 /
ERROR:
ORA-01756: quoted string not properly terminated
check your statement 19
Delete- statement_types => `SELECT,INSERT,UPDATE,DELETE'
starting quote is not proper, convert it to following
- statement_types => 'SELECT,INSERT,UPDATE,DELETE'
also, plz post the problem related to the post plz. this is not a forum its a blog.
ok....Thank You.
ReplyDeleteNimish Garg: "trigger" keyword missing in create trigger (for logoff trigger)
ReplyDeletethanx :) dont really know how it got missed
DeleteError(5,1): PL/SQL: SQL Statement ignored
ReplyDeleteError(31,7): PL/SQL: ORA-00942: table or view does not exist
Row# NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
ReplyDelete1 LOGOFF_AUDIT_TRIGGER TRIGGER 1 28 7 PL/SQL: ORA-00942: table or view does not exist ERROR 0
2 LOGOFF_AUDIT_TRIGGER TRIGGER 2 2 1 PL/SQL: SQL Statement ignored ERROR 0
did u create myuser_audit table ?
DeleteVery shortly this site will be famous amid all blogging
ReplyDeleteand site-building people, due to it's good articles
I don't unremarkablyy comment bbut I gotta state thank you for thhe post on this one :D.
ReplyDeleteSir,
ReplyDeleteKindly share the blog to use of pragma and it's types-:
Pragma serially reusable
Pragma inline
Pragma restrict Refrence
Pragma autonomous transaction
Pragma exception init
Give with e.g
It's awesome desigtned for me to have a web site, which is
ReplyDeletegood designed for my knowledge. thanks admin