Solved

oracle auditing

Posted on 2009-05-19
8
1,255 Views
Last Modified: 2012-05-07
we need to enable auditing for dml and ddl but exclude app users I have 3 users that I want to exclude from auditing but enable auditing for the rest how can I do that?

thanks
0
Comment
Question by:it-rex
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24424973
Using standard auditing you can audit everything a specific user does:

It is recommended to use explicit list of privileges instead of using ALL.

See Oracle docs:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm
--To audit most DDL, you can use:

audit all by user1 by access;
 

--To audit DML:

audit insert any table, update any table, delete any table by user1;

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24424998
Make sure to set audit_trail to a valid setting (db or os is common). It requires reboot.

alter system set audit_trail = db scope = spfile;

shutdown immediate

startup open


0
 
LVL 47

Accepted Solution

by:
schwertner earned 200 total points
ID: 24425422
Objects that can be audited are : tables, views, sequences, packages, stored, procedures/functions. Note that because some objects may be dependant of other objects (example function -> view -> table ) as a result several audit records might be inserted when these objects are audited.

Object auditing options are set for all users of the database and cannot be set
for individual users.

What options can be set? This can be seen from  all_def_audit_opts.

Example object auditing options
-------------------------------

   SQL> connect system/manager
   SQL> select * from all_def_audit_opts;

 Result:

 ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
 --- --- --- --- --- --- --- --- --- --- --- --- ---
 -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-  

These correspond to the following object options respectively:

alter, audit, comment, delete, grant, index, insert, lock, rename,
select, update, reference and execute .

All except reference and execute can be applied to tables, otherwise
you will get the error ora-1982 "invalid auditing option for tables".

Example of auditing scott.emp
-----------------------------

 SQL> connect system/manager
 SQL> audit select on scott.emp by session;
=> Audit succeeded.
 
 Check: Which objects are audited

   SQL> col owner format a7
   SQL> col object_name format a7
   SQL>  select * from dba_obj_audit_opts
         where owner='SCOTT' and OBJECT_NAME='EMP';

 result:

 OWNER   OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
 ------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
 SCOTT   EMP     TABLE     -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-  
 
 Generate some audit information:

   SQL> connect scott/tiger
   SQL> select * from emp;
     => all scott.emp's rows are shown
   SQL> connect t/tl
   SQL> select * from scott.emp;
     => ERROR at line 1:
        ORA-00942: table or view does not exist    
   SQL> connect system/manager
   SQL> select * from scott.emp;
     => all scott.emp's rows are shown
 
 
 Results of auditing:

   SQL> connect system/manager

   SQL> col username format a8
   SQL> col priv_used format 999
   SQL> /

   SQL> select username, priv_used, ses_actions from dba_audit_object
        where obj_name='EMP' and owner='SCOTT';

 Result:


  USERNAME PRIV_USED                                SES_ACTIONS
  -------- ---------------------------------------- ----------------
  SYSTEM   SELECT ANY TABLE                         ---------S---
  T                                                 ---------F---
  SCOTT                                             ---------S---  


3. Auditing on privilege Level
------------------------------

All system privileges can be audited.
The different privileges can be selected from system_privilege_map.
If you attempt to use a value that does not belong to the list, you get the
following error:

    SQL> audit drop snapshot by access;
    audit drop snapshot by access
          *
    ERROR at line 1:
    ORA-00956: missing or invalid auditing option

Example: auditable system privileges
------------------------------------
   SQL> connect system/manager
   SQL> select * from system_privilege_map;

 Result (this result is version dependant and still going up):

     PRIVILEGE NAME
   ---------- ----------------------------------------
           -3 ALTER SYSTEM
           -4 AUDIT SYSTEM
           -5 CREATE SESSION
           -6 ALTER SESSION
           -7 RESTRICTED SESSION
          -10 CREATE TABLESPACE
          -11 ALTER TABLESPACE
          -12 MANAGE TABLESPACE
          -13 DROP TABLESPACE
   ..... not entire result is shown ......  
          -167 GRANT ANY PRIVILEGE
          -172 CREATE SNAPSHOT
          -173 CREATE ANY SNAPSHOT
          -174 ALTER ANY SNAPSHOT
          -175 DROP ANY SNAPSHOT
          -194 WRITEDOWN DBLOW
          -195 READUP DBHIGH
          -196 WRITEUP DBHIGH
          -197 WRITEDOWN
          -198 READUP
         -199 WRITEUP                          


