Set up Fine-Grained Auditing

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Unified Auditing
[2] Mixed Mode Auditing

Auditing tracks changes that users make in the database.

Auditing is the monitoring and recording of configured database actions, 
from both database users and nondatabase users.

"Nondatabase users" refers to application users who are recognized in the 
database using the CLIENT_IDENTIFIER attribute. To audit this type of user, 
you can use a unified audit policy condition, a fine-grained audit policy, 
or Oracle Database Real Application Security.

You can base auditing on individual actions, such as the type of SQL 
statement executed, or on combinations of data that can include the user 
name, application, time, and so on.

You can configure auditing for both successful and failed activities, and 
include or exclude specific users from the audit. In a multitenant environment, 
you can audit individual actions of the pluggable database (PDB) or individual 
actions in the entire multitenant container database (CDB). In addition to 
auditing the standard activities the database provides, auditing can include 
activities from Oracle Database Real Application Security, Oracle Recovery 
Manager, Oracle Data Pump, Oracle Data Mining, Oracle Database Vault, 
Oracle Label Security, and Oracle SQL*Loader direct path events.

Auditing is enabled by default. All audit records are written to the unified 
audit trail in a uniform format and are made available through the 
UNIFIED_AUDIT_TRAIL view.
These records reside in the AUDSYS schema.
The audit records are stored in the SYSAUX tablespace by default.

Oracle recommends that you configure a different tablespace for the 
unified audit trail. Be aware that for most Oracle Database editions 
except for Enterprise Edition, you can only associate the tablespace 
for unified auditing once. You should perform this association before 
you generate any audit records for the unified audit trail. After you 
have associated the tablespace, you cannot modify it because partitioning 
is only supported on Enterprise Edition.

You can configure auditing by using any of the following methods:

1. Group audit settings into one unified audit policy.
-> You can create one or more unified audit policies that define all 
the audit settings that your database needs
2. Use one of the default unified audit policies. 
-> Oracle Database provides three default unified audit policies that 
encompass the standard audit settings that most regulatory agencies require. 
3. Create fine-grained audit policies.
-> You can create fine-grained audit policies that capture data such as 
the time an action occurred

Why Is Auditing Used?
=====================
Enable accountability for actions
Deter users (or others, such as intruders) from inappropriate actions 
based on their accountability.
Investigate suspicious activity
Notify an auditor of the actions of an unauthorized user
Monitor and gather data about specific database activities
Detect problems with an authorization or access control implementation
Address auditing requirements for compliance.

Best Practices for Auditing
===========================
As a general rule, design your auditing strategy to collect the amount 
of information that you need to meet compliance requirements, but focus 
on activities that cause the greatest security concerns. For example, 
auditing every table in the database is not practical, but auditing 
tables with columns that contain sensitive data, such as salaries, is. 
With both unified and fine-grained auditing, there are mechanisms you 
can use to design audit policies that focus on specific activities to audit.

Periodically archive and purge the audit trail data. See Purging Audit 
Trail Records for more information.

[1] Unified Auditing
What Is Unified Auditing?
=========================
In unified auditing, the unified audit trail captures audit information 
from a variety of sources.

Unified auditing enables you to capture audit records from the following 
sources:

1. Audit records (including SYS audit records) from unified audit policies 
   and AUDIT settings
2. Fine-grained audit records from the DBMS_FGA PL/SQL package
3. Oracle Database Real Application Security audit records
4. Oracle Recovery Manager audit records
5. Oracle Database Vault audit records
6. Oracle Label Security audit records
7. Oracle Data Mining records
8. Oracle Data Pump
9. Oracle SQL*Loader Direct Load

-> The unified audit trail, which resides in a read-only table in the 
AUDSYS schema in the SYSAUX tablespace, 
-> Makes this information available in a uniform format in the 
UNIFIED_AUDIT_TRAIL data dictionary view
-> The user SYS, who have been granted the AUDIT_ADMIN and AUDIT_VIEWER 
roles can query these views. 
-> If your users only need to query the views but not create audit policies, 
then grant them the AUDIT_VIEWER role.

