APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 37,356 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    September 2009
    M T W T F S S
    « Aug   Jan »
     123456
    78910111213
    14151617181920
    21222324252627
    282930  
  • Archives

  • Authors

Database Audit

Posted by appsinfo on September 1, 2009

Oracle has provided built-in audit capabilities for many years and in the most recent versions, this auditing is very sophisticated. The Fine Grained Auditing (FGA) that you can use now can provide audits for all DML against a database, a schema or specific objects. FGA can also audit who was selecting data and not just who updated data. Oracle can also track who changed database objects (DDL, like alter table or compile procedure), what they did and when they did it.

The first thing to do is to activate audit which is disabled by default. To do so, use the command:

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

Then, you have to restart the database, using SHUTDOWN, and STARTUP to start it up again.

First of all, we create a new user:

CONNECT sys/pass AS SYSDBA

CREATE USER audit_user IDENTIFIED BY pass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRAND connect TO audit_user;
GRAND create table, create procedure TO audit_user;

Then, let’s audit all operations of our user:

AUDIT ALL BY audit_user BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_user BY ACCESS;

This will audit all DDL and DML queries, and some system events, like logon/logoff.

Now, we connect with the user and make some operations, in order to be audited.

CONNECT audit_user/pass

CREATE TABLE tabTest ( id NUMBER );
INSERT INTO tabTest (id) VALUES (1);
UPDATE tabTest SET id = id;
SELECT * FROM tabTest;
DELETE FROM tabTest;
DROP TABLE tabTest;

Now that we have some interesting stuff to look at in the audit trail, let’s go!

COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username, extended_timestamp, owner, obj_name, action_name
FROM dba_audit_trail
WHERE owner = ‘AUDIT_USER’
ORDER BY timestamp;

And the result is:
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
———- ———————————– ———- ——– —————————-
AUDIT_USER 25-APR-2007 19:22:06.992930 +01:00 AUDIT_USER TABTEST CREATE TABLE
AUDIT_USER 25-APR-2007 19:22:19.296248 +01:00 AUDIT_USER TABTEST INSERT
AUDIT_USER 25-APR-2007 19:22:34.234981 +01:00 AUDIT_USER TABTEST UPDATE
AUDIT_USER 25-APR-2007 19:22:46.776770 +01:00 AUDIT_USER TABTEST SELECT
AUDIT_USER 25-APR-2007 19:22:57.049840 +01:00 AUDIT_USER TABTEST DELETE
AUDIT_USER 25-APR-2007 19:23:03.705906 +01:00 AUDIT_USER TABTEST DROP TABLE

6 rows selected.

We used the view dba_audit_trail, but there are other views where you can find some more information about audit.

For further information, please refer to Oracle 10g documentation or metalink.

Advertisements

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: