8.1、激活审计 sqlplus / as sysdba SQL> show parameter audit
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean FALSE audit_syslog_level string
audit_trail string NONE
SQL> alter system set audit_sys_operations=TRUE scope=spfile; --审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db_extended scope=spfile; SQL> startup force; SQL> show parameter audit
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean TRUE audit_syslog_level string
SQL> insert into u_test.t_test (c2,c5) values ('test1','2'); SQL> commit;
SQL> delete from u_test.t_test; SQL> commit; SQL> conn /as sysdba SQL> select
OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_bind,sql_text from dba_audit_trail; sql> audit select table by u_test by access;
如果在命令后面添加by user则只对user的操作进行审计,如果省去by用户,则对系统中所有的用户进行审计(不包含sys用户). 例:
AUDIT DELETE ANY TABLE; --审计删除表的操作
审计连接或断开连接: AUDIT SESSION;
AUDIT SESSION BY jeff, lori; -- 指定用户 审计权限(使用该权限才能执行的操作):
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; 对象审计:
AUDIT DELETE ON jeff.emp;
AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL; 取消审计: NOAUDIT session;
NOAUDIT session BY jeff, lori; NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE; NOAUDIT ALL; -- 取消所有statement审计 NOAUDIT ALL PRIVILEGES; -- 取消所有权限审计 NOAUDIT ALL ON DEFAULT; -- 取消所有对象审计 10、清除审计信息
DELETE FROM SYS.AUD$;
AUDIT_ACTIONS -- action代码
ALL_DEF_AUDIT_OPTS -- 对象创建时默认的对象审计选项 DBA_STMT_AUDIT_OPTS -- 当前数据库系统审计选项 DBA_PRIV_AUDIT_OPTS -- 权限审计选项 DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS ; -- 对象审计选项 DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL -- 审计记录 DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT -- 审计对象列表 DBA_AUDIT_SESSION
USER_AUDIT_SESSION -- session审计 DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT -- 语句审计
DBA_AUDIT_EXISTS -- 使用BY AUDIT NOT EXISTS选项的审计 DBA_AUDIT_POLICIES -- 审计POLICIES
alter table sys.aud$ move tablespace users;
alter table sys.aud$ move lob(sqlbind) store as( tablespace USERS); alter table sys.aud$ move lob(SQLTEXT) store as( tablespace USERS);
alter index sys.I_AUD1 rebuild tablespace u
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean FALSE audit_syslog_level string
audit_trail string NONE
SQL> alter system set audit_sys_operations=TRUE scope=spfile; --审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db_extended scope=spfile; SQL> startup force; SQL> show parameter audit
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean TRUE audit_syslog_level string
audit_trail string DB_EXTENDED
8.2、开始审计 sqlplus / as sysdba --记录对一个表的所有操作
SQL> insert into u_test.t_test (c2,c5) values ('test1','2'); SQL> commit;
SQL> delete from u_test.t_test; SQL> commit; SQL> conn /as sysdba SQL> select
OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_bind,sql_text from dba_audit_trail; sql> audit select table by u_test by access;
如果在命令后面添加by user则只对user的操作进行审计,如果省去by用户,则对系统中所有的用户进行审计(不包含sys用户). 例:
AUDIT DELETE ANY TABLE; --审计删除表的操作
AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL; --只审计删除失败的情况 AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL; --只审计删除成功的情况 AUDIT DELETE,UPDATE,INSERT ON user.table by test; --审计test用户对表user.table的delete,update,insert操作
8.3、撤销审计
SQL> noaudit all on t_test;
9、审计语句
多层环境下的审计:appserve-应用服务器,jackson-client AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson;审计连接或断开连接: AUDIT SESSION;
AUDIT SESSION BY jeff, lori; -- 指定用户 审计权限(使用该权限才能执行的操作):
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; 对象审计:
AUDIT DELETE ON jeff.emp;
AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL; 取消审计: NOAUDIT session;
NOAUDIT session BY jeff, lori; NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE; NOAUDIT ALL; -- 取消所有statement审计 NOAUDIT ALL PRIVILEGES; -- 取消所有权限审计 NOAUDIT ALL ON DEFAULT; -- 取消所有对象审计 10、清除审计信息
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='EMP';
11、审计视图
STMT_AUDIT_OPTION_MAP -- 审计选项类型代码AUDIT_ACTIONS -- action代码
ALL_DEF_AUDIT_OPTS -- 对象创建时默认的对象审计选项 DBA_STMT_AUDIT_OPTS -- 当前数据库系统审计选项 DBA_PRIV_AUDIT_OPTS -- 权限审计选项 DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS ; -- 对象审计选项 DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL -- 审计记录 DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT -- 审计对象列表 DBA_AUDIT_SESSION
USER_AUDIT_SESSION -- session审计 DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT -- 语句审计
DBA_AUDIT_EXISTS -- 使用BY AUDIT NOT EXISTS选项的审计 DBA_AUDIT_POLICIES -- 审计POLICIES
DBA_COMMON_AUDIT_TRAIL -- 标准审计+精细审计
12、将审计结果表从system表空间里移动到别的表空间上
实际上sys.aud$表上包含了两个lob字段,并不是简单的move table就可以。 下面是具体的过程:alter table sys.aud$ move tablespace users;
alter table sys.aud$ move lob(sqlbind) store as( tablespace USERS); alter table sys.aud$ move lob(SQLTEXT) store as( tablespace USERS);
alter index sys.I_AUD1 rebuild tablespace u