Solved

oracle auditing

Posted on 2009-05-19
8
1,260 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 35

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 35

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 12c patching 1 61
sql query 5 70
Clone Oracle 12c Database 5 43
Oracle 10g standard edition server with 4 processors 3 38
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

864 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

24 Experts available now in Live!

Get 1:1 Help Now