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

Audit is used to monitor the database operations performed by users, and audit records can exist in data dictionary tables (called audit records: stored in the SYS.AUD$ table in the system tablespace and can be viewed through the view dba_audit_trail) or in the operating system audit records (determined by the audit_file_dest parameter). Auditing is not turned on by default.

1. There are four types of audit:

Statement Auditing (statement audit): perform an audit at the statement level, such as an audit that executes SELECT TABLE statements, rather than against a single object.

Privilege Auditing (permission audit): audit the usage of permissions on a system, if audit the CREATE ANY TABLE permissions used when creating tables.

Schema Object Auditing (object audit): audit operations on specified objects, such as INSERT operations on table scott.emp.

Fine-Grained Auditing (fine-grained audit): used to specify finer-grained audits, implemented in the DBMS_FGA package.

2. Related parameters:

AUDIT_TRAIL parameter

This parameter determines whether the database audit is turned on or off. Can be assigned the following values

DB, enable database audit, and record audit records to SYS.AUD$ in the database

XML, enable database audit, and record the audit in the file system at the beginning of the XML file

DB,EXTENDED, which has the same functionality as DB, and records the SQL bind and SQL text CLOB-type columns in SYS.AUD$ if necessary.

XML,EXTENDED, which has the same functionality as XML and records the SQL bind and SQL text CLOB-type columns in the XML file when available.

OS, enable database auditing, and record audit records in operating system files.

NONE, database auditing is not enabled, default value.

AUDIT_FILE_DEST parameter

If AUDIT_TRAIL=OS, the files of the audit records are stored in the directory specified by AUDIT_FILE_DEST.

AUDIT_SYS_OPERATIONS parameter

Specifies whether to enable auditing for SYS users. The default is FALSE, and the enable setting is TRUE. Records are not stored in AUD$, but are recorded elsewhere. In the case of windows platform, audti trail is recorded in the event management of windows, and in the file specified by the audit_file_dest parameter in the case of linux/unix platform.

3. Enable and disable database auditing

Use the ALTER SYSTEM statement to set the AUDIT_TRAIL parameter, which cannot be modified online. After modification, the database instance needs to take effect again. The example sentences are as follows:

ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE

Disable database audit using the following statement. Restart the database to take effect.

ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE

4. The syntax for enabling and stopping the audit function:

1) enable auditing to use audit statement

2) stop the audit function

To disable the audit function, change the above AUDIT to NOAUDIT.

Before enabling auditing, the AUDIT_TRAIL parameter must be set to non-NONE, otherwise the database will not audit.

5. Example of using audit function

Enable database audit

Sys@TEST > alter system set audit_trail=DB scope=spfile;System altered.

1) Statement Auditing (statement audit)

Audit all SELECT TABLE issued by SCOTT users

Sys@TEST > audit select table by scott;Audit succeeded.sys@TEST > select * from dba_stmt_audit_opts USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE -SCOTT SELECT TABLE BY SESSION BY SESSIONscott@TEST > select count (*) from emp COUNT (*)-14 sys@TEST > select timestamp,sql_text from dba_audit_trail TIMESTAMP SQL_TEXT--2017-04-24 23:07:47 select count (*) from emp

2) Privilege Auditing (permission audit)

Audit all system permissions

Sys@TEST > audit all privileges;Audit succeeded.sys@TEST > 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. [oracle@rhel6 ~] $sqlplus scott/tiger.sys@TEST > 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 select operations on SCOTT.EMP tables

Sys@TEST > audit select on scott.emp;Audit succeeded.sys@TEST > 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-/- -/-- /-/-- /-/-scott@TEST > select ename from emp ENAME--SMITHALLEN.sys@TEST > select timestamp,sql_text from dba_audit_trail TIMESTAMP SQL_TEXT--2017-04-24 23:24:28 select ename from emp

AUD$ is located in the SYSTEM table space, and audit-related tables can be moved to other table spaces based on the stability and performance considerations of Oracle.

Alter table audit$ move tablespace

Alter index i_audit rebuild online tablespace

Alter table audit_actions move tablespace

Alter index i_audit_actions rebuild online tablespace

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/

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.