standard fga and fga

two categories: standard audit and fga audit

 

enable standard audit :

parameters : audit_file_dest =<admin/adump> , audit_trail = db_extended  ,audit_sys_operations =true

command : audit all on <table_name> ; audit select,update,insert,delete on <table_name>;

if the command is not issued by sys , you can get the audit trail by query aud$ or dba_audit_trail . (move aud$ to non-system tablespace) , by seting db_extended , you can get the full sqltext in the audit trail.

if the command is issued by sys , you cann't get the row from aud$ or dba_audit_trail , the trail is recored as an operating system file named as ora_<pid>.aud in audit_file_dest folder.

this behavier is to protect from the sys user deleting the audit trail from aud$ table to avoid himself being audited.

when setting audit_sys_operations=false , there still some information in the audit file but with less information.

 

audit session all;

audit select on <table_name > by session -- record once.

audit select on <table_name> by access -- recored everytime.

 

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

 

FGA

SQL> SQL> desc dbms_fga
PROCEDURE ADD_POLICY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT
 OBJECT_NAME                    VARCHAR2                IN
 POLICY_NAME                    VARCHAR2                IN
 AUDIT_CONDITION                VARCHAR2                IN     DEFAULT
 AUDIT_COLUMN                   VARCHAR2                IN     DEFAULT
 HANDLER_SCHEMA                 VARCHAR2                IN     DEFAULT
 HANDLER_MODULE                 VARCHAR2                IN     DEFAULT
 ENABLE                         BOOLEAN                 IN     DEFAULT
 STATEMENT_TYPES                VARCHAR2                IN     DEFAULT
 AUDIT_TRAIL                    BINARY_INTEGER          IN     DEFAULT
 AUDIT_COLUMN_OPTS              BINARY_INTEGER          IN     DEFAULT

SQL> exec dbms_fga.add_policy( object_schema=>'QDL',object_name=>'TAB1',policy_name=>'POL_1', audit_column=>'OBJECT_ID', enable=>true, statement_types=>'SELECT,INSERT,UPDATE',audit_trail=>sys.dbms_fga.db+sys.dbms_fga.extended);

PL/SQL procedure successfully completed.

 

SQL> exec dbms_fga.drop_policy('QDL','TAB1','POL_1');

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.