Fine-Grained Auditing (FGA).
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAILparameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.
FGA does not record the statement when no rows matched the user’s query, or if the query was not successful due to some reason.
FGA can handle only four types of statements: SELECT, INSERT, UPDATE, and DELETE.
FGA works on PL*SQL package called “DBMS_FGA”.
The DBMS_FGA package contains the following procedures.
-
ADD_POLICY -
DROP_POLICY -
ENABLE_POLICY -
DISABLE_POLICY
In detailed…
In Oracle 9i fine grained auditing was limited to queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.
Now we will create user called aud_test with required privileges.
|
1 2 3 4 5 6 |
SYS>>CREATE USER aud_test IDENTIFIED BY aud_test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; User created. SYS>>GRANT connect TO aud_test; Grant succeeded. SYS>>GRANT create table, create procedure TO aud_test; Grant succeeded. |
Give permissions to audit all operations to the VINOD user.
SYS>> grant connect,resource,unlimited tablespace to vinod identified by vinod;
|
1 2 3 4 5 6 |
SYS>>AUDIT ALL BY vinod by access; Audit succeeded. SYS>>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY vinod by access; Audit succeeded. SYS>> AUDIT EXECUTE PROCEDURE BY VINOD BY ACCESS; Audit succeeded. |
Connect to VINOD user and create a table object.
|
1 2 3 4 |
SYS>> conn vinod/vinod connected. VINOD>>create table sep_aud(inventory_id varchar2(10),catalog_ref_id varchar2(10),stock_level varchar2(10),country_code char(2)); Table created. |
Add policies using dbms_fga packages.
|
1 2 3 4 5 6 7 8 9 10 |
VINOD>>BEGIN DBMS_FGA.add_policy( object_schema => 'VINOD', object_name => 'SEP_AUD', policy_name => 'SEP_AUDIT', audit_condition => NULL, -- Equivalent to TRUE audit_column => 'STOCK_LEVEL', statement_types => 'SELECT,INSERT,UPDATE,DELETE'); END; / PL/SQL procedure successfully completed. |
Now will do few transaction into SEP_AUD table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
VINOD>>INSERT INTO SEP_AUD VALUES('&s1','&s2','&s3','&s4'); Enter value for s1: m45784 Enter value for s2: 5478 Enter value for s3: -1 Enter value for s4: ca old 1: INSERT INTO SEP_AUD VALUES('&s1','&s2','&s3','&s4') new 1: INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') 1 row created. VINOD>>/ Enter value for s1: m8578 Enter value for s2: 4566 Enter value for s3: -1 Enter value for s4: tx old 1: INSERT INTO SEP_AUD VALUES('&s1','&s2','&s3','&s4') new 1: INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') 1 row created. VINOD>>commit; Commit complete. |
Check the audit trail using dba_fga_audit_trail.
|
1 2 3 4 5 6 7 |
VINOD>>conn / as sysdba Connected. SYS>>SELECT sql_text FROM dba_fga_audit_trail; SQL_TEXT -------------------------------------------------------------------------------- INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') |
Perform UPDATE statement on same column which we enabled the auditing.
|
1 2 3 4 |
VINOD>>update sep_aud set stock_level=2; 2 rows updated. VINOD>>commit; Commit complete. |
Connect to SYS user and check audit log.
|
1 2 3 4 5 6 7 8 |
VINOD>>conn / as sysdba Connected. SYS>>select sql_text from dba_fga_audit_trail; SQL_TEXT -------------------------------------------------------------------------------- INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') update sep_aud set stock_level=2 |
In audit log we have two inset operations and one update .
All three operations we performed on STOCK_LEVEL column.
Now i will perform another update operation on different column.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SYS>>conn vinod/vinod Connected. VINOD>>select * from sep_aud; INVENTORY_ CATALOG_RE STOCK_LEVE COUNTRY_CODE ---------- ---------- ---------- ----------- m45784 5478 2 aj m8578 4566 2 tx VINOD>>update sep_aud set country_code='CA' where country_code='aj'; 1 row updated. VALUES>>commit; Commit complete. VINOD>>select * from sep_aud; INVENTORY_ CATALOG_RE STOCK_LEVE COUNTRY_CODE ---------- ---------- ---------- ------------ m45784 5478 2 CA m8578 4566 2 tx VINOD>>col OS_USER for a10 VINOD>col DB_USER for a10 VINOD>>col USERHOST for a15 VINOD>col OBJECT_SCHEMA for a15 VINOD>>select OS_USER,DB_USER,USERHOST,OBJECT_SCHEMA,OBJECT_NAME,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail where DB_USER=UPPER('vinod'); OS_USER DB_USER USERHOST OBJECT_SCHEMA OBJECT_NAME STATEMENT SQL_TEXT ---------- ---------- --------------- --------------- --------------- ---------- -------------------------------------------------- oracle VINOD dba20.ord.com VINOD SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') oracle VINOD dba20.ord.com VINOD SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') oracle VINOD dba20.ord.com VINOD SEP_AUD UPDATE update sep_aud set stock_level=2 oracle VINOD dba20.ord.com VINOD SEP_AUD SELECT SELECT * FROM SEP_AUD oracle VINOD dba20.ord.com VINOD SEP_AUD SELECT select * from sep_aud |
In above result the new update operation is not audited
The thumb rule is on what columns we add policy those columns modification only audited.
How to enable & disable auditing??
By using same package but procedure is different.
DBMS_FGA.ENABLE_POILCY
DBMS_FGA.DISABLE_POILCY
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
##########DISABLING THE POLICY ###################################### VINOD>>EXEC DBMS_FGA.DISABLE_POLICY(POLICY_NAME=>'SEP_AUDIT',OBJECT_NAME=>'SEP_AUD'); PL/SQL procedure successfully completed. VINOD>>UPDATE SEP_AUD SET stock_level='10' WHERE stock_level=2; 2 rows updated. AJAY>>select OS_USER,DB_USER,USERHOST,OBJECT_SCHEMA,OBJECT_NAME,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail where DB_USER=UPPER('VINOD'); OS_USER DB_USER USERHOST OBJECT_SCHEMA OBJECT_NAME STATEMENT SQL_TEXT ---------- ---------- --------------- --------------- --------------- --------- -------------------------------------------------- oracle VINOD dba20.ord.com AJAY SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') oracle VINOD dba20.ord.com AJAY SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') oracle VINOD dba20.ord.com AJAY SEP_AUD UPDATE update sep_aud set stock_level=2 oracle VINOD dba20.ord.com AJAY SEP_AUD SELECT SELECT * FROM SEP_AUD oracle VINOD dba20.ord.com AJAY SEP_AUD SELECT select * from sep_aud ######ENABLING THE POLICY ################# VINOD>EXEC DBMS_FGA.ENABLE_POLICY(POLICY_NAME=>'SEP_AUDIT',OBJECT_NAME=>'SEP_AUD'); PL/SQL procedure successfully completed. VINOD>>UPDATE SEP_AUD SET stock_level='11'WHERE stock_level=10; 2 rows updated. VINOD>>select OS_USER,DB_USER,USERHOST,OBJECT_SCHEMA,OBJECT_NAME,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail where DB_USER='VINOD'; OS_USER DB_USER USERHOST OBJECT_SCHEMA OBJECT_NAME STATEMENT SQL_TEXT ---------- ---------- --------------- --------------- --------------- ---------- -------------------------------------------------- oracle VINOD dba20.ord.com VINOD SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m45784','5478','-1','ca') oracle VINOD dba20.ord.com VINOD SEP_AUD INSERT INSERT INTO SEP_AUD VALUES('m8578','4566','-1','tx') oracle VINOD dba20.ord.com VINOD SEP_AUD UPDATE update sep_aud set stock_level=2 oracle VINOD dba20.ord.com VINOD SEP_AUD SELECT SELECT * FROM SEP_AUD oracle VINOD dba20.ord.com VINOD SEP_AUD SELECT select * from sep_aud oracle VINOD dba20.ord.com VINOD SEP_AUD UPDATE UPDATE SEP_AUD SET stock_level='11'WHERE stock_level=10 7 rows selected. |
THANK YOU ………..
Loading...
