可以用审计,即audit 先要设初始化参数,在参数文件中 audit_trail = db 即允许进行审计,且审计结果记录在数据库中. 审计的结果在dba_audit_trail,user_audit_trail中查下面是例子,建议还是去看一下文档,sql reference中的auditAuditing SQL Statements Relating to Roles: ExampleTo choose auditing for every SQL statement that creates, alters, drops, or sets a role, regardless of whether the statement completes successfully, issue the following statement:AUDIT ROLE; To choose auditing for every statement that successfully creates, alters, drops, or sets a role, issue the following statement:AUDIT ROLE WHENEVER SUCCESSFUL; To choose auditing for every CREATE ROLE, ALTER ROLE, DROP ROLE, or SET ROLE statement that results in an Oracle error, issue the following statement:AUDIT ROLE WHENEVER NOT SUCCESSFUL; Auditing Query and Update SQL Statements: ExampleTo choose auditing for any statement that queries or updates any table, issue the following statement:AUDIT SELECT TABLE, UPDATE TABLE; To choose auditing for statements issued by the users hr and oe that query or update a table or view, issue the following statement:AUDIT SELECT TABLE, UPDATE TABLE BY hr, oe; Auditing Deletions: ExampleTo choose auditing for statements issued using the DELETE ANY TABLE system privilege, issue the following statement:AUDIT DELETE ANY TABLE; Auditing Statements Relating to Directories: ExamplesTo choose auditing for statements issued using the CREATE ANY DIRECTORY system privilege, issue the following statement:AUDIT CREATE ANY DIRECTORY;To choose auditing for CREATE DIRECTORY (and DROP DIRECTORY) statements that do not use the CREATE ANY DIRECTORY system privilege, issue the following statement:AUDIT DIRECTORY;To choose auditing for every statement that reads files from the bfile_dir directory, issue the following statement:AUDIT READ ON DIRECTORY bfile_dir;Auditing Queries on a Table: ExampleTo choose auditing for every SQL statement that queries the employees table in the schema hr, issue the following statement:AUDIT SELECT ON hr.employees; To choose auditing for every statement that successfully queries the employees table in the schema hr, issue the following statement:AUDIT SELECT ON hr.employees WHENEVER SUCCESSFUL; To choose auditing for every statement that queries the employees table in the schema hr and results in an Oracle error, issue the following statement:AUDIT SELECT ON hr.employees WHENEVER NOT SUCCESSFUL; Auditing Inserts and Updates on a Table: ExampleTo choose auditing for every statement that inserts or updates a row in the customers table in the schema oe, issue the following statement:AUDIT INSERT, UPDATE ON oe.customers; Auditing Operations on a Sequence: ExampleTo choose auditing for every statement that performs any operation on the employees_seq sequence in the schema hr, issue the following statement:AUDIT ALL ON hr.employees_seq; The preceding statement uses the ALL shortcut to choose auditing for the following statements that operate on the sequence: * ALTER SEQUENCE * AUDIT * GRANT * any statement that accesses the sequence's values using the pseudocolumns CURRVAL or NEXTVALSetting Default Auditing Options: ExampleThe following statement specifies default auditing options for objects created in the future:AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE ON DEFAULT;
先要设初始化参数,在参数文件中
audit_trail = db
即允许进行审计,且审计结果记录在数据库中.
审计的结果在dba_audit_trail,user_audit_trail中查下面是例子,建议还是去看一下文档,sql reference中的auditAuditing SQL Statements Relating to Roles: ExampleTo choose auditing for every SQL statement that creates, alters, drops, or sets a role, regardless of whether the statement completes successfully, issue the following statement:AUDIT ROLE; To choose auditing for every statement that successfully creates, alters, drops, or sets a role, issue the following statement:AUDIT ROLE
WHENEVER SUCCESSFUL; To choose auditing for every CREATE ROLE, ALTER ROLE, DROP ROLE, or SET ROLE statement that results in an Oracle error, issue the following statement:AUDIT ROLE
WHENEVER NOT SUCCESSFUL; Auditing Query and Update SQL Statements: ExampleTo choose auditing for any statement that queries or updates any table, issue the following statement:AUDIT SELECT TABLE, UPDATE TABLE; To choose auditing for statements issued by the users hr and oe that query or update a table or view, issue the following statement:AUDIT SELECT TABLE, UPDATE TABLE
BY hr, oe; Auditing Deletions: ExampleTo choose auditing for statements issued using the DELETE ANY TABLE system privilege, issue the following statement:AUDIT DELETE ANY TABLE; Auditing Statements Relating to Directories: ExamplesTo choose auditing for statements issued using the CREATE ANY DIRECTORY system privilege, issue the following statement:AUDIT CREATE ANY DIRECTORY;To choose auditing for CREATE DIRECTORY (and DROP DIRECTORY) statements that do not use the CREATE ANY DIRECTORY system privilege, issue the following statement:AUDIT DIRECTORY;To choose auditing for every statement that reads files from the bfile_dir directory, issue the following statement:AUDIT READ ON DIRECTORY bfile_dir;Auditing Queries on a Table: ExampleTo choose auditing for every SQL statement that queries the employees table in the schema hr, issue the following statement:AUDIT SELECT
ON hr.employees; To choose auditing for every statement that successfully queries the employees table in the schema hr, issue the following statement:AUDIT SELECT
ON hr.employees
WHENEVER SUCCESSFUL; To choose auditing for every statement that queries the employees table in the schema hr and results in an Oracle error, issue the following statement:AUDIT SELECT
ON hr.employees
WHENEVER NOT SUCCESSFUL; Auditing Inserts and Updates on a Table: ExampleTo choose auditing for every statement that inserts or updates a row in the customers table in the schema oe, issue the following statement:AUDIT INSERT, UPDATE
ON oe.customers; Auditing Operations on a Sequence: ExampleTo choose auditing for every statement that performs any operation on the employees_seq sequence in the schema hr, issue the following statement:AUDIT ALL
ON hr.employees_seq; The preceding statement uses the ALL shortcut to choose auditing for the following statements that operate on the sequence: * ALTER SEQUENCE
* AUDIT
* GRANT
* any statement that accesses the sequence's values using the pseudocolumns CURRVAL or NEXTVALSetting Default Auditing Options: ExampleThe following statement specifies default auditing options for objects created in the future:AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
ON DEFAULT;
用noaudit加上与上面audit中对应的语句
我的意思是,有一个表被删除了,但我不知道是谁和在什么时候删除的,我想这个删除事件在Oracle中一定有记录,我现在需要做的事情是把“drop table”这个事件查找出来,让我知道是在什么时候发生,最好还知道是从哪一个IP发出的指令。
就是这样,劳繁各位指教!
删除表的操作在昨天做的,现在在v$sql和v$sqlarea中找不到这条"drop table"命令,我想可能已经退出sql缓冲区了
用Redo Log Files 可以吗?
我的Oracle是8.0.5版,好像没有Logminer工具,有其他的方法吗?