When dropping is adding?
I was working through some example files and test scripts with Virtual Private Databases and discovered a nifty and potentially misleading error. Google didn’t pick up any search results with it, so I thought noting it would be a good idea.
When you create a security policy with DBMS_RLS.ADD_POLICY incorrectly, and then try to drop it, you must make sure to include the OBJECT_SCHEMA parameter. If you don’t and provide named parameters like the following, you’ll raise an error.
BEGIN DBMS_RLS.DROP_POLICY(object_name=>'valid_table' ,policy_name=>'valid_policy'); END; / |
The error is quite misleading, as shown below.
BEGIN * ERROR at line 1: ORA-28103: adding a policy TO an object owned BY SYS IS NOT allowed ORA-06512: at "SYS.DBMS_RLS", line 59 ORA-06512: at line 2 |
The error is actually triggered when the OBJECT_SCHEMA is required. The default value is a NULL in the DBMS_RLS package specification.
The correct syntax is:
BEGIN DBMS_RLS.DROP_POLICY(object_schema=>'valid_schema' ,object_name=>'valid_table' ,policy_name=>'valid_policy'); END; / |
Subscribe via RSS