Go ORACLE Audit Capabilities

Auditing is the monitoring and recording of selected user actions, typically used to:

U review suspicious activities. For example, the data is deleted by an unauthorized user, at which point the security administrator can decide to audit all connections to the database and to successfully or unsuccessfully delete all tables of the database.

U Monitor and collect data about the specified database activity. For example, DBAs can collect statistics such as what has been modified and how many times logical I/O has been executed.

ORACLE supports three types of audits:

U statement Audit, for some type of SQL statement audit, do not specify structure or object.

U Privilege Audit, audit of the use of system privileges to perform corresponding actions.

U Object Audit, an audit of the specified statement on a special mode object.

The audit options allowed by ORACLE are limited to the following:

U audit statements are executed successfully, unsuccessful, or both.

You execute each user session Audit statement once or each time the statement is audited.

You audit the activity of all users or designated users.

When the audit of the database is enabled, an audit record is generated during the statement execution phase. Audit records contain information such as audited actions, actions performed by the user, date and time of the operation, and so on. Audit records can exist in a data dictionary table (called an audit record) or in an operating system audit record. The database audit record is in the aud$ table of the SYS schema.

Set upORACLEAudit

The following steps can set the auditing capabilities of ORACLE :

1. Modify the parameter file (Init.ora, if you use the server parameter file using alter system set = Scope=spfile|both, details refer to 1.1 section for a description of the parameter file), set the audit_trail parameter, and restart the database. The values of the Audit_trail are as follows:

L Db/true: Start the audit function and store the audit results in the database SYS. In the aud$ table

L OS: Start audit function and store audit results in the audit information of the operating system

L db_extended: With db/true function, fill in the aud$ SQLBIND and sqltext fields

L None/false: Turn off audit function

2. If you set audit_trail = OS, You also need to modify the parameter audit_file_dest.

If the operating system supports setting Audit_trail=os, the file is automatically stored in the audit_file

_dest The specified directory, and the file name contains the PIDof the process.

Like what:

audit_file_dest = $ORACLE _home/rdbms/audit

$ ls-l $ORACLE _home/rdbms/audit

-RW-RW----1 ora92 dba 881 Mar 09:57 Ora_13264.aud

$ ps-ef|grep 13264

Ora92 13264 13235 0 09:56:43? 0:00 oracleV92 (description= (local=y)

Sql> select spid, program, username from V$process;

SPID program USERNAME

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

...

13264 [email protected] (TNS v1-v3) ora92

Note:Windows NT does not use the audit_file_dest parameter, and if you use OS settings, the audit information is stored in Windows NT In the event log. The following chapters will be devoted to this.

3. Verify that the audit-related tables have been installed

sqlplus> connect/as SYSDBA

sqlplus> select * from sys.aud$; -- no record returned

sqlplus> select * from Dba_audit_trail; - no records returned

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

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.

   

4. Close and restart the database

5. set up the required audit information

Here is an example

sql> Connect System/manager

Sql> Grant audit system to Scott;

Sql> Connect Scott/tiger

Sql> Audit session;

Stop auditing:

sql> noaudit session;

To set an instance of an audit: an audit of access to an attempt to try a password

This section discusses an example of an audit that is used to record examples of attempts to decipher an ORACLE account password through brute-attempt methods:

1. Modify audit-related parameters (refer to the method described in the previous section)

2. Restart the database

3. Setting up Audit information

Sql>audit All by ACCESS whenever not successful

4. Query aud$

sql> Select ReturnCode, action#, UserID, Userhost, Terminal,timestamp

From aud$

returncode action# USERID userhost TERMINAL

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

1017 SCOTT WPRATA-BR

1017 SCOTT WPRATA-BR

1017 SCOTT WPRATA-BR

The meaning of ORA-1017 is the wrong username password. By looking at the aud$ table, you can clearly see wprata-br trying to decipher SCOTT 's password. The following stored procedure can be used to analyze the aud$ table 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-dd:hh24:mi:ss ')

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;

/

The result of the execution:

Sql>set serveroutput on;

Sql> Execute Auditlogin (' 2004-01-01 ', 2);

USER Broken Alarm:sys

host:,term:xuji,time:2004-09-22:11:08:00

Host:,term:xuji,time:2004-09-22:11:08:01

Host:,term:xuji,time:2004-09-22:11:09:29

Host:,term:xuji,time:2004-09-22:11:09:29

The PL/SQL process has completed successfully.

move an audit-related table to another table space

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

Sql>connect/as Sysdba;

Sql>alter table aud$ move tablespace;

Sql>alter index I_AUD1 rebuild online tablespace;

Sql> ALTER TABLE audit$ move tablespace;

sql> ALTER index I_audit rebuild online tablespace;

Sql> ALTER TABLE audit_actions move tablespace;

sql> ALTER index i_audit_actions rebuild online tablespace;

Go ORACLE Audit Capabilities