配置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) |
转播
