[oracle@localhost ~]$ export ORACLE_SID=path

[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 6 15:07:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;
NAME
---------
PATH

SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
audit_trail string NONE

Here we just check for the parameter audit_trail and it is NONE, so our
auditing is disabled. So we execute the following command to start
auditing.

SQL> alter system set audit_trail=TRUE;
alter system set audit_trail=TRUE
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

But we get the error when we execute the above command, as audit_trail is
a static parameter and it canít be changed. So we

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Open the Pfile in vim editor and set audit_trail=TRUE into it.
SQL> host
[oracle@localhost ~]$ vi
/u01/app/oracle/admin/path/pfile/init.ora.8212010115339

[oracle@localhost ~]$ export ORACLE_SID=path

[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 6 15:17:12 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
audit_trail string NONE

the parameter audit_trail is still set to NONE because the changes were
made in the Pfile and the database starts with the spfile first and as no
changes were done in the spfile so the parameter value doesnít change. So
we shut the db and start it again using the pfile.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=/u01/app/oracle/admin/path/pfile/init.ora.8212010115339

ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
audit_trail string TRUE

SQL> show user
USER is "SYS"

Now some practical example on Auditing, we audit on the privilege Create
Session whenever it is unsuccessful.

SQL> audit create session whenever not successful;
Audit succeeded.

SQL> select user_name,audit_option,success,failure from
dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ------------- --------- -------------
CREATE SESSION NOT SET BY ACCESS

SQL> save dba_stmt_audit_opts
Created file dba_stmt_audit_opts.sql

SQL> Select os_username,username,terminal,action_name,to_char
(timestamp,'dd/mm/yyyy:hh24:mi:ss') timestamp from dba_audit_session;
no rows selected

SQL> save dba_audit_sessions

SQL> audit table by u_scott;
audit table by u_scott
*
ERROR at line 1:
ORA-01435: user does not exist

SQL> @dba_stmt_audit_opts
USER_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ------------- --------- -------------
CREATE SESSION NOT SET BY ACCESS

SQL> conn uscott/uscott
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;

OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ----- ----------- -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51

SQL> create user u_scott identified by u_scott;
User created.

SQL> grant connect,resource to u_scott;
Grant succeeded.

SQL> audit table by u_scott;
Audit succeeded.

SQL> conn u_scott/u_scott
Connected.

SQL> create table audtest(tno number);
Table created.

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35

SQL> show user
USER is "SYS"

SQL> audit select on u_scott.audtest whenever successful;
Audit succeeded.

SQL> select owner,object_name,object_type,sel,upd,del from
dba_obj_audit_opts where owner='U_SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE SEL UPD DEL
---------- ------------- ----------- ---- ----- ----
U_SCOTT AUDTEST TABLE S/- -/- -/-

SQL> save dba_obj_audit_opts

SQL> conn u_scott/u_scott
Connected.

SQL> select * from audtest;
no rows selected

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:15:45:27

SQL> audit update on u_scott.audtest by access whenever not successful;
Audit succeeded.

SQL> select owner,object_name,object_type,sel,upd,del from
dba_obj_audit_opts where owner='U_SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE SEL UPD DEL
---------- ------------- ----------- ---- ----- ----
U_SCOTT AUDTEST TABLE S/- -/A -/-

SQL> conn u_scott/u_scott
Connected.
SQL> insert into audtest values(10);
1 row created.
SQL> insert into audtest values(20);
1 row created.
SQL> insert into audtest values(30);
1 row created.
SQL> commit;
Commit complete.
SQL> update audtest set tno=11 where tno=30;
1 row updated.
SQL> commit;
Commit complete.

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:15:45:27

SQL> conn u_scott/u_scott
Connected.

SQL> update audtest set tno=44 where tno=77;
0 rows updated.

SQL> update audtest set tno=55 where ename='GG';
update audtest set tno=55 where ename='GG'
*
ERROR at line 1:
ORA-00904: "ENAME": invalid identifier

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:15:45:27
Oracle U_SCOTT U_SCOTT UPDATE AUDTEST 06-10-2010:15:55:52

SQL> Audit create view by u_scott whenever successful;

SQL> select user_name,privilege,success,failure from dba_priv_audit_opts;
USER_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ----------------------------------------
CREATE SESSION NOT SET BY ACCESS
U_SCOTT CREATE VIEW BY ACCESS NOT SET

SQL> save dba_priv_audit_opts
Created file dba_priv_audit_opts.sql

SQL> conn u_scott/u_scott
Connected.

SQL> create view v_test as select * from audtest;
create view v_test as select * from audtest
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:15:45:27
Oracle U_SCOTT U_SCOTT UPDATE AUDTEST 06-10-2010:15:55:52

SQL> show user
USER is "SYS"

SQL> grant create view to u_scott;
Grant succeeded.

SQL> conn u_scott/u_scott
Connected.

SQL> create view v2 as select * from audtest;
View created.

SQL> conn / as sysdba
Connected.

SQL> select os_username,username,owner,action_name,obj_name,
to_char(timestamp,'dd-mm-yyyy:hh24:mi:ss') as timestamp
from dba_audit_trail;
OS_USERNAME USERNAME OWNER ACTION_NAME OBJ_NAME TIMESTAMP
----------- -------- ------- ------------ -------- ------------------
Oracle USCOTT LOGON 06-10-2010:15:26:51
Oracle U_SCOTT U_SCOTT CREATE TABLE AUDTEST 06-10-2010:15:28:35
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:15:45:27
Oracle U_SCOTT U_SCOTT UPDATE AUDTEST 06-10-2010:15:55:52
Oracle U_SCOTT U_SCOTT CREATE VIEW V2 06-10-2010:16:08:07
Oracle U_SCOTT U_SCOTT SESSION REC AUDTEST 06-10-2010:16:08:07

SQL> show user
USER is "SYS"

SQL> grant sysdba,dba to u_scott with admin option;
Grant succeeded.

SQL> select grantee,privilege from dba_sys_privs where grantee not in
('SYS','DBA') and grantee not like '%ADMIN%' and admin_option='YES';
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
U_SCOTT UNLIMITED TABLESPACE
SYSTEM UNLIMITED TABLESPACE

NOW Look into something called Fine Grain Auditing (FGA)
SQL> begin
2 dbms_fga.add_policy('U_SCOTT','AUDTEST','myauditpo licy','tno>70');
3 end;
4 /
PL/SQL procedure successfully completed.

SQL> select * from u_scott.audtest where tno<13;
TNO
----------
10
11

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;
no rows selected

SQL> select * from u_scott.audtest where tno>13;
TNO
----------
20

SQL> insert into u_scott.audtest values(444);
1 row created.

SQL> commit;
Commit complete.

SQL> conn u_scott/u_scott
Connected.

SQL> select * from u_scott.audtest where tno>70;
TNO
----------
444

SQL> conn / as sysdba
Connected.

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;
TIMESTAMP DB_USER OS_USER SQL_TEXT
---------- --------- -------- -----------------------------------------
06-OCT-10 U_SCOTT oracle select * from u_scott.audtest where no>70
ORACLE SECURITY

The Oracle Instructor Shoaibansari73@gmail.com