Shulou(Shulou.com)06/01 Report--

Http://guestart.blog.51cto.com/1510660/1896340

Http://www.itpub.net/thread-454252-1-1.html

Https://yq.aliyun.com/articles/16914

1. What is database audit? Database audit is to track the activities of the database, including database connection, SQL statement execution, database object access and so on. Second, the storage of audit records is divided into two ways: one is stored in the operating system file, and the other is stored in the SYS.AUD$ table in the system tablespace. Third, considering the impact on database performance, audit is bound to take up CPU. Therefore, it is necessary to comprehensively balance the balance between audit requirements and performance in order to determine the best approval strategy. Fourth, what information is included in the audit results? the storage of the approval results mentioned earlier can be divided into two ways: database storage and file storage. In the case of database storage, the SYS.AUD$ table contains the following information: 1) operating system user name

2) Database user name

3) connection session identification

4) Terminal identification

5) the name of the schema object accessed

6) attempted operation

7) complete code for the operation

8) date and time stamp

If the audit record is stored in an external operation file, the file may contain the following information: 1) the audit record generated by the operating system

2) Audit records of the database

3) audited database operations

4) Audit records of Super Admin (SYS)

Among them, the audit records written to the file are stored in the way of coding. If you want to understand the information corresponding to these codes, you can query the following data dictionary table: coding information data dictionary table-database operation code indicates the attempted operation. Its related description can be queried from the data dictionary table AUDIT_ actions table. The permissions used for the operation can be queried in the dictionary table SYSTEM_PRIVILEGE_MAP. The complete operation code will return 0 when the operation succeeds, and the relevant oracle error information will be returned if it fails. The description of the error message code can be obtained from the official oracle documentation-5. Default audit regardless of whether the audit function is currently enabled or not, the database will write some database-related operations to the external audit file (note: not written to the SYS.AUD$ table) The default audited actions are: 1) connections to the database with Super Admin privileges (connectASSYSDBA or connectASSYSOPER)

2) Database startup

3) Database stop

Audit of the actions of Super Admin users Super Admin users refer to users who connect to the database by ASSYSDBA or ASSYSOPER. The initialization parameter AUDIT_SYS_OPERATIONS is used to specify the audit option for the Super Admin. If AUDIT_SYS_OPERATIONS is set to TRUE, all Super Admin operations will be audited, regardless of whether the audit function is currently enabled or not, and all audit information is written to the external audit file (note: not written to the SYS.AUD$ table). 7. Executive audit 1. Setting the location of audit records initialization parameter AUDIT_TRAIL specifies the location of audit records. This parameter 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 turns off audit (default)

The initialization parameter AUDIT_FILE_DEST specifies the target storage path of the current external audit file, and its default value is $ORACLE_HOME/rdbms/audit

two。 Audit option this audit option refers to the optional AUDIT statement that performs the audit. For the syntax of the AUDIT statement, please refer to the oracle official SQL reference. 3. An example is given to illustrate audit a. Audit the creation and termination of a user's session AUDIT SESSION BY scott,fey

b. Audit the creation and termination of all users' sessions AUDIT SESSION

c. Audit delete table operation AUDIT DELETE ANY TABLE

d. Audit delete table operation (restriction: audit only if deletion fails) AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL

e. Audit delete table operation (restriction: audit only if the deletion is successful) AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL

f. Audit the operation of deleting the table (restriction: the same action statement is audited only once in the same session) AUDIT DELETE ANY TABLE BY SESSION; (Note: in this case, this effect is not reflected if the audit record is set to be stored in an external file, because oracle cannot determine whether the same operation statement has been audited.)

g. Audit operations to delete tables (restriction: each delete statement is audited, regardless of whether there are multiple identical action statements in the same session) AUDIT DELETE ANY TABLE BY ACCESS

h. Audit delete operations on the fey.employee table AUDIT DELETE ON fey.employee

i. Audit uses NOAUDIT statement for delete, updet, insert operations AUDIT DELETE,UPDATE,INSERT ON fey.employee; 8 and stop audit of fey.employee table. For the syntax of this statement, please refer to the official SQL reference of oracle. Here is an example to illustrate the use of NOAUDIT: a. Stop all audit NOAUDIT SESSION for session creation and termination

b. Stop the audit NOAUDIT SESSION BY fey,scott for the creation and termination of the session of user fey,scott

c. Stop the audit delete table operation NOAUDIT DELETE ANY TABLE

d. Stop audit delete table operation (restriction: stop audit delete failure) NOAUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL

e. Stop audit delete table operation (restriction: stop audit delete successful case) NOAUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL

f. Stop auditing delete operations on fey.employee tables NOAUDIT DELETE ON fey.employee

g. Stop auditing the delete, updet, insert operations of the fey.employee table NOAUDIT DELETE,UPDATE,INSERT ON fey.employee; IX, the management of the audit record table SYS.AUD$ when there are more and more audit records of the SYS.AUD$ table, and even reach the storage limit, errors will occur because the audit records can not be written. Maybe I also need to dump or defragment the data in the table, or delete some audit records that we think are no longer needed. These are all things to consider. Here is a way to defragment the table:

a. Select into the data from this table to another table, or export it to an external file using export

B.truncate this table (you have to log in as a Super Admin)

c. Then load the previously dumped data back in. Perhaps we need to audit the operations against the SYS.AUD$ table, such as: AUDIT INSERT,UPDATE,DELETE ON sys.aud$ BY ACCESS; X, Fine-Grained audit as mentioned earlier, the audit record does not contain the data for the action of the operation, for example, the select against 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, a Fine-Grained audit is required. Fine-Grained audit is based on oracle events and its principle is to intercept relevant data in insert,update or delete related events. It belongs to the category of orale program development. For more information, please refer to the relevant oracle documents. Audit-related data dictionary view STMT_AUDIT_OPTION_MAP AUDIT_ACTIONS ALL_DEF_AUDIT_OPTS DBA_STMT_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS DBA_AUDIT_TRAIL USER_AUDIT_TRAIL DBA_AUDIT_OBJECT USER_AUDIT_OBJECT DBA_AUDIT_SESSION USER_AUDIT_SESSION DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT DBA_AUDIT_EXISTS DBA_AUDIT_POLICIES DBA_FGA_AUDIT_TRAIL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.