ORACLE 管理 安全与审计
超级用户的审计: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;
/
页:
[1]