贴个触发器,作用:记录数据库用户登陆信息
SQL> create table log$information(
2 logon_time timestamp,
3 host_name varchar2(100),
4 username varchar2(40),
5 schemaname varchar2(40),
6 sessionuser varchar2(40),
7 ip_address varchar2(100)
8 );
Table created.
SQL> CREATE OR REPLACE TRIGGER tr_login_record
2 AFTER LOGON
3 ON DATABASE
4 BEGIN
5 INSERT INTO log$information
6 SELECT SYSTIMESTAMP,
7 SYS_CONTEXT ('USERENV', 'HOST'),
8 SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
9 SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
10 SYS_CONTEXT ('USERENV', 'SESSION_USER'),
11 SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
12 FROM DUAL;
13 COMMIT;
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 NULL;
18 END;
19 /
Trigger created.
SQL> select * from log$information;
LOGON_TIME HOST_NAME USERNAME SCHEMANAME SESSIONUSE IP_ADDRESS
----------------------------------- ------------------------------ ---------- -------------------- ---------- --------------------
10-NOV-12 12.49.02.699244 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.49.02.995042 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.49.14.847775 AM OELx64 SYS SYS SYS
10-NOV-12 12.49.39.985996 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.50.15.092293 AM OELx64 SYS SYS SYS
10-NOV-12 12.51.15.348530 AM OELx64 SYS SYS SYS
10-NOV-12 12.52.15.582945 AM OELx64 SYS SYS SYS
10-NOV-12 12.53.15.847327 AM OELx64 SYS SYS SYS
10-NOV-12 12.53.19.688558 AM WORKGROUP\YALLONKING-PC SYS SYS TEST 192.168.137.1
10-NOV-12 12.53.19.937570 AM WORKGROUP\YALLONKING-PC SYS SYS TEST 192.168.137.1
10 rows selected.
SQL> create table log$information(
2 logon_time timestamp,
3 host_name varchar2(100),
4 username varchar2(40),
5 schemaname varchar2(40),
6 sessionuser varchar2(40),
7 ip_address varchar2(100)
8 );
Table created.
SQL> CREATE OR REPLACE TRIGGER tr_login_record
2 AFTER LOGON
3 ON DATABASE
4 BEGIN
5 INSERT INTO log$information
6 SELECT SYSTIMESTAMP,
7 SYS_CONTEXT ('USERENV', 'HOST'),
8 SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
9 SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
10 SYS_CONTEXT ('USERENV', 'SESSION_USER'),
11 SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
12 FROM DUAL;
13 COMMIT;
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 NULL;
18 END;
19 /
Trigger created.
SQL> select * from log$information;
LOGON_TIME HOST_NAME USERNAME SCHEMANAME SESSIONUSE IP_ADDRESS
----------------------------------- ------------------------------ ---------- -------------------- ---------- --------------------
10-NOV-12 12.49.02.699244 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.49.02.995042 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.49.14.847775 AM OELx64 SYS SYS SYS
10-NOV-12 12.49.39.985996 AM WORKGROUP\YALLONKING-PC SYS SYS SYSTEM 192.168.137.1
10-NOV-12 12.50.15.092293 AM OELx64 SYS SYS SYS
10-NOV-12 12.51.15.348530 AM OELx64 SYS SYS SYS
10-NOV-12 12.52.15.582945 AM OELx64 SYS SYS SYS
10-NOV-12 12.53.15.847327 AM OELx64 SYS SYS SYS
10-NOV-12 12.53.19.688558 AM WORKGROUP\YALLONKING-PC SYS SYS TEST 192.168.137.1
10-NOV-12 12.53.19.937570 AM WORKGROUP\YALLONKING-PC SYS SYS TEST 192.168.137.1
10 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-749112/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26143577/viewspace-749112/