Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

How do you Auditing of Schema Objects

I have this description of what objec auditing is. However, I still don't understand how it should be implemented or what I should change. Here is a description:

Description:  Object auditing data can be reviewed in the Audit Configuration report. Object level auditing can be enabled for Oracle objects such as tables, views, stored procedures, functions, and packages. One or more operations for each object can be audited for all users or specific users. In addition, you may choose to generate one record for each access to an object (BY ACCESS) or to create one audit record per session (BY SESSION).  Schema object auditing required by DISA is limited to RENAME on custom application objects and UPDATE and DELETE operations on the audit data table if applicable.

Manual Check:  From SQL*Plus:
select count(*) from ALL_DEF_AUDIT_OPTS where ren='A/A';

I ran the manual check and it returned no rows. Is this ren field suppose to be populated? What does it mean? Will populating it break anything? Could someone who understands this please help me? I need to resolve this today. Thanks!


0
sikyala
Asked:
sikyala
1 Solution
 
schwertnerCommented:
Auditing:
~~~~~~~~~
Auditing can be set on statement level, object level and privilege level.
Also administrative connections are logged.
 
1. Auditing administrative connections
--------------------------------------
Administrative connections are CONNECT INTERNAL and CONNECT / AS SYSDBA.
There are some good examples of output of audited administrative connections
for Unix and for NT in:



In RDBMS ver. 9.2 and up, it is now possible to audit the SYS user with the
parameter AUDIT_SYS_OPERATIONS , for details, please check this note:




2. Auditing on object Level
---------------------------
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  


 
4. Auditing statements                                                            
----------------------
Statement auditing is the selective auditing of related groups of statements for
a type of database structure or type of schema object for ddl (data definition
language) or dml (data manipulation language) statements.
For example when 'audit table' is audited create, alter and drop table
statements are audited.
When 'audit select table' is audited all select statements from table, view,
snapshot.. will be audited.

The difference with privilege auditing is that in the latter case a system
privilege is audited. For example the 'audit create table' will only audit
create table and not alter or drop table statements.
When similar statement and privilege options are set only one audit record is
generated.

