Oracle常见问题
大约 2 分钟
提示
Oracle常见问题
1. 打开数据库审计
alter system set audit_sys_operations=TRUE scope=spfile; //审计管理用户
alter system set audit_trail=db,extended scope=spfile; //将sql语句写入审计表中
2. 重启数据库
SQL> shutdown immediate;
SQL> startup
3. 添加审计策略
--- 审计用户caiwu所有成功的操作
audit all by caiwu by access whenever successful;
--- 查表审计
audit select table by caiwu by access;
--- 更新审计
audit update table by caiwu by access;
--- 删除审计
audit delete table by caiwu by access;
--- 插入审计
audit insert table by caiwu by access;
--- 针对某表的更新、删除审计(错误也审计)
AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;
--- 保护审计
audit all on sys.aud$ by access;
audit all by ${DB} by access whenever successful;
grant select on dba_audit_trail to ${DB};
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ${DB} BY ACCESS;
4. 取消审计
NOAUDIT UPDATE,DELETE,INSERT ON T_TEST by access;
5. 查询审计结果
select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail;
6. 将审计表查询开放给某个用户
grant select on dba_audit_trail to caiwu;
7. 清空审计记录
DELETE FROM SYS.AUD$;
8. 查看审计功能是否开启
SQL> show parameter audit;
9. 示例:
# 第1步:以sysdba登录数据库:
sqlplus / as sysdba
# 第2步:显示当前审计参数:
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
#audit_trail 的value值为NONE表示不开启;
#audit_trail 的value值为FALSE表示不开启;
#audit_trail 的value值为DB表示开启;
#audit_trail 的value值为TURE表示开启;
#audit_trail 的value值为OS表示审计记录写入一个操作系统文件(不是特别理解)
# 第3步:开启审计功能
SQL> alter system set audit_sys_operations=TRUE scope=spfile;--审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> show parameter audit;
# 第4步:重启实例
SQL> shutdown immediate
SQL> startup
SQL> show parameter audit;
# 第3步:关闭审计功能
sqlplus /nolog
SQL> show parameter audit
SQL> alter system set audit_trail = none scope=spfile;