When the database is writeable, audit records are written to the 
unified audit trail. If the database is not writable, then audit records 
are written to new format operating system files in the
$ORACLE_BASE/audit/$ORACLE_SID directory.

The benefits of a unified audit trail are many.
-> After unified auditing is enabled, it does not depend on the 
initialization parameters that were used in previous releases. 
-> The audit records, including records from the SYS audit trail, for 
all the audited components of your Oracle Database installation are 
placed in one location and in one format, rather than your having to 
look in different places to find audit trails in varying formats. 
This consolidated view enables auditors to co-relate audit information 
from different components.

For example, if an error occurred during an INSERT statement, standard 
auditing can indicate the error number and the SQL that was executed.
Oracle Database Vault-specific information can indicate 
whether this error happened because of a command rule violation or 
realm violation. Note that there will be two audit records with a 
distinct AUDIT_TYPE. With this unification in place, SYS audit 
records appear with AUDIT_TYPE set to Standard Audit.

-> The management and security of the audit trail is also improved by 
having it in single audit trail.

-> Overall auditing performance is greatly improved. By default, the 
audit records are automatically written to an internal relational table
(AUD$UNIFIED) in the AUDSYS schema.

-> You can create named audit policies that enable you to audit the 
supported components listed at the beginning of this section, as well as 
SYS administrative users. Furthermore, you can build conditions and exclusions 
into your policies.

-> If you are using an Oracle Audit Vault and Database Firewall environment, 
then the unified audit trail greatly facilitates the collection of audit data,
because all of this data will come from one location.

#Checking if Your Database Has Migrated to Unified Auditing
The V$OPTION dynamic view indicates if your database has been migrated to 
unified auditing.

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

SQL> select parameter,value from v$option where parameter like 'Unified%';

PARAMETER VALUE
----------------------------------------------------------------
Unified Auditing FALSE

#Enable UA
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

SQL> startup
ORACLE instance started.

Total System Global Area 830472192 bytes
Fixed Size 2929840 bytes
Variable Size 566233936 bytes
Database Buffers 255852544 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

#Disable UA
shutdown immediate
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_off ioracle


[2] Mixed Mode Auditing
-> Mixed mode auditing is the default auditing in a newly installed database.

-> Mixed mode auditing enables both traditional (that is, the audit facility 
from releases earlier than Release 12c) and the new audit facilities 
(unified auditing).

-> You can enable the database in either of these two modes: the mixed mode 
auditing or pure unified auditing mode.

-> Even though the features of unified auditing are enabled in both these modes, 
there are differences between them. 
1. In mixed mode, you can use the new unified audit facility alongside the 
traditional auditing facility. Enable any unified audit policy. There is no 
need to restart the database.

2. In pure unified auditing, you only use the unified audit facility. 
Link the oracle binary with uniaud_on, and then restart the database.


Auditing in a Multitenant Environment
-> Unified auditing can be used in a multitenant environment.
-> You can apply audit settings to individual PDBs or to the CDB as a whole, 
depending on the type of policy. In a multitenant environment, each PDB, 
including the root, has own unified audit trail.

1. Unified audit policies created with the CREATE AUDIT POLICY and 
   AUDIT statements
-> You can create policies for both the root and individual PDBs. 
2. Fine-grained audit policies: You can create policies for individual PDBs 
   only, not the root. 
3. Purging the audit trail: You can perform purge operations for both 
   the root and individual PDBs.

-------------------------------------------------------
Auditing Specific Activities with Fine-Grained Auditing
-------------------------------------------------------
Fine-grained auditing enables you to create audit policies at the granular 
level.

You cannot create unified audit policies using fine-grained auditing but 
you can use fine-grained auditing to create very customized audit settings, 
such as auditing the times that data is accessed.

