Wednesday , July 26 2017
Home / Oracle DBA / ORACLE AUDITING

ORACLE AUDITING

ORACLE AUDITING

  1. It is the process of recording user actions in the database.
  2. Auditing is enabled by setting AUDIT_TRAIL to
    1. None – no auditing enabled (default)
    2. DB – audited information will be recorded in AUD$ table and can be viewed using DBA_AUDIT_TRAIL view
Note: AUDIT_TRAIL=DB is default in 11g i.e auditing is enabled by default in 11g
    1. OS – audited information will be stored in the form of trace files at OS level. For this we need to set AUDIT_FILE_DEST parameter.
By default audit file destination will be $ORACLE_HOME/admin/SID/a dump
    1. DB, Extended – it is same as DB option but will still record info like SQL_TEXT, BIND_VALUE etc.
    2. XML – it will generated XML files to store auditing information.
    3. XML, Extended – same as XML but will record much more information.
  1. Even though we set AUDIT_TRAIL parameter to some value, oracle will not start auditing until one of the following types of auditing commands are issued.
    1. Statement level auditing
    2. Schema level auditing
    3. Object level auditing
    4. Database auditing (it is only till 9i. due to performance issues it was removed from 10g)
  2. By default some activites like startup & shutdown of database, any structural changes to database are audited and recorded in alert log file.
  3. If auditing is enabled with DB, then we need to monitor space in SYSTEM tablespace as there is a chance of getting full when more and more information is keep on recorded.
  4. SYS user activities can also be captured by setting AUDIT_SYS_OPERATIONS to TRUE.
  5. Auditing should use following scopes.
    1. Whenever successful / not successful
    2. By session / By access
  6. Disadvantage of auditing is, it will not capture changed values. For that DBA will use triggers. This is replaced with Fine Grained Auditing (FGA) which will capture old and new values when a record is modified.
Note: when we use triggers, they will create separate tables to store the audited information which are called trigger tables. To access them we need to create indexes and maintain them which is a quite difficult task
  1. FGA can be initiated using DBMS_FGA and by creating and setting audit policies. Information that is captured can be viewed using DBA_FGA_AUDIT_TRAIL view.
  2. Enabling auditing at database level will have adverse impact on the database performance.

COMMANDS

# To enable auditing
SQL> alter system set audit_trail=DB/OS/XML scope=spfile;
Note: AUDIT_TRAIL parameter is static and require a restart of database before going to be effective
# To audit what is required
SQL> Audit create table;   statement level auditing
SQL> Audit update on table SALARY; object level auditing
SQL> Audit all by scott; schema level auditing
SQL> Audit session by scott;
SQL> Audit all privileges;
# To turn off auditing
SQL>Noaudit session;
SQL>Noaudit update on table SALARY;
SQL>Noaudit all privileges;
Read More:

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *