The question came up this week about whether the predicates for row level security are offloaded to Smart Scan. The simple answer is yes as long as the policy_function contains off-loadable predicates.
Let’s see this in action. Using the public TPC-H schema we can set up the customer table so that customers can only see their own entries in that table. We need a row level security policy to see if each row is one that the current user is permitted to see and a function for the policy to call to make that determination:
grant dba to Customer#000000042 identified by hello;
grant select on customer to public;
create or replace function tpch.trls_c_name(
p_schema in varchar2,
p_object in varchar2)
return varchar2
as begin
return 'sys_context(''userenv'',''session_user'') in (upper(c_name), ''TPCH'')';
end;
/
begin dbms_rls.add_policy(
object_schema => 'TPCH',
object_name => 'CUSTOMER',
policy_name => 'P1',
policy_function => 'trls_c_name');
end;
/
This defines the row level policy on the customer table to call the trls_c_name function on a per-row basis. This function checks that the current user is either TPC-H who owns the data or the current user who can only see rows that match the session_user to the c_name column.
Now we can connect as the customer and try it:
connect Customer#000000042/hello
select * from tpch.customer where c_acctbal > 0;
C_NAME C_NATIONKEY
------------------------- -----------
Customer#000000042 5
So the customer is now restricted to seeing their own entries, let’s look at the plan:
explain plan for select * from tpch.customer where c_acctbal > 0;
select * from table(dbms_xplan.display(format=>'all'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / CUSTOMER@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("C_ACCTBAL">0 AND (SYS_CONTEXT('userenv','session_user')='TPCH'
OR UPPER("C_NAME")=SYS_CONTEXT('userenv','session_user')))
filter("C_ACCTBAL">0 AND (SYS_CONTEXT('userenv','session_user')='TPCH' OR UPPER("C_NAME")=SYS_CONTEXT('userenv','session_user')))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C_NATIONKEY"[NUMBER,22], "C_NAME"[VARCHAR2,25]
2 - "C_NATIONKEY"[NUMBER,22], "C_NAME"[VARCHAR2,25]
This shows that sys_context is off-loadable in this usage and shows that in order for the evaluation of sys_context to work the metadata sent to the Smart Scan encapsulates the relevant contents of userenv to be available to be checked on the cell.
If you think this is the cause of a Smart Scan problem you are encountering, you can use the hidden parameter "_cell_offload_sys_context" to disable it and see if that resolves the issue.