Recently Flora tweeted to her blog post about a conditional audit for Fine Grant Audit (FGA) and questioned “Who on earth is doing that ?!”, and I remembered that I made something similar at my previous job. So, here a little glimpse of how to use the FGA, AUDIT_CONDITION, and SYS_CONTEXT to conditionally decide if you audit or no.
So, at my previous job, years ago (while still using 11g), I received these requests: “We need to audit everything that does not come from the application”, and more, “the audit needs to have the small overhead possible”.
And to explain the context why these are important, the system was used to control the judicial processes (court of justice) and the database handles more the 1 million of IOPS (the reason for the small needed overhead). And since is a sensible area, the system itself if heavily audited (application have own audit system), so we don’t need to audit the app – but everything else.
Environment
Here I am running at Oracle 19c, but this will work since 11g version. I have one table:
SQL> DESC simon.testfga; Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 NUMBER(5) MOMENT DATE SQL>
And this table will have a simple policy associated that checks for INSER/UPDATE/DELETE. But what is described below will work for all the policies that you create.
FGA
The way to answer these questions was using FGA with audit_condition in place. But to do that we need to understand that:
- FGA is context-aware: This means that when the policy is triggered, it reads the SYS_CONTEXT/USERENV of the user that is executing the SQL against the table.
- And, by documentation, the AUDIT_CONDITION is (as Flora noticed too): “The audit_condition must be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted… The audit_condition is evaluated using the privileges of the user who creates the policy”.
So, basically, FGA uses audit_condition to check if some requirement is met when the audit is trigger. Is, more or less, one where condition. And since FGA is context-aware, we can use the audit_condition to check the SYS_CONTEXT/USERENV of the user.
And the other catchy point in the documentation is: “…For example, suppose you create a function that executes an INSERT statement on the HR.EMPLOYEES table. The policy audit_condition contains this function and it is for INSERT statements (as set by the statement_types parameter). When the policy is used, the function executes…”. So, this means that audit_condition can call a function, and not just one simple expression like “>”, “<” or “=”. The key is that the audit_condition is one expression that needs to be evaluated every time that audit is triggered.
Function
Since, by definition, the audit_condition can’t be one sub-select but can be one function, I created one:
SQL> CREATE OR REPLACE FUNCTION system.CheckAppFGA(pTxtUser IN VARCHAR2) 2 RETURN PLS_INTEGER IS 3 BEGIN 4 IF(UPPER(pTxtUser) = 'NOFGAAUDIT') THEN 5 RETURN 1; 6 ELSE 7 RETURN 0; 8 END IF; 9 END; 10 / Function created. SQL>
What this function check is simple: If the parameter that it received is equal NOFGAAUDIT return 1, otherwise return 0.
Since I will create my policy with sys/system user, the function needs to be created at the same schema of the user that creates the policy, here, I created in SYSTEM schema. If you have one specific user that creates all your policies, you need to create the function is this schema.
The function that I created below is a simple example, it receives just one parameter. In your case can be different and have additional parameters, two, three, or even four. Your needs will define this. But the logic will be the same, needs to returns something to be compared.
I recommend to test the function before just to see if is ok:
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.ENABLE(1000);
3
4 IF system.CheckAppFGA('NOFGAAUDIT') = 0 THEN
5 DBMS_OUTPUT.PUT_LINE('AUDIT');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('NO AUDIT');
8 END IF;
9 END;
10 /
NO AUDIT
PL/SQL procedure successfully completed.
SQL>
The Policy
The policy that I created was:
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'SIMON'
3 , OBJECT_NAME => 'TESTFGA'
4 , POLICY_NAME => 'AUD_IUD_TESTFGA'
5 , AUDIT_CONDITION => 'SYSTEM.CheckAppFGA(SYS_CONTEXT(''USERENV'', ''SESSION_USER'')) = 0'
6 , STATEMENT_TYPES => 'INSERT,UPDATE,DELETE'
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
As you can see is a simple check that will collect all INSERT/UPDATE/DELETE commands where the audit_condition is equal to zero. And the value that I pass to the function is the SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’). But look at the audit_condition parameter.
Joining all the concepts until now (function, SYS_CONTEXT, and audit_condition) this means that audit will be triggered if the username of who executed the SQL over the table is different than NOFGAAUDIT.
Example and Validation
Let’s do a test. I am connecting with user “simon” and will execute one insert:
SQL> connect simon/simon123@orcloel7-ic.oralocal:1521/ORCL19P
Connected.
SQL> INSERT INTO testfga(COL1, MOMENT) VALUES (1, SYSDATE);
1 row created.
SQL> SELECT col1, TO_CHAR(moment, 'DD/MM HH24:MI:SS') AS moment FROM testfga;
COL1 MOMENT
---------- --------------
1 29/09 23:57:59
SQL>
And the audit was fired and the result is:
SQL> SELECT db_user, sql_text, object_name, TO_CHAR(timestamp, 'DD/MM HH24:MI:SS') AS time FROM DBA_FGA_AUDIT_TRAIL; DB_USER SQL_TEXT OBJECT_NAME TIME ---------- ---------------------------------------------------------------------- --------------- -------------- SIMON INSERT INTO testfga(COL1, MOMENT) VALUES (1, SYSDATE) TESTFGA 29/09 23:57:59 SQL>
And now connected with NOFGAAUDIT user:
SQL> connect nofgaaudit/acess123@orcloel7-ic.oralocal:1521/ORCL19P
Connected.
SQL> INSERT INTO simon.testfga(COL1, MOMENT) VALUES (1, SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT col1, TO_CHAR(moment, 'DD/MM HH24:MI:SS') AS moment FROM simon.testfga;
COL1 MOMENT
---------- --------------
1 29/09 23:57:59
1 30/09 00:22:36
SQL>
And at audit:
SQL> SELECT db_user, sql_text, object_name, TO_CHAR(timestamp, 'DD/MM HH24:MI:SS') AS time FROM DBA_FGA_AUDIT_TRAIL; DB_USER SQL_TEXT OBJECT_NAME TIME ---------- ---------------------------------------------------------------------- --------------- -------------- SIMON INSERT INTO testfga(COL1, MOMENT) VALUES (1, SYSDATE) TESTFGA 29/09 23:57:59 SQL>
As you can see above, there is no audit for the last insert from NOFGAAUDIT.
And again, a new test:
SQL> connect simon/simon123@orcloel7-ic.oralocal:1521/ORCL19P
Connected.
SQL> INSERT INTO simon.testfga(COL1, MOMENT) VALUES (1, SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT col1, TO_CHAR(moment, 'DD/MM HH24:MI:SS') AS moment FROM simon.testfga;
COL1 MOMENT
---------- --------------
1 29/09 23:57:59
1 30/09 00:22:36
1 30/09 00:24:05
SQL> DELETE FROM simon.testfga;
3 rows deleted.
SQL> COMMIT;
Commit complete.
SQL>
And at audit:
SQL> SELECT db_user, sql_text, object_name, TO_CHAR(timestamp, 'DD/MM HH24:MI:SS') AS time FROM DBA_FGA_AUDIT_TRAIL; DB_USER SQL_TEXT OBJECT_NAME TIME ---------- ---------------------------------------------------------------------- --------------- -------------- SIMON INSERT INTO testfga(COL1, MOMENT) VALUES (1, SYSDATE) TESTFGA 29/09 23:57:59 SIMON INSERT INTO simon.testfga(COL1, MOMENT) VALUES (1, SYSDATE) TESTFGA 30/09 00:24:05 SIMON DELETE FROM simon.testfga TESTFGA 30/09 00:25:31 SQL>
Conditional Audit
So, as you can see above in the example, when I am connected with user NOFGAAUDIT I am not audited. When I connected as SIMON, I am. And this occurs because:
- FGA is context-aware: When I am connected with SIMON, the SYS_CONTEXT will reflect this USERENV info, and the FGA/function CheckAppFGA will receive this value.
- AUDIT_CONTITION: The function is called for every execution and the result will depend on the input and according to #1, will vary.
- Small overhead: Since I am passing the SYS_CONTEXT (that is already populated when the user connects) and the CheckAppFGA does not select any additional table and execute a simple memory/value comparison, it is fast.
Combining these topics, we can have a conditional audit for FGA. Based on the user context we can, by audit_conditional function evaluation, if will audit or no. We don’t need to change code or implement something complex (or even audit everything just to delete not needed records).
Returning to the questions at the beginning that I needed to answer. In that case, I created a more complex function that received more parameters as input. I used MODULE, and IP from SYS_CONTEXT to identify if the connection is coming from something that is different from the application, and if is not coming from the trusted application servers IP’s. And since the function is just memory value evaluation, the overhead was minimal. And another security point, since the audit was created with another user than the application used to connect the database, the application team not know that they were audited (and just to be clear, we discovered some interesting audits 😉).
I recommend you check the SYS_CONTEXT/USERENV documentation to verify what you can use. From there you can’t use CURRENT_SQL, CURRENT_SQL_LENGTH, and CURRENT_BIND as defined in audit_condition documentation. But you can use a lot of things to create your conditional checks like SERVICE_NAME, MODULE, ACTION, or OS_USER.
Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”