oracle audit system,oracle 审计 audit 学习笔记

配置audit

AUDIT_TRAIL参数选项：

--> DB/TRUE enables systemwide auditing where audited records are written to the

database audit trail(the SYS.AUD$ table). The only audit data that will not

be written to the table is the audit data pertaining to the activities of SYSDBA.

--> OS enables systemwide auditing where audit data is written to text files

into the directory specified by the AUDIT_FILE_DEST parameter. This is true for both

privileged and ordinary database users.

--> DB,EXTENDED(DB_EXTENDED) enables systemwide auditing as DB/TRUE does. In addition, it

populates the SQLTEXT and SQLBIND CLOB columns of the SYS.AUD$ table.

--> XML enables systemwide auditing. The audit data will be written to XML files into the

directory specified by the AUDIT_FILE_DEST parameter.

--> XML,EXTENDED(XML_EXTENDED) enables systemwide auditing. It behaves  It also populates

the SQLBIND and SQLTEXT columns.

配置审计需要重启实例

SQL> alter system set AUDIT_TRAIL = DB scope=spfile;

SQL> shutdown immediate

SQL> startup

如果审计表aud$不存在,需要手工创建

SQL> conn / as sysdba

SQL> @?/rdbms/admin/cataudit.sql

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

手工移动审计表所在表空间

alter table AUD$ move tablespace AUD;

alter table aud$ move lob (sqlbind) store as (tablespace );

alter table aud$ move lob (sqltext) store as (tablespace );

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

移动审计表所在表空间(对于10.2.0.5以上版本)

To move the table to a locally managed tablespace with ASSM and then shrink it do the following:

1)

conn / as sysdba

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

audit_trail_location_value => 'USERS');

END;

/

2)

alter table sys.aud$ enable row movement;

alter table sys.aud$ shrink space cascade;

3)If needed the table can be moved back to the SYSTEM tablespace:

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

audit_trail_location_value => 'SYSTEM');

END;

/

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

将审计表移出system表空间的脚本

Script to move SYS.AUD$ table out of SYSTEM tablespace [ID 1019377.6]

---Restart the database with audit_trail=NONE before running the script ---

create tablespace "AUDIT"

datafile '$HOME/data/aud01.dbf' size 500k

default storage (initial 100k next 100k pctincrease 0)

/

create table audx tablespace "AUDIT"

storage (initial 50k next 50k pctincrease 0)

as select * from aud$ where 1 = 2

/

rename AUD$ to AUD$$

/

rename audx to aud$

/

create index i_aud2

on aud$(sessionid, ses$tid)

tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)

/

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

如何检测潜在的登录攻击

数据库启动审计

开启审计

SQL>AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

SQL>AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

过一段时间，检查审计结果

select returncode, action#, userid, userhost, terminal from aud$ where returncode='1017' and action=100;

RETURNCODE ACTION#    USERID   USERHOST             TERMINAL

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

1017       100        SCOTT    WPRATA-BR

1017       100        SCOTT    WPRATA-BR

1017       100        SCOTT    WPRATA-BR

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

审计速查

Quick Reference to Auditing Information

Database Audit mode

~~~~~~~~~~~~~~~~~~~

show parameter audit

AUDIT_TRAIL   --> DB, DB_EXTENDED, OS, XML, XML_EXTENDED, FALSE or NONE

AUDIT_FILE_DEST --> Audit File location

AUDIT_SYS_OPERATIONS --> Controls whether the activities of SYSDBA are audited or not.

AUDIT_SYSLOG_LEVEL    --> specifies a SYSLOG facility that will receive the audit information

What Statements are being audited ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To set audit:

AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

select * from dba_stmt_audit_opts where USER_NAME='...';

Columns are:

AUDIT_OPTION from STMT_AUDIT_OPTION_MAP

SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'

FAILURE ""

What Privileges are being audited ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To set audit:

AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

select * from dba_priv_audit_opts where USER_NAME='...';

Columns are:

PRIVILEGEfrom SYSTEM_PRIVILEGE_MAP

SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'

FAILURE ""

What Objects are being audited ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To set Auditing:

AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS]

[WHENEVER {NOT} SUCCESSFUL]

select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...';

select * from all_def_audit_opts;

Columns are:

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

X/Y - is no option set

X is when successful

Y is when Unsuccessful

S set by session

A set by access

Audit results

~~~~~~~~~~~~~

Raw results can go to various places depending on the value of parameter AUDIT_TRAIL:

- when audit_trail is DB or DB_EXTENDED the audit data will go to AUD$ (DBA_AUDIT_TRAIL is a view on top of this table ).

Main where columns are: USERNAME, TIMESTAMP, OWNER

- when audit_trail is OS or XML or XML_EXTENDED the audit data will be written to files located in the AUDIT_FILE_DEST directory

- when AUDIT_SYSLOG_LEVEL is defined and audit_trail is set to OS the audit data will be sent to SYSLOG

For underlying results see:

Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;

Auditing administrative connections

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER) are always logged regardless of audit setting.

On UNIX platforms these are logged to *.aud files in $ORACLE_HOME/rdbms/audit when the instance is stopped and to AUDIT_FILE_DEST

when the instance is started regardless of any init.ora parameter settings. See Note 103964.1 for more details.

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

例子

audit CREATE TABLE by scott;

audit CREATE TABLE, CREATE VIEW, ALTER USER;

audit INDEX;  --包括CREATE INDEX, DROP INDEX, ALTER INDEX and ANALYZE INDEX

audit INDEX by scott;

audit ALL whenever SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

audit select any table;

audit select any table, delete any table by scott, system;

audit select on SCOTT.EMP whenever successful;

audit delete on SCOTT.EMP by access;

audit ALL on SCOTT.EMP;

audit select on DEFAULT;

AUDIT NETWORK;

AUDIT ROLE WHENEVER NOT SUCCESSFUL;

AUDIT CREATE ANY DIRECTORY;

阅读(8809) | 评论(0) | 转发(1) |