EXECUTE IMMEDIATE - insufficient privileges

 

Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production 

Schematu HR.

Jeśli chcemy trochę elastyczności w naszym systemie - to nie ma jak dynamiczny SQL! 

Ale czasem dynamiczny SQL odmawia współpracy. Mimo, że mamy nadane wszystkie potrzebne uprawniania - otrzymujemy błąd:

ORA-01031: insufficient privileges

Sprawdźmy, jaka może być tego przyczyna i jak możemy rowiązać problem z brakiem uprawnień.

Chcemy przygotować skrypt, który utworzy dynamicznie tabelkę 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;
/

Tabelka została utworzona. Możemy sprawdzić wykonując zapytanie:

select * from tab_dyn;

no rows selected

Przetestowane, wszystko działa. Możemy więc nasz anonimowy blok zapisać w postaci procedury.

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;
/

Uruchamiamy procedurkę:

exec proc_dyn;

i?

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


PL/SQL procedure successfully completed.

Niewystarczające uprawnienia? Ale jak to? Przed chwilą działało :o

Problem polega na tym, że jeśli chcemy wykorzystywać dynamiczny SQL - uprawnienia do obiektów musimy nadać bezpośrednio, nie przez rolę. W tym przypadku musimy użytkownikowi HR nadać uprawnienie CREATE ANY TABLE.

-- sys
grant create any table to hr;

I spróbujmy jeszcze raz:

exec proc_dyn;

select * from tab_dyn;

no_rows_selected

Tym razem działa.

Innym rozwiązaniem jest utworzenie procedury z uprawnieniami AUTHID CURRENT_USER.

Usuniemy uprawnienia i skasujemy tabelę:

revoke create any table from hr;

drop table tab_dyn;

Utworzony jeszcze raz procedurę proc_dyn.

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;

/

Czas na test:

exec proc_dyn;

select * from tab_dyn;

no_rows_selected

Działa :)