纪录用户登陆信息的触发器

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;

SYS_CONTEXT('USERENV','IP_ADDR
--------------------------------------------------------------------------------

SQL> SELECT SYS_CONTEXT('USERENV','MODULE') FROM DUAL;

SYS_CONTEXT('USERENV','MODULE'
--------------------------------------------------------------------------------
PL/SQL Developer

SQL> SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL;

SYS_CONTEXT('USERENV','OS_USER
--------------------------------------------------------------------------------
LUCKYlucky.lau

SQL> SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT
--------------------------------------------------------------------------------
SYS

SQL> CREATE TABLE T_USER_LOGON
2 (DB_USER VARCHAR2(30),
3 OS_USER VARCHAR2(30),
4 MODULE VARCHAR2(48),
5 HOST VARCHAR2(54),
6 IP_ADDRESS VARCHAR2(30),
7 LOGON_TIME DATE);

Table created

SQL> SELECT * FROM T_USER_LOGON;

DB_USER OS_USER MODULE HOST IP_ADDRESS LOGON_TIME
------------------------------ ------------------------------ ------------------------------------------------ ------------------------------------------------------ ------------------------------ -----------

SQL> CONN / AS SYSDBA
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL> CONN SCOTT/TIGER
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SCOTT

SQL> CONN / AS SYSDBA
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL> SELECT * FROM T_USER_LOGON;

DB_USER OS_USER MODULE HOST IP_ADDRESS LOGON_TIME
------------------------------ ------------------------------ ------------------------------------------------ ------------------------------------------------------ ------------------------------ -----------
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS SYSTEM LUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11

8 rows selected

SQL> /

DB_USER OS_USER MODULE HOST IP_ADDRESS LOGON_TIME
------------------------------ ------------------------------ ------------------------------------------------ ------------------------------------------------------ ------------------------------ -----------
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS SYSTEM LUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11

9 rows selected

SQL>
SQL> create or replace trigger TGR_USER_LOGON
2 after LOGON ON DATABASE
3 declare
4 -- local variables here
5 begin
6 INSERT INTO T_USER_LOGON
7 VALUES(SYS_CONTEXT('USERENV','SESSION_USER'),SYS_CONTEXT('USERENV','OS_USER'),SYS_CONTEXT('USERENV','MODULE')
8 ,SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','IP_ADDRESS'),SYSDATE);
9 COMMIT;
10 EXCEPTION
11 WHEN OTHERS THEN
12 ROLLBACK;
13 end TGR_USER_LOGON;
14 /

Trigger created

SQL> SELECT * FROM T_USER_LOGON;

DB_USER OS_USER MODULE HOST IP_ADDRESS LOGON_TIME
------------------------------ ------------------------------ ------------------------------------------------ ------------------------------------------------------ ------------------------------ -----------
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS SYSTEM LUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11
SYS SYSTEM LUCKY 2006-6-9 11
SYS LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11
SYS LUCKYlucky.lau plsqldev.exe WORKGROUPLUCKY 2006-6-9 11
SYS SYSTEM LUCKY 2006-6-9 11
SYSMAN SYSTEM LUCKY 2006-6-9 11
SYS LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11
SCOTT LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11
SCOTT LUCKYlucky.lau sqlplus.exe WORKGROUPLUCKY 2006-6-9 11

17 rows selected

SQL>

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4584/viewspace-844240/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4584/viewspace-844240/