EXECUTE IMMEDIATE - insufficient privileges

 

Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production 

Schematu HR.

If we want some flexibility in our system, there's nothing like dynamic SQL!

But sometimes dynamic SQL refuses to cooperate. Even though we have all the necessary permissions granted, we receive an error:

ORA-01031: insufficient privileges

Let's check what could be the cause of this and how we can solve the problem of lack of permissions.

We want to prepare a script that will dynamically create the table tab_dyn.

declare
    vsql varchar2(32000);
begin

    vsql := 'create table tab_dyn (id number)';

    DBMS_OUTPUT.put_line(vsql);

    execute immediate vsql;
exception when others then
    DBMS_OUTPUT.put_line(sqlerrm);
end;
/

The table has been created. We can verify it by executing the query:

select * from tab_dyn;

no rows selected

Tested, everything is working. So, we can save our anonymous block as a procedure:

drop table tab_dyn;

create or replace procedure proc_dyn  is
    vsql varchar2(32000);
begin

    vsql := 'create table tab_dyn (id number)';

    DBMS_OUTPUT.put_line(vsql);

    execute immediate vsql;
exception when others then
    DBMS_OUTPUT.put_line(sqlerrm);
end;
/

We run the procedrue:

exec proc_dyn;

and..,?

create table tab_dyn (id number)
ORA-01031: insufficient privileges


PL/SQL procedure successfully completed.

Insufficient permissions? But how's that possible? It was working just a moment ago :o

The issue lies in the fact that if we want to use dynamic SQL, permissions for objects must be granted directly, not through a role. In this case, we need to grant the HR user the CREATE ANY TABLE privilege.

-- sys
grant create any table to hr;

Let's do that again:

exec proc_dyn;

select * from tab_dyn;

no_rows_selected

This time it works.

Another solution is to create the procedure with AUTHID CURRENT_USER privileges.

We'll remove the permissions and drop the table:

revoke create any table from hr;

drop table tab_dyn;

Let's create the procedure proc_dyn again:

create or replace procedure proc_dyn AUTHID CURRENT_USER is

vsql varchar2(32000);
begin

vsql := 'create table tab_dyn (id number)';

DBMS_OUTPUT.put_line(vsql);

execute immediate vsql;
exception when others then
    DBMS_OUTPUT.put_line(sqlerrm);
end;

/

Let's test it:

exec proc_dyn;

select * from tab_dyn;

no_rows_selected

It works!!!:)