Conditional audit with Fine-Grained Auditing

Photo by Sue Thomas on Unsplash

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_condition must 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.

1 thought on “Conditional audit with Fine-Grained Auditing

  1. Pingback: FGA, AUDIT_CONDITION, and SYS_CONTEXT | Fernando Simon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s