随着数据的安全意识的不断加深,现在很多的企业已经开始逐步深化对数据安全的管控措施。作为数据库龙头老大的O记自当在其自身产品中充分的考虑到数据安全的问题,本篇文章将简单介绍一下基于Oracle数据库的数据审计入门配置。

查看数据库审计功能状态

//使用sqlplus通过sysdba角色登陆Oracle
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:32:00 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size		    2232960 bytes
Variable Size		  654314880 bytes
Database Buffers	  171966464 bytes
Redo Buffers		    6590464 bytes
Database mounted.
Database opened.
SQL>
//查看审计参数是否开启
SQL> show parameter audit

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

其中audit_trail代表数据库审计功能的状态,需设置db,extended,各项值含义如下:

  • none

Disables database auditing.

  • os

Enables database auditing and directs all audit records to the operating system’s audit trail.

  • db

Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).

  • db,extended

Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD\$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

  • xml

Enables database auditing and writes all audit records to XML format OS files.

  • xm,extended

Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

开启审计

//开启审计功能
SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;

System altered.
//关闭数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
//启动数据实例
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size		    2232960 bytes
Variable Size		  654314880 bytes
Database Buffers	  171966464 bytes
Redo Buffers		    6590464 bytes
Database mounted.
Database opened.
//查看审计参数是否开启
SQL> show parameter audit


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

查看审计日志表

SQL> set line 120
SQL> col obj_name format a5
SQL> col obj_name for a10
SQL> col SQL_TEXT format a30
SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where obj_name is not null;


USERN TIMESTAMP      OBJ_NAME	ACTION_NAME		     SQL_TEXT
----- -------------- ---------- ---------------------------- ---------------------------

0 rows selected.

验证准备

$ sqlplus stl/sun

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:51:01 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test (c1 varchar2(20));

Table created.

开启指定对象审计

[ora11g@db ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:52:49 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> audit insert, select, update, delete on stl.test by access whenever successful;

Audit succeeded.

验证审计

$ sqlplus stl/sun

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:51:01 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test (c1 varchar2(20));

Table created.

SQL> select * from test;

no rows selected

SQL> insert into test values(1);

1 row created.

SQL> update test set c1 = 2;

1 row updated.

SQL> select * from test;

C1
--------------------
2

SQL> delete from test;

1 row deleted.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 16 16:52:49 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where obj_name ='TEST';

USERN TIMESTAMP      OBJ_NAME	ACTION_NAME		     SQL_TEXT
----- -------------- ---------- -------------------------- -----------------------------
STL   10/16/19 16:55 TEST	INSERT			     insert into test values(1)
STL   10/16/19 16:55 TEST	SELECT			     select * from test
STL   10/16/19 16:54 TEST	SELECT			     select * from test
STL   10/16/19 16:55 TEST	UPDATE			     update test set c1 = 2
STL   10/16/19 16:55 TEST	DELETE			     delete from test

其他常用命令

--取消指定对象审计
SQL> noaudit all on test;
--删除审计日志
SQL> DELETE FROM SYS.AUD$ WHERE obj$name='TEST';


参考链接:

Oracle 11g 审计

Oracle审计详解

Oracle 10g安全加固(审计、监听密码)