Today I will stay on fine-grained audit option. Firstly it was introduced in Oracle9i Database to record an audit trail when a user selects from a table, not just changes it.
Please note, FGA is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table.
If you prefer to write the fine-grained audit records to the SYS.FGA_LOG$ table, then set the audit_trail parameter for the DBMS_FGA.ADD_POLICY parameter to DB or DB+EXTENDED.
You can use fine-grained auditing to audit the following types of actions:
- Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
- Using an IP address from outside the corporate network
- Selecting or updating a table column
- Modifying a value in a table column
Let`s use. First set audit_trail parameter and restart database to use spfile
SQL> alter system set audit_trail=DB scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 310380928 bytes
Database Buffers 104857600 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameters audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL>
--create new table and insert some records.
SQL> conn ulfet/ulfet
Connected.
Connected.
SQL> create table telebeler (
id number,
f_name varchar2(10),
l_name varchar2(15),
lesson_name char(20),
score number);
Table created.
SQL> insert into telebeler values(1, 'Azer', 'Ibrahimov', 'History', 5);
1 row created.
SQL> insert into telebeler values(2, 'Kerim', 'Veliyev', 'History', 2);
1 row created.
1 row created.
SQL> insert into telebeler values(2, 'Kerim', 'Veliyev', 'History', 2);
1 row created.
SQL> insert into telebeler values(3, 'Ulfet', 'Tanriverdiyev', 'History', 4);
1 row created.
SQL> insert into telebeler values(4, 'Mamed', 'Aliyev', 'History', 3);
1 row created.
SQL> insert into telebeler values(5, 'Omar', 'Tanriverdiyev', 'History', 5);
1 row created.
SQL> commit;
Commit complete.
Table is ready, now create audit policy.
The following policy audits any queries of score greater and equal than 4
--Connect as sys
SQL> conn sys/?????? as sysdba
Connected.
SQL> BEGIN
DBMS_FGA.add_policy(
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT',
audit_condition => 'score >= 4',
audit_column => 'SCORE');
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT',
audit_condition => 'score >= 4',
audit_column => 'SCORE');
END;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
SQL>
Now connect as ulfet and select some records.
Querying telebelers which score less than 4 and greater than 4.
SQL> select * from telebeler where score <=3;
ID F_NAME L_NAME LESSON_NAME SCORE
---------- ------------ --------------- -------------------- ----------
2 Kerim Veliyev History 2
4 Mamed Aliyev History 3
---------- ------------ --------------- -------------------- ----------
2 Kerim Veliyev History 2
4 Mamed Aliyev History 3
SQL> select * from telebeler where score=5;
ID F_NAME L_NAME LESSON_NAME SCORE
---------- ------------ --------------- -------------------- ----------
1 Azer Ibrahimov History 5
5 Omar Tanriverdiyev History 5
---------- ------------ --------------- -------------------- ----------
1 Azer Ibrahimov History 5
5 Omar Tanriverdiyev History 5
SQL> select * from telebeler where score>=4;
ID F_NAME L_NAME LESSON_NAME SCORE
---------- ------------ --------------- -------------------- ----------
1 Azer Ibrahimov History 5
3 Ulfet Tanriverdiyev History 4
5 Omar Tanriverdiyev History 5
---------- ------------ --------------- -------------------- ----------
1 Azer Ibrahimov History 5
3 Ulfet Tanriverdiyev History 4
5 Omar Tanriverdiyev History 5
SQL>.
Now connect as sysdba and check audit records.
--connect as sys
SQL> conn sys/pass as sysdba
SQL> select db_user,policy_name,sql_text,TIMESTAMP from dba_fga_audit_trail order by 4;
DB_USER POLICY_NAME SQL_TEXT TIMESTAMP
---------------- ---------------------- --------------- -----------------------------------------------------------------
ULFET CHECK_SCORE_AUDIT select * from telebeler where score=5 07-DEC-12
ULFET CHECK_SCORE_AUDIT select * from telebeler where score>=4 07-DEC-12
To list of FGA policy_name use DBA_AUDIT_POLICIES
SQL> SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES;
POLICY_NAME
-----------------------------
CHECK_SCORE_AUDIT
SQL>
In Oracle 9i
fine-grained auditing was limited queries, but in Oracle 10g it has been
extended to include DML statements. For using this option just use statement_types parameter
within DBMS_FGA.ADD_POLICY
statement_types
=> 'SELECT,INSERT,UPDATE,DELETE');
How to use and what permission are needed to create a Fine-Grained
Audit Policy?
To create a fine-grained audit policy,
you must have EXECUTE privileges on the DBMS_FGA PL/SQL
package. The package is owned by the SYS user.
Particular user how has FGA essential grant can enable/disable FGA
using DBMS_FGA.DISABLE_POLICY as:
SQL> BEGIN
DBMS_FGA.DISABLE_POLICY(
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT');
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT');
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL>
Enable
Enable
SQL> BEGIN
DBMS_FGA.ENABLE_POLICY(
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT',
enable => TRUE);
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT',
enable => TRUE);
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL>
To drop FGA policy use:
To drop FGA policy use:
SQL> BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT');
object_schema => 'ULFET',
object_name => 'TELEBELER',
policy_name => 'CHECK_SCORE_AUDIT');
END;
/
PL/SQL procedure successfully completed.
In Oracle 11g has new audit feature, DBMS_AUDIT_MGMT package which provide you to move audit tables to different tablespace.
By default, the SYSTEM tablespace stores the database audit trail SYS.AUD$ and SYS.FGA_LOG$ tables.
To do that use the following package`s procedure:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'AUD_AUX');
END;
In this example:
- AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:
- DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
- DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
- DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
- AUDIT_TRAIL_LOCATION_VALUE: Specifies the destination tablespace. This example specifies a tablespace named AUDIT_TABLES.
Let`s check
SQL> conn /as sysdba
Connected.
SQL> SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY 1;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM
------------------------------ ------------------------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM
SQL>
Create new tablespace for audit table store
SQL> create tablespace audit_tables datafile '/u01/app/oracle/oradata/testdb/audit_tables1.dbf' size 50m autoextend on;
Tablespace created.
SQL>
Next we move the fine-grained audit trail table.
SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_TABLES');
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_TABLES');
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
Be patient while processing, it can last long time depending table`s size.
--Check above select
SQL> SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY 1;
TABLE_NAME TABLESPACE_NAME
-------------------------------------- ------------------------------
AUD$ SYSTEM
FGA_LOG$ AUDIT_TABLES
SQL>
Source:
No comments:
Post a Comment