Oracle Database Audit

There are two ways to ensure database security: one is to prevent security events through permission access control, that is, to prevent security events through pre-control; the other is database audit, that is, to track and record database activities. When a security event occurs, the database activity record is audited to discover and fix security problems.

Oracle uses a large number of different audit methods to monitor which permissions are used and which objects are accessed. Auditing does not prevent the use of these permissions, but provides useful information to reveal abuse and misuse of permissions. The main objects of database audit include database links, SQL statement execution, and database object access. There are two options for storing audit records: one is stored in the operating system file and the other is stored in the sys. aud $ table in the system tablespace.

1. audit-related parameters

(1) audit_sys_operations

(2) audit_trail

(3) audit_file_dest: Specifies the audit information folder.

SQL> show parameter audit-two parameters related to query and audit

2. audit_sys_operations Parameters

The audit_sys_operations parameter audits SYSDBA activities. The default value is false. The relevant audit information is recorded in the operating system file (because the database is not started when the record is possible ). When the parameter value is false, the following operating system will forcibly record it in the folder specified by audit_file_dest:

(1) connect to the instance with the administrator privilege

(2) Start the database

(3) shut down the database

After the audit_sys_operations parameter is set to true, each statement published by the user who connects to the database as SYSDBA and SYSOPER will be written to the audit of the operating system, this gives a complete record of the operations performed by the DBA. The statement for setting the audit_sys_operations parameter is as follows:

SQL> alter system set audit_sys_operations = TRUE scope = spfile;

SQL> alter system set audit_sys_operations = FALSE scope = spfile;

3. audit_trail Parameters

(1) The value of the audit_trail parameter can be set to the following:

? NONE: audit is not performed. Default Value.

? DB: Enable the audit function to record audit to the sys. aud $ table (the audit results only contain connection information, not the SQLBIND and SQLTEXT fields ).

? OS: Write audit records to an operating system file.

? DB, extended: Enable the audit function to record audit to the sys. aud $ table (the audit information includes the connection information, the specific statements executed at the time, and records the SQL bind and SQLTEXT fields ).

? Xml: audit records are written to operating system files in xml format.

? Xml, extended: audit records are written to operating system files in xml format, including SQLBIND and SQLTEXT values.

Note: this parameter is a static parameter written to spfile. You need to restart the database.

(2) set the parameter value

SQL> alter system setaudit_trail = 'db' scope = spfile;

Note: The AUDIT_TRAIL parameter is not dynamic. to make changes in the AUDIT_TRAIL parameter take effect, you must close the database and restart the database. When auditing the SYS. AUD $ table, you should monitor the table size to avoid affecting the space requirements of other objects in the SYS tablespace.

4. Enable and disable Audit

(1) Enable audit Cases

SQL> conn/as sysdba

SQL> show parameter audit

SQL> alter system set audit_sys_operations = TRUE scope = spfile;

-- Audit Management User (log in as sysdba/sysoper)

SQL> alter system set audit_trail = db, extended scope = spfile;

SQL> startup force -- enable Audit

SQL> show parameter audit-compare two reference values

(2) close audit Cases

SQL> conn/as sysdba

SQL> show parameter audit

SQL> alter system set audit_trail = none scope = spfile;

SQL> startup force -- close the Audit

SQL> conn/as sysdba

SQL> show parameter audit-compare two reference values

(3) audit-related table Installation

SQLPLUS> connect/AS SYSDBA

SQLPLUS> select * from sys. aud $;

SQLPLUS> select * from dba_audit_trail;

If the table does not exist during the above query, it indicates that the audit-related table has not been installed and needs to be installed. The method is as follows:

SQLPLUS> connect/as sysdba

SQLPLUS> @ $ ORACLE_HOME/rdbms/admin/cataudit. SQL

The audit table is installed in the SYSTEM tablespace. Therefore, make sure that the SYSTEM tablespace has enough space to store audit information. Restart the database after installation.

5. Audit type

The following table summarizes different types of audits in Oracle databases.