Example of auditing CREATE TABLE by scott and system
----------------------------------------------------

  SQL> connect system/manager
  SQL> audit create table by scott, system;
=> Audit succeeded.
 
This means each create table is audited for system and scott once per session.
Should each create table be audited then the clause 'by access' must be added.

 check: Which privileges are audited

   SQL> col user_name format a7
   SQL> col privilege format a15
   SQL> connect system/manager
   SQL> select * from sys.dba_priv_audit_opts;

 Result:

  USER_NA PRIVILEGE       SUCCESS    FAILURE
  ------- --------------- ---------- ----------
  SCOTT   CREATE TABLE    BY ACCESS  BY ACCESS
  SYSTEM  CREATE TABLE    BY ACCESS  BY ACCESS          
 
 Generate some audit information:

   SQL> connect scott/tiger
   SQL> create table t1 (c1 number);
        create table t1 (c1 number)
                     *
        ERROR at line 1:
        ORA-00955: name is already used by an existing object
   SQL> create table tsc (c1 number);
     => table created
   SQL> connect t/tl
   SQL> create table tsc (c1 number);
     => table created  
   SQL> connect system/manager
   SQL> create table scott.t1(c1 number);
     => create table scott.t1(c1 number)
        ERROR at line 1:
        ORA-00955: name is already used by an existing object
   SQL> create table tsc (c1 number);
     => table created  
 
 Results of auditing:

   SQL> connect system/manager

   SQL> col username format a8
   SQL> col priv_used format 999
   SQL> /

   SQL> select username, priv_used, ses_actions from dba_audit_object;


 Result:

 USERNAME PRIV_USED                                SES_ACTIONS
 -------- ---------------------------------------- -------------------
 SCOTT    CREATE TABLE
 SCOTT    CREATE TABLE
 SYSTEM   CREATE TABLE


   SQL> connect system/manager

   SQL> select action, action_name, username
        from dba_audit_trail ;

 Result:

      ACTION ACTION_NAME                 USERNAME
  ---------- --------------------------- --------
           1 CREATE TABLE                SCOTT
           1 CREATE TABLE                SCOTT  
           1 CREATE TABLE                SYSTEM  

0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 24425945
It is possible to audit only particular users.  The default is to audit all users, but if you add the clause: "by user [user1], [user2], [user3], etc."  you can audit only the actions of particular users.  Be aware then that if someone creates a new user account, the new account will *NOT* be audited, unless you repeat the audit command and include the new user account.

No, you cannot explicitly exclude user accounts from auditing, you can only explicitly include those that you list.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:it-rex
ID: 24427833
now I have 3 heavy users that i need to turn off auditing for all of them what should i do???
please
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 100 total points
ID: 24428008
As markgeer noted, if you are not ALREADY auditing by user (ie. previously you activated auditing for each user with explicit commands), then you cannot turn off auditing by user. You will need to revert all blanket auditing in the database, then activate auditing by user, only including the users you want to audit.
0
 
LVL 11

Author Comment

by:it-rex
ID: 24428260
"You will need to revert all blanket auditing in the database, then activate auditing by user, only including the users you want to audit"
how can I do that?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24443209
If you used a statement like this to turn on auditing for all users in the past:
AUDIT SELECT TABLE, UPDATE TABLE, DELETE TABLE, INSERT TABLE
whenever successful;

Use a statement like this to turn it off:
noaudit SELECT TABLE, UPDATE TABLE, DELETE TABLE, INSERT TABLE;

If you aren't sure which audit options or actions have been turned on, use these queries to help you find them:

select * from dba_priv_audit_opts;
select * from dba_stmt_audit_opts;
select * from dba_obj_audit_opts
where ALT || AUD || COM || DEL || GRA || IND || INS || LOC
|| REN || SEL || UPD || REF || EXE || CRE || REA ||WRI || FBK
<>  '-/--/--/--/--/--/--/--/--/--/--/--/--/--/--/--/--/-';

(Note: that last statement may return a few "false positives" that have a pattern like this:
 /  /  /  /  /  /  /  /  /  /  / -/- /  /  /  /  /
but they can be ignored.)
' /  /  /  /  /  /  /  /  /  /  / -/- /  /  /  /  / ');
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 84
oracle query help 29 62
SQL query of Oracle 10g database. 8 57
Best RAID for a BDD Oracle 4 15
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now