Learn Oracle's audit (Auditing)

Auditing (Audit) is used to monitor database operations performed by users, and audit records can exist in data dictionary tables called Audit Records: SYS stored in the system table space. aud$ tables, which can be viewed dba_audit_trail view) or operating system audit records (Audit_file_dest parameters are determined). Auditing is not turned on by default.

1. There are four types of audits:

• Statement Auditing (statement Auditing): Audit at the statement level, such as auditing execution of a Select Table statement, rather than a single object.

• Privilege Auditing (Rights Audit): Audit the usage of a system's permissions if the Create any table permission that was used when the table was created is audited.

• Schema Object Auditing (Object audit): Audits an operation on a specified object, such as an audit of an insert operation on a table scott.emp.

• Fine-grained Auditing (fine-grained audit): used to specify a finer-grained audit, implemented with a DBMS_FGA package.

2. Related parameters:

Audit_trail parameters

This parameter determines the opening and closing of the database audit. Can be assigned to the following values

• DB, enable database auditing, and record audit records in the database sys.aud$
  

• XML, enabling database auditing, and storing audit records in the file system at the beginning of the XML file
  

• Db,extended, which has the same functionality as DB, and, if necessary, records SQL bind and SQL text Clob-type columns in sys.aud$.

• xml,extended, with XML-like functionality, and when available, records SQL bind and SQL text Clob-type columns in an XML file.

• OS, enable database auditing, and log audit records to the operating system files.

• None, database auditing is not enabled, default value.

Audit_file_dest parameters

If Audit_trail=os, the file of the audit record is stored in the directory specified by Audit_file_dest.

Audit_sys_operations parameters

Specifies whether auditing of the SYS user is enabled. The default is false, enabling set to true. Records are not stored in aud$, but are recorded elsewhere. If it is a Windows platform, Audti Trail is recorded in event management for Windows, and if it is the Linux/unix platform, it is recorded in the file specified in the Audit_file_dest parameter.

3. Enable and Deactivate database audits

Use the ALTER SYSTEM statement to set the Audit_trail parameter, which is not available for online modification and requires the database instance of the vault to take effect after modification. Examples are as follows:

ALTER SYSTEM SET audit_trail=db,extended scope=spfile;

Deactivate a database audit use the following statement to restart the database for effective

ALTER SYSTEM SET Audit_trail=none scope=spfile;

4. Syntax for enabling and stopping the audit function:

1) Enable auditing using the audit statement

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/92/4F/wKiom1j-EfDxoTsbAAGXMKVir5E923.png "title=" 1.png "alt=" Wkiom1j-efdxotsbaagxmkvir5e923.png "/>

2) Stop audit function

To deactivate the audit function, change the above audit to Noaudit.

Before auditing can be enabled, the Audit_trail parameter must be set to not none, otherwise the database will not be audited.

5. Examples of using audit features

Enable database auditing

[Email protected]>alter system set AUDIT_TRAIL=DB scope=spfile; System altered.

1) Statement Auditing (statement audit)

Audit all Select TABLE issued by a Scott user

[email protected]>audit select table by scott; Audit succeeded. [email protected]>select * from dba_stmt_audit_opts; User_name       proxy_name      audit_option      success    failure------------------------------ ------ ------------------------ ------------------------------ ------------------------------ -------- ----------------------scott      select table      By session     by session[email protected]>select count (*)  from emp;  count (*)----------14[email protected]>select timestamp,sql_text  from dba_audit_trail; Timestamp    sql_text------------------- -------------------------------------------- ------------------------------------2017-04-24 23:07:47 select count (*)  from emp 

2) Privilege Auditing (Authority Audit)

Audit all system permissions

[email protected]>audit all privileges; Audit succeeded. [email protected]>select * from dba_priv_audit_opts; User_name       proxy_name      privilege       success    failure------------------------------  ------------------------------ ------------------------------ ------------------------------ -- ----------------------------      FLASHBACK ARCHIVE ADMINISTER    by access    by access      create  SESSION                  by access    by access ... [[Email protected] ~]$ sqlplus scott/tiger ...] [email protected]>select username,timestamp,priv_used from dba_audit_trail where priv_used is not null; Username       timestamp   priv_ Used------------------------------ ------------------- --------------------------------------------- ---------------------------------------------------------------------------scott        2017-04-24 23:18:02 create session

3) Schema Object Auditing (Object audit)

Audit of select operations on scott.emp tables

[email protected]>audit select on scott.emp; Audit succeeded. [Email protected]>select * from dba_obj_audit_opts;owner   object_nam  OBJECT_TYPE     ALT   AUD COM   DEL    gra   ind ins   loc   ren   sel  UPD   REF   EXE   CRE REA   WRI    FBK---------- ---------- ------------------------------ ----- ----- -----  ----- ----- ----- ----- ----- ----- ----- ----- ----- -----  ----- ----- ----- -----scott   emp       table     -/-   -/- -/-   -/-   -/-    -/- -/-   -/-   -/-   s/s -/-   -/-   -/-    -/- -/-   -/-   -/-[email protected]>select ename  from emp; Ename------------------------------Smithallen ... [email protected]>select timestamp,sql_text from dba_audit_trail; Timestamp    sql_text------------------- -------------------------------------------- ------------------------------------2017-04-24 23:24:28 select ename from emp

The aud$ is located in the system table space and can be moved to other tablespaces based on Oracle's stability and performance considerations.

ALTER TABLE audit$ move tablespace <tablespace_name>;
Alter index I_audit rebuild online tablespace <tablespace_name>;
ALTER TABLE audit_actions move tablespace <tablespace_name>;
Alter index i_audit_actions rebuild online tablespace <tablespace_name>;

Official Document: Http://docs.oracle.com/cd/B19306_01/network.102/b14266/auditing.htm#CHDJBDHJ

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

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm#i2059073

Reference: http://blog.itpub.net/9399028/viewspace-712457/

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

Learn Oracle's audit (Auditing)