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

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Type of audit

Besides SYSDBA audit, ORACLE audit technology includes database audit, value-based audit and fine-grained audit.

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/orcl/adump

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

1.1 SYSDBA audit

Audit_sys_operations (default is false) is set to TRUE, so every statement issued as a user of SYSDBA or SYSOPER connecting to the database is written to the operating system audit trail, giving a complete record of what DBA does.

Alter system set audit_sys_operations=TRUE scope=spfile

Then restart the database.

After that, the operation of DBA will be recorded in the audit file.

1.2 Database audit

Before you set up a database audit, you must set the value of audit_trail, which can have the following values:

NONE (or FALSE): disable database auditing

OS: the audit record will be written to the operating system file

DB: audit records are written to SYS.AUD$.

DB_EXTENDED: much the same as DB, except that it contains SQL statements with bound variables that generate audit records.

XML: much the same as OS, but formatted using the XML tag.

XML_EXTENDED: much the same as XML, but using SQL statements and binding variables.

For example, audit the emp table on scott

If the value of audit_trail is "DB", then the statement executed is not visible, so it is modified to:

Alter system set audit_trail=DB_EXTENDED scope=spfile

Restart the database.

Audit select,delete,update on scott.emp by access

-- turn off audit: noaudit

Select * from emp

Update emp

Set comm=1500

Where empno=7900

Insert into emp

Select 7935,ename,job,mgr,hiredate,sal,comm,deptno

From emp

Where empno=7934

Delete from emp

Where empno=7935

-- View audit information

Select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME

Sessionid,os_process,sql_text

From dba_audit_trail

Where obj_name='EMP'

Order by timestamp desc

If the value of audit_trail is DB, you can't see the information about sql_text, so change audit_trail to db_extended and run it again.

1.3 perform a value-based audit based on triggers

Use triggers in conjunction with the audit to record the changed value to a table. It can be executed even if the audit policy is not configured.

For example, if we are interested in changing the value of the comm column on the scott.emp table, we can create a trigger to write the value of the operation to the table.

Create table AUDIT_VALUE_TRAIL

(

Terminal VARCHAR2 (256)

Sessionid VARCHAR2 (256)

Isdba VARCHAR2 (256)

Current_user VARCHAR2 (256)

Os_user VARCHAR2 (256)

Ip_address VARCHAR2 (256)

Obj_user VARCHAR2 (10)

Obj_name VARCHAR2 (22)

Act_value VARCHAR2 (255)

);

Create or replace trigger tri_emp_audit

After update of comm on scott.emp

Referencing new as new old as old

For each row

Begin

If: old.comm! =: new.comm then

Insert into sys.audit_value_trail

Values (SYS_CONTEXT ('USERENV','TERMINAL'))

SYS_CONTEXT ('USERENV','sessionid')

SYS_CONTEXT ('USERENV','ISDBA')

SYS_CONTEXT ('USERENV','CURRENT_USER')

SYS_CONTEXT ('USERENV','OS_USER')

SYS_CONTEXT ('USERENV','IP_ADDRESS')

'scott','emp',:new.empno | |' comm is changed from'| |: old.comm | |'to'| |: new.comm)

End if

End

/

Execute:

Update emp

Set comm=2000

Where empno=7900

Commit

Whether the results of dba_audit_trail and audit_value_trail are the same.

1.4 Fine-grained audit (FGA)

Fine-grained audits can be configured to generate audit records only when accessing specific rows or specific columns of specific rows, and to run a block of PL/SQL code if audit conditions are violated.

Configuring FGA will involve the package DBMS_FGA, and to create an FGA audit policy, you need to use the add_policy procedure, which accepts the parameters shown in the following table:

Reference: OCP/OCA Certification examination Guide Chapter 6: ORACLE Security Section 6 using Standard Database Audit

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.