One of my colleague was trying to find out which application was inserting “wrong” values on a column of a specific table. It took me some time to understand what a “wrong” value can be, but in this case, it was pretty simple : for example, any value greater than 6 on column NUMBER_OF_LEGS of table INSECTS was considered a “wrong” value.
And actually Fine-Grained Auditing makes it possible in a simple way. Here is an example on Oracle Cloud “Always Free” Autonomous Database 19.5 :
Let’s create a table with 1 row :
CREATE TABLE nature_repository.insects(
number_of_legs NUMBER,
insect_name VARCHAR2(50) NOT NULL
);
INSERT INTO nature_repository.insects VALUES (6, 'Ant');
COMMIT ;
And grant some privilege to the app user :
GRANT SELECT, INSERT, UPDATE, DELETE ON nature_repository.insects TO app_nature ;
The DBMS_FGA package provides fine-grained security functions. It is necessary to have the AUDIT_ADMIN role (or the EXECUTE privilege) to use it. And this is the case for user ADMIN on Oracle Cloud Always Free Autonomous Database :
SELECT sys_context('userenv', 'current_user') FROM DUAL ;
SYS_CONTEXT('USERENV','CURRENT_USER')
----------------------------------------------------------------------
ADMIN
SELECT granted_role FROM user_role_privs WHERE granted_role like 'AUDIT%' ;
GRANTED_ROLE
----------------------------------------------------------------------
AUDIT_ADMIN
AUDIT_VIEWER
In particular, this packages contains a subprogram ADD_POLICY to create a new policy. The mandatory parameters are object_name and policy_name. Among all other parameters, audit_condition is the one I was looking for. According to the official documentation :
A condition in a row that indicates a monitoring condition. […] The
audit_conditionmust be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted.
In this example, the condition would be translated to number_of_legs>6.
Connected as ADMIN, let’s create an FGA policy with this condition in parameter audit_condition :
EXEC DBMS_FGA.ADD_POLICY(object_schema => 'NATURE_REPOSITORY', object_name => 'INSECTS', policy_name => 'STRICT_INSECT_POLICY', audit_condition => 'number_of_legs>6', statement_types => 'INSERT, UPDATE');
This new policy is enabled by default :
SELECT object_schema, object_name, policy_owner, policy_name, policy_text, enabled FROM dba_audit_policies ;
OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_TEXT ENABLED
--------------------------------------------------------------------------------------------------
NATURE_REPOSITORY INSECTS ADMIN STRICT_INSECT_POLICY number_of_legs>6 YES
And now, when the application inserts a “wrong” value, for example :
INSERT INTO nature_repository.insects VALUES (8, 'Arachnid');
COMMIT ;
There is a new row in view unified_audit_trail :
SELECT os_username, userhost, dbusername, action_name, fga_policy_name, sql_text FROM unified_audit_trail WHERE fga_policy_name='STRICT_INSECT_POLICY' ;
OS_USERNAME USERHOST DBUSERNAME ACTION_NAME FGA_POLICY_NAME SQL_TEXT
-------------------------------------------------------------------------------------------------
nature appserver-nature APP_NATURE INSERT STRICT_INSECT_POLICY INSERT INTO nature_repository.insects VALUES (8, 'Arachnid')
Now when the application inserts 2 new rows :
INSERT INTO nature_repository.insects VALUES (6, 'Beetle');
INSERT INTO nature_repository.insects VALUES (NULL, 'Fly');
COMMIT ;
The first one does not meet the condition of the policy. But the second one does, so it appears in unified_audit_trail :
SELECT os_username, userhost, dbusername, action_name, fga_policy_name, sql_text FROM unified_audit_trail WHERE fga_policy_name='STRICT_INSECT_POLICY' ;
OS_USERNAME USERHOST DBUSERNAME ACTION_NAME FGA_POLICY_NAME SQL_TEXT
-------------------------------------------------------------------------------------------------
nature appserver-nature APP_NATURE INSERT STRICT_INSECT_POLICY INSERT INTO nature_repository.insects VALUES (8, 'Arachnid')
nature appserver-nature APP_NATURE INSERT STRICT_INSECT_POLICY INSERT INTO nature_repository.insects VALUES (NULL, 'Fly')
And finally, when the application inserting “wrong” values has been identified, it is time to disable and drop the policy :
EXEC DBMS_FGA.DISABLE_POLICY(object_schema => 'NATURE_REPOSITORY', object_name => 'INSECTS', policy_name => 'STRICT_INSECT_POLICY');
EXEC DBMS_FGA.DROP_POLICY(object_schema => 'NATURE_REPOSITORY', object_name => 'INSECTS', policy_name => 'STRICT_INSECT_POLICY');
This example is extremely simple, and a situation like this should have been managed with a proper check constraint on the number_of_legs column, to avoid any “wrong” value. But sometimes it is not trivial to change the definition of a table. This conditional auditing is very handy to spot an unexpected behavior.
In my case, the developers were expecting higher values on an ID column, that were supposed to be generated from specific a sequence, but actually they were not. Next time, they might use an identity column to manage this.
Floo Bar 
Pingback: FGA, AUDIT_CONDITION, and SYS_CONTEXT | Fernando Simon