audit all on hr.employees;--开启审计 col username format a20 col userhost format a30 col sql_text format a30 set linesize 300 select username,userhost,sql_text,timestamp from dba_audit_trail where username='DBMONITOR' and timestamp>=trunc(sysdate) and sql_text is not null / noaudit all on hr.employees;--关闭审计
我要评论
COL username FORMAT a20
COL userhost FORMAT a30
COL sql_text FORMAT a30
SET LINESIZE 300
SELECT username,
userhost,
sql_text,
timestamp
FROM dba_audit_trail
WHERE username = 'DBMONITOR'
AND timestamp >= TRUNC (SYSDATE)
AND sql_text IS NOT NULL
/
BEGIN
DBMS_FGA.add_policy (object_schema => 'HR',--FGA审计对象schema
object_name => 'EMPLOYEES',--审计table
policy_name => 'audit_emps_salary',--FGA审计名称
audit_condition => 'department_id=10',--FGA审计条件
audit_column => 'SALARY,COMMISSION_PCT',--FGA审计的列名
handler_schema => 'secure',--指定调用schema
handler_module => 'log_emps_salary',--指定执行程式
enable => TRUE,--启用审计
statement_types => 'SELECT,UPDATE');--审计dml类型
END;
/
BEGIN--删除fga审计
DBMS_FGA.drop_policy (object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'audit_emps_salary');
END;
/
BEGIN
DBMS_FGA.add_policy (object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'audit_emps_salary',
audit_condition => 'department_id=10',
audit_column => 'SALARY,COMMISSION_PCT',
--handler_schema => 'secure',--如果不指定存储路径，默认会存放在dba_fga_audit_trail视图中
-- handler_module => 'log_emps_salary',
enable => TRUE,
statement_types => 'SELECT,UPDATE');
END;
/
SELECT salary
FROM employees
WHERE department_id = 10;
SELECT commission_pct
FROM employees
WHERE department_id = 10;
SELECT salary, commission_pct
FROM employees
WHERE department_id = 10;
DELETE employees
WHERE department_id = 10;
SELECT salary, commission_pct FROM employees;
COL DB_USER FORMAT a20
COL userhost FORMAT a30
COL sql_text FORMAT a30
SET PAGESIZE 600
SET LINESIZE 300
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SELECT DB_USER,
userhost,
sql_text,
timestamp
FROM dba_fga_audit_trail
WHERE --DB_USER='DBMONITOR'
timestamp >= TRUNC (SYSDATE) AND sql_text IS NOT NULL
/
DB_USER USERHOST SQL_TEXT TIMESTAMP
-------------------- ------------------------------ ------------------------------ -------------------
DBMONITOR ***************** SELECT salary FROM hr.employee 2018-03-30 11:04:44
s WHERE department_id=10
HR ***************** SELECT salary FROM employees w 2018-03-30 11:15:59
here department_id=10
HR ***************** SELECT commission_pct FROM emp 2018-03-30 11:16:31
loyees WHERE department_id=10
HR ***************** SELECT salary,commission_pct f 2018-03-30 11:17:00
rom employees WHERE department
_ID=10
HR ***************** SELECT salary,commission_pct f 2018-03-30 11:17:00
rom employees
BEGIN
DBMS_FGA.add_policy (object_schema => 'HR',
object_name => 'EMP',
policy_name => 'audit_emp_salary',
audit_condition => 'department_id=90',
--audit_column => 'SALARY,COMMISSION_PCT',
--handler_schema => 'secure',
-- handler_module => 'log_emps_salary',
enable => TRUE,
statement_types => 'DELETE');
END;
/
BEGIN
DBMS_FGA.drop_policy (object_schema => 'HR',
object_name => 'EMP',
policy_name => 'audit_emp_salary');
END;
/
DELETE emp
WHERE department_id = 90;
SELECT SALARY, COMMISSION_PCT
FROM emp
WHERE department_id = 90;
DELETE emp ;
SELECT DB_USER,
userhost,
sql_text,
timestamp
FROM dba_fga_audit_trail
WHERE --DB_USER='DBMONITOR'
timestamp >= TRUNC (SYSDATE) AND sql_text IS NOT NULL
/
ALTER TABLE SYS.AUD$ MOVE TABLESPACE <TABLESPACE_NAME>;
SYS.FGA_LOG$--表上有LONG字段无法进行直接迁移，可采用DROP再CREATE.
3:53:14 SQL> select table_name,tablespace_name from dba_tables
13:53:21 2 where table_name='AUD$';
TABLE_NAME TABLESPACE_NAME
---------------- ------------------------------------------------------------
AUD$ TP
13:53:23 SQL> ALTER TABLE SYS.AUD$ MOVE TABLESPACE SYSTEM;
13:53:59 SQL> select table_name,tablespace_name from dba_tables
13:54:02 2 where table_name='AUD$';
TABLE_NAME TABLESPACE_NAME
----------------- ------------------------------------------------------------
AUD$ SYSTEM
如对本文有疑问， 点击进行留言回复！！
oracle:docker镜像部署11g 创建用户名，远程连接
网友评论