Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1294
  • Last Modified:

oracle auditing

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
it-rex
Asked:
it-rex
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
mrjoltcolaCommented:
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
 
mrjoltcolaCommented:
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
 
schwertnerCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Mark GeerlingsDatabase AdministratorCommented:
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
 
it-rexAuthor Commented:
now I have 3 heavy users that i need to turn off auditing for all of them what should i do???
please
0
 
mrjoltcolaCommented:
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
 
it-rexAuthor Commented:
"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
 
Mark GeerlingsDatabase AdministratorCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now