Getting error when trying to run SQL statement in a oracle function

Question

create or replace function auditif
return number as
l_filter_count  NUMBER;
begin
 EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR ' || SYS_CONTEXT('USERENV','CURRENT_SQL');
select count(*)
     into l_filter_count
     from table(dbms_xplan.display(format=>'PREDICATE'))
     where (plan_table_output like '% - filter(%' OR plan_table_output like '% - access(%')
     and plan_table_output not like '%SYS_AUDIT(%';
     dbms_output.put_line(l_filter_count);
if  (SUBSTR(UPPER(sys_context('userenv','CLIENT_PROGRAM_NAME')),1,3)='SQL' AND l_filter_count=0)
or 
(SUBSTR(UPPER(sys_context('userenv','CLIENT_PROGRAM_NAME')),1,4)='TOAD' AND l_filter_count=0)
then return 1;
else return 0; 
end if;
end;
/

I have called this function inside a DBMS_FGA policy for enable audit conditionally

begin
  DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA =>'AIM_DBA',
    object_name =>'EMP_DATA_I',
    policy_name =>'PROTECT_AIM_DBA_SCHEMA_TABLE',
    audit_condition=>'AIM_DBA.AUDITIF=1',
    statement_types =>'UPDATE,DELETE');
end;
/

But when running sql statement to check audit policy functionality getting following error:

delete EMP_DATA_I
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "AIM_DBA.AUDITIF", line 5

Alex Poole · Accepted Answer · 2022-05-05 14:01:52Z

1

The result of the context call appears to be null, so you're attempting to run

explain plan for

as a complete statement, with nothing after the for ; which will throw that error.

From the DBMS_FGA docs:

The audit_condition must be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. The expression can also use functions, such as the USER or SYS_CONTEXT functions.

The expression must not combine conditions using operators such as AND and OR. audit_condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:

Subqueries or sequences

The following attributes of the USERENV namespace when accessed using the SYS_CONTEXT function:

CURRENT_SQL

CURRENT_SQL_LENGTH

CURRENT_BIND

Any use of the pseudo columns LEVEL, PRIOR, or ROWNUM.

And in the sys_context docs:

CURRENT_SQL returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event. ... You can specify these attributes only inside the event handler for the fine-grained auditing feature.

So you can use that in handler_module function, but not in a function you call as part of the audit_condition.

answered May 5, 2022 at 14:01

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ashwani Kumar Over a year ago

I want to limiting audit trail generation in FGA_LOG$ , currently each and every update, delete statement getting captured in FGA_LOG$, I want capture only those sql which don't have "Where" clause.

ashwani Kumar Over a year ago

Do you have any workaround or solution for this

Collectives™ on Stack Overflow

Getting error when trying to run SQL statement in a oracle function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related