//
you're reading...
Administration, Security

Securing Oracle Database – Standard Auditing (I)

Auditing helps you in monitor user’s database activity and records what was done in your database. It will help you to track their activity, how they performed it, what the command they used or their logon attempt whether successful or not. In short with the help of auditing you can able to track down any unauthorized actions.

When we enable database auditing, Oracle records these audit activities in audit records. These records give the information about the activities performed by the users, when they did it (date and time), what SQL text they used and many more. These records can be stored in either Database or in OS files.

There are different ways of enable auditing such as using custom triggers, Standard Database Auditing, Fine-Grained Auditing (FGA) etc. For now we discuss Oracle’s Standard Database Auditing.

IMPLEMENTING STANDARD AUDITING

Standard auditing allows you audit SQL statements (Insert, update, delete, select etc.), system privileges, and schema objects etc. To enable standard auditing, you need to set AUDIT_TRAIL initialization parameter. The default value is DB, which specify Oracle to write audit records in database. After changing the value of AUDIT_TRAIL parameter you must start your database for the parameter to take effect.

NOTE:
Apart from DB, there are other values for AUDIT_TRAIL parameter are DB_EXTENDED, OS, XML, XML_EXTENDED and NONE. If you use DB or DB_EXTENDED, then Oracle will write audit records in a data-dictionary table SYS.AUD$. You use the view DBA_AUDIT_TRAIL to view the contents of the table. The difference between DB and DB_EXTENDED is that in DB_EXTENDED you can get the actual SQL statement (SQLTEXT) that was issued with bind variable (SQLBIND) for each record when available. If you set parameter to OS, XML or XML_EXTENDED then the operating system file is used to store the audit records. In case of OS it will store audit records in text file format but in the case of XML and XML_EXTENDED audit records (audit trail) is written in form of XML documents. The difference between XML and XML_EXTENDED is the later has all the functionality of XML but also have the SQLTEXT and SQLBIND value for each record. The value NONE disables standard auditing.

dbadmin@O11R1>SELECT * FROM v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 – Production


dbadmin@O11R1>ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=spfile;
System altered.

sys@O11R1>SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@O11R1>STARTUP
ORACLE instance started.

Total System Global Area    962527232 bytes
Fixed Size                  1303972 bytes
Variable Size               562039388 bytes
Database Buffers            394254576 bytes
Redo Buffers                4919296 bytes
Database mounted.
Database opened.

dbadmin@O11R1>SHOW PARAMETER AUDIT_TRAIL;
NAME                    TYPE            VALUE
-----------------       -----------     -----------------
audit_trail             string          DB

dbadmin@O11R1>AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees;
Audit succeeded.

Now we start two sessions. One by user scott and another by user$plsql.

scott@O11R1>SELECT last_name||' ' ||first_name NAME, salary 
            FROM hr.employees 
            WHERE employee_id=109;
NAME                 SALARY
-----------------    ----------
Faviet Daniel        9000

scott@O11R1>UPDATE hr.employees 
            SET salary=11500
            WHERE employee_id=109;
1 row updated.
user$plsql@O11R1>DELETE FROM hr.employees
                 WHERE last_name LIKE 'Jo%';
2 rows deleted.

User scott performed a SELECT operation followed by an UPDATE statement and user$plsql perform a DELETE operation. Now query DBA_AUDIT_TRAIL view:

dbadmin@O11R1>SELECT username, owner, action, action_name, 
              sql_text, TO_CHAR(timestamp,'DD-MON-YYYY HH24:MI:SS') Time 
              FROM dba_audit_trail
              WHERE obj_name='EMPLOYEES';

USERNAME      OWNER    ACTION  ACTION_NAME   SQL_TEXT  TIME
-----------   ------   ------  ------------- --------  -----------
DBADMIN       HR       17      GRANT OBJECT            27-AUG-2012 04:43:58
DBADMIN       HR       17      GRANT OBJECT            27-AUG-2012 04:43:58
SCOTT         HR       103     SESSION REC             27-AUG-2012 04:48:40
USER$PLSQL    HR       103     SESSION REC             27-AUG-2012 04:52:50

We can see that SQL_TEXT column doesn’t have any value because we set AUDIT_TRAIL parameter value to DB. But what happen with ACTION_NAME column? It shows the value SESSION_REC instead of the operation performed by SCOTT and USER$PLSQL.

There are two more audit trail option we can specify audit in statement: BY SESSION and BY ACCESS. If you specify BY SESSION, oracle will create one audit record per session for an audit action whereas specifying BY ACCESS you allow oracle to write one record for each audited statement or operation.

NOTE:
Starting with Oracle Database 11g Release 2 both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements.
Prior to Oracle Database 11g R2 If you do not specify either clause, then BY SESSION is the default. Starting with 11.2.0.2, the standard audit records will by default be generated using the BY ACCESS clause functionality of the AUDIT statement.


Another thing you can do if you want more precise meaning of SESSION_REC is query the SES_ACTIONS column of DBA_AUDIT_TRAIL view.

dbadmin@O11R1>SELECT username, owner, ses_actions 
              FROM dba_audit_trail 
              WHERE obj_name='EMPLOYEES';

USERNAME          OWNER    SES_ACTIONS
---------------   -------- -------------------
. . . .                                     
SCOTT             HR       ---------SS-----
USER$PLSQL        HR       ---S------------
. . . .

