In an Oracle Database we can mention following auditing types:
  • Mandatory Auditing
  • Standard Auditing
  • Fine-Grained Auditing
  • SYS Auditing
Mandatory Auditing causes database start-up/shut-down and SYSDBA-SYSOPER login logout information to be written into AUDIT_FILE_DEST. This auditing cannot be turned off and it's always written into operating system directory specified with AUDIT_FILE_DEST.

Standard Auditing is controlled with AUDIT_TRAIL parameter. (NONE,OS,DB,DB_EXTENDED,XML,XML_EXTENDED)
When DB or DB_EXTENDED is used, audit records are written into database (aud$ table). (Not the mandatory auditing records, they're always on OS)
XML options write to operating system in XML format.
When *EXTENDED is used SQLBIND and SQLTEXT information is included in the audit trail. If not used, not included.
By default, Standard Auditing audits SQL statements which use following privileges:
ALTER ANY PROCEDURE | CREATE ANY LIBRARY | DROP ANY TABLE | ALTER ANY TABLE | CREATE ANY PROCEDURE | DROP PROFILE | ALTER DATABASE | CREATE ANY TABLE | DROP USER | ALTER PROFILE | CREATE EXTERNAL JOB | EXEMPT ACCESS POLICY | ALTER SYSTEM | CREATE PUBLIC DATABASE LINK | GRANT ANY OBJECT PRIVILEGE | ALTER USER | CREATE SESSION | GRANT ANY PRIVILEGE | AUDIT SYSTEM | CREATE USER | GRANT ANY ROLE | CREATE ANY JOB | DROP ANY PROCEDURE
It's possible to audit Statements, Privileges and Objects with Standard Auditing. For example:
  • Privilege auditing: audit select any table; 
  • Statement auditing: audit select table; 
  • Object auditing: audit select on SCOTT.SALARY;
Following views give information about current Standard Auditing configuration in the database:
  • DBA_STMT_AUDIT_OPTS; ==> describes current statements being audited across the system
  • DBA_PRIV_AUDIT_OPTS; ==> describes current system privileges being audited across the system
  • DBA_OBJ_AUDIT_OPTS;   ==> describes auditing options for all objects 
You can use the SQL "AUDIT" statement to set auditing options regardless of the setting of AUDIT_TRAIL parameter. However, Oracle Database does not generate audit records until you enable Standard Auditing using AUDIT_TRAIL parameter.
Auditing to OS offers higher performance when compared with DB. 

Fine-Grained Auditing is used to audit operations like: 
  • Accessing a table outside of normal working hours 
  • Logging in from a particular IP address 
  • Selecting or updating a particular table column
DBMS_FGA package is used to manage Fine-Grained Auditing. DBA_AUDIT_POLICIES view describes all fine-grained auditing policies in the database.
It's not mandatory to enable Standard Auditing in order to use Fine-Grained Auditing or SYS Auditing.
SYS AuditingAUDIT_SYS_OPERATIONS parameter (TRUE/FALSE) enables or disables the auditing of SQL statements that directly issued by users connected with SYSDBA or SYSOPER privileges (SQL statements run from within PL/SQL procedures or functions are not audited). These audit records are written into OS.
It's not mandatory to enable Standard Auditing in order to use SYS Auditing.




9 Responses so far.

  1. MSBI remains for Microsoft Business Intelligence. This apparatus utilizes Visual studio alongside SQL server.
    do you want to learn a high level language ?then you get job opportunity for all over world. || https://www.gangboard.com/app-programming-scripting-training/php-training
    https://www.gangboard.com/cloud-computing-training/vmware-training
    https://www.gangboard.com/business-intelligence-training/msbi-training
    https://www.gangboard.com/app-programming-scripting-training/chef-training
    https://www.gangboard.com/cloud-computing-training/openstack-training


  2. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.


    Digital Marketing Training in Chennai

  3. Sathya G says:


    the blog is very useful, interesting and informative. thank you for sharing the blog with us. keep on updating.
    Email Marketing Chennai

  4. priya says:

    I simply couldn’t depart your site before suggesting that I really enjoyed the usual information an individual supply in your visitors? Is going to be again steadily to check out new posts.

    Online Training in Chennai

  5. Priya says:

    Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    oracle training in chennai | oracle training institutes in chennai

  6. Priya says:

    Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    salesforce training in chennai | salesforce training institute in chennai

  7. kalai says:

    very interesting to read,thanks for sharing that wonderful useful information,given programming coding was very excellent and easily observe all provided information.


    Dot Net training

  8. Good post. I learn something totally new and challenging on blogs I stumble upon on a daily basis. It will always be interesting to read articles from other authors and practice something from their websites...


    Dental Hospital In Chennai

  9. Thanks for great topic such a very nice post. about the oracle fusion financials great topics and really best points were started in the blog for more details can below visit .

DOAG 2016

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -