I needed to drop a Row Level Security Policy from a table in an Oracle 12.1 database; but before I removed the policy, I wanted to make a backup of the code that would be necessary to restore the policy in case problems arose.
After searching the internet high and low, I was unable to find any examples of using DBMS_METADATA to back up an RLS policy (although I did find at least one other person that was looking for the same thing I was).
I decided to create my own query to use.
WARNING: This query comes with absolutely no guarantees. I don’t promise that it’s correct, or that it will fit your version of Oracle. Remember that you are solely responsible for whatever you run in your database.
select q'< begin dbms_rls.add_policy( object_schema => '>' || object_schema || q'<' , object_name => '>' || object_name || q'<' , policy_name => '>' || policy_name || q'<' , function_schema => '>' || function_schema || q'<' , policy_function => '>' || policy_function || q'<' , statement_types => '>' || statement_types || q'<' , policy_type => >' || policy_type || q'< , update_check => >' || update_check || q'< , enable => >' || enable || q'< , static_policy => >' || static_policy || q'< , long_predicate => >' || long_predicate || q'< ); end; />' sql from (select object_schema , object_name , policy_name , function_schema , policy_function , listagg(statement_types, ',') within group (order by case statement_types when 'select' then 1 when 'insert' then 2 when 'update' then 3 when 'delete' then 4 else 5 end ) statement_types , policy_type , update_check , enable , static_policy , long_predicate from (select object_owner object_schema , object_name , policy_name , pf_owner function_schema , package || '.' || function policy_function , statement_types , 'dbms_rls.' || policy_type policy_type , case chk_option when 'NO' then 'false' when 'YES' then 'true' end update_check , case enable when 'NO' then 'false' when 'YES' then 'true' end enable , case static_policy when 'NO' then 'false' when 'YES' then 'true' end static_policy , case long_predicate when 'NO' then 'false' when 'YES' then 'true' end long_predicate from dba_policies dp unpivot ( y_or_n for statement_types in (sel as 'select', ins as 'insert', upd as 'update', "DEL" as 'delete', idx as 'index') ) where object_owner = '&table_owner' and object_name = '&table_name' and policy_name = '&policy_name' -- Below is a mandatory filter and y_or_n = 'YES' ) group by object_schema , object_name , policy_name , function_schema , policy_function , policy_type , update_check , enable , static_policy , long_predicate);
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets