Enable audit for a table in Oracle (audit)

Enable audit for a table in Oracle (audit)

Oracle databases enable standard auditing using the audit_trail Parameter

Its Parameters include the following:

NONE: Audit disabled

OS: The audit information is summarized in the system. For Linux, the audit information is determined by audit_file_dest. For Windows, the audit information is determined by the event viewer.

DB or TRUE: indicates that the audit information is stored in the database, that is, the sys user's aud $ table.

The audit scope is divided into session and access.

Session: indicates that the same SQL statement executed after logon is recorded only once, and other identical SQL statements are not recorded;

Access: indicates that the SQL statement is audited every time it is executed.

1. Enable audit parameters

SQL> alter system set audit_trail = db_extended scope = spfile sid = '*';

2. Restart the database

To make the parameter take effect

Su-oracle

$ Srvctl stop database-d orcl

$ Srvctl start database-d orcl

SQL> show parameter audit;

3. Set audit for tables

In this way, each time a user operates on a table, the corresponding records will be added to the aud $, and Oracle creates a view to facilitate data reading.

Operations on a table are recorded for each user, but operations on the sys user are not recorded. Operations on all other users are recorded.

SQL> audit all on scott. emp by access;

If you want to see the effect after setting, you can log on to the table as Scott.

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------

4. query audit records

$ Su-oracle

$ Sqlplus/as sysdba

Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Col OS _USERNAME for a10

Col USERNAME for a11

Col USERHOST for a10

Col TERMINAL for a10

Col TIMESTAMP for a20

Col obj_name for a10

Col OWNER for a10

Col ACTION_NAME for a11

Col TRANSACTIONID for a16

Col SQL _text for a30

Select username, userhost, timestamp, owner, obj_name, action_name, SQL _text from dba_audit_trail;

Username Login User

Host logged on by userhost

Timestamp

Owner of the operation table

Tables operated by obj_name

What action does action_name perform?

SQL _text

Others:

1. Clear aud $

This system table can be truncated using the TRUNCATE command. After it is deleted, the records in the view will disappear accordingly.

SQL> truncate table aud $;

2. Cancel audit of a table

SQL> noaudit all on scott. emp;

3. Close Audit

SQL> alter system set audit_trail = none;