ITPub博客

首页 > 数字化转型 > ERP > 纪录用户登陆信息的触发器

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

原创 ERP 作者:lucky_lau 时间:2006-06-20 16:25:09 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: logminer示例
下一篇: oracle 10g技术概述
请登录后发表评论 登录
全部评论

注册时间:2008-02-26

  • 博文量
    20
  • 访问量
    608352