can an SQL function be used for a predicate when applying a policy to a table?

[Thobela]

The intention here is that the SQL function is executed based on some criteria every time a select statement is executed against the said table.

[Thobela]

This is an oracle label security related question.

[PAVB]

Yes. Like when you want to determine if the current session has appropriate access rights to the targetted row/rows.

[Thobela]

Thanks PAVB for your response.

what I really want to happen is to pass a function for the predicate and I want the function to execute when the policy is enforced.

my current situation is as follows

1. if I pass the function in quotes(as illustrated below) for the predicate I get an SQL error code 28113(policy predicate has an error)

SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
policy_name => 'POLICY_NAME'
,schema_name => 'DB_USER'
,table_name => 'ols_test'
,table_options =>'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL'
,label_function =>NULL
,predicate =>'my_function(SA_SESSION.SA_USER_NAME(''POLICY_NAME''))'
);
end;

if I execute the following query i get the SQL error code 28113
SELECT * FROM ols_test;


2. If I pass the function for the predicate without the quotes(as illustrated below). the function is executed when I apply the policy to the table. as a result the same predicate is used when enforcing the policy irrespective of the user logged on user.

SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
policy_name => 'POLICY_NAME'
,schema_name => 'DB_USER'
,table_name => 'ols_test'
,table_options =>'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL'
,label_function =>NULL
,predicate =>my_function(SA_SESSION.SA_USER_NAME(''POLICY_NAME''))
);
end;

In principle what I want is to generate the predicate based on the user that is logged on.

Regards
Thobela

[PAVB]

In principle what I want is to generate the predicate based on the user that is logged on.

Have you considered dynamic sql?
Just build the statement on the fly and execute it via "execute immediate".

[Thobela]

I solved this by using a VPD policy to generate the predicate on the fly. so I am using an OLS policy together with a VPD policy on the same table.

what I had hoped for was using just an OLS policy and provide a SQL function for the predicate option/argument when applying the OLS policy to a table such that this function is called when the OLS policy is being enforced (meaning that the predicate would be generated on the fly based on user session).

Pablo, thanks for you replies.

[PAVB]

I solved this by using a VPD policy to generate the predicate on the fly. so I am using an OLS policy together with a VPD policy on the same table.

what I had hoped for was using just an OLS policy and provide a SQL function for the predicate option/argument when applying the OLS policy to a table such that this function is called when the OLS policy is being enforced (meaning that the predicate would be generated on the fly based on user session).

Pablo, thanks for you replies.

Nicely done, congrats!
Glad to help.