12 rows selected.

As you can SES_ACTIONS column looks like a string of 16 characters. Every position has its own meaning and position indicates the operation. Apart from that you can see SES_ACTIONS contain S. S means SUCCESS other values are F stands for FAILURE, B stands for BOTH and stands for NONE.

In the output, SCOTT contains two S at tenth and eleventh position that means SELECT, UPDATE operation is performed and USER$PLSQL contains one S at fourth position that means DELETE operation is performed.

You can find more information about each position’s meaning in SES_ACTIONS column of DBA_AUDIT_TRAIL view in Oracle Database Reference Document.

If we specify BY ACCESS in AUDIT statement, ACTION_NAME column in DBA_AUDIT_TRAIL will show the action performed rather than SESSION_REC.

dbadmin@O11R1>AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY 
              ACCESS;
Audit succeeded.

Now user SCOTT performs an update operation and USER$PLSQL performs INSERT operations.

scott@O11R1>UPDATE hr.employees 
            SET salary=12000
            WHERE employee_id=109;
1 row updated.

user$plsql@O11R1>INSERT INTO hr.employees 
          VALUES(179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', ' 04-JAN-08', 'SA_REP', 6200, .1, 149, 80);
1 row created.

user$plsql@O11R1>INSERT INTO hr.employees 
          VALUES(195, 'Vance', 'Jones', 'VJONES', '650.501.4876', '17-MAR-07', 'SH_CLERK', 2800, NULL, 123, 50);
1 row created.

dbadmin@O11R1>SELECT username, owner, action, action_name, 
              sql_text, TO_CHAR(timestamp,'DD-MON-YYYY HH24:MI:SS') Time 
              FROM dba_audit_trail
              WHERE obj_name='EMPLOYEES';

USERNAME      OWNER    ACTION  ACTION_NAME   SQL_TEXT  TIME
-----------   ------   ------  ------------- --------  -----------
. . . . . . . . 
SCOTT         HR       103     SESSION REC             27-AUG-2012 04:48:40
USER$PLSQL    HR       103     SESSION REC             27-AUG-2012 04:52:50
SCOTT         HR       6       UPDATE                  27-AUG-2012 05:10:35
USER$PLSQL    HR       2       INSERT                  27-AUG-2012 05:14:24
USER$PLSQL    HR       2       INSERT                  27-AUG-2012 05:16:41

15 rows selected.

Now let’s change the value of AUDIT_TRAIL parameter to DB_EXTENDED.

dbadmin@O11R1>alter system set audit_trail=db_extended scope=spfile;
System altered.

sys@O11R1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@O11R1>startup
ORACLE instance started.

Total System Global Area    962527232 bytes
Fixed Size                  1303972 bytes
Variable Size               562039388 bytes
Database Buffers            394254576 bytes
Redo Buffers                4919296 bytes
Database mounted.
Database opened.

dbadmin@O11R1>show parameter audit_trail;
NAME                    TYPE            VALUE
-----------------       -----------     -----------------
audit_trail             string          DB_EXTENDED

NOTE:
You can also specify DB_EXTENDED as DB, EXTENDED.

user$plsql issue two SELECT statements and SCOTT issue an update statement.

user$plsql@O11R1>SELECT first_name, last_name, email, salary, doj 
                 FROM hr.employees;
ERROR at line 1:
ORA-00904: "DOJ": invalid identifier

User$plsql@O11R1>SELECT employee_id, last_name||' '||first_name NAME,  email, 
                 salary FROM hr.employees;
107 rows selected.

scott@O11R1>UPDATE hr.employees
            SET salary=salary+salary*.7
            WHERE salary=2800;
4 rows updated.

Now if we query on DBA_AUDIT_TRAIL view then it will show the SQL statement in the SQL_TEXT column.

dbadmin@O11R1>SELECT username, action_name, sql_text, 
              TO_CHAR(timestamp,'DD-MON-YYYY HH24:MI:SS') Time 
              FROM dba_audit_trail
              WHERE obj_name='EMPLOYEES';

USERNAME    ACTION_NAME   TIME                   SQL_TEXT            
----------  ------------  -------------          --------------------------
. . . . . .                                              
USER$PLSQL  INSERT        27-AUG-2012 05:14:24           
USER$PLSQL  INSERT        27-AUG-2012 05:16:41              
USER$PLSQL  SELECT        27-AUG-2012 05:22:31   select first_name,  
                                                 last_name, email,
                                                 salary, doj
                                                 from hr.employees
USER$PLSQL  SELECT        27-AUG-2012 05:23:10   select employee_id,
                                                 last_name||' '||first_name
                                                 , email, salary
                                                 from hr.employees
SCOTT       UPDATE        27-AUG-2012 05:24:46   update hr.employees 
                                                 set salary=salary+salary*.7
                                                 where salary=2800
18 rows selected.

You can disable auditing any time using NOAUDIT command.

dbadmin@O11R1>NOAUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees;
Noaudit succeeded.

In next part we will see the example of OS, XML and XML_EXTENDED. Hope you will like it.

Note:
It is strongly recommended to audit the database. But you must design your Audit strategy to make sure you focus on those activities that cause the greatest security concerns.

IMPORTANT LINK
Auditing Database Activity

Auditing General Activities with Standard Auditing

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: