"Reading notes" database audit

DB version: Oracle database 11g Enterprise Edition release 11.2.0.1.0

I. Types of audits

Oracle's audit technology, in addition to SYSDBA audit, there are database audit, value-based audit, 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 (False by default) is set to true, then every statement published by a user who is a SYSDBA or sysoper connected database is written to the operating system audit trail, giving the full record of what the DBA is doing.

Alter system set AUDIT_SYS_OPERATIONS=TRUE Scope=spfile;

Then restart the database.

The DBA's operation is then recorded in the audit file.

1.2 Database Audits

Before setting 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: Audit records are written to the operating system's files;

DB: Audit records are written to sys.aud$.

Db_extended: The same as DB, but contains SQL statements with bound variables that generate audit records.

XML: Basically the same as the OS, but formatted using XML tags.

Xml_extended: Works much the same as XML, but uses SQL statements and binding variables.

For example, auditing an EMP table on Scott

If the value of Audit_trail is "DB", then the executed statement is not visible and therefore modified to:

Alter system set audit_trail=db_extended Scope=spfile;

Restart the database.

Audit select,delete,update on scott.emp by Access;

--Close 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, the Sql_text information is not visible, so Audit_trail is modified to db_extended and rerun once.

1.3 Performing a value-based audit based on triggers

Use triggers with auditing to record the value of the change to a table. You can do this even if you do not configure an audit policy.

For example, if we are interested in the change 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

/

Perform:

Update emp

Set comm=2000

where empno=7900;

Commit

Compare The results of Dba_audit_trail and Audit_value_trail.

1.4 Fine-grained audit (FGA)

Fine-grained auditing can be configured to generate audit records only when specific columns of a particular row or row are accessed, and can also be configured to run a PL/SQL code block when an audit condition is violated.

Configuring FGA will involve package DBMS_FGA, in order to create a FGA audit policy that requires the add_policy process, which takes the parameters shown in the following table:

Reference: "OCP/OCA certification exam Guide book" Sixth: Oracle Security Section 6th using standard database auditing

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1835066

"Reading notes" database audit