In a definer's rights procedure, the procedure executes with the privileges of the owner. The privileges are bound to the schema in which they were created. An invoker's rights procedure executes with the privileges of the current user, that is, the user who invokes the procedure."
However, due to how roles are treated in pl/sql blocks an odd behaviour could be observed as below. The test case involves creating a user with a custom role that has few privileges.
SQL> create user testuser identified by test321 default tablespace users quota unlimited on users; User created. SQL> create role testrole; Role created. SQL> grant create session, create table, create procedure to testrole; Grant succeeded. SQL> grant testrole to testuser; Grant succeeded.Connect as the testuser and verify user can execute actions using each of the privileges.
sqlplus testuser/test321@devpdb SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 23 20:49:03 2021 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 SQL> create table x (a number); Table created. SQL> create or replace function t return number as 2 begin 3 return 1; 4 end; 5 / Function created.So user can connect, create a table and a function.
Next create a function that dynamically creates a table.
SQL> create or replace function testfunc return number as
begin
execute immediate 'create table testtable as select * from x';
return 1;
end;
/
Function created.This function executes a creat table as statement. However, calling the function result in an error.SQL> declare 2 i number; 3 begin 4 i := testfunc; 5 end; 6 / declare * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "TESTUSER.TESTFUNC", line 3 ORA-06512: at line 4Intresting that it says insufficient privileges. If the create table as statement is run from a sql prompt it runs fine.
SQL> create table testtable as select * from x; Table created.
The issue here is that "all roles are disabled in any named PL/SQL block that executes with definer's rights". By default, each procedure is a definer's rights unit.
In this case both the executor of the procedure (invoker) and owner (definer) of the objects referenced by it are the same. Nontheless, because roles are disabled in the named PL/SQL block the function fails as it can't see the create table privilege needed.
This seems strange error to have since failure only happens when invovked through the function. Each statement inside the function would execute without any error when run outside the function on its own.
There are two ways to fix this.
One is to grant the create table privilege directly to the user rather than to the role.
grant create table to testuser;
SQL> declare
i number;
begin
i := testfunc;
end;
/
PL/SQL procedure successfully completed.Second method is to run the function with invoker's right (even though in this case both definer and invoker are the same). Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles (Remember to revoke the create table from user if it was granted in previous method).create or replace function testfunc return number authid current_user as
begin
execute immediate 'create table testtable as select * from x';
return 1;
end;
/
SQL> declare
i number;
begin
i := testfunc;
end;
/
PL/SQL procedure successfully completed.Another odd behaviour is when PL/SQL object references other objects. Using the same example as above grant execute on dbms_lob to role.SQL> grant execute on dbms_lob to testrole; Grant succeeded.Assuming there's a table with a blob column, the dbms_lob package could be used to get the length of the data in lob column.
SQL> select DBMS_LOB.GETLENGTH(b) from blob_table;
DBMS_LOB.GETLENGTH(B)
---------------------
2But if the call to dbms_lob is included in a function it will fail to compile. Complain is it cannot identify the dbms_lob package.SQL> CREATE OR REPLACE FUNCTION blobsize RETURN NUMBER AS
i number;
BEGIN
select DBMS_LOB.GETLENGTH(b) into i from blob_table;
RETURN i;
END;
/
Warning: Function created with compilation errors.
SQL> show errors;
Errors for FUNCTION BLOBSIZE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
4/12 PL/SQL: ORA-00904: "DBMS_LOB"."GETLENGTH": invalid identifierThis situation is different to earlier one. Oracle development guide states that "if the subprogram or package that you create references schema objects, then you must have the necessary object privileges for those objects. These privileges must be granted to you explicitly, not through roles".Even though it may seems odd, but the behaviour is by design. To fix this grant exeucte on dbms_lob direct to user.
SQL> grant execute on dbms_lob to testuser; Grant succeeded.Afterwards package compiles without error.
SQL> alter function blobsize compile; Function altered.
