A detailed explanation of Oracle database audit function

I. Classification of audits:

Oracle Audit in general can be divided into "standard audit" and "fine-grained audit" the latter also known as "policy-based Audit", after the oracle10g function has been greatly enhanced. Standard audit can be divided into user-level audit and system-level audit. User-level auditing is an audit that any Oracle user can set up, primarily by auditing the database tables or views that they create for themselves, documenting all successful and/or unsuccessful access requirements for all users to those tables or views, and various types of SQL operations. System-level audits can only be set by the DBA to monitor successful or failed logon requirements, monitoring grant and revoke operations, and operations under other database-level permissions.

Second, the standard audit:

1, classification:

Each of the following three standard audit types is supported in Oracle:

Statement audits, auditing some type of SQL statement, and not specifying a structure or object.

Privilege audits, audit of the use of system privileges to perform appropriate actions.

Object auditing, an audit of a specified statement on a particular pattern object.

These three standard audit types audit each of the following 3 areas:

Successful execution of an audit statement, unsuccessful execution, or both.

Execute once for each user session audit statement or once for each execution of the statement.

An audit of the activities of all users or specified users.

When the audit function of the database is turned on, an audit record is generated at the execution stage of the statement. Audit records contain information such as audit operations, actions performed by the user, date and time of operation, and so on. Audit records can exist in a data dictionary table (called an audit record) or an operating system audit record. The database audit record is in the aud$ table in sys mode.

2. Set up Oracle Standard Audit:

The following steps allow you to set the standard auditing capabilities for Oracle:

(1) Modify initialization parameter file (Initsid.ora)

If you use ALTER system set Parameter=value Scope=spfileboth using the server parameter file, refer to the description of the parameter file in section 1.1, set the Audit_trail parameter, and restart the database. The audit_trail values are as follows:

Dbtrue: The audit function is initiated and the audit results are stored in the SYS of the database. In the aud$ table

OS: Launch audit function and keep audit results in the audit information of operating system

db_extended: With dbtrue function, fill in the aud$ SQLBind and SQLText fields

Nonefalse: Turn off audit function

(2) Set Audit_trail parameters:

If you set the Audit_trail = OS, you also need to modify the parameter audit_file_dest.

If the operating system supports setting up Audit_trail=os, the files are automatically stored in the Audit_file

_dest the specified directory, and the file name contains the PID of the process.

Like what:

Audit_file_dest = $ORACLE _homerdbmsaudit

$ ls-l $ORACLE _homerdbmsaudit

-RW-RW----1 ora92dba881 0957 Ora_13264.aud

$ ps-efgrep 13264

Ora92 13264 13235 0 095643 oracleV92 (description= (local=y)

SQL select spid, program, username from V$process;

SPID program USERNAME

------ -------------------------------------------- -------------

...

13264ORACLE@FRHP11 (TNS v1-v3) ora92

(3) Confirm that the audit related table has been installed

Sqlplus Connect as SYSDBA

Sqlplus Select from sys.aud$; --No records returned

Sqlplus Select from Dba_audit_trail; --No records returned

If you find that the table does not exist when you make the above query, the audit-related table is not installed and needs to be installed.

Sqlplus Connect as Sysdba

Sqlplus @ $ORACLE _homerdbmsadmincataudit.sql

The audit table is installed in the system table space. So make sure that the system tablespace has enough space to store the audit information.

(4) Shutdown and restart the database

(5) Setting the required audit information

Here is an example

SQL Connect Systemmanager

SQL Grant audit system to Scott;

SQL Connect Scotttiger

SQL Audit session;

Stop auditing:

SQL Noaudit session;

Usually after the standard audit is set up through the audit statement to open the audit, using the Noaudit statement to recover the audit. As shown below:

To audit the operation of the SC table structure or data, you can use the following statement:

Audie alter,update on SC;

Cancel any audit of the SC list the following statement can be used:

Noaudit all on SC;

3. Set up an instance of the audit (audit of access to an attempt to try the password):

The following is an example of an audit to record an example of trying to decipher an Oracle account password by brute-try Method:

(1) Modify audit related parameters (refer to the method described above)

(2) Restart the database

(3) Set up audit information

Sqlaudit all by ACCESS whenever not successful

(4) Query aud$

SQL Select ReturnCode, action#, UserID, Userhost, Terminal,timestamp

From aud$

returncodeaction# USERID userhost TERMINAL

---------- ---------- -------- -------------------- --------------------

1017100 SCOTTWPRATA-BR

1017100 SCOTTWPRATA-BR

1017100 SCOTTWPRATA-BR

The meaning of the ORA-1017 is the wrong username password. You can clearly see wprata-br trying to decipher Scott's password by looking at the aud$ table. You can analyze the aud$ table by using one of the following stored procedures to find suspicious information:

Create or Replace procedure Auditlogin (Since varchar2,times Pls_integer)

Is

user_id VARCHAR2 (20);

Cursor C1 is select Userid,count () from sys.aud$ where returncode= ' 1017 ' and timestamp#=to_date (Since, ' yyyy-mm-dd ')

GROUP BY UserID;

Cursor C2 is Select userhost, Terminal,to_char (timestamp#, ' Yyyy-mm-ddhh24miss ')

From sys.aud$ WHERE returncode= ' 1017 ' and timestamp#=to_date (Since, ' yyyy-mm-dd ') and userid=user_id;

CT Pls_integer;

V_userhost VARCHAR2 (40);

V_terminal VARCHAR (40);

V_date VARCHAR2 (40);

BEGIN

OPEN C1;

Dbms_output.enable (1024000);

LOOP

FETCH C1 into user_id,ct;

EXIT when C1%notfound;

IF (ct=times) THEN

Dbms_output. Put_Line (' USER broken ALARM ' user_id);

OPEN C2;

LOOP

FETCH C2 into V_userhost,v_terminal,v_date;

Dbms_output. Put_Line (CHR (9) ' HOST ' v_userhost ', TERM ' v_terminal ', Time ' v_date);

EXIT when C2%notfound;

End LOOP;

Close C2;

End IF;

End LOOP;

Close C1;

End;

Here is the result of the execution:

Sqlset serveroutput on;

SQL Execute Auditlogin (' 2004-01-01 ', 2);

USER Broken Alarmsys

host,termxuji,time2004-09-22110800

host,termxuji,time2004-09-22110801

host,termxuji,time2004-09-22110929

host,termxuji,time2004-09-22110929

The PLSQL process has completed successfully.

4. Move audit-related tables to other table spaces:

Because audit-related tables such as aud$ tables are stored in the system tablespace, it is best to move aud$ to other tablespaces in order not to affect the performance of the system and to protect the table space. You can use the following statement to move:

SQLConnect as SYSDBA;

Sqlalter table aud$ move tablespace new tablespace;

Sqlalter index I_AUD1 rebuild online tablespace new tablespace;

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45407.htm