Oracle Database Auditing

There are two ways to ensure the security of the database, one is through the access control mechanism of the privilege, that is, to prevent the occurrence of security events through the pre-control, and the other is database audit, that is, to make the database activity tracking record, when the security event occurs, the database activity record is audited to discover and fix the security problem.

Oracle uses a number of different audit methods to monitor what permissions to use and which objects to access. Auditing does not prevent the use of these permissions, but it can provide useful information to uncover abuses and misuse of permissions. The main objects of database audit include database link, SQL statement execution, database object access and so on. Audit record storage is available in two choices: one is stored in the operating system file and one is stored in the sys.aud$ table of the system table space.

1, and audit-related parameters

(1) audit_sys_operations

(2) Audit_trail

(3) Audit_file_dest: Specify the folder for audit information

Sql>show parameter audit– two parameters related to query and audit

2. Audit_sys_operations parameters

The Audit_sys_operations parameter audits the activity of the SYSDBA, the default value is False, and the associated audit information is recorded in the operating system file (because the database is not started when it is possible to log). When the parameter value is false, the following operating systems are forced to record in the folder specified in Audit_file_dest:

(1) Connect an instance with Administrator privileges

(2) Start the database

(3) Closing the database

When the Audit_sys_operations parameter is set to TRUE, each statement published by the user who connects to the database as SYSDBA and Sysoper is written to the operating system's audit, giving the full record of the operation performed by the DBA. The statement that sets 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 in the following ways:

? None: No auditing, default value.

? DB: Turn on auditing, log audit to sys.aud$ table (the results of the audit are only connection information and are not recorded in the SQLBind and SQLText fields).

? OS: An audit record is written to an operating system file.

? Db,extended: The audit function is turned on, the audit is recorded to the sys.aud$ table (the audit information, in addition to the connection information, contains the specific statements executed at that time, recorded in the SQLBind and SQLText fields).

? XML: An audit record is written to an operating system file in XML format.

? Xml,extended: Audit records are written to the operating system files in XML format, including SQLBind and SQLText values.

Note: This parameter is written to the static parameters inside the SPFile and requires a restart of the database.

(2) Setting parameter values

Sql> alter system setaudit_trail= ' DB ' scope=spfile;

Note: The parameter audit_trail is not dynamic, and the database must be shut down and restarted for the changes in the Audit_trail parameter to take effect. When auditing the sys.aud$ table, it is important to monitor the size of the table to avoid affecting the space requirements of other objects in the SYS table space.

4. Open and close audits

(1) Open audit case

Sql>conn/as SYSDBA

Sql>show parameter Audit

Sql>alter system set Audit_sys_operations=true scope=spfile;

--Audit management user (login with sysdba/sysoper role)

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

Sql>startup Force--completion of audit opening

Sql>show parameter audit– Comparison of two times reference value

(2) Closing audit cases

Sql>conn/as SYSDBA

Sql>show parameter Audit

Sql>alter system set Audit_trail=none scope=spfile;

Sql>startup Force-completion of audit closure

Sql>conn/as SYSDBA

Sql>show parameter audit– Comparison of two times reference value

(3) Audit-related table installation

Sqlplus> Connect/as SYSDBA

Sqlplus> select * from sys.aud$;

Sqlplus> select * from Dba_audit_trail;

If you do the above query when the table does not exist, the audit related tables have not been installed, need to install. Here's how:

Sqlplus> Connect/as SYSDBA

sqlplus> @ $ORACLE _home/rdbms/admin/cataudit.sql

The audit table is installed in the system table space. So make sure that the system tablespace has enough space to hold the audit information. Restart the database after installation.

5. Audit type

The following table summarizes the different types of audits in the Oracle database.

Audit type	Description
Statement auditing	Audit SQL statements by statement type regardless of which particular schema object is accessed, or you can specify one or more users in the database to audit those users for specific statements.
Rights audits	Audit system permissions, such as CREATE TABLE or alter INDEX. As with statement auditing, a permission audit can designate one or more specific users as the target of an audit.
Schema Object auditing	Audit specific statements that run on a particular pattern object (for example, an UPDATE statement on a departments table); Schema object auditing is always applied to all users in the database.
Fine-grained auditing	Audit table access and permissions based on the contents of the Access object, and use package DBMS_FGA to establish policies on specific tables.

(1) Statement-level auditing

All types of audits use the Audit command to open an audit and use the Noaudit command to turn off auditing. For statement auditing, the format of the Audit command is as follows:

AUDIT sql_statement_clause by {SESSION | ACCESS}

Whenever [not] successful;

which

? Sql_statement_clause contains a number of different information, such as the type of SQL statement you want to audit and who to audit.

? You want to audit each action when it occurs (by Access), and only once (by session) when the action repeats. The default is by session.

? Audit the statements executed successfully, using wheneversuccessful. The audit did not successfully execute the statement, using whenever not successful.

