Could someone help me understand why I can call a dynamic sql script containing a reference to a DBMS_RANDOM procedure when the logic is called from an anonymous block, however, when I take that same logic and drop it into my own stored procedure, the previously runnable script fails to execute with a ORA-00904: "DBMS_RANDOM"."STRING": invalid identifier error?

I feel confident that my privileges are correct. I can run the script that is being passed as a variable directly without issue and run this logic as an anonymous PL/SQL block. Do I need to change my syntax with the stored proc or is it possible that this practice is prevented for security reasons?

Any explanation would be great but if you can point me to the Oracle documentation, I would be ecstatic. I have looked extensively, especially around Oracle's Dynamic SQL documentation but I haven't seen a description of this behavior. I am using Oracle 11g.

To recreate the behavior I am seeing:

Test Data Creation:

SPOOL ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;

--Create Test Table
CREATE TABLE TEST_DYNAMIC_TBL (
ID NUMBER PRIMARY KEY,
MY_COL VARCHAR2(50));

--INSERT a line of data and confirm
INSERT INTO TEST_DYNAMIC_TBL VALUES(1, 'SOME TEXT'); 
COMMIT;
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

PL/SQL Anonymous Block (Successful Example)

DECLARE
    l_script VARCHAR2 (32767);
BEGIN
    l_script := 'UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING(''U'',5)';
    DBMS_OUTPUT.put_line ('Script sent to Exec Immediate: ' || l_script);
    EXECUTE IMMEDIATE l_script;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line (' ERROR: ' || SUBSTR (SQLERRM, 1, 64));
        ROLLBACK;
END;
/

--Check value (This results in a successful update)
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

Script sent to Exec Immediate: UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING('U',5)

PL/SQL procedure successfully completed.

Your query value will vary depending on the seed that DBMS_RANDOM picked

Stored Procedure Example (Failure Example)

--Procedure created with identical logic
CREATE OR REPLACE PROCEDURE TEST_DYNAMIC
AS
    l_script VARCHAR2 (32767);
BEGIN
    l_script := 'UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING(''U'',5)';    
    DBMS_OUTPUT.put_line ('Script sent to Exec Immediate: ' || l_script);    -- This string will execute successfully if run directly
    EXECUTE IMMEDIATE l_script;
    COMMIT;

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (' ERROR: ' || SUBSTR (SQLERRM, 1, 64));
        ROLLBACK;
END;
/

--Reset  and verify Data
UPDATE TEST_DYNAMIC_TBL SET MY_COL = 'SOME TEXT'; 
COMMIT;
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

--Execute through Procedure (Will throw error)
EXECUTE TEST_DYNAMIC;

--Check Value of Table
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

Stored Procedure Results:

Script sent to Exec Immediate: UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING('U',5)

ERROR: ORA-00904: DBMS_RANDOM: invalid identifier

PL/SQL procedure successfully completed.

CodePudding user response：

It isn't about it being dynamic, it's about the privileges and how they were granted. You would see the same thing if you had a static insert using dbms_random (and in your example anyway there is no need for it to be dynamic).

It appears that you have execute privilege on dbms_random granted through a role, not directly to the package owner. From the documentation (emphasis added):

If the procedure owner grants to another user the right to use the procedure, then the privileges of the procedure owner (on the objects the procedure references) apply to the grantee's exercise of the procedure. The privileges of the procedure's definer must be granted directly to the procedure owner, not granted through roles. These are called definer's rights.

The user of a procedure who is not its owner is called the invoker. Additional privileges on referenced objects are required for an invoker's rights procedure, but not for a definer's rights procedure.

That only applies to stored PL/SQL - i.e. procedures, functions, packages, triggers etc. - not to anonymous blocks.

You can either get the privilege on dbms_random granted directly to the package owner, or change your procedure to use invoker's rights:

CREATE OR REPLACE PROCEDURE TEST_DYNAMIC
AUTHID CURRENT_USER
AS
...

In the latter case, anyone calling your procedure will then need the privilege on dbms_random - but they can have it through a role.

As access to that package is sometimes locked down, a direct grant to the owner might be preferable, but it depends on your security constraints.