Audit type	Description
Statement Audit	Audit SQL statements by statement type, regardless of the specific mode object accessed. You can also specify one or more users in the database to audit these users for specific statements.
Permission Audit	Audit system permissions, such as create table or alter index. Like Statement Audit, permission audit can specify one or more specific users as the audit targets.
Pattern Object audit	Audits specific statements run on objects in a specific mode (for example, the UPDATE Statement on the orders tables). Pattern Object audit is always applied to all users in the database.
Fine-grained Audit	Audit table access and permissions based on the Access Object content; Use the package DBMS_FGA to create a policy for a specific table.

(1) Statement-level Audit

All types of audits use the audit command to open the audit, and use the noaudit command to close the audit. For statement auditing, the audit Command Format is as follows:

AUDIT SQL _statement_clause BY {SESSION | ACCESS}

WHENEVER [NOT] SUCCESSFUL;

Where:

? SQL _statement_clause contains many different pieces of information, such as the types of SQL statements to be audited and the persons to be audited.

? You want to audit an action every time it occurs. If the action is repeated, it is only audited once (by session ). The default value is by session.

? Use wheneversuccessful to audit successfully executed statements. If the audit fails, use whenever not successful.

For example, to audit successful and unsuccessful logon in the conventional mode, two audit commands are required:

SQL> audit session whenever successful;

SQL> audit session whenever not successful;

? For most audit methods, if you really want to audit all types of table access or any permissions of a user, you can specify all instead of a single statement type or object.

Table 1 lists the types of statements that can be audited, and each category contains a brief description of related statements. If all is specified, any statements in the list are audited. The statement types in Table 2 do not belong to the all class when audit is enabled; they must be explicitly specified in the audit Command.

Table 1 Audit statements included in ALL categories

Sentence Selection	SQL operations
ALTER SYSTEM	All alter system options, such as dynamically changing instance parameters, switching to the next log file group, and terminating user sessions.
CLUSTER	CREATE, ALTER, DROP, or TRUNCATE Cluster
CONTEXT	Create context or DROP CONTEXT
DATABASE LINK	CREATE or DROP DATABASE LINK
DIMENSION	CREATE, ALTER, or DROP dimension
DIRECTORY	CREATE or DROP directory
INDEX	CREATE, ALTER, or DROP index
MATERIALIZED VIEW	CREATE, ALTER, or DROP materialized views
NOT EXISTS	SQL statement failure caused by nonexistent reference object
PROCEDURE	CREATE or drop function, LIBRARY, PACKAGE, package body, or PROCEDURE
PROFILE	CREATE, ALTER, or DROP configuration file
PUBLIC DATABASE LINK	CREATE or DROP public database link
PUBLIC SYNONYM	CREATE or DROP public Synonym
ROLE	CREATE, ALTER, DROP, or SET roles
ROLLBACK SEGMENT	CREATE, ALTER, or DROP rollback segments
SEQUENCE	CREATE or DROP sequence
SESSION	Logon and exit
SYNONYM	Synonym for CREATE or DROP
SYSTEM AUDIT	AUDIT or NOAUDIT with system Permissions
SYSTEM GRANT	GRANT or REVOKE system permissions and Roles
TABLE	CREATE, DROP, or TRUNCATE tables
TABLESPACE	CREATE, ALTER, or DROP tablespace
TRIGGER	CREATE, ALTER (ENABLE/DISABLE), DROP trigger; alter table with enable all triggers or DISABLE ALL TRIGGERS
TYPE	CREATE, ALTER, and DROP types and type subjects
USER	CREATE, ALTER, or DROP user
VIEW	CREATE or DROP View

Table 2 explicitly specifies the statement type

Sentence Selection	SQL operations
ALTER SEQUENCE	Any alter sequence command
ALTER TABLE	Any alter table command
COMMENT TABLE	Add comments to tables, views, materialized views, or any column in them
DELETE TABLE	Delete rows in a table or view
EXECUTE PROCEDURE	The process, function, or any variable or cursor in the execution package
GRANT DIRECTORY	GRANT or REVOKE permission on the DIRECTORY object
GRANT PROCEDURE	GRANT or REVOKE process, function, or package Permissions
GRANT SEQUENCE	GRANT or REVOKE sequence Permissions
GRANT TABLE	GRANT or REVOKE table, view, or materialized view Permissions
GRANT TYPE	GRANT or revoke type Permissions
INSERT TABLE	Insert into table or view
LOCK TABLE	Lock table command on a TABLE or view
SELECT SEQUENCE	Any command that references the sequence's CURRVAL or NEXTVAL
SELECT TABLE	Select from table, view, or Materialized View
UPDATE TABLE	Execute UPDATE on a table or view