For example: Audit successful and unsuccessful logins in a regular way, which requires two audit commands:

Sql>audit session whenever successful;

Sql>audit session whenever not successful;

? For most categories of auditing methods, if you do want to audit all types of table access or any permissions for 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 contains a brief description of the related statements in each category. If all is specified, any statements in the list are audited. The statement types in table 2 do not belong to the all category when auditing is enabled, and they must be explicitly specified in the audit command.

Table 1 includes auditable statements in the all category

Statement options	SQL operations
ALTER SYSTEM	All alter system options, such as changing instance parameters dynamically, switching to the next log file group, and terminating user sessions.
CLUSTER	CREATE, ALTER, drop, or truncate clusters
CONTEXT	CREATE context or drop context
DATABASE LINK	Create or DROP database links
DIMENSION	CREATE, alter, or drop dimensions
DIRECTORY	Create or Drop directory
INDEX	CREATE, alter, or DROP index
Materialized VIEW	CREATE, alter, or drop materialized views
Not EXISTS	Failure of SQL statement due to non-existent reference object
PROCEDURE	Create or Drop FUNCTION, LIBRARY, package, package body, or procedure
Profile	CREATE, Alter, or drop configuration files
Public DATABASE LINK	Create or drop public database link
public synonym	Create or drop public synonym
ROLE	CREATE, ALTER, drop, or set role
ROLLBACK SEGMENT	CREATE, ALTER, or drop rollback segments
SEQUENCE	Create or drop sequence
SESSION	Login and exit
Synonym	Create or drop synonyms
SYSTEM AUDIT	Audit or Noaudit of system permissions
SYSTEM GRANT	Grant or revoke system permissions and Roles
TABLE	CREATE, drop, or truncate tables
Tablespace	CREATE, alter, or drop table spaces
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 bodies
USER	CREATE, Alter, or drop users
VIEW	Create or Drop view

Table 2 explicitly specified statement types

Statement options	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	Execute a procedure, function, or any variable or cursor in a package
GRANT DIRECTORY	Permissions on a grant or revoke directory object
GRANT PROCEDURE	Grant or revoke permissions on a procedure, function, or package
GRANT SEQUENCE	Permissions on a grant or revoke sequence
GRANT TABLE	Grant or revoke permissions on a table, view, or materialized view
GRANT TYPE	Permissions on grant or revoke type
INSERT TABLE	INSERT into table or view
LOCK TABLE	The lock Table command on a table or view
SELECT SEQUENCE	Any command that refers to a sequence of currval or Nextval
SELECT TABLE	SELECT from table, view, or materialized view
UPDATE TABLE	Perform an update on a table or view

? Case 1:

Sql> Conn/as SYSDBA

--You can use the following command to audit the index 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 the audit trail in the data dictionary view Dba_audit_trail

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 ';

--Close HR audit on hr.jobs table

Sql> Noaudit index by HR;

? Case 2:

Sql> Conn/as SYSDBA

--Audit All 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 made by the Scott user to create the table are audited, ReturnCode represents the return code, 0 indicates correct execution, and 955 indicates an error: "The name is already in use by an existing object."

(2) Authority level audit

Audit system permissions have the same basic syntax as statement auditing, and you need to specify system permissions on Sql_statement_clause. For example, you might want to grant Altertablespace permissions to all DBAs, but you want to generate audit records when this happens. The command to enable auditing of this permission is as follows:

Sql> audit alter tablespace by Access whenever successful;

At this point, each time you successfully use the ALTER TABLESPACE permission, a line of content is added to the 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 auditing

The command format for auditing access to various schema objects is as follows:

Auditschema_object_clause by {SESSION | ACCESS} whenever [not] successful;

Description

? Schema_object_clause Specifies the type of object access and the object accessed. You can audit 14 different types of operations on a specific object, and these actions are listed in the following table.

Object Options	Description
Alter	Change a table, sequence, or materialized view
AUDIT	Audit commands on any object
COMMENT	Add comments to a table, view, or materialized view
DELETE	Delete rows from a table, view, or materialized view
EXECUTE	Execute a procedure, function, or package
FLASHBACK	Perform a flashback operation on a 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 a table, view, or materialized view
LOCK	Lock a table, view, or materialized view
READ	To perform read operations on the contents of a directory object
RENAME	Renaming a table, view, or procedure
SELECT	Select rows from a table, view, sequence, or materialized view
UPDATE	Update a table, view, or materialized view

? Case: Audit of INSERT and UPDATE commands for all users on the Hr.jobs table each successful

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 , 50000);

sql> INSERT INTO Hr.jobs (Job_id,job_title, min_salary, max_salary) VALUES (' Oe_vld ', ' Order Entry ccvalidation ', 5500, 20000);

Sql> Conn/as SYSDBA

--Check the audit trail in the data dictionary view Dba_audit_trail

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 ';