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.




15 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 .

  10. You have shared very useful details with me. Thanks for your great effort.

    DBA course | Oracle dba course

  11. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here"Oracle Training in Chennai".

Powered by Blogger.

Page Views

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