Audit of opening a table in Oracle

Oracle uses the audit_trail parameter to control whether auditing is enabled

There are several parameters to the Audit_trail:

None: Audit not open

OS: Description Audit information is put on the system summary, if it is Linux then determined by Audit_file_dest , if it is Windows is determined by Event Viewer

DB or TRUE : Indicates that the audit information is stored in the database, which is the aud$ table of the SYS user .

the meaning of the audit_sys_operations parameter:

False: Do not audit sys user, default does not audit

True: Audit sys user

Audit scope is divided into two kinds of session and access

Session: Indicates that the same SQL executed after the user login is logged only once, the other same SQL is no longer logged;

Access: Represents the audit record for each SQL execution.

Refer to the official documentation for details

Http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#CIHDICID

1 , open audit parameters

sql> show parameter audit name                                          TYPE        value----------------------------------------------- ------------------------------ audit_file_dest                       string      /u01/app/oracle/admin/mydb /adumpaudit_sys_operations                 boolean  FALSEaudit_syslog_level                  stringaudit_trail                                  string     none sql> alter system setaudit_trail= db,extended scope=spfile;

2 , restart the database

static parameters, which need to be restarted in order for the parameters to take effect

Sql> shutdown immediate;database closed. Database dismounted. Oracle instance shut down. sql> startuporacle instance started. total system global area   599785472 bytesFixed Size               2085776 bytesVariable Size                 192941168 bytesDatabase Buffers       398458880 bytesRedo Buffers                  6299648 bytesdatabase mounted. database opened. sql> show parameter audit name                                          type        VALUE----------------------------------------------- ------------------------------audit_file_dest                        string      /u01/app/oracle/admin/mydb/adumpaudit_sys_ operations                     boolean  FALSEaudit_syslog_level                  stringaudit_trail                                  string     db, extended

3 , set up an audit of the table

In this way, each time a user operates on a table, a corresponding record is added to the aud$ , and Oracle creates a view for easy data reading.

Although each user's action on the table is logged, the operation of the SYS user is not logged , and all other users record it.

sql> conn / as sysdbaconnected.sql> audit all on zx.num_t  by accesswhenever successful; audit succeeded. sql> set linesize  200sql> select * from dba_obj_audit_opts; owner                               OBJECT_NAME                  object_type      alt  aud    COM   DEL   GRA   IND  INS    LOC  REN   SEL   UPD  REF EXE    CRE   REA     WRI    FBK------------------------------------------------------------ ----------------- ----- ----- ----- ----- ---------- ----- -----  ----- ----- ----- --- ----- ----- ----- ----- -----zx                                NUM_T                                 TABLE             A/-   A/-  A/-   A/-   A/-    a/-  a/-   a/-   a/-   a/-   a/-   -/- -/-   -/-   -/-  -/-   a/-

Front column

A means access, which is logged every time an audit is made, such as scott.emp 's Select Audit, so anyone select will trigger an audit. and recorded in the aud$ .

S represents the session, and each session is logged once for the audited operation.

use different users to make different access to the zx.num_t table:

Sql> Conn Zx/zxconnected.sql> Select COUNT (*) from zx.num_t; COUNT (*)----------0sql> INSERT INTO zx.num_t (ID1) values (1); 1 row created. Sql> commit; Commit complete. Sql> Conn Scott/tigerconnected.sql> Select COUNT (*) from zx.num_t; COUNT (*)----------1sql> Delete from zx.num_t;1 row deleted. Sql> commit; Commit complete. sql> INSERT INTO zx.num_t (ID2) values (2); 1 row created. sql> rollback; Rollback complete.

4 , query audit Records

sql> alter session setnls_date_format= ' Yyyymmdd hh24:mi:ss ';  Session altered.  sql> set lines 200col os_username for a10col username for  a11col userhost for a10col terminal for a10col timestamp for  a20col obj_name for a10col owner for a10col action_name for  a11col transactionid for a16col sql_text for a50select username,       USERHOST,      TIMESTAMP,       OWNER,      OBJ_NAME,       Action_name,      sql_text from dba_audit_trail where obj_ Name= ' num_t '  ORDER BY TIMESTAMP; USERNAME   USERHOST    Timestamp     owNer      obj_name   action_name sql_text----------- --- ------- ------------------------------ ---------- --------------------------------------------- ----------------zx         rhel5      20161107 11:57:55    zx       num_t         NOAUDIT OBJ noaudit all on num_t                                                                                  ect zx         rhel5     20161107  12:00:07    zx        num_t         select     select count (*)  from zx.num_ tzx         rhel5     20161107  12:00:21    zx       num_t         INSERT     insert into zx.num_t  (ID1)   VALUES (1) scott          rhel5      20161107 12:00:37    zx       num_t         select     select count (*)  from  Zx.num_tscott          rhel5     20161107 12:00:45     zx       num_t        delete      delete from zx.num_tSCOTT           rhel5     20161107 12:01:27    ZX        NUM_T       INSERT      insert into zx.num_t  (ID2)  values (2)  6 rows selected.

5 , cancellation of audits

Sql> Noaudit all on num_t; Noaudit succeeded.

6 , empty aud$

This system table is available The TRUNCATE command is truncated. After you delete it, the records in the view disappear accordingly.

sql> truncate TABLE aud$; Sql> SELECT * from Dba_fga_audit_trail; No rows selected

More details refer to official documentation

Http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#BABCFIHB

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1870181

Audit of opening a table in Oracle