oracle audit_actions,配置Oracle安全审计选项audit

最新推荐文章于 2025-01-08 10:51:42 发布

慈老湿

最新推荐文章于 2025-01-08 10:51:42 发布

阅读量401

点赞数

文章标签： oracle audit_actions

本文介绍如何在Oracle数据库中配置不同类型的审计操作，包括SYSDBA/SYSOPER操作、语句级别、权限级别及默认对象的审计，并解释了相关参数设置及审计命令的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2. SYSDBA/SYSOPER操作的审计

SQL>show parameter audit；

NAME TYPE VALUE

------------------------------------ ----------- ---------------

audit_file_dest string /oracle/PQ1/102_64/rdbms/audit

audit_sys_operations boolean TRUE

audit_syslog_level string

audit_trail string OS

设置步骤

SQL> alter system set audit_trail=os scope=spfile;

System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

System altered.

SQL> alter system set audit_file_dest='C:\APP\tom\ADMIN\ORCL\ADUMP' scope=spfile;

System altered.

重新启动数据库：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1375704 bytes

Variable Size 465568296 bytes

Database Buffers 50331648 bytes

Redo Buffers 5832704 bytes

Database mounted.

Database opened.

检查当前的审计参数：

SQL> show parameter audit;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string C:\APP\tom\ADMIN\ORCL\ADUMP

audit_sys_operations boolean TRUE

audit_trail string OS

请根据实际需要将C:\APP\tom\ADMIN\ORCL\ADUMP 修改为/oracle/PQ1/102_64/rdbms/audit 或其他审计目录

3. 语句级别的审计命令

脚本：

audit create session by tom by access;

audit SELECT TABLE by tom by access;

audit INSERT TABLE by tom by access;

audit UPDATE TABLE by tom by access;

audit DELETE TABLE by tom by access;

请按照实际需要将tom修改为需要审计的数据库用户

示范：

SQL> select * from dba_stmt_audit_opts;

no rows selected

SQL> audit create session by tom by access;

Audit succeeded.

SQL> audit SELECT TABLE by tom by access;

Audit succeeded.

SQL> audit INSERT TABLE by tom by access;

Audit succeeded.

SQL> audit UPDATE TABLE by tom by access;

Audit succeeded.

SQL> audit DELETE TABLE by tom by access;

Audit succeeded.

SQL> select * from dba_stmt_audit_opts;

USER_NAME PROX AUDIT_OPTION SUCCESS FAILURE

-------------------- ---- ------------------------------ ---------- ----------

tom CREATE SESSION BY ACCESS BY ACCESS

tom DROP ANY TABLE BY ACCESS BY ACCESS

tom SELECT ANY TABLE BY ACCESS BY ACCESS

tom SELECT TABLE BY ACCESS BY ACCESS

tom INSERT TABLE BY ACCESS BY ACCESS

tom UPDATE TABLE BY ACCESS BY ACCESS

tom DELETE TABLE BY ACCESS BY ACCESS

关闭语句审计

noaudit create session by tom;

noaudit select table by tom;

noaudit insert table by tom;

noaudit update table by tom;

noaudit delete table by tom;

4. 权限级别的审计

脚本：

audit drop any table by tom by access;

audit select any table by tom by access;

audit create session by tom by access;

请按照实际需要将tom修改为需要审计的数据库用户

示范

SQL> audit drop any table by tom by access;

Audit succeeded.

SQL> audit select any table by tom by access;

Audit succeeded.

SQL> audit create session by tom by access;

Audit succeeded.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROX PRIVILEGE SUCCESS FAILURE

-------------------- ---- ------------------------------ ---------- ----------

tom SELECT ANY TABLE BY ACCESS BY ACCESS

tom DROP ANY TABLE BY ACCESS BY ACCESS

tom CREATE SESSION BY ACCESS BY ACCESS

关闭权限审计

noaudit drop any table by tom;

noaudit select any table by tom;

noaudit create session by tom;

5. 默认对象的审计

脚本：

audit alter on default by session;

audit audit on default by session;

audit delete on default by session;

audit grant on default by access;

audit lock on default by session;

示范：

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

SQL> audit alter on default by session;

Audit succeeded.

SQL> audit audit on default by session;

Audit succeeded.

SQL> audit delete on default by session;

Audit succeeded.

SQL> audit grant on default by access;

Audit succeeded.

SQL> audit lock on default by session;

Audit succeeded.

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

S/S S/S -/- S/S A/A -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-

关闭默认对象审计：

noaudit alter on default ;

noaudit audit on default ;

noaudit delete on default ;

noaudit grant on default;

noaudit lock on default;

6. 其他注意事项

1. 审计命令选项

by session对每个session中发生的重复操作只记录一次，by access对每个session中发生的每次操作都记录，而不管是否重复