This enables you to monitor data access based on content. It provides 
granular auditing of queries, and INSERT, UPDATE, and DELETE operations. 
You can use fine-grained auditing to audit the following types of actions:

1. Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
2. Using an IP address from outside the corporate network
3. Selecting or updating a table column
4. Modifying a value in a table column

In general, fine-grained audit policies are based on simple, user-defined 
SQL predicates on table objects as conditions for selective auditing. 
During fetching, whenever policy conditions are met for a row, the query 
is audited.

Fine-grained auditing records are stored in the AUDSYS schema.

The audit policies described in "Auditing Activities with Unified Audit 
Policies and the AUDIT Statement" can perform most of the operations that 
fine-grained audit policies can perform, except for the following actions:

Auditing specific columns. 
-> You can audit specific relevant columns that hold sensitive information, 
such as salaries or Social Security numbers.

Using event handlers. 
-> For example, you can write a function that sends an email alert to a 
security administrator when an audited column that should not be changed 
at midnight is updated.

Note:
1. Fine-grained auditing is supported only with cost-based optimization. 
For queries using rule-based optimization, fine-grained auditing checks 
before applying row filtering, which could result in an unnecessary audit 
event trigger.

2. Policies currently in force on an object involved in a flashback query 
are applied to the data returned from the specified flashback snapshot based 
on time or system change number (SCN).

3. If you want to use fine-grained auditing to audit data that is being 
directly loaded (for example, using Oracle Warehouse Builder to execute 
DML statements), then Oracle Database transparently makes all direct loads
that are performed in the database instance into conventional loads. 
If you want to preserve the direct loading of data, consider using 
unified audit policies instead.

Where Are Fine-Grained Audit Records Stored?
-> Fine-grained auditing records are stored in the AUDSYS schema.

-> These audit records are stored in the SYSAUX tablespace by default. 
-> You can supply a new tablespace by using the 
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure. 
-> This tablespace can be an encrypted tablespace. 
To find the records have been generated for the audit policies that are in effect, 
you can query UNIFIED_AUDIT_TRAIL data dictionary view.

Who Can Perform Fine-Grained Auditing?
Oracle provides roles for privileges needed to create fine-grained audit 
policies and to view and analyze fine-grained audit policy data.
The fine-grained audit privileges are as follows:

->To create fine-grained audit policies, you must be granted d the 
AUDIT_ADMIN role or the EXECUTE privilege on the DBMS_FGA package.
->To view and analyze fine-grained audit data, you must be granted the 
AUDIT_VIEWER role.
-> You can find the roles that user have been granted by querying the 
DBA_ROLE_PRIVS data dictionary view.

Fine-Grained Auditing on Tables or Views That Have Oracle VPD Policies
-> The audit trail captures the VPD predicate for fine-grained audited 
tables or views that are included in an Oracle VPD policy.
-> This behavior is similar to how the unified audit trail captures 
the VPD predicate for unified audit policies.
-> The predicate information is stored in the RLS_INFO column of the 
UNIFIED_AUDIT_TRAIL data dictionary view.
-> If there are multiple VPD policies applied to the same table or view, 
then by default the predicates for these policies are concatenated in the
RLS_INFO column.

-> You can reformat the output so that each predicate is in its own row 
(identified by its corresponding VPD policy name and other information) 
by using the ORA_GET_RLS_PREDICATE function

Fine-Grained Auditing in a Multitenant Environment

-> You can create fine-grained audit policies in the root or in PDBs.
Note the following:

1. You cannot create policies on SYS objects.

2. When you create a policy in the root, the policy cannot be applied to all PDBs; it only applies to objects within the root. 
(In other words, there is no such thing as a common fine-grained audit policy.)

3. If you want to create a fine-grained audit policy to audit a common object’s access in all the PDBs, then you must explicitly 
create that policy in each PDB and then enable it on the common objects that is accessible in the PDB.

4. When you create a policy in a PDB, it applies only to objects within the PDB.

Fine-Grained Audit Policies with Editions

