Oracle-27-审计

审计分两种：普通用户审计、超级用户审计

一、超级用户审计

查看当前审计状态

show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/sundb/adump                                        
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

创建会话审计

audit session by scott;

连接SCOTT用户并查看审计

conn scott/scott

select userid,userhost,action#,returncode,comment$text,ntimestamp#,process# from aud$ where userid='SCOTT';

USERID	USERHOST	ACTION#	RETURNCODE	COMMENT$TEXT				NTIMESTAMP#			PROCESS#
SCOTT	dadvfm0514	100	0		Authenticated by: DATABASE	15-8？  -09 07.50.14.499782 ？？	30537
SCOTT	dadvfm0514	101	0		15-8？  			-09 07.50.14.732491 ？？		30537

创建表审计

audit table by scott;

create table a(x number);

select userid,userhost,action#,returncode,comment$text,ntimestamp#,process# from aud$ where userid='SCOTT';

USERID	USERHOST	ACTION#		RETURNCODE	COMMENT$TEXT				NTIMESTAMP#			PROCESS#
SCOTT	RHEL6		12		0		03-5？  				-18 07.28.16.527044 ？？	3265
SCOTT	RHEL6		1		0		03-5？  				-18 07.28.26.019013 ？？	3265
SCOTT	RHEL6		101		0		03-5？  				-18 07.28.08.502008 ？？	2939
SCOTT	RHEL6		100		0		Authenticated by: DATABASE03-5？  	-18 07.28.08.546188 ？？	3265

查看action为1的项操作是什么

select name from audit_actions where action=1;

NAME
----------------------------
CREATE TABLE

Elapsed: 00:00:00.02

select name from audit_actions where action=12;

NAME
----------------------------
DROP TABLE

Elapsed: 00:00:00.01

同样的我们还可以审计INSERT、DELETE等操作

audit insert table by scott;

grant create any table to scott;

audit create any table by scott;

audit delete on scott.emp;

audit select on scott.emp by access;

SELECT undo_sql FROM FLASHBACK_TRANSACTION_QUERY WHERE XID='0D00010026000000'                 

如果审计信息存放在数据库中，可以用以下语句清除

delete from sys.aud$;
delete from sys.aud$ where obj$name='EMP';

细颗粒审计

select db_user,timestamp,sql_text from dba_fga_audit_trail;

begin
dbms_fga.add_policy(
object_schema => 'scott',
object_name => 'emp',
policy_name => 'mypolicy',
audit_condition => 'sal<100',
audit_column => 'comm,sal',
handler_schema => null,
handler_module => null,
enable => true,
statement_types => 'insert,update',
audit_trail => dbms_fga.db + dbms_fga.extended,
audit_column_opts => dbms_fga.any_columns);
end;
/

关闭细颗粒审计

begin
dbms_fga.drop_policy(
object_schema => 'scott',
object_name => 'emp',
policy_name => 'mypolicy');
end;
/

查看用户启用的语句审计

col audit_option for a30
col audit_option for a20

select audit_option,failure,success,user_name from dba_stmt_audit_opts;

AUDIT_OPTION                   FAILURE    SUCCESS    USER_NAME
------------------------------ ---------- ---------- ------------------------------
ALTER SYSTEM                   BY ACCESS  BY ACCESS
SYSTEM AUDIT                   BY ACCESS  BY ACCESS
CREATE SESSION                 BY ACCESS  BY ACCESS  SCOTT
CREATE SESSION                 BY ACCESS  BY ACCESS
TABLE                          BY ACCESS  BY ACCESS  SCOTT
CREATE USER                    BY ACCESS  BY ACCESS
ALTER USER                     BY ACCESS  BY ACCESS
DROP USER                      BY ACCESS  BY ACCESS
PUBLIC SYNONYM                 BY ACCESS  BY ACCESS
DATABASE LINK                  BY ACCESS  BY ACCESS
ROLE                           BY ACCESS  BY ACCESS
PROFILE                        BY ACCESS  BY ACCESS
CREATE ANY TABLE               BY ACCESS  BY ACCESS
ALTER ANY TABLE                BY ACCESS  BY ACCESS
DROP ANY TABLE                 BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK    BY ACCESS  BY ACCESS
GRANT ANY ROLE                 BY ACCESS  BY ACCESS
SYSTEM GRANT                   BY ACCESS  BY ACCESS
ALTER DATABASE                 BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE           BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE            BY ACCESS  BY ACCESS
DROP ANY PROCEDURE             BY ACCESS  BY ACCESS
ALTER PROFILE                  BY ACCESS  BY ACCESS
DROP PROFILE                   BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE            BY ACCESS  BY ACCESS
CREATE ANY LIBRARY             BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY           BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE     BY ACCESS  BY ACCESS
CREATE ANY JOB                 BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB            BY ACCESS  BY ACCESS

30 rows selected.

Elapsed: 00:00:00.01

查看用户的特权审计

select privilege,user_name from dba_priv_audit_opts order by user_name;

PRIVILEGE                                USER_NAME
---------------------------------------- ------------------------------
CREATE SESSION                           SCOTT
ALTER SYSTEM
GRANT ANY OBJECT PRIVILEGE
EXEMPT ACCESS POLICY
CREATE ANY LIBRARY
GRANT ANY PRIVILEGE
DROP PROFILE
ALTER PROFILE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
ALTER DATABASE
GRANT ANY ROLE
CREATE PUBLIC DATABASE LINK
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
DROP USER
ALTER USER
CREATE USER
CREATE SESSION
AUDIT SYSTEM
CREATE EXTERNAL JOB
CREATE ANY JOB

24 rows selected.

Elapsed: 00:00:00.01

查看用户的对象审计

col owner for a10
col object_name for a15
col object_type for a15
select * from dba_obj_audit_opts where owner='SCOTT';

取消所有语句审计

noaudit all;

取消所有权限审计

noaudit all privileges;

取消所有对象审计

noaudit all on default;