How to audit table operations in Oracle databases

How to audit table operations in Oracle databases? A basic configuration information table in the application stores dictionary information, which is added by the application in sequence as needed when the key value is used. This

How to audit table operations in Oracle databases? A basic configuration information table in the application stores dictionary information, which is added by the application in sequence as needed when the key value is used. This

In the basic configuration information table of the application, some dictionary information is saved. The key value is added by the application in sequence as needed. This type of requirement is used for primary key field value management. The dictionary table is inserted with the same key, resulting in multiple key values.

How can we solve this problem? There are two points to think. First, set the key field of the dictionary table to a unique constraint. Second, enable the audit function to find out which applications have inserted records with the same name.

The former and the latter must be both a cure and a cure. Therefore, we set the audit function to solve this problem.

1. How to enable audit?

Modify the database initialization parameter audit_trail from none to your desired value.

It has many options, as shown below:

AUDIT_TRAIL = {none | OS | db, extended | xml, extended}

We select the db value as the parameter value. The audit function is enabled. audit records are stored in the sys. aud $ table.

Modify the parameter configuration value in the initialization parameter file spfile.

Alter system set audit_trail = db SCOPE = SPFILE sid = '*';

Note: This parameter takes effect only after the database instance is restarted.

2. What functions does audit provide?

You can audit DML and DDL operations on database objects, query, execute, and perform system events such as logon and logout.

As follows:

DDL (CREATE, ALTER & DROP of objects)

DML (insert update, DELETE)

SELECT

EXECUTE

System events (LOGON, LOGOFF etc)

Each function also has options, such as audit in each session or in each access, when audit is successful or failed.

The complete audit syntax is as follows:

AUDIT

{SQL _statement_clause | schema_object_clause | NETWORK}

[BY {SESSION | ACCESS}]

[WHENEVER [NOT] SUCCESSFUL];