I. What is a database audit? Database audit, is the activity of the database to do tracking records, mainly including database connection, SQL statement execution, database object access to these aspects of tracking records. Second, the memory of the audit record is divided into two kinds: one is stored in the operating system files, one is stored in the system table space in the sys.aud$ table. Third, the consideration of the effect of database performance audit must occupy the CPU, therefore, need to balance the audit needs and performance of the problem, in order to determine the best trial Xu strategy. Iv. What information is included in the results of the audit? There are two ways to store the database storage and file storage of the results. For the case of database storage, SYS. The aud$ table contains the following information: 1) operating system User Name 2) Database user name 3) connection session ID 4) Terminal ID 5) Accessed schema object name 6) attempted operation 7) Full code for Operation 8) date and time stamp If the audit record is stored in an external operations file, the file may contain the following information: 1) Audit records generated by the operating system 2) Audit record of the database 3) audited Database operations 4) Audit record for Super Administrator (SYS) where the audit record written to the file is encodedmethod, if you want to understand the information corresponding to these encodings, you can query the following data dictionary table: the Encoded Information data dictionary table-------------------------------The database operation code represents the attempted operation. Its related description can be queried from the Data dictionary table audit_actions table the permissions used by the operation can be queried in the dictionary table System_privilege_map to the corresponding instructions the complete operation code will return 0 when the operation succeeds, and the associated Oracle error information is returned on failure. The description of the error message code can be obtained from the Oracle official documentation-------------------------------- Five, the default audit regardless of whether the audit function is currently turned on, The database writes some database related operations to the external audit file (note: Not written to the sys.aud$ table), which are audited by default: 1) Connect to the database with Super Administrator privileges (CONNECTASSYSDBA or Connectassysoper) 2) database startup 3) Database stop VI, An audit of the behavior of the Super Administrator user The Super Administrator user refers to the user who connects the database in ASSYSDBA or Assysoper mode. The initialization parameter audit_sys_operations is used to specify an audit option for the Super Administrator, and if Audit_sys_operations is set to True, all super administrator actions will be audited, regardless of whether there is currently an audit enabled, And all audit information is written to an external audit file (note: Not written to the sys.aud$ table). Implementation Audit 1. Setting the location initialization parameter of audit record audit_trail specifies the location of the audit record, which has three values: 1) DB audit records are stored in the sys.aud$ table of the database 2) OS audit records are stored in external operating system files 3) None close Audit (default) initialization parameter audit_file_dest specifies the destination storage path for the external auditing file, whose default value is $oracle_home/rdbms/ Audit2. Audit Options This audit option refers to the optional audit statement that performs the audit. For the syntax of the audit statement, refer to Oracle's official SQL Reference. 3. An example of audit a. Audit the creation and end of a user's session auditsessionbyscott,fey;b. Audit the creation and end of sessions for all users auditsession; C. Audit Delete table Operation auditdeleteanytable;c. Audit Delete table operation (limit: Audit delete failure only) auditdeleteanytablewhenevernotsuccessful;d. Audit Delete Table operations ( Limit: Audit Delete success only) auditdeleteanytablewheneversuccessful;e. Audit Delete Table operations (restriction: The same action statement in the same session is audited only once) Auditdeleteanytablebysession; (note: In this case, if the audit record is set to be stored in an external file, this effect is not reflected because Oracle cannot determine whether the same action statement has been audited.) F. Audit delete table operation (limit: Each DELETE statement is audited, regardless of whether there are more than one operation statement in the same session) Auditdeleteanytablebyaccess; G. Auditing the delete operation of the Fey.employee table auditdeleteonfey.employee;h. Audit the Delete, Updet, and insert operations of the Fey.employee table Auditdelete, update,insertonfey.employee; Audit Stop Audit use NOAUDIT statement, the syntax of the statement refer to the Oracle official SQL Reference. The following example illustrates the use of Noaudit: A. Stop all audit noauditsession;b for session creation and completion. Stop the audit Noauditsessionbyfey,scott of the creation and end of the session for the user Fey,scott; C. Stop auditing the Delete table operation noauditdeleteanytable;c. Stop Audit Delete table operation (limit: Stop audit delete failure) NOAUDITDELETEANYTABLEWHENEVERNOTSUCCessful;d. Stop Audit Delete table operation (limit: Stop audit Delete successful case) noauditdeleteanytablewheneversuccessful; G. Stop auditing The delete operation on the Fey.employee table noauditdeleteonfey.employee;h. Stop auditing The Delete, Updet, and Fey.employee tables Insert Operation noauditdelete,update,insertonfey.employee; Ix. management of Audit record table sys.aud$ when the audit record of the sys.aud$ table is more and more, even when the storage limit is reached, Errors occur because audit records cannot be written. Maybe I also need to do some dumping or defragmentation of the table's data, or delete some audit records that we don't think we need anymore. These are all things to consider. Here's a way to defragment the table: A. Selectinto the table's data to another table, or export it to an external file using export b.truncate This table (to be logged in as a Super Administrator) C. Then load the previously dumped data back in. Maybe we need to audit the operation against the sys.aud$ table, such as:auditinsert,update,deleteonsys.aud$byaccess; 10, fine-grained audit as previously mentioned, The audit record does not contain the data that the action is in, for example, a select for a table needs to be audited, and the data returned by the SELECT statement needs to be included in the audit record. At this point, you need to use the fine-grained audit. Fine-grained auditing is an Oracle-based event that intercepts related data in insert,update or delete-related events. belongs to the scope of Orale program development. Refer to the relevant Oracle documentation for details. 11, audit-related data dictionary view Stmt_audit_option_mapaudit_actionsall_def_audit_optsdba_stmt_audit_optsdba_priv_audit_ Optsdba_obj_audit_optsuser_obj_audit_optsdba_audit_trailuser_audit_traildba_audit_objectuser_audit_objectdba_ Audit_sessionuser_audit_sessiondbA_audit_statementuser_audit_statementdba_audit_exists dba_audit_policiesdba_fga_audit_trail
Oracle Audit (i)
