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 = '<>';
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!
No comments:
Post a Comment