ORACLE audit summary

1. What is audit? Audit (Audit) is used to monitor database operations performed by users, and Oracle stores audit trail resultsOS file(The default location is $ oracle_base/admin/$ oracle_sid/adump/) orDatabase(Stored in the system tablespaceSYS. AUD $Table, which can be viewed through the dba_audit_trail view.Audit is disabled by default. Whether or not you enable the database audit function, the following operating systems will forcibly record: Use the Administrator permission to connect to the Instance, start the database, and close the database. 2. audit-related parameters Audit_sys_operations: The default value is false. When it is set to true, all operations on Sys users (including those logged on as sysdba and sysoper) will be recorded,Audit Trail is not written in the AUD $ table. It is easy to understand that if the database has not started AUD $, connection information such as Conn/As sysdba can only be recorded elsewhere. If it is a Windows platform, aupd-trail will be recorded in Windows event management. If it is a Linux/Unix platform, it will be recorded in the file specified by the audit_file_dest parameter. Audit_trail: None: the default value. No audit is performed; DB: records the audit trail in the audit-related tables of the database, such as AUD $. The audit results only contain connection information; DB, extended: in this way, in addition to the connection information, the audit results also contain the specific statements executed at that time; OS: records the audit trail in the operating system file. The file name is specified by the audit_file_dest parameter; XML: New in 10 Gb. Note: These two parameters are static and need to be restarted to take effect. 3. Audit Level After the audit function is enabled, You can audit the database at three levels:Statement (statement),Privilege (permission),Object ). Statement: Audit by statement. For example, audit table audits all the create table, drop table, and truncate table statements in the database. Alter session by cmy audits all database connections of cmy users. Privilege: Audit by permission. If the user uses this permission, the user is audited. For example, after the grant select any table to a statement is executed and the audit select any table statement is executed, when user a accesses user B's table (for example, select * from B. t) The select any table permission is used, so it will be audited. Note that you are the owner of your table, soUsers accessing their own tables will not be audited. Object: Audits by object. Only the on keyword is used to audit the operations on the specified object, such as aduit alter, delete, drop, and insert on cmy. t by scott; here, the t table of cmy users is audited, but the by clause is used at the same time, so only operations initiated by scott users are audited. Note that Oracle does not provide the audit function for all objects in the schema. Only one object can be audited. For the objects created later, Oracle provides the on default clause for automatic audit, for example, after you execute audit drop on default by access;, all the drop operations on the subsequently created objects will be audited. However, this default option will be valid for all the database objects created later. It seems that it is impossible to specify that it is only valid for the objects created by a user. In this case, the trigger can "Audit" the schema DDL ", this function is slightly insufficient. 4. Other Audit options By access/by session: By access generates an audit trail for each audited operation. By session: Only one audit trail is generated for operations of the same type in a session. The default value is by session. Whenever [not] successful: When the whenever successful operation is successful (the returncode field in dba_audit_trail is 0, Whenever not successful. If this clause is omitted, it will be audited whether the operation is successful or not. 5. audit-related views Dba_audit_trail:Saves all audit trails. In fact, it is only a view based on aud $. Other views dba_audit_session, dba_audit_object, and dba_audit_statement are only a subset of dba_audit_trail. Dba_stmt_audit_opts: it can be used to view the statement audit-level audit options, that is, the statement-level audit set by the database. Dba_obj_audit_opts, dba_priv_audit_opts view functions are similar All_def_audit_opts: used to check which default Object audit is set in the on default clause of the database. 6. Cancel Audit Change the audit statement of the corresponding audit statement to noaudit. For example, the audit statement of the audit session whenever successful is noaudit session whenever successful; 7. Audit in 10 Gb tells everything Oracle Database 10g audit captures user behavior at a very detailed level, which can eliminate manual, trigger-based audit. Assume that user Joe has the permission to update the table and updates a row of data in the table as follows: Updated SCOTT. EMP set salary = 12000 where empno = 123456; How do you track such behavior in the database? In Oracle 9i Database and earlier versions, audit can only capture "who" to perform this operation, but cannot capture what "to execute. For example, it lets you know that Joe updated all SCOTT's tables EMP, but it does not show that he updated the table's salary column with employee number 123456. It does not display the value of the salary column before the change-to capture such detailed changes, you will have to write your own trigger to capture the value before the change, or use LogMiner to retrieve them from the archived logs. Fine-grained auditing (FGA) is introduced in Oracle 9i. It can record SCN numbers and row-level changes to recreate old data, but they can only be used for select statements, not DML, such as update, insert, and delete statements. Therefore, for Oracle database versions earlier than 10 Gb, using triggers is the only reliable method, although it is not attractive for tracking users' initial changes at the row level. 8. instance description 8.1 activate Audit SQL> conn/as sysdba SQL> show parameter audit NAME TYPE VALUE ----------------------------------------------------------------------------- Audit_file_dest string/u01/app/oracle/admin/ORCL/adump Audit_sys_operations boolean FALSE Audit_syslog_level string Audit_trail string NONE SQL> alter system set audit_sys_operations = TRUE scope = spfile; -- Audit Management User (log in as sysdba/sysoper) SQL> alter system set audit_trail = db, extended scope = spfile; SQL> startup force; SQL> show parameter audit Name type value ----------------------------------------------------------------------------- Audit_file_dest string/u01/APP/Oracle/admin/orcl/adump Audit_sys_operations Boolean true Audit_syslog_level string Audit_trail string dB, extended 8.2 start auditing SQL> Conn/As sysdba SQL> audit all on t_test; SQL> conn u_test SQL> select * From t_test; SQL> insert into u_test.t_test (C2, C5) values ('test1', '2 '); SQL> commit; SQL> Delete from u_test.t_test; SQL> commit; SQL> Conn/As sysdba SQL> Col dest_name format A30 Col OS _username format A15 Col username format A15 Col Userhost format A15 Col terminal format A15 Col obj_name format A30 Col SQL _text format A60 SQL> select OS _username, username, Userhost, terminal, timestamp, owner, obj_name, action_name, sessionid, OS _process, SQL _text from dba_audit_trail; SQL> Audit select table by u_test by access; If by user is added after the command, only user operations are audited. If by user is omitted, all users in the system are audited (excluding sys users ). Example: Audit delete any table; -- Audit Delete table operations Audit delete any table whenever not successful; -- only audit deletion failures Audit delete any table whenever successful; -- only audit deletion success Audit Delete, update, insert on user. Table by test; -- Audit The delete, update, and insert operations on the table user. Table by test. 8.3 audit Revocation SQL> noaudit all on t_test; 9. audit statement Audit in a multi-layer environment: appserve-application server, Jackson-Client Audit select table by appserve on behalf of Jackson; Audit connection or disconnect: Audit Session; Audit Session by Jeff, Lori; -- specifies the user Audit permission (operations that can be performed only with this permission ): Audit delete any table by access whenever not successful; Audit delete any table; Audit select table, insert table, delete table, execute procedure by access whenever not successful; Object audit: Audit delete on Jeff. EMP; Audit select, insert, delete on jward. Dept by access whenever successful; Cancel audit: Noaudit session; Noaudit session by Jeff, Lori; Noaudit delete any table; Noaudit select table, insert table, delete table, execute procedure; Noaudit all; -- Cancel all statement audits Noaudit all privileges; -- Cancel all permission Audit Noaudit all on default; -- Cancel all object audits 10. Clear audit information Delete from SYS. AUD $; Delete from SYS. AUD $ where OBJ $ name = 'emp '; 11. Audit View Stmt_audit_option_map -- Audit option type code Audit_actions -- Action Code All_def_audit_opts -- default Object audit option when an object is created Dba_stmt_audit_opts -- Current Database System Audit Option Dba_priv_audit_opts -- permission audit Option Dba_obj_audit_opts User_obj_audit_opts -- Object audit Option Dba_audit_trail User_audit_trail -- audit record Dba_audit_object User_audit_object -- List of audit objects Dba_audit_session User_audit_session -- session Audit Dba_audit_statement USER_AUDIT_STATEMENT -- Statement Audit DBA_AUDIT_EXISTS -- AUDIT using the by audit not exists option DBA_AUDIT_POLICIES -- Audit POLICIES DBA_COMMON_AUDIT_TRAIL -- standard audit + fine Audit 12. Move the audit result table from the system tablespace to another tablespace. Actually, the sys. aud $ table contains two lob fields, which is not a simple move table. The specific process is as follows: Alter table sys. aud $ move tablespace users; Alter table sys. aud $ move lob (sqlbind) store as (tablespace USERS ); Alter table sys. aud $ move lob (SQLTEXT) store as (tablespace USERS ); Alter index sys. I _AUD1 rebuild tablespace users;