Topic: Oracle Auditing

 

Audit is the monitoring and recording of selected user actions. It is usually used:

U reviews suspicious activities. For example, if the data is deleted by an unauthorized user, the security administrator may decide to audit all connections to the database, and audit the deletion of all tables in the database.

U monitors and collects data about specified database activities. For example, DBA can collect statistics on the modified and executed logic.

Oracle supports three audit types:

U Statement Audit: audits certain types of SQL statements without specifying structures or objects.

U privileged audit: audits the use of system privileges that execute the corresponding actions.

U Object audit: audits specified statements on a special mode object.

Oracle allows the following audit options:

U audit statement is successfully executed, not successfully executed, or both.

U executes the audit statement for each user session or audit the statement each time.

U audits the activities of all or specified users.

When Database Auditing is enabled, audit records are generated during statement execution. Audit records include audit operations, operations performed by users, date and time of operations, and so on. Audit records can be stored in data dictionary tables (called audit records) or operating system audit records. Database Audit records are in the AUD $ table in SYS mode.

Set Oracle Audit
Follow these steps to configure the Oracle audit function:

 

1. modify the parameter file (init <Sid>. ora. If you use the server parameter file alter system set <parameter >=< value> scope = spfile | both, For details, refer to the introduction to the parameter file in section 1.1), set the audit_trail parameter, and restart the database. The values of audit_trail are as follows:

L dB/true: Enable the audit function and store the audit results in the SYS. AUD $ table of the database.

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

L db_extended: Provides the DB/true function. In addition, fill in the sqlbind and sqltext fields of AUD $.

L NONE/false: Disable the audit function.

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

If the operating system supports setting audit_trail = OS, files are automatically stored in audit_file.

The directory specified by _ DEST, and the file name contains the PID of the process.

For example:

Audit_file_dest = $ ORACLE_HOME/rdbms/Audit

$ LS-L $ ORACLE_HOME/rdbms/Audit

-RW ---- 1 ora92 DBA 881 Mar 17 ora_13264.aud

$ PS-Ef | grep 13264

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

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

Spid program Username

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

...

13264 Oracle @ frhp11 (TNS V1-V3) ora92

Note: Windows NT does not use the audit_file_dest parameter. If OS is used, audit information is stored in Windows NT Event Logs. The following sections will provide a special introduction.

3. Check whether the audit-related tables have been installed.

 

Sqlplus> connect/As sysdba

Sqlplus> select * From SYS. AUD $; -- no record is returned

Sqlplus> select * From dba_audit_trail; -- no record is returned

If the table does not exist during the above query, it indicates that the audit-related table has not been installed and needs to be installed.

 

Sqlplus> connect/As sysdba

Sqlplus> @ $ ORACLE_HOME/rdbms/admin/cataudit. SQL

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

4. Shut down and restart the database

5. Set the required audit information

The following 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;

Set audit instance: Audit of access attempts to try passwords
This section describes an example of an audit used to record an attempt to crack the password of an Oracle account by means of a brutal trial:

 

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

2. Restart the database

3. Set 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 100 Scott WPRATA-BR

1017 100 Scott WPRATA-BR

1017 100 Scott WPRATA-BR

 

The ORA-1017 means the wrong user name password. By viewing the AUD $ table, you can clearly see that the WPRATA-BR tried to crack Scott's password. You can use the following stored procedure to analyze the AUD $ table and 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 = '000000' 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 = '20140901' 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;

Endloop;

Close C2;

End if;

Endloop;

Close C1;

End;

/

The execution result is as follows:

SQL> set serveroutput on;

SQL> execute auditlogin ('2017-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 is successfully completed.

Move audit-related tables to other tablespaces
Because audit-related tables such as AUD $ are stored in the system tablespace, it is best to move AUD $ to other tablespaces to protect the system tablespace without affecting system performance. You can use the following statement to move:

 

SQL> connect/As sysdba;

SQL> ALTER TABLE AUD $ move tablespace <New tablespace>;

SQL> alter index I _aud1 rebuild online tablespace <New tablespace>;

SQL> ALTER TABLE audit $ move tablespace <New tablespace>;

SQL> alter index I _audit rebuild online tablespace <New tablespace>;

SQL> ALTER TABLE audit_actions move tablespace <New tablespace>;

SQL> alter index I _audit_actions rebuild online tablespace <New tablespace>;