oracle實驗記錄 （audit）
不常使用,實驗記錄下
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部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-610529/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle10g_audit_記錄業務_solarisOracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle
- oracle實驗記錄 （函式index）Oracle函式Index
- oracle實驗記錄 （bigfile tablespace）Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- oracle實驗記錄 （transport tablespace(Rman)）Oracle
- oracle實驗記錄 （使用exp/imp）Oracle
- oracle實驗記錄 （dump index b*tree）OracleIndex
- oracle實驗記錄 （CKPT的觸發）Oracle
- oracle實驗記錄 （dump undo4）Oracle
- oracle實驗記錄 （dump undo3）Oracle
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄 手工 duplicate database(1)OracleDatabase
- oracle實驗記錄Rman duplicate database(1)OracleDatabase
- oracle實驗記錄 （許可權,role）Oracle
- oracle實驗記錄 （SQL*PLUS 命令操作）OracleSQL
- oracle實驗記錄 （PFILE 啟動SPFILE）Oracle
- oracle實驗記錄 （SHARED server MODE）OracleServer
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄（恢復-checkpoint cnt）Oracle
- oracle實驗記錄 （可恢復session）OracleSession
- oracle實驗記錄 （transport tablespace(EXPDP/IMPDP)）Oracle
- oracle實驗記錄 （使用outlines）Oracle
- oracle實驗記錄 （管理outlines）Oracle
- oracle實驗記錄 （exp/imp transport tablespace）Oracle