Use DBMS_METADATA to get DDL for Row Level Security (RLS) Policy

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