{"id":6999,"date":"2022-11-10T10:29:25","date_gmt":"2022-11-10T10:29:25","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/database-auditing-monitoring-and-securing-database\/"},"modified":"2026-11-03T19:27:26","modified_gmt":"2026-11-03T19:27:26","slug":"database-auditing-monitoring-and-securing-database","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/de\/blogs\/database-auditing-monitoring-and-securing-database\/","title":{"rendered":"Database Auditing: Monitoring and Securing Database"},"content":{"rendered":"

Database auditing monitors the activities occurring in a database. Oracle has functions that allow us to audit actions in the database and against the database.<\/p>\n

The Database auditing facility is an action-based process that runs SQL statements that meet a specific requirement.<\/p>\n

Types of Auditing:<\/strong><\/p>\n

    \n
  1. Schema object auditing:<\/strong> It allows us to specify schema objects to be audited.<\/li>\n
  2. SQL statement auditing:<\/strong> It allows us to specify SQL statements to be audited.<\/li>\n
  3. SQL privilege auditing:<\/strong> It allows us to specify system privileges to be audited.<\/li>\n<\/ol>\n

    Scope of Audit:<\/strong><\/p>\n

    \"technical-blog-database-auditing-image-1\"<\/p>\n

     <\/p>\n

    The audit facility allows you to specify the scope of audit actions as follows:<\/p>\n

      \n
    • By user:<\/strong> This allows us to audit a certain user, by default it is all users.<\/li>\n
    • Whenever Successful\/Whenever unsuccessful<\/strong>: This allows us to specify whether we want auditing to always occur or only whether the specific action was successful or unsuccessful. By default, it is BOTH.<\/li>\n
    • By Session\/ By Access:<\/strong> This allows us to assign how frequently audit files are to be created. By default, it is by access. A stream pool<\/em> is utilized to build a buffer for the data pump.<\/li>\n<\/ul>\n

      Limits of Auditing:<\/strong> Database auditing service works on the statement level only. It will record a scenario in case a specific user has run a select query against a particular table, but it is incapable of telling you which rows have been retrieved. This enables auditing to the database.<\/p>\n

      Implementing Auditing:<\/strong> Auditing can be activated by following steps:<\/p>\n

        \n
      • Enable auditing at the database level with the init.ora parameter (AUDIT_TRAIL).<\/li>\n
      • Enable the level of auditing through the audit SQL statement.<\/li>\n<\/ul>\n

        The audit_trail parameter must be enabled for auditing to work. Available values are DB, NONE, and OS. Parameters can be as below:<\/p>\n

        Audit_trail=true[audit_trail=db] or audit_trail=false[audit_trail=none] or audit_trail=os Audit_trail=db: <\/code><\/p>\n

        This enables auditing to the internal data dictionary<\/p>\n

        Audit_trail=os:<\/code> This enables auditing of the OS audit trail. When set to OS another parameter AUDIT_FILE_DEST must be set in the init.ora<\/code> for dumping audit_trail files in the OS. AUDIT_TRAIL=none<\/code>: This disables all auditing.<\/p>\n

        Note:<\/strong> DB bounce is not needed if you change the objects auditing. DB bounce is needed only if you switch off or on all auditing.<\/p>\n

        Audit trail views<\/strong>:<\/p>\n

        Dba_audit_object, dba_audit_session, dba_audit_statement, dba_audit_trail, dba_object_audit_opts, dba_stmt_audit_opts, dba_priv_audit_opts, dba_audit_exists, audit_actions<\/code><\/p>\n

        Scenarios:<\/strong><\/p>\n

        1. Statement Level Auditing:<\/p>\n

        SYS > audit create session whenever not successful.
        \nSYS > select user_name, audit_option, success,failure from dba_stmt_audit_opts;
        \nSYS > save dba_stmt_audit_opts
        \nSYS > select os_username, username, terminal, action_name, to_char(timestamp, \u2018dd\/mm\/yyyy:hh24:mi:ss\u2019) time_stamp from dba_audit_session;
        \nSYS > save dba_audit_sessions;
        \nSYS > audit table by U_SCOTT;
        \nSYS > @dba_stmt_audit_opts
        \nSYS > conn U_SCOTT\/U_SCOTT
        \nU_SCOTT > create table test (tno number);
        \nSYS > select os_username, username,owner,action_name,obj_name,to_char(timestamp, \u2018dd\/mm\/yyyy:hh24:mi:ss\u2019) as time_stamp from dba_audit_trail;
        \nSYS > save dba_audit_trail;<\/p>\n

        2. Object Level Auditing:<\/p>\n

        SYS > audit select on u_scott.dept whenever successful;
        \nSYS > select owner,object_name,object_type,sel,upd,del from dba_obj_audit_opts where owner=\u2019U_SCOTT\u2019;
        \nSYS > save dba_obj_audit_opts
        \nU_SCOTT > select * from dept;
        \nSYS > @dba_audit_trail
        \nSYS > audit update on u_scott.emp by access whenever at unsuccessful;
        \nSYS > @dba_obj_audit_opts
        \nU_SCOTT > update emp set sal=6000 where empno=\u2019U_SCOTT\u2019;
        \nU_SCOTT > update emp set sal=7000 where empno=\u2019U_SCOTT\u2019;
        \nU_SCOTT > update emp set sal=8000 where empno=\u2019U_SCOTT\u2019;
        \nSYS > @dba_audit_trail<\/p>\n

        3. Privilege Level Auditing:<\/p>\n

        SYS > audit create view by u_scott whenever successful;
        \nSYS > select user_name, privilege, success, failure from dba_priv_audit_opts;
        \nSYS > save dba_priv_audit_opts
        \nu_scott > create view v_test as select * from emp; 
        \nSYS > @dba_audit_trail<\/p>\n

        4. Disabling Audit:<\/p>\n

        SYS > @dba_stmt_audit_opts
        \nSYS > noaudit table by u_scott;
        \nSYS > @dba_stmt_audit_opts
        \nSYS > noaudit create session;
        \nSYS > noaudit create view by u_scott;
        \nSYS > @dba_stmt_audit_opts
        \nSYS > @dba_priv_audit_opts
        \nIn DB level init.ora file remove parameter audit_trail=true<\/p>\n

        Fine-Grained Auditing:<\/strong> It is used for monitoring and accessing the data in the database based on the rules or SQL statements. There is an in-built process in the database that obstructs users from skipping the audit.<\/p>\n

        It provides an expandable view in creating different policies to audit DML statements on objects like views and tables.<\/p>\n

        Policies combined with the objects may also define appropriate columns so that any given statement type touching an applicable column is audited.<\/p>\n

        Note:<\/strong> AUDIT_TRAIL parameter is not needed to enable sys auditing or FGA.<\/p>\n","protected":false},"excerpt":{"rendered":"

        Database Auditing: Monitoring and Securing Database<\/p>\n","protected":false},"author":2,"featured_media":7001,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26060","content_type":"blog","created_by":"11255","authored_on":"2023-09-20T09:29:25+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Understand the critical importance of database auditing in monitoring, securing, and ensuring data integrity and compliance.","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26060-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Relational Databases","product":"","publisheddate":"2022-11-10T10:29:25","rxt_solutions":"","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Database Auditing: Monitoring and Securing Database","field_topics":"","field_slug":"Database Auditing: Monitoring and Securing Database","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-6999","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/6999","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=6999"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/6999\/revisions"}],"predecessor-version":[{"id":8713,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/6999\/revisions\/8713"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7001"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=6999"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=6999"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=6999"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}