logon,logoff on database trigger在审计中应用

最近的一个项目业务比较BT,业务用户一大堆,开发用户也一大堆,为了对这些用户进行审计,开始考虑FGA做,但是看了FGA后,只能针对特定的表进行审计,而不能对于具体的用户进行审计,没办法,只能用TRIGGER做简单的用户行为审计,再配合LOGMNR.

相关脚本如下:

CREATE TABLE LOGON_AUDIT
(
  LOGON_DATE           DATE                     DEFAULT SYSDATE,
  MACHINE              VARCHAR2(256),
  SESSIONID            VARCHAR2(256),
  INSTANCE             VARCHAR2(256),
  DB_USER              VARCHAR2(256),
  DB_NAME              VARCHAR2(256),
  OS_USER              VARCHAR2(256),
  IP_ADDRESS           VARCHAR2(256),
  AUTHENTICATION_TYPE  VARCHAR2(256),
  PROGRAM              VARCHAR2(100),
  TYPE                 NUMBER(2)               --1代表登陆，0代表退出
)
TABLESPACE GAME;

CREATE OR REPLACE TRIGGER logon_audit_trig
AFTER LOGON ON DATABASE
DECLARE
V_PROGRAM VARCHAR2(100);
V_SESSIONID NUMBER(20);
V_USERNAME VARCHAR2(20);
BEGIN
SELECT PROGRAM,SID,USERNAME INTO V_PROGRAM,V_SESSIONID,V_USERNAME
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND rownum<2;

IF USER IN ('具体用户') THEN
      INSERT INTO logon_audit
(MACHINE, SESSIONID, INSTANCE, DB_USER, DB_NAME, OS_USER, IP_ADDRESS, AUTHENTICATION_TYPE,PROGRAM,type)
 VALUES
(SYS_CONTEXT('USERENV','TERMINAL'),
V_SESSIONID,
SYS_CONTEXT('USERENV','INSTANCE'),
V_USERNAME,
SYS_CONTEXT('USERENV','DB_NAME'),
SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE'),
V_PROGRAM,1);

END IF;

END;
/

CREATE OR REPLACE TRIGGER logoff_audit_trig
BEFORE LOGOFF ON DATABASE
DECLARE
V_PROGRAM VARCHAR2(100);
V_SESSIONID NUMBER(20);
V_USERNAME VARCHAR2(20);
BEGIN
SELECT PROGRAM,SID,USERNAME INTO V_PROGRAM,V_SESSIONID,V_USERNAME
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND rownum<2;

IF USER IN ('具体用户') THEN
      INSERT INTO logon_audit
(MACHINE, SESSIONID, INSTANCE, DB_USER, DB_NAME, OS_USER, IP_ADDRESS, AUTHENTICATION_TYPE,PROGRAM,type)
 VALUES
(SYS_CONTEXT('USERENV','TERMINAL'),
V_SESSIONID,
SYS_CONTEXT('USERENV','INSTANCE'),
V_USERNAME,
SYS_CONTEXT('USERENV','DB_NAME'),
SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE'),
V_PROGRAM,0);

END IF;

END;

/

BTW,在研究FGA的时候,发现FGA还是挺强大的,除了进行粒度审计，估计以后在进行数据同步，复制，迁移的时候能用上.

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