用户密码被修改增加审计记录

创建记录审计的用户jyc：

create user jyc identified by jyc;
grant resource to jyc;

JYC用户下创建审计记录表：（当然也可放到其它用户下）

/* -------- DDL事件审计表 -------- /
CREATE TABLE Audit_DDL_OBJ(
Opr_Time DATE,
Session_Id NUMBER,
OS_User VARCHAR2(200),
IP_Address VARCHAR2(200), 
Terminal VARCHAR2(200), 
Host VARCHAR2(200),
User_Name VARCHAR2(30),
DDL_Type VARCHAR2(30),
DDL_Sql VARCHAR2(2000),
Object_Type VARCHAR2(18),
Owner VARCHAR2(30),
Object_Name VARCHAR2(128)
);

/* -------- DDL事件审计表索引 --------- */
CREATE INDEX IX_AUDIT_DDL_SESSIONID ON AUDIT_DDL_OBJ(SESSION_ID);
CREATE INDEX IX_AUDIT_DDL_OPRTIME ON AUDIT_DDL_OBJ(OPR_TIME);

sys用户下创建触发器：（注意修改审计记录对应的用户表）

CREATE OR REPLACE TRIGGER SYS.DDL_Audit_Trigger
AFTER DDL ON DATABASE

DECLARE

Session_Id_Var NUMBER;
Os_User_Var VARCHAR2(200);
IP_Address_Var VARCHAR2(200); 
Terminal_Var VARCHAR2(200);
Host_Var VARCHAR2(200);
Cut NUMBER;
Sql_Text ORA_NAME_LIST_T;
L_Trace NUMBER;
DDL_Sql_Var VARCHAR2(2000);

BEGIN

SELECT SYS_CONTEXT('USERENV','SESSIONID'),
SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','TERMINAL'),
SYS_CONTEXT('USERENV','HOST')
INTO Session_Id_Var,
Os_User_Var,
IP_Address_Var,
Terminal_Var,
Host_Var
FROM DUAL;

BEGIN

  SELECT COUNT(*) INTO L_Trace FROM DUAL
   WHERE ORA_DICT_OBJ_NAME NOT LIKE 'MLOG%'
     AND ORA_DICT_OBJ_NAME NOT LIKE '%LOG'
     AND UTL_INADDR.GET_HOST_ADDRESS IS NOT NULL
     AND SYS_CONTEXT('USERENV','IP_ADDRESS') IS NOT NULL
     AND SYS_CONTEXT('USERENV','IP_ADDRESS') <> UTL_INADDR.GET_HOST_ADDRESS;

  IF L_Trace > 0 THEN
     Cut := ORA_SQL_TXT(Sql_Text);
     FOR i IN 1..Cut LOOP
        DDL_Sql_Var := SUBSTR(DDL_Sql_Var || Sql_Text(i),1,2000);
     END LOOP;
  END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

IF ORA_SYSEVENT='ALTER' AND ORA_DICT_OBJ_TYPE='USER' THEN
		INSERT INTO JYC.Audit_DDL_OBJ(
		Opr_Time,
		Session_Id,
		OS_User, 
		IP_Address,
		Terminal,
		Host,
		User_Name,
		DDL_Type,
		DDL_Sql,
		Object_Type,
		Owner,
		Object_Name
		)
		VALUES( to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),
		Session_Id_Var,
		Os_User_Var,
		IP_Address_Var,
		Terminal_Var,
		Host_Var,
		ORA_LOGIN_USER,
		ORA_SYSEVENT,
		DDL_Sql_Var,
		ORA_DICT_OBJ_TYPE,
		ORA_DICT_OBJ_OWNER,
		ORA_DICT_OBJ_NAME);
		COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
NULL;
END DDL_Audit_Trigger;
/

测试：

alter user jyc identified by j;

检查审计记录：

alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
select * from JYC.Audit_DDL_OBJ;

相关参考：

Oracle 审计及密码策略更改。 - 走看看