On Oracle 19c.
We have users whose accounts are provisioned by specifying a comma separated list of department_code values. Each of the department_code values is a string of five alpha-numeric [A-Z0-9] characters. This comma separated value list of five character department_codes is what we call the user's security_string. We use this security_string to limit which rows the user may retrieve from a table, Restricted, by applying the following predicate.
select *
from Restricted R
where :security_string like '%' || R.department_code || '%';
A given department_code can be in Restricted many times and a given user can have many department_codes in their comma-separated value :security_string.
This predicate approach to applying row-level security is inefficient. No index can be used and it requires a full table scan on Restricted.
In alternative is to use dynamic SQL to do something like as follows.
execute immediate 'select *
from Restricted R
where R.department_code in(' || udf_quoted_values(:security_string) || ')';
Where udf_quoted_values is a user-defined function (UDF) that wraps in single quotes each department_code value within the :security_string.
However, this alternative approach also seems unsatisfactory as it requires a UDF, dynamic sql, and a full table scan is still likely.
I've considered bit-masking, but the number of bits needed is large 60 million (=36^5) and it would still require a UDF, dynamic sql, and a full table scan (function based index doesn't seem to be a candidate here). Also, bit-masking doesn't make much sense here as there is no nesting/hierarchy of department_codes.
execute immediate 'select *
from Restricted R
where BITAND(R.department_code_encoded,' || udf_encoded_number(:security_string) || ') > 0';
Where Restricted.department_code_encoded is a numeric encoded value of Restricted.department_code and udf_encoded_number is a user-defined function (UDF) that returns a number encoding the department_codes in the :security_string.
I've considered creating a separate table of just department codes, Department, and joining that to the Restricted table.
select *
from Restricted R
join Department D
on R.deparment_code = D.department_code
where :security_string like '%' || D.department_code || '%';
We still have the same problems as before, but now it is on the smaller (table cardinality) Department table (Department.department_code is unique where as Restricted.department_code is not unique). This provides for a smaller full table scan on Department than on Restricted, but now we have a join.
It is possible for us to change security_string or add additional user specific security values when the account is provisioned. We can also change the Oracle objects and queries. Note, the department_codes are not static, but don't change all that regularly either.
Any recommendations? Thank you in advance.