Whenever [not] successful/whenever successful

操作成功(dba_audit_trail中returncode字段为0) 才审计. whenever not successful 操作失败才设计.

省略该子句(Whenever [not] successful/whenever successful )的话，不管操作成功与否都会审计。

2. 审计数据库初始化参数文件中AUDIT_TRAIL＝OS时，审计记录存在操作系统的文件中，即参数AUDIT_FILE_DEST 指定的值，如果是windows，审计记录存放在事件管理器的应用程序日志中。数据库初始化参数文件中AUDIT_TRAIL＝DB时，审计记录存在数据库中

3. 审计信息相关的数据字典视图

DBA_AUDIT_TRAIL 所有审计记录

DBA_AUDIT_EXISTS NOT EXISTS 审计产生的记录

DBA_AUDIT_OBJECT 关于对象的审计记录

DBA_AUDIT_SESSION 连接和断开的记录

DBA_AUDIT_STATEMENT 语句级别的审计记录

SYS.AUD$ 是唯一保留审计结果的表。其它的都是视图

AUDIT_ACTIONS 包含对审计跟踪动作类型代码的说明

ALL_DEF_AUDIT_OPTS 包含默认对象审计选项。当创建对象时将应用这些选项

DBA_STMT_AUDIT_OPTS 由用户设置的跨系统的当前系统审计选项

DBA_PRIV_AUDIT_OPTS 由用户正在审计的跨系统的当前系统权限

DBA_OBJ_AUDIT_OPTS 在所有对象上的审计选项

USER_OBJ_AUDIT_OPTS USER 视图描述当前用户拥有的所有对象上的审计选项

4. 有时候“语句审计”和“权限审计”是相互重复的。并不需要明确的区分这2种类型。例如下面红色粗体部分：

SQL> audit create table;

Audit succeeded.

SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME PROX AUDIT_OPTION SUCCESS FAILURE

-------------------- ---- ------------------------------ ---------- ----------

tom CREATE SESSION BY ACCESS BY ACCESS

CREATE TABLE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

tom DROP ANY TABLE BY ACCESS BY ACCESS

tom SELECT ANY TABLE BY ACCESS BY ACCESS

tom SELECT TABLE BY ACCESS BY ACCESS

tom INSERT TABLE BY ACCESS BY ACCESS

tom UPDATE TABLE BY ACCESS BY ACCESS

tom DELETE TABLE BY ACCESS BY ACCESS

9 rows selected.

SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME PROX PRIVILEGE SUCCESS FAILURE

-------------------- ---- ------------------------------ ---------- ----------

tom SELECT ANY TABLE BY ACCESS BY ACCESS

tom DROP ANY TABLE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

CREATE TABLE BY ACCESS BY ACCESS

tom CREATE SESSION BY ACCESS BY ACCESS

5. ALL_DEF_AUDIT_OPTS 数据字典视图说明

-/-: no default auditing

S/-: auditing whenever successful

-/S: auditing whenever not successful

Column Datatype NULL Description

ALT VARCHAR2(3) Auditing ALTER WHENEVER SUCCESSFUL / UNSUCCESSFUL

AUD VARCHAR2(3) Auditing AUDIT WHENEVER SUCCESSFUL / UNSUCCESSFUL

COM VARCHAR2(3) Auditing COMMENT WHENEVER SUCCESSFUL / UNSUCCESSFUL

DEL VARCHAR2(3) Auditing DELETE WHENEVER SUCCESSFUL / UNSUCCESSFUL

GRA VARCHAR2(3) Auditing GRANT WHENEVER SUCCESSFUL / UNSUCCESSFUL

IND VARCHAR2(3) Auditing INDEX WHENEVER SUCCESSFUL / UNSUCCESSFUL

INS VARCHAR2(3) Auditing INSERT WHENEVER SUCCESSFUL / UNSUCCESSFUL

LOC VARCHAR2(3) Auditing LOCK WHENEVER SUCCESSFUL / UNSUCCESSFUL

REN VARCHAR2(3) Auditing RENAME WHENEVER SUCCESSFUL / UNSUCCESSFUL

SEL VARCHAR2(3) Auditing SELECT WHENEVER SUCCESSFUL / UNSUCCESSFUL

UPD VARCHAR2(3) Auditing UPDATE WHENEVER SUCCESSFUL / UNSUCCESSFUL

REF CHAR(3) This column is obsolete and maintained for backward compatibility. The value of this column is always -/-

EXE VARCHAR2(3) Auditing EXECUTE WHENEVER SUCCESSFUL / UNSUCCESSFUL

FBK VARCHAR2(3) Auditing FLASHBACK WHENEVER SUCCESSFUL / UNSUCCESSFUL

REA VARCHAR2(3) Auditing READ WHENEVER SUCCESSFUL / UNSUCCESSFUL