不常使用,实验记录下
audit 对于 sysdba sysoper defautlt 都在 记录 startup,shutdown conn 等操作
win下 在事件查看器中可以看
unix 在 $ORACLE_HOME/rdbms/audit
若想启动对sysdba or sysoper 的其他审记 则
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE 改为true(default false)
audit分3种
语句:例 audit session whenever succefful (or whenever not succefful)
对象:audit insert,updata on schema.object
权限:audit insert any table by user|role(connect,dba ,resource)
by session: 按SESSION 若在同一会话中 审计同一条SQL语句,只记录一次(default)
by access:按访问,每执行一次,记录一次
whenever successful;只对成功执行的SQL语句audit
whenever not successful只对失败的sql audit
不指定whenever的话 成功失败都审计
开启audit
audit_trail string NONE
SQL>
audit_trail 为db 存在sys.aud$ (可以移动tablespace)
为os win中事件查看器,unix中 $ORACLE_HOME/rdbms/audit
none不审计(default)
实验下
SQL> conn / as sysdba
Connected.
SQL> alter system set audit_trail='DB' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_trail string DB
SQL>
SQL> audit session whenever successful; 对于session conncet,disc审计
还有很多比如 table 审计create table,drop table
SQL> desc dba_audit_session;
Name Null? Type
----------------------------------------- -------- ----------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
ACTION_NAME VARCHAR2(28)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
SESSIONID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER****************** 0 成功 1005时 输入了username 但没password 1017 时输入了错误的password
CLIENT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
SQL> select username,decode(returncode,'0','connected','1005','failed null','101
7','faild')status ,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') logon_time from db
a_audit_session;
USERNAME STATUS LOGON_TIME
------------------------------ ----------- -------------------
XH connected 2009-07-27 11:40:34
ZZ connected 2009-07-27 11:40:48
SQL> conn xh/a831115
Connected.
SQL> disc
SQL> select username,decode(returncode,'0','connected','1005','failed null','101
7','faild')status ,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') logon_time,action_
name from dba_audit_session;
USERNAME STATUS LOGON_TIME
------------------------------ ----------- -------------------
ACTION_NAME
----------------------------
XH connected 2009-07-27 11:40:34
LOGOFF
ZZ connected 2009-07-27 11:40:48
LOGOFF
XH connected 2009-07-27 11:47:54~~~~~~~~~~~~~~~~~~~~~~~~~~***********disc还有很多信息可以查比如退出时间.理逻辑读写IO等
LOGOFF
SQL> noaudit session ;~`关闭
Noaudit succeeded.
~~~~~~~~~~~~~~~权限audit
可以audit role
connect audit connect and disc 及该role 其他系统权限
resource audit,create,drop
dba:audit 需要DBA权限的命令~~grant,revoke等
实验
SQL> audit create table by xh whenever successful;
Audit succeeded.
SQL> select * from dba_priv_audit_opts;~~~~~~~~~~~~~查询设置了哪写 权限audit
USER_NAME PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
XH
CREATE TABLE BY ACCESS NOT SET
SQL> conn / as sysdba
Connected.
SQL> select username,action_name,obj_name from dba_audit_trail;~~~~~~~~~~~~所有审计条目
USERNAME ACTION_NAME
------------------------------ ----------------------------
OBJ_NAME
--------------------------------------------------------------------------------
XH LOGOFF
ZZ LOGOFF
XH LOGOFF
USERNAME ACTION_NAME
------------------------------ ----------------------------
OBJ_NAME
--------------------------------------------------------------------------------
XH LOGON
XH CREATE TABLE
XHAUDIT
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name='X
HAUDIT';
USERNAME ACTION_NAME
------------------------------ ----------------------------
OBJ_NAME
--------------------------------------------------------------------------------
XH CREATE TABLE
XHAUDIT
SQL> desc dba_audit_trail;
Name Null? Type
----------------------------------------- -------- ---------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(28)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
SCN NUMBER
SQL_BIND NVARCHAR2(2000)
SQL_TEXT NVARCHAR2(2000)
对象审计
SQL> audit insert ,update on xh.test by access;
Audit succeeded.
SQL> col owner format a10
SQL> col object_name format a10
SQL> select owner,object_name,object_type,ins,upd from dba_obj_audit_opts where~~~~~~~~~~~~~~~~~看看开启哪写 对象audit
object_name='TEST';
OWNER OBJECT_NAM OBJECT_TY INS UPD
---------- ---------- --------- ----- -----
XH TEST TABLE A/A A/A****************************
YY TEST TABLE -/- -/-
A/A a 表示启动by access s 表示by session, / 左边出现时候 whenever successful /右出现时 whenever not successful
因为例子 defalut 成功不成功都audit所以两边都有
SQL> conn xh/a831115
Connected.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
SQL> conn xh/a831115
Connected.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(38)
SQL> insert into test values(1);
1 row created.
SQL> insert into test values('a');
insert into test values('a')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> insert into test values(2);
1 row created.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~3次 insert 2次成功1次失败
SQL> col obj_name format a10
SQL> col username format a10
SQL> col action_name format a10
SQL> select username,action_name,obj_name,ses_actions,returncode from dba_audit_
object where obj_name='TEST' and wner='XH';
USERNAME ACTION_NAM OBJ_NAME SES_ACTIONS RETURNCODE
---------- ---------- ---------- ------------------- ----------
XH INSERT TEST 0~~~~~~~~~~~~~~~~~~0成功~非0失败
XH INSERT TEST 1722
XH INSERT TEST 0
by access 所以每条都记录
SQL> audit delete on xh.test by session;
Audit succeeded.
SQL> conn xh/a831115
Connected.
SQL> select * from test;
A
----------
1
1
1
1
1
2
1
2
3
1
3
A
----------
1
2
13 rows selected.
SQL> delete from test where a=1;
8 rows deleted.
SQL> delete from test where a=2;
3 rows deleted.
SQL> conn / as sysdba
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from xh.test;
A
----------
3
3
SQL> select username,action_name,obj_name,ses_actions,returncode from dba_audit_
object where obj_name='TEST' and wner='XH';
USERNAME ACTION_NAM OBJ_NAME SES_ACTIONS RETURNCODE
---------- ---------- ---------- ------------------- ----------
XH INSERT TEST 0
XH INSERT TEST 1722
XH INSERT TEST 0
XH SESSION RE TEST ---S------------ 0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2条 同样的del只有一条记录
C
SQL> conn / as sysdba
Connected.
SQL> select owner,object_name,object_type,ins,upd from dba_obj_audit_opts where
object_name='TEST';
OWNER OBJECT_NAM OBJECT_TY INS UPD
---------- ---------- --------- ----- -----
XH TEST TABLE A/A A/A
YY TEST TABLE -/- -/-
SQL> select owner,object_name,object_type,ins,upd,del from dba_obj_audit_opts wh
ere object_name='TEST';
OWNER OBJECT_NAM OBJECT_TY INS UPD DEL
---------- ---------- --------- ----- ----- -----
XH TEST TABLE A/A A/A S/S
YY TEST TABLE -/- -/- -/-
SQL> noaudit insert on xh.test;
Noaudit succeeded.
SQL> noaudit delete,update on xh.test;
Noaudit succeeded.
SQL> select owner,object_name,object_type,ins,upd,del from dba_obj_audit_opts wh
ere object_name='TEST';
OWNER OBJECT_NAM OBJECT_TY INS UPD DEL
---------- ---------- --------- ----- ----- -----
XH TEST TABLE -/- -/- -/-
YY TEST TABLE -/- -/- -/-
ALL_DEF_AUDIT_OPTS 缺省审计选项
DBA_STMT_AUDIT_OPTS 语句审计选项
DBA_PRIV_AUDIT_OPTS 权限审计选项
DBA_OBJ_AUDIT_OPTS 方案对象审计选项
DBA_AUDIT_TRAIL 所有审计线索条目
DBA_AUDIT_EXISTS 有关AUDIT EXISTS/NOT EXISTS
的记录
DBA_AUDIT_OBJECT 有关方案对象的记录
DBA_AUDIT_SESSION 所有连接和断开连接条目
DBA_AUDIT_STATEMENT 语句审计记录
管理aud$
SQL> select username,action_name,obj_name,ses_actions,returncode from dba_audit_~~~~~~~~~~~~~~~~这些记录还在
object where obj_name='TEST' and wner='XH';
USERNAME ACTION_NAM OBJ_NAME SES_ACTIONS RETURNCODE
---------- ---------- ---------- ------------------- ----------
XH INSERT TEST 0
XH INSERT TEST 1722
XH INSERT TEST 0
XH SESSION RE TEST ---S------------ 0
C
SQL> select username,action_name,obj_name,ses_actions,returncode from dba_audit_
object where obj_name='TEST' and wner='XH';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=6 Card=1 Bytes=195
)
1 0 NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=195)
2 1 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=191)
3 2 NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=186)
4 3 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=181)
5 4 TABLE ACCESS (FULL) OF 'AUD$' (TABLE) (Cost=2 Card~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~来原于 aud$
=1 Bytes=163)
6 4 INDEX (RANGE SCAN) OF 'I_AUDIT_ACTIONS' (INDEX (UN
IQUE)) (Cost=1 Card=1 Bytes=18)
7 3 INDEX (RANGE SCAN) OF 'I_SYSTEM_PRIVILEGE_MAP' (INDE
X (UNIQUE)) (Cost=1 Card=1 Bytes=5)
8 2 INDEX (RANGE SCAN) OF 'I_SYSTEM_PRIVILEGE_MAP' (INDEX
(UNIQUE)) (Cost=1 Card=1 Bytes=5)
9 1 INDEX (RANGE SCAN) OF 'I_STMT_AUDIT_OPTION_MAP' (INDEX (
UNIQUE)) (Cost=1 Card=1 Bytes=4)
create or replace view DBA_AUDIT_OBJECT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~从cataudit.sql中看的更详细(一个view)
as
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP,
OWNER, OBJ_NAME, ACTION_NAME, NEW_OWNER, NEW_NAME,
SES_ACTIONS, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID,
RETURNCODE, PRIV_USED, CLIENT_ID, SESSION_CPU,
EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER,
OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT
from dba_audit_trail
where (action between 1 and 16)
or (action between 19 and 29)
or (action between 32 and 41)
or (action = 43)
or (action between 51 and 99)
or (action = 103)
or (action between 110 and 113)
or (action between 116 and 121)
or (action between 123 and 128)
or (action between 160 and 162)
/
create or replace public synonym DBA_AUDIT_OBJECT for DBA_AUDIT_OBJECT (oracle用同义词 保护基表,以后实验中会更详细介绍 数据字典)
/
grant select on DBA_AUDIT_OBJECT to select_catalog_role
/
********************************************************************
delete any table 给 管理员
delete_catalog_role 给管理员
audit insert,update,delete on sys.aud$ by access or audit all on sys.aud$ by access;
目的 只有sysdba,sysoper才能删除aud$且对sysoper,sysdba 操作的记录到sys.aud$
另外move tablespace 还可以 create table XX as select * from sys.aud$ 然后EXP ,IMP 然后删除sys.aud$
*********************************************************用PL/SQL
DBMS_FGA~~~~~~精细audit
谓词存在sys.FGA$(dba_audit_policies),audit记录存在SYS.FGA_LOG$ (dba_fga_audit_trail) SQL.BSP创建
SQL> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT~~~~~~~~~~~~~~~~条件
AUDIT_COLUMN VARCHAR2 IN DEFAULT~~~~~~~~~~~~对的列
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT~~~~~~~~~~~audit 语句类型
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
先创建策略
SQL> exec dbms_fga.add_policy(object_schema=>'XH',object_name=>'TEST',POLICY_NAM
E=>'test_fga',AUDIT_CONDITION=>'a>=0',AUDIT_COLUMN=>'a',statement_types=>'select
,update');
PL/SQL procedure successfully completed.
SQL> desc dba_audit_policies;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~看看已经定义的策略
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_SCHEMA NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
POLICY_NAME NOT NULL VARCHAR2(30)
POLICY_TEXT VARCHAR2(4000)
POLICY_COLUMN VARCHAR2(30)
PF_SCHEMA VARCHAR2(30)
PF_PACKAGE VARCHAR2(30)
PF_FUNCTION VARCHAR2(30)
ENABLED VARCHAR2(3)
SEL VARCHAR2(3)
INS VARCHAR2(3)
UPD VARCHAR2(3)
DEL VARCHAR2(3)
AUDIT_TRAIL VARCHAR2(11)
POLICY_COLUMN_OPTIONS VARCHAR2(11)
SQL> select policy_text,policy_name,enabled,OBJECT_NAME from dba_audit_policies;
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_NAME ENA OBJECT_NAM
------------------------------ --- ----------
a>=0
TEST_FGA YES TEST
ADD一个策略后oracle自动启用(enabled)
PROCEDURE DISABLE_POLICY~~~~~~~~~~~~~~~~~关闭
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~删除~
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~启用
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
SQL> conn xh/a831115
Connected.
SQL> select * from test;
A
----------
3
3
SQL> update test set a=6;
2 rows updated.
SQL> desc dba_fga_audit_trail;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~查看结果
Name Null? Type
----------------------------------------- -------- ----------------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
CLIENT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
SCN NUMBER
SQL_TEXT NVARCHAR2(2000)
SQL_BIND NVARCHAR2(2000)
COMMENT$TEXT VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(7)
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
STATEMENTID NUMBER
ENTRYID NUMBER
SQL> col db_user format a10
SQL> col object_name format a10
SQL> col policy_name format a10
SQL> col sql_text format a40
SQL> select db_user,object_name,policy_name,sql_text from dba_fga_audit_trail;
DB_USER OBJECT_NAM POLICY_NAM SQL_TEXT
---------- ---------- ---------- ----------------------------------------
XH TEST TEST_FGA select * from test
XH TEST TEST_FGA update test set a=6
SQL> exec dbms_fga.drop_policy('xh','test','test_fga');~~~~~~~~~~~~~~~~~~~~删除
PL/SQL procedure successfully completed.
*****************************************************
*****************************************************
SQL> conn / as sysdba
Connected.
SQL> audit insert any table by xh by access;
Audit succeeded.
SQL> conn xh/a831115
Connected.
SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
XH
INSERT ANY TABLE BY ACCESS BY ACCESS
SQL> insert into t1 values(1);
1 row created.
SQL> insert into sys.test values(1);
1 row created.
SQL> insert into zz.test values(1);
insert into zz.test values(1)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into zz.test2 values(1);
1 row created.
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name in
('T1','TEST','TEST2');
USERNAME ACTION_NAME OBJ_NAME
---------- ------------------------------ ----------
XH INSERT TEST
XH INSERT TEST2~~~~~~~~~~~
可以看到没有T1的 AUDIT 记录
SQL> noaudit insert any table by xh ;~~~~~~~~~~再实验一次
Noaudit succeeded.
SQL> audit insert any table by xh by access;
Audit succeeded.
SQL> conn xh/a831115
Connected.
SQL> insert into t1 values(1);
1 row created.
SQL> insert into sys.test values(1);
1 row created.
SQL> insert into zz.test2 values(1);
1 row created.
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name in
('T1','TEST','TEST2');
USERNAME ACTION_NAME OBJ_NAME
---------- ------------------------------ ----------
XH INSERT TEST
XH INSERT TEST2
XH INSERT TEST
XH INSERT TEST2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~还是无T1 因为是BYACCESS 所以每句都有
记录
SQL> noaudit insert any table by xh ;
Noaudit succeeded.
SQL> audit insert table by xh by access;
Audit succeeded.
SQL> conn xh/a831115
Connected.
SQL> insert into t1 values(1);
1 row created.
SQL> insert into sys.test values(1);
1 row created.
SQL> insert into zz.test2 values(1);
1 row created.
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name in
('T1','TEST','TEST2');
USERNAME ACTION_NAME OBJ_NAME
---------- ------------------------------ ----------
XH INSERT TEST
XH INSERT TEST2
XH INSERT TEST
XH INSERT TEST2
XH INSERT T1~~~~~~~~~~~~~~~~~~~~~用insert table就可以了
XH INSERT TEST
XH INSERT TEST2
7 rows selected.
SQL> insert into t1 values(11);
1 row created.
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name in
('T1','TEST','TEST2');
USERNAME ACTION_NAME OBJ_NAME
---------- ------------------------------ ----------
XH INSERT TEST
XH INSERT TEST2
XH INSERT TEST
XH INSERT TEST2
XH INSERT T1
XH INSERT TEST
XH INSERT TEST2
XH INSERT T1
8 rows selected.
SQL>
SQL> noaudit insert table by xh ;
Noaudit succeeded.
SQL> conn / as sysdba
Connected.
SQL> create table t2(a int);
Table created.
SQL> conn xh/a831115
Connected.
SQL> create table t2(a int);
SQL> conn / as sysdba
Connected.
SQL> audit insert any table by xh by access;
Audit succeeded.
SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
XH
INSERT ANY TABLE BY ACCESS BY ACCESS
SQL> conn xh/a831115
Connected.
SQL> insert into t2 values(11);
1 row created.
SQL> insert into sys.t2 values(11);
1 row created.
SQL> conn / as sysdba
Connected.
SQL> select username,action_name,obj_name from dba_audit_trail where obj_name='T
2';
USERNAME ACTION_NAME OBJ_NAME
---------- ------------------------------ ----------
XH INSERT T2
2个 T2属于2个schema,但 AUDIT 只在不是 XH SCHEMA中对T2 AUDIT成功
SQL> noaudit insert any table by xh;
Noaudit succeeded.
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> audit insert table by xh by access;
Audit succeeded.
SQL> select * from dba_priv_audit_opts;
no rows selected~~~~~~~~~~~~~~~~~~~insert table 显示不出来
SQL> select * from dba_stmt_audit_opts;~~~~~~~~~~在这个view中
USER_NAME PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
XH
INSERT TABLE BY ACCESS BY ACCESS
dba_stmt_audit_opts:包含user设置的系统audit选项(查 现有的语句级audit)
insert table 是语句级audit
SQL> select * from dba_priv_audit_opts;
no rows selected
小结 对于 权限audit insert any table 并不能审查 自己schema中对象 (例中为XH SCHEMA中对象)
要用 语句级别inset table 或者 对象audit (对 XH.T1,XH.T2);
SQL> conn asda/asd@xh as sysdba
Connected.
SQL> audit insert ,update on xh.test by access;
Audit succeeded.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> col owner format a10
SQL> col object_name format a10
SQL> select owner,object_name,object_type,ins,upd from dba_obj_audit_opts where
object_name='TEST';
OWNER OBJECT_NAM OBJECT_TY INS UPD
---------- ---------- --------- ----- -----
XH TEST TABLE A/A A/A
SQL> col sql_text format a30
SQL> select username,action_name,obj_name,sql_text,returncode from dba_audit_obj
ect where obj_name='TEST' and wner='XH';
USERNAME ACTION_NAM OBJ_NAME SQL_TEXT RETURNCODE
---------- ---------- ---------- ------------------------------ ----------~~~~~~~~~~没有
SQL_TEXT
XH INSERT TEST 0
XH INSERT TEST 0
SQL> alter system set audit_trail='db_extended' scope=spfile;
System altered.
SQL> startup force
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 788672 bytes
Variable Size 145487680 bytes
Database Buffers 167772160 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_trail string DB_EXTENDED
SQL>
SQL> conn xh/a831115
Connected.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(4);
1 row created.
SQL> select username,action_name,obj_name,sql_text,returncode from dba_audit_obj
ect where obj_name='TEST' and wner='XH';
USERNAME ACTION_NAM OBJ_NAME SQL_TEXT RETURNCODE
---------- ---------- ---------- ------------------------------ ----------
XH INSERT TEST 0
XH INSERT TEST 0
XH INSERT TEST insert into test values(1) 0
XH INSERT TEST insert into test values(4) 0
audit_trail = db_extended
如果使用该参数，该参数将在各列中记录 SQL 文本和绑定变量值。该值在早期的版本中不可用。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-610529/ ，如需转载，请注明出处，否则将追究法律责任。
