How to export Oracle Virtual Private Database (a.k.a.: VPD, or "Row Level Security") policy creation statements

Hey crew,

Long time no post.

Have you ever had to drop an Oracle VPD policy from a table, and then realized that it isn't very easy to put it back?

Hopefully this little snippet will help with backing up the VPD policy definition so that you can easily add it back later.

I hope this is helpful:

-- Build the ADD policy statement from the dictionary
SELECT 'BEGIN
   DBMS_RLS.add_policy (object_schema         => ' || DBMS_ASSERT.enquote_literal (str => object_owner) || '
                      , object_name           => ' || DBMS_ASSERT.enquote_literal (str => object_name) || '
                      , policy_name           => ' || DBMS_ASSERT.enquote_literal (str => policy_name) || '
                      , function_schema       => ' || DBMS_ASSERT.enquote_literal (str => pf_owner) || '
                      , policy_function       => ' || DBMS_ASSERT.enquote_literal (str => CASE WHEN package IS NOT NULL
                                                                                               THEN package || '.'
                                                                                       END
                                                                                    || FUNCTION
                                                                               ) || '
                      , statement_types       => ' || DBMS_ASSERT.enquote_literal (str => LTRIM (RTRIM (CASE sel
                                                                                                            WHEN 'YES' THEN 'SELECT'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE ins
                                                                                                            WHEN 'YES' THEN 'INSERT'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE upd
                                                                                                            WHEN 'YES' THEN 'UPDATE'
                                                                                                         END
                                                                                                      || ','
                                                                                                      || CASE del
                                                                                                            WHEN 'YES' THEN 'DELETE'
                                                                                                         END
                                                                                                      , ','
                                                                                                       )
                                                                                                  , ','
                                                                                                   )
                                                                                   ) || '
                      , update_check          => ' || CASE WHEN chk_option = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , enable                => ' || CASE WHEN enable = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , static_policy         => ' || CASE WHEN static_policy = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , policy_type           => DBMS_RLS.' || policy_type || '
                      , long_predicate        => ' || CASE WHEN long_predicate = 'YES' THEN 'TRUE' ELSE 'FALSE' END || '
                      , sec_relevant_cols     => ' || DBMS_ASSERT.enquote_literal (str => (SELECT listagg (dsrc.sec_rel_column, ',') WITHIN GROUP (ORDER BY dsrc.sec_rel_column ASC) AS col_list
                                                                                             FROM dba_sec_relevant_cols dsrc
                                                                                            WHERE dsrc.object_owner = dp.object_owner
                                                                                              AND dsrc.object_name = dp.object_name
                                                                                              AND dsrc.policy_name = dp.policy_name
                                                                                          )
                                                                                  ) || '
                      , sec_relevant_cols_opt => ' || CASE WHEN EXISTS (SELECT 1
                                                                          FROM dba_sec_relevant_cols dsrc
                                                                         WHERE dsrc.object_owner = dp.object_owner
                                                                           AND dsrc.object_name = dp.object_name
                                                                           AND dsrc.policy_name = dp.policy_name
                                                                           AND dsrc.column_option = 'ALL_ROWS'
                                                                       ) THEN 'DBMS_RLS.ALL_ROWS'
                                                                         ELSE 'NULL'
                                                      END || '
                       );
END;
/' AS stmt
  FROM dba_policies dp
 WHERE object_owner = '<>'
   AND object_name = '<>';

Take care!