Example: auditable statements
-----------------------------
The statements taht can be adited can be seen from stmt_audit_option_map.

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

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

      OPTION# NAME                                       PROPERTY
  ---------- ---------------------------------------- ----------
           3 ALTER SYSTEM                                      0
           4 SYSTEM AUDIT                                      0
           5 CREATE SESSION                                    0
           6 ALTER SESSION                                     0
           7 RESTRICTED SESSION                                0
           8 TABLE                                             0
           9 CLUSTER                                           0
          10 CREATE TABLESPACE                                 0
          11 ALTER TABLESPACE                                  0
          12 MANAGE TABLESPACE                                 0
          13 DROP TABLESPACE                                   0
   ..... not entire result is shown ......  
         221 CONTEXT                                           0
         234 ON COMMIT REFRESH                                 0
         235 EXEMPT ACCESS POLICY                              0
         236 RESUMABLE                                         0
         237 SELECT ANY DICTIONARY                             0
         238 DEBUG CONNECT SESSION                             0
         239 DEBUG CONNECT USER                                0
         240 DEBUG CONNECT ANY                                 0
         241 DEBUG ANY PROCEDURE                               0
         242 DEBUG PROCEDURE                                   0

   165 rows selected.                  

    SQL> select * from stmt_audit_option_map
         where name like '%TABLE%';

 Result:

     OPTION# NAME                                       PROPERTY
  ---------- ---------------------------------------- ----------
           8 TABLE                                             0
          10 CREATE TABLESPACE                                 0
          11 ALTER TABLESPACE                                  0
          12 MANAGE TABLESPACE                                 0
          13 DROP TABLESPACE                                   0
          14 TABLESPACE                                        0
          15 UNLIMITED TABLESPACE                              0
          40 CREATE TABLE                                      0
          41 CREATE ANY TABLE                                  0
          42 ALTER ANY TABLE                                   0
          43 BACKUP ANY TABLE                                  0
          44 DROP ANY TABLE                                    0
          45 LOCK ANY TABLE                                    0
          46 COMMENT ANY TABLE                                 0
          47 SELECT ANY TABLE                                  0
          48 INSERT ANY TABLE                                  0
          49 UPDATE ANY TABLE                                  0
          50 DELETE ANY TABLE                                  0
          54 ALTER TABLE                                       0
          57 LOCK TABLE                                        0
          58 COMMENT TABLE                                     0
          65 SELECT TABLE                                      0
          66 INSERT TABLE                                      0
          67 UPDATE TABLE                                      0
          68 DELETE TABLE                                      0
          69 GRANT TABLE                                       0
                                                                       

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

   SQL> connect system/manager
   SQL> audit table by scott, system;
 => Audit succeeded.
 

 check: Which statements are audited

   SQL> col user_name format a8
   SQL> col proxy_name format a6
   SQL> col audit_option format a9
   SQL> col privilege format a15
   SQL> connect system/manager
   SQL> select * from dba_stmt_audit_opts;

 Result:

   USER_NAM PROXY_ AUDIT_OPT SUCCESS    FAILURE
   -------- ------ --------- ---------- ----------
   SYSTEM          TABLE     BY ACCESS  BY ACCESS
   SCOTT           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> drop table t1;
     => table dropped
   SQL> connect system/manager
   SQL> create table scott.t1(c1 number);
     => table created
   SQL> drop table no;
     =>            *
        ERROR at line 1:
        ORA-00942: table or view does not exist

 Results of auditing:

   SQL> connect system/manager

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

   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
           12 DROP TABLE                  SCOTT
            1 CREATE TABLE                SYSTEM
           12 DROP TABLE                  SYSTEM

   SQL> select username, priv_used, ses_actions from dba_audit_object;

  Result:

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

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

   SQL> connect system/manager
   SQL> audit select table by scott, system;
 => Audit succeeded.
 

 Check: Which statements are audited

   SQL> col user_name format a8
   SQL> col proxy_name format a6
   SQL> col audit_option format a13
   SQL> col privilege format a15
   SQL> connect system/manager
   SQL> select * from dba_stmt_audit_opts;

 Result:

  USER_NAM PROXY_ AUDIT_OPTION  SUCCESS    FAILURE
  -------- ------ ------------- ---------- ----------
  SYSTEM          SELECT TABLE  BY SESSION BY SESSION
  SCOTT           SELECT TABLE  BY SESSION BY SESSION
   
 Generate some audit information:

   SQL> connect scott/tiger
   SQL> delete from emp where ename='KING';
       
   SQL> connect system/manager
   SQL> insert into scott.emp (empno, ename) values (1, 'TEST');
   SQL> select * from scott.emp;

 Results of auditing:

   SQL> connect system/manager

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

   SQL> select username, priv_used, ses_actions from dba_audit_object;


 Result:

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

   SQL> connect system/manager

   SQL> select action, action_name, username
        from dba_audit_trail ;

 Result:

      ACTION ACTION_NAME                 USERNAME
  ---------- --------------------------- --------
         103 SESSION REC                 SCOTT
         103 SESSION REC                 SCOTT
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SCOTT
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM
         103 SESSION REC                 SYSTEM

0
 
sikyalaSenior Database AdministratorAuthor Commented:
I learned that needed to audit rename by access on the schema objects within the database as indicated in the following statement:

> Schema object auditing required by DISA is limited to RENAME on custom application objects

The way to do this is run the following statement:

AUDIT RENAME ON DEFAULT BY ACCESS;
0
 
szadbaCommented:
Can I audit BY ACCESS and BY SESSION both at the same time?
For example:
AUDIT SELECT, INSERT, UPDATE, DELETE
     ON schema.dept BY ACCESS;

AUDIT SELECT, INSERT, UPDATE,DELETE ON schema.dept  BY SESSION;
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now