Oracle sql 跟踪

环境:Oracle 11g    Centos6.5

 

1、安装审计组件:
sqlplus sys/你的密码 as sysdba @$ORACLE_HOME/rdbms/admin/cataudit.sql

2、开启数据库审计功能

sqlplus sys/你的密码 as sysdba

SQL> alter system set audit_sys_operations=TRUE scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;

重启数据库
SQL> shutdown inmmediate;
SQL> startup;

3、检查审计功能是否打开
SQL> show parameter audit;

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /home/oracle/app/admin/trsdb/adump
audit_sys_operations boolean TRUE —– 表示打开了
audit_syslog_level string
audit_trail string DB, EXTENDED — 审计级别

4、创建审计策略,因为数据库表有很多个,建一个存储过程来创建

create or replace procedure auditUser(auditUser in varchar2,oper in int)
is
policyName varchar2(2000);
policynum varchar2(20);
cursor tables is select object_name from dba_objects where owner=’TRS’ and object_type=’TABLE’;
begin

for tableName in tables loop
begin
policynum := ‘AUDIT1_’;
policyName := concat(policynum,tableName.Object_Name);
if oper=1 then
dbms_fga.add_policy(object_schema=>auditUser,object_name=>tableName.Object_Name,policy_name => policyName,
statement_types=>’select,insert,update,delete’,enable=>true);
end if;
if oper=0 then
dbms_fga.drop_policy(object_schema=>auditUser,object_name=>tableName.Object_Name,policy_name => policyName);
end if;
exception
when others then
NULL;
end;
end loop;
commit;
end;

存储过程参数解释:
auditUser in varchar2,— 数据库的业务用户名
oper in int — 1 新建审计策略,0 删除审计策略

cursor tables is select object_name from dba_objects where owner=’TRS’ and object_type=’TABLE’;”
这里的 owner=’TRS’ TRS 换成你实际的用户名

5、执行存储过程创建审计策略:

SQL> exec auditUser(‘TRS’,1); — TRS 换成你实际的用户名

6、查看审计结果:
SQL> SELECT * FROM DBA_FGA_AUDIT_TRAIL order by timestamp desc

Leave a Reply

Your email address will not be published. Required fields are marked *