执行了以下红色代码后
ALTER SYSTEM SET AUDIT_TRAIL='XML';audit delete on system.aa1 by access whenever successful;delete from system.aa1 where ...;select * from V$XML_AUDIT_TRAIL;看到的是:
AUDIT_TYPE SESSION_ID PROXY_SESSIONID STATEMENTID ENTRYID
---------------------- ---------- --------------- ----------- ----------
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
GLOBAL_UID DB_USER
-------------------------------- ------------------------------
CLIENT_ID
..........
AUDIT_TYPE SESSION_ID PROXY_SESSIONID STATEMENTID ENTRYID
---------------------- ---------- --------------- ----------- ----------
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
GLOBAL_UID DB_USER
-------------------------------- ------------------------------
CLIENT_ID
----------------------------------------------------------------
ECONTEXT_ID
----------------------------------------------------------------
EXT_NAME
--------------------------------------------------------------------------------
OS_USER
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
OS_PROCESS
----------------
TERMINAL
--------------------------------------------------------------------------------
INSTANCE_NUMBER OBJECT_SCHEMA
--------------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
POLICY_NAME NEW_OWNER
------------------------------ ------------------------------
NEW_NAME
--------------------------------------------------------------------------------
ACTION STATEMENT_TYPE AUDIT_OPTION
---------- ---------------------------- ----------------------------------------
TRANSACTIONID RETURNCODE SCN
---------------- ---------- ----------
COMMENT_TEXT
--------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
OBJ_PRIVILEGE SYS_PRIVILEGE AD OS_PRIV
---------------- ---------------------------------------- -- -------
GRANTEE PRIV_USED
------------------------------ ----------------------------------------
SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE
------------------- -------------- ------------ ------------ -------------
LOGOFF_DLOCK SESSION_CPU
---------------------------------------- -----------
已选择42行。看不出什么内容,而直接打开adump里面的一个XML文件,却能看到记录,但是看起来很不方便,咋整?
<?xml version="1.0" encoding="UTF-8"?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd">
<Version>10.2</Version>
<AuditRecord><Audit_Type>8</Audit_Type><EntryId>1</EntryId><Extended_Timestamp>2010-12-30T10:17:19.000000</Extended_Timestamp><DB_User>/</DB_User><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Terminal>MICROSOF-3D3874</Terminal><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
CONNECT
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>2</StatementId><EntryId>2</EntryId><Extended_Timestamp>2010-12-30T10:18:28.046000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
[color=#FF0000]audit delete on system.aa1 by access whenever successful
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>3</StatementId><EntryId>3</EntryId><Extended_Timestamp>2010-12-30T10:20:28.421000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
delete from system.aa1 where aa=888
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>4</StatementId><EntryId>4</EntryId><Extended_Timestamp>2010-12-30T10:20:54.875000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
select * from v$xml_audit_trail
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>5</StatementId><EntryId>5</EntryId><Extended_Timestamp>2010-12-30T10:26:55.078000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
select * from dba_audit_trail
</Sql_Text>
</AuditRecord>
</Audit>[/color]
ALTER SYSTEM SET AUDIT_TRAIL='XML';audit delete on system.aa1 by access whenever successful;delete from system.aa1 where ...;select * from V$XML_AUDIT_TRAIL;看到的是:
AUDIT_TYPE SESSION_ID PROXY_SESSIONID STATEMENTID ENTRYID
---------------------- ---------- --------------- ----------- ----------
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
GLOBAL_UID DB_USER
-------------------------------- ------------------------------
CLIENT_ID
..........
AUDIT_TYPE SESSION_ID PROXY_SESSIONID STATEMENTID ENTRYID
---------------------- ---------- --------------- ----------- ----------
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
GLOBAL_UID DB_USER
-------------------------------- ------------------------------
CLIENT_ID
----------------------------------------------------------------
ECONTEXT_ID
----------------------------------------------------------------
EXT_NAME
--------------------------------------------------------------------------------
OS_USER
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
OS_PROCESS
----------------
TERMINAL
--------------------------------------------------------------------------------
INSTANCE_NUMBER OBJECT_SCHEMA
--------------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
POLICY_NAME NEW_OWNER
------------------------------ ------------------------------
NEW_NAME
--------------------------------------------------------------------------------
ACTION STATEMENT_TYPE AUDIT_OPTION
---------- ---------------------------- ----------------------------------------
TRANSACTIONID RETURNCODE SCN
---------------- ---------- ----------
COMMENT_TEXT
--------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
OBJ_PRIVILEGE SYS_PRIVILEGE AD OS_PRIV
---------------- ---------------------------------------- -- -------
GRANTEE PRIV_USED
------------------------------ ----------------------------------------
SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE
------------------- -------------- ------------ ------------ -------------
LOGOFF_DLOCK SESSION_CPU
---------------------------------------- -----------
已选择42行。看不出什么内容,而直接打开adump里面的一个XML文件,却能看到记录,但是看起来很不方便,咋整?
<?xml version="1.0" encoding="UTF-8"?>
<Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd">
<Version>10.2</Version>
<AuditRecord><Audit_Type>8</Audit_Type><EntryId>1</EntryId><Extended_Timestamp>2010-12-30T10:17:19.000000</Extended_Timestamp><DB_User>/</DB_User><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Terminal>MICROSOF-3D3874</Terminal><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
CONNECT
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>2</StatementId><EntryId>2</EntryId><Extended_Timestamp>2010-12-30T10:18:28.046000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
[color=#FF0000]audit delete on system.aa1 by access whenever successful
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>3</StatementId><EntryId>3</EntryId><Extended_Timestamp>2010-12-30T10:20:28.421000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
delete from system.aa1 where aa=888
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>4</StatementId><EntryId>4</EntryId><Extended_Timestamp>2010-12-30T10:20:54.875000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
select * from v$xml_audit_trail
</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>5</StatementId><EntryId>5</EntryId><Extended_Timestamp>2010-12-30T10:26:55.078000</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>MICROSOF-3D3874\new</Ext_Name><OS_User>MICROSOF-3D3874\new</OS_User><Userhost>WORKGROUP\MICROSOF-3D3874</Userhost><OS_Process>1900:1624</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OS_Privilege>SYSDBA</OS_Privilege>
<Sql_Text>
select * from dba_audit_trail
</Sql_Text>
</AuditRecord>
</Audit>[/color]
用plsql developer工具
哦,多谢了,果然是这样那为什么select * from dba_audit_trail;查不到任何内容?dba_audit_trail是查什么东西的?
OK,我试试另外请教个问题:为什么我删除了一行数据后,执行select * from v$xml_audit_trail;,关于这次删除的大多数记录都能看到(比如,用户,主机名等等),但是就是操作看不到,也就是delete from ......这条语句没记录的,是什么原因?
提示“ORA-02095: 无法修改指定的初始化参数”
alter system set audit_trail =DB scope =spfile;
怎么操作的。。我这里可以。。
....但并没有数据
insert into bb (1)
*
第 1 行出现错误:
ORA-00928: 缺失 SELECT 关键字
SQL> insert into bb values(1);已创建 1 行。SQL> commit;提交完成。SQL> insert into bb values(2);已创建 1 行。SQL> commit;提交完成。SQL>
-----注意上下两个动作交付进行SQL> alter system set audit_trail ='DB' scope =spfile;系统已更改。SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。Total System Global Area 192937984 bytes
Fixed Size 1248044 bytes
Variable Size 138413268 bytes
Database Buffers 50331648 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> audit insert on aa;审计已成功。SQL> insert into aa values('1');已创建 1 行。SQL> commit;提交完成。SQL> select count(*) from sys.aud$; COUNT(*)
----------
0SQL> audit insert on scott.bb by access;审计已成功。SQL> select count(*) from sys.aud$; COUNT(*)
----------
3SQL>
哦,我用的是system下的表以及普通用户的表,没用sys的