ORACLE 管理 安全与审计

happy_boy

　　超级用户的审计:
　　audit_file_dest='/u1/oracle/admin/updb/adump' --审计线索保存的位置
　　audit_sys_operations=true --起用对SYSDBA和SYSOPER的附加审计信息
　　普通用户的审计:
　　审计信息的保存位置:
　　audit_trail={ none | os | db | db,extended | xml | xml,extended }
　　None ：是默认值，不做审计；
　　os : 将审计信息记录于操作系统！windows记录于事件查看器，unxi记录于audit_file_dest所指定路径
　　DB ：将审计线索记录在数据库中，aud$
　　db,extended ：extended选项会在审计中增加SQLBIND 和 SQLTEXT的信息
　　xml ：将审计线索记录在xml文件中audit_file_dest参数所指定的目录
　　语句审计:跟踪会话的连接和注销
　　audit session by scott; -- 审计指定用户的连接或断开连接操作
　　noaudit session by scott;
　　audit session; --审计用户连接或断开连接操作
　　noaudit session;
　　查看审计ACTION对应的编码:
　　select name from AUDIT_ACTIONS where ACTION=100;
　　select name from AUDIT_ACTIONS where ACTION=101;
　　语句审计(审计create table,drop table,truncate table):
　　audit table by scott; --成功与否都生成审计线索
　　audit table by scott whenever successful; --只有成功生成审计线索
　　audit table by scott whenever not successful; --只有失败生成审计线索
　　noaudit table by scott;
　　audit table;
　　audit table whenever successful;
　　audit table whenever not successful;
　　noaudit table;
　　语句审计(审计DML语句):
　　audit insert table by scott;
　　audit insert table by scott whenever successful;
　　audit insert table by scott whenever not successful;
　　audit insert table by scott by access;
　　audit insert table by scott by access whenever successful;
　　audit insert table by scott by access whenever not successful;
　　noaudit insert table by scott;
　　(BY ACCESS每次都生成一条审计记录,
　　BY SESSION相同操作只生成一条审计记录)
　　查看用户被启用的语句审计的选项:
　　col AUDIT_OPTION for a20
　　select audit_option,failure,success,user_name from dba_stmt_audit_opts;
　　特权审计(使用了特定的系统权限才会触发审计线索):
　　audit create any table;
　　audit create any table by scott;
　　audit drop any table by scott;
　　audit select any table by scott by session;
　　查看用户被启用的特权审计的选项:
　　select privilege,user_name from dba_priv_audit_opts order by user_name;
　　对象审计(跟踪指定对象权限的操作):
　　audit delete on scott.e;
　　audit delete on scott.e by session;
　　audit delete on scott.e by session whenever successful;
　　audit delete on scott.e by session whenever not successful;
　　audit select on scott.emp by access;
　　audit delete on scott.e by access whenever successful;
　　audit delete on scott.e by access whenever not successful;
　　noaudit delete on scott.e;
　　查看用户被启用的对象审计的选项:
　　col OWNER for a10
　　col OBJECT_NAME for a15
　　col OBJECT_TYPE for a15
　　select * from dba_obj_audit_opts where owner='SCOTT';
　　successful /not successful
　　S --> session
　　A --> access
　　-- 取消所有statement审计
　　noaudit all;
　　-- 取消所有权限审计
　　noaudit all privileges;
　　-- 取消所有对象审计
　　noaudit all on default;
　　将审计线索保存到数据库:
　　alter system set audit_trail=db scope=spfile;
　　aud$
　　col OBJ$CREATOR for a10
　　col USERID for a10
　　col USERHOST for a22
　　col OBJ$NAME for a10
　　col NTIMESTAMP# for a28
　　col COMMENT$TEXT for a30
　　--audit session by scott;
　　select USERID,USERHOST,ACTION#,RETURNCODE,COMMENT$TEXT,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';
　　ps -ef | grep PROCESS#
　　--audit table by scott;
　　select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';
　　--audit insert table by scott;
　　select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,SES$ACTIONS,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';
　　select UNDO_SQL from flashback_transaction_query where xid='03000B0048010000';
　　--audit create any table by scott;
　　select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';
　　--audit delete on scott.e;
　　select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,SES$ACTIONS,NTIMESTAMP#,PROCESS#,XID from aud$ where userid='SCOTT';
　　--audit select on scott.emp by access;
　　select USERID,USERHOST,ACTION#,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,PROCESS# from aud$ where userid='SCOTT';
　　如果审计信息记录在数据库中怎样清除审计信息
　　delete from sys.aud$;
　　delete from sys.aud$ where obj$name='EMP';
　　commit;
　　细颗粒审计:
　　alter syste set audit_trail=os scope=spfile;
　　begin
　　DBMS_FGA.ADD_POLICY (
　　object_schema => 'scott',
　　object_name => 'emp',
　　policy_name => 'mypolicy1',
　　audit_condition => 'sal < 100',
　　audit_column => 'comm,sal',
　　handler_schema => NULL,
　　handler_module => NULL,
　　enable => TRUE,
　　statement_types => 'INSERT, UPDATE',
　　audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED,
　　audit_column_opts => DBMS_FGA.ANY_COLUMNS);
　　end;
　　/
　　begin
　　DBMS_FGA.DISABLE_POLICY (
　　object_schema => 'scott',
　　object_name => 'emp',
　　policy_name => 'mypolicy1');
　　end;
　　/
　　begin
　　DBMS_FGA.ENABLE_POLICY (
　　object_schema => 'scott',
　　object_name => 'emp',
　　policy_name => 'mypolicy1',
　　enable => TRUE);
　　end;
　　/
　　begin
　　DBMS_FGA.DROP_POLICY (
　　object_schema => 'scott',
　　object_name => 'emp',
　　policy_name => 'mypolicy1');
　　end;
　　/