? Case 1:

SQL> conn/as sysdba

-- You can use the following command to audit indexes created by hr:

SQL> audit index by hr;

SQL> conn hr/hr-hr User Login

SQL> create index job_title_idx onhr.jobs (job_title );

SQL> conn/as sysdba

-- Check audit trail in DBA_AUDIT_TRAIL of Data Dictionary View

SQL> select username, to_char (timestamp, 'Mm/DD/YY HH24: MI ') Timestamp, obj_name, action_name, SQL _text from dba_audit_trail where username = 'hr ';

-- Disable audit of HR on the HR. JOBS table

SQL> noaudit index by hr;

? Case 2:

SQL> conn/as sysdba

-- Audit all the create/drop/alter table statements in the database.

SQL> audit table by scott by access;

SQL> conn scott/abc123

SQL> create table taudit1 (a int );

SQL> create table taudit1 (a int );

SQL> insert into taudit1 values (1 );

SQL> select * from taudit1;

SQL> insert into taudit1 values (1 );

SQL> conn/as sysdba

SQL> select username, timestamp, owner, action_name, obj_name, returncode from dba_audit_object where username = 'Scott ';

Note: All statements used by scott to create tables are audited. returncode indicates the return code, 0 indicates the execution is correct, and 955 indicates the error: "The name is used by an existing object ".

(2) Permission-level Audit

The audit system permission has the same basic syntax as statement audit. You must specify the system permission on SQL _statement_clause. For example, you may want to grant the ALTERTABLESPACE permission to all DBAs, but you want to generate audit records in this case. The command to enable audit for this permission is as follows:

SQL> audit alter tablespace by access whenever successful;

At this time, each time the alter tablespace permission is successfully used, a row of content will be added to SYS. AUD $.

? Case

SQL> conn/as sysdba

SQL> audit select table by scott bysession;

SQL> conn scott/abc123

SQL> select * from taudit1;

SQL> select * from taudit1;

SQL> conn/as sysdba

SQL> select username, timestamp, owner, action_name, obj_name, returncode from dba_audit_object where username = 'Scott ';

(3) object-level Audit

The command format for Auditing access to various mode objects is as follows:

AUDITschema_object_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL;

Note:

? Schema_object_clause specifies the object access type and object to be accessed. You can audit 14 different operation types on a specific object. These operations are listed in the following table.

Object Selection	Description
ALTER	Change a table, sequence, or Materialized View
AUDIT	Audit commands on any object
COMMENT	Add comments to tables, views, or materialized views
DELETE	Delete rows from tables, views, or materialized views
EXECUTE	Execution Process, function, or package
FLASHBACK	Execute the flash back operation on the table or view
GRANT	Grant permissions on any type of object
INDEX	Create an index on a table or Materialized View
INSERT	Insert rows into tables, views, or materialized views
LOCK	Lock a table, view, or Materialized View
READ	Read the contents of the DIRECTORY object.
RENAME	Rename a table, view, or process
SELECT	Select rows from tables, views, sequences, or materialized views
UPDATE	Update a table, view, or Materialized View

? Case: audit the insert and update commands successfully performed by all users on the HR. JOBS table

SQL> conn/as sysdba

SQL> audit insert, update on hr.jobs byaccess whenever successful;

SQL> conn hr/hr-hr User Login

SQL> insert into hr.jobs (job_id, job_title, min_salary, max_salary) values ('in _ cfo', 'Internet Chief FunOfficer ', 7500,500 00 );

SQL> insert into hr.jobs (job_id, job_title, min_salary, max_salary) values ('oe _ VLD ', 'order Entry CCValidation', 5500,200 00 );

SQL> conn/as sysdba

-- Check audit trail in DBA_AUDIT_TRAIL of Data Dictionary View

SQL> select username, to_char (timestamp, 'Mm/DD/YY HH24: MI ') Timestamp,

2 obj_name, action_name, SQL _text from dba_audit_trail

3 where username = 'hr ';