oracle 审计 audit 学习笔记-凯发app官方网站

配置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:
privilege from 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;

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

上一篇：oracle动态视图v$archived_log中name为空

下一篇：oms 12.1.0.4 最新版patch安装后结果

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们