-> You can prepare an application for edition-based redefinition, and cover each table that the application uses with an editioning view.
-> If you do this, then you must move the fine-grained audit polices that protect these tables to the editioning view. 
-> You can find information about the currently configured editions by querying the DBA_EDITIONS data dictionary view. 
-> To find information about fine-grained audit policies, query DBA_AUDIT_POLICIES.

Using the DBMS_FGA PL/SQL Package to Manage Fine-Grained Audit Policies

Creating a Fine-Grained Audit Policy
By default, Oracle Database executes the policy predicate with the privileges of the user who owns the policy. 
The maximum number of fine-grained policies on any table or view object is 256.
Oracle Database stores the policy in the data dictionary table, but you can create the policy on any table or view that is not in the SYS schema. 
In a multitenant environment, the fine grained policy is only created in the local PDB.

-> You cannot modify a fine-grained audit policy after you have created it. If you must modify the policy, then drop and recreate it.
-> Be aware that if a table column has a fine-grained audit policy, you cannot encrypt or decrypt this column.

The DBMS_FGA.ADD_POLICY procedure syntax is as follows:

DBMS_FGA.ADD_POLICY(
object_schema IN VARCHAR2 DEFAULT NULL 
object_name IN VARCHAR2,
policy_name IN VARCHAR2, 
audit_condition IN VARCHAR2 DEFAULT NULL, 
audit_column IN VARCHAR2 DEFAULT NULL 
handler_schema IN VARCHAR2 DEFAULT NULL, 
handler_module IN VARCHAR2 DEFAULT NULL, 
enable IN BOOLEAN DEFAULT TRUE, 
statement_types IN VARCHAR2 DEFAULT SELECT,
audit_trail IN BINARY_INTEGER DEFAULT NULL, 
audit_column_opts IN BINARY_INTEGER DEFAULT ANY_COLUMNS, 
policy_owner IN VARCHAR2 DEFAULT NULL);


Example 1 : Test DBMS_FGA with Mixed Mode Auditing

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb2/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL> select parameter,value from v$option where parameter like 'Unified%';

PARAMETER VALUE
----------------------------------------------------------------
Unified Auditing FALSE


Example: Fine Grained Auditing

SQL> connect sys/oracle_4U@racnode2/pdb2 as sysdba
Connected.
SQL>

SQL> grant AUDIT_ADMIN,DBA to usr_audit_test identified by password;

Grant succeeded.

SQL> alter user usr_audit_test default tablespace users
2 quota unlimited on users;

User altered.

SQL> connect usr_audit_test/password@racnode2/pdb2
Connected.
SQL>
SQL> create table tbl_fga_01
2 ( id number,name varchar2(20),surname varchar2(20));

Table created.

SQL> INSERT INTO tbl_fga_01 values(1,'First name','First surname');

1 row created.

SQL> INSERT INTO tbl_fga_01 values(2,'Second name','Second surname');

1 row created.

SQL> commit;

Commit complete.

