CODEWITHSUNDEEP
oracle-databaseoracle11g
blaze
blaze

Reputation: 2678

Calling DBMS_RLS.ENABLE_POLICY inside a function?

I am trying to call SYS.DBMS_RLS.ENABLE_POLICY() from inside a function defined in a package. But it shows the following error:

Error(19,16): PLS-00201: identifier 'SYS.DBMS_RLS' must be declared

Code:

CREATE OR REPLACE PACKAGE BODY foopackage IS
    FUNCTION foobar RETURN t_table PIPELINED IS
    BEGIN
        EXECUTE(SYS.DBMS_RLS.ENABLE_POLICY( -- error in this line
            object_schema => 'foo',
            object_name => 'bar',
            policy_name =>'bar2',
            enable => FALSE
        ));
        -- some more code
        EXECUTE(SYS.DBMS_RLS.ENABLE_POLICY( -- error in this line
            object_schema => 'foo',
            object_name => 'bar',
            policy_name =>'bar2',
            enable => TRUE
        ));
        RETURN;
    END;
END foopackage;

Upvotes: 1

Views: 2883

Answers (1)

Justin Cave
Justin Cave

Reputation: 231781

First off, if you want to call procedure A from within procedure B, there is no need to use EXECUTE (doing so would be an error since there is no EXECUTE procedure). Just call the other procedure.

CREATE OR REPLACE PACKAGE BODY foopackage IS
    FUNCTION foobar RETURN t_table PIPELINED IS
    BEGIN
        SYS.DBMS_RLS.ENABLE_POLICY( -- error in this line
            object_schema => 'foo',
            object_name => 'bar',
            policy_name =>'bar2',
            enable => FALSE
        );
        -- some more code
        SYS.DBMS_RLS.ENABLE_POLICY( -- error in this line
            object_schema => 'foo',
            object_name => 'bar',
            policy_name =>'bar2',
            enable => TRUE
        );
        RETURN;
    END;
END foopackage;

Correcting this error may or may not resolve the problem. If you are still getting compilation errors, the likely problem is that you are creating a definer's rights stored procedure but the definer (the owner of the package) does not have EXECUTE privileges on the DBMS_RLS package as a direct grant to the user. Privileges that are granted via roles are not available in a definer's rights stored procedure (though they may be available in a session).

Taking a step back, though, it seems unlikely that you really want to try to execute procedure calls that issue DDL and that cause transactions to commit inside of a function. That is going to make it very difficult to call the function in what I assume is the manner you intend to call it. What is the problem that you are trying to solve? If you are trying to write code that bypasses the policy function, it seems likely that what you really want to do is to modify the policy function in order to allow your code to bypass it by doing something like setting an override in the context the policy function uses (assuming it uses a context) or by granting the owner of the package the EXEMPT ACCESS POLICY privilege or otherwise coding bypass functionality into the policy function itself.

Upvotes: 2

Related Questions