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.
If you want informations about the AUDIT command, please visit http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm, , in other case, let's audit!
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 see the source of the article.
Source : Oracle-base.com