BEGIN
DBMS_FGA.ADD_POLICY (
object_schema => 'USR_AUDIT_TEST',
object_name => 'TBL_FGA_01',
policy_name => 'PLC_FGA_TEST',
audit_condition => 'ID=1',
audit_column => 'ID,NAME',
enable => true,
statement_types => 'SELECT',
--audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

PL/SQL procedure successfully completed.

SQL> select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

no rows selected.

SQL> select * from tbl_fga_01;

ID NAME SURNAME
---------- -------------------- --------------------
1 First name First surname
2 Second name Second surname

SQL> select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

USERHOST OBJECT_NAME DB_USER POLICY_NAME SQL_TEXT
--------------------------------------------------------------------------------
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select * from tbl_fga_01

1 rows selected.

SQL> select surname from tbl_fga_01;

SURNAME
--------------------
First surname
Second surname

SQL> select name from tbl_fga_01;

NAME
--------------------
First name
Second name

SQL> select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

USERHOST OBJECT_NAME DB_USER POLICY_NAME SQL_TEXT
--------------------------------------------------------------------------------
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select * from tbl_fga_01
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select name from tbl_fga_01

2 rows selected.

SQL> select surname from tbl_fga_01 where id=1;

SURNAME
--------------------
First surname

SQL> select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

USERHOST OBJECT_NAME DB_USER POLICY_NAME SQL_TEXT
--------------------------------------------------------------------------------
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select * from tbl_fga_01
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select name from tbl_fga_01
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select surname from tbl_fga_01 where id=1

3 rows selected.

SQL> select surname from tbl_fga_01 where id=2;

SURNAME
--------------------
Second surname

SQL> select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

USERHOST OBJECT_NAME DB_USER POLICY_NAME SQL_TEXT
--------------------------------------------------------------------------------
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select * from tbl_fga_01
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select name from tbl_fga_01
racnode2 TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST select surname from tbl_fga_01 where id=1

3 rows selected.

To get the list of available policies, use DBA_AUDIT_POLICIES

SET LINESIZE 150
COL policy_owner FORMAT a20
COL policy_column FORMAT a10
COL object_name FORMAT a15
SELECT object_schema,object_name,policy_owner,policy_name,policy_column,sel,ins,upd,del from dba_audit_policies;

OBJECT_SCH OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_COL SEL INS UPD DEL
---------------------------------------------------------------------------------------------------
USR_AUDIT_TEST TBL_FGA_01 USR_AUDIT_TEST PLC_FGA_TEST ID YES NO NO NO


To get the list of fine grained auditing columns, use DBA_AUDIT_POLICY_COLUMNS view

SELECT * FROM DBA_AUDIT_POLICY_COLUMNS;

OBJECT_SCH OBJECT_NAME POLICY_NAME POLICY_COL
---------------------------------------------- ----------
USR_AUDIT_TEST TBL_FGA_01 PLC_FGA_TEST ID
USR_AUDIT_TEST TBL_FGA_01 PLC_FGA_TEST NAME

BEGIN
DBMS_FGA.DISABLE_POLICY(
object_schema => 'USR_AUDIT_TEST',
object_name => 'TBL_FGA_01',
policy_name => 'PLC_FGA_TEST');
END;
/

select object_name,policy_name,enabled from DBA_AUDIT_POLICIES;

OBJECT_NAME POLICY_NAME ENABLED
----------------------------------------------------------
TBL_FGA_01 PLC_FGA_TEST NO

BEGIN
DBMS_FGA.DROP_POLICY
(object_schema => 'USR_AUDIT_TEST',
object_name => 'TBL_FGA_01',
policy_name => 'PLC_FGA_TEST'
);
END;
/

SQL> select distinct FGA_POLICY_NAME from UNIFIED_AUDIT_TRAIL;

FGA_POLICY_NAME
------------------------------

Example 2 : Test DBMS_FGA with pure unified auditing

cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk uniaud_on ioracle

SQL> startup
-bash-4.1$ sql

SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 14 15:35:53 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/cdb2/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
SQL> select value from v$option where parameter ='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> select parameter,value from v$option where parameter like 'Unified%';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Unified Auditing
TRUE

SQL> connect sys/oracle_4U@racnode2/pdb2 as sysdba
Connected.
SQL> select value from v$option where parameter ='Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> connect usr_audit_test/password@racnode2/pdb2
Connected.
SQL>
SQL>
SQL> BEGIN
DBMS_FGA.ADD_POLICY (
object_schema => 'USR_AUDIT_TEST',
object_name => 'TBL_FGA_01',
policy_name => 'PLC_FGA_TEST',
audit_condition => 'ID=1',
audit_column => 'ID,NAME',
enable => true,
statement_types => 'SELECT',
--audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

PL/SQL procedure successfully completed.


SQL> select count(*) from DBA_FGA_AUDIT_TRAIL;

COUNT(*)
----------
17

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
18

SQL> select * from tbl_fga_01;

ID NAME SURNAME
---------- -------------------- --------------------
1 First name First surname
2 Second name Second surname

SQL> select count(*) from DBA_FGA_AUDIT_TRAIL;

COUNT(*)
----------
17

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
19

SQL> exec dbms_audit_mgmt.flush_unified_audit_trail;

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
20

SQL> select count(*) from DBA_FGA_AUDIT_TRAIL;

COUNT(*)
----------
17

SQL> select distinct FGA_POLICY_NAME from UNIFIED_AUDIT_TRAIL;

FGA_POLICY_NAME
------------------------------

PLC_FGA_TEST


SQL> COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A20
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20

SELECT event_timestamp,
dbusername,
action_name,
object_schema,
object_name,
FGA_POLICY_NAME,SQL_TEXT
FROM unified_audit_trail
ORDER BY event_timestamp desc;

EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME FGA_POLICY_NAME SQL_TEXT
------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------
14-SEP-19 03.39.47.426090 PM USR_AUDIT_TEST EXECUTE SYS DBMS_AUDIT_MGMT BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;
14-SEP-19 03.38.00.161742 PM USR_AUDIT_TEST SELECT USR_AUDIT_TEST TBL_FGA_01 PLC_FGA_TEST select * from tbl_fga_01


SQL> select * from tbl_fga_01 where id=1;

ID NAME SURNAME
---------- -------------------- --------------------
1 First name First surname


SQL> select * from tbl_fga_01 where id=2

ID NAME SURNAME
---------- -------------------- --------------------
2 Second name Second surname


EVENT_TIMESTAMP DBUSERNAME ACTION_NAME OBJECT_SCH OBJECT_NAME FGA_POLICY_NAME SQL_TEXT
------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------
14-SEP-19 03.39.47.426090 PM USR_AUDIT_TEST EXECUTE SYS DBMS_AUDIT_MGMT BEGIN dbms_audit_mgmt.flush_unified_audit_trail; END;
14-SEP-19 03.38.00.161742 PM USR_AUDIT_TEST SELECT USR_AUDIT_TEST TBL_FGA_01 PLC_FGA_TEST select * from tbl_fga_01
14-SEP-19 03.47.05.410214 PM USR_AUDIT_TEST SELECT USR_AUDIT_TEST TBL_FGA_01 PLC_FGA_TEST select * from tbl_fga_01 where id=1


views:
AUDIT_UNIFIED_ENABLED_POLICIES
CDB_UNIFIED_AUDIT_TRAIL
UNIFIED_AUDIT_TRAIL

OCM 11g Preparation - Implement fine-grained access control
Before studying this topic, I would recommend you to study the Create and 
manage contexts as VPD can be used together with Contexts and they both 
mix very well.

This is yet another topic that is impossible to do fast in the exam, 
if requested, without using the official documentation. So, be prepared 
to access it. The docs has 3 tutorials that are very, very good. So be 
prepared to use and adapt them.

Also, I recommend you to have knowledge in the policy views:

(ALL/DBA/USER)_POLICIES
(ALL/DBA/USER)_POLICY_CONTEXTS
(ALL/DBA/USER)_POLICY_GROUPS
(ALL/DBA/USER)_SEC_RELEVANT_COLS
V$VPD_POLICY

======================
Path to Documentation:
======================

Database Security Guide -> 7 Using Oracle Virtual Private Database to Control Data Access
Part III Controlling Access to Data
Using Application Contexts to Retrieve User Information
Using Oracle Virtual Private Database to Control Data Access
Using Transparent Sensitive Data Protection
Manually Encrypting Data

Oracle Database Online Documentation 12c Release 1 (12.1) 
-> Database Security Guide -> Part VI Monitoring Database Activity with Auditing
-> Database Security Guide -> 21 Introduction to Auditing
-> Database Security Guide -> 22 Configuring Audit Policies
-> Database Security Guide -> 23 Administering the Audit Trail

Thank you for visiting this blog 🙂