Oracle Database Audit Overview

Source: Internet
Author: User
Oracle provides three technologies for SYSDBA audit: 1. Database Audit User Privilege, command execution and access table, and login status. 2. Database trigger

Oracle provides three technologies for SYSDBA audit: 1. Database Audit User Privilege, command execution and access table, and login status. 2. Database trigger

Oracle provides three technologies for SYSDBA Auditing:

1. Database Audit user privileges, commands executed and tables accessed, and logon status

2. Use a database trigger to initiate a value-based audit;

3. fine-grained auditing can track which rows in the table are accessed;

When the initialization parameter file AUDIT_SYS_OPERATIONS is set to true, the statements executed by SYSDBA and SYSOPER are recorded in the audit file of the operating system;

Standard audit

Before database audit, the AUDIT_TRAIL initialization parameter file must be set:

  • NONE: audit is not performed.
  • OS: the audit file is written to the Operating System -- the Application Log on windws, or the AUDIT_FILE_DEST directory on Uinx
  • DB: The audit file is written to the database's data dictionary table: SYS. AUD $
  • DB_EXTENDED
  • XML
  • XML_EXXTENDED
  • Example:

    Audit create any trigger; -- audit all trigger creation operations

    Auditselect any table by session;

    Audit insert on scott. emp whenever successful; -- another option is whenever not successful;

    Audit allon scott. emp;

    Audit session whenever not successful; -- audit User Logon;

    -- View audit information generated by the system

    Select * fromdba_audit_trail;

    Other audit information views also include:

    DBA_AUDIT_OBJECT, DBA_AUDIT_STATEMENT, DBA_AUDIT_SESSION

    Use triggers to audit values

    A database trigger is a block of PL/SQL code that wil runautomaitcally whenever in INSERT, UPDATE, OR DELETE is executed against a table.

    Example:

    Create orreplace trigger system. creditrating_audit

    Afterupdat of creditrating

    ON scott. customers

    REFERENCINGNEW AS NEW OLD AS OLD

    FOR EACHROW

    BEGIN

    IF: old. creditrationg! =: New. creditrating THEN

    Insert into system. creditrating_audit

    VALUES (sys_context ('userenv', 'OS _ user '),

    Sys_context ('userenv', 'IP _ address '),

    : New. customer_id | 'credit rating changed from '|: old. creditrating | 'to' |: new. creditrating );

    End if;

    END;

    /

    Fine-Grained audit Fine-Grained Auditing (FGA)

    FGA isconfigured with the package DBMS_FGA

    SQL> execute dbms_fga.add_policy (-

    Object_schema => 'hr ',-

    Object_name => 'ployees ',-

    Policy_name => 'pol1 ',-

    Audit_condition => 'department _ id = 80 ',-

    Audit_column => 'salary ');

    DBA_AUDIT_TRIALis used for standard database auditing;

    DBA_FGA_AUDIT_TRAIL: is used for fine-grained auditing;

    DBA_COMMON_AUDIT_TRAIL: is used for both;

    To seethe results of auditing with triggers, you must create your own views thataddress your own tables;

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.