I have this procedure that dynamically grants system privileges that when executed is giving me error "ORA-01031: insufficient privileges". However, when the same grant command alone is executed it is working fine.
SQL> select user from dual;
USER
--------------------------------------------------------------------------------
SUPERUSER
SQL>
SQL> create or replace procedure superuser.dblink_proc as
2 begin
3 execute immediate 'grant create database link to test1';
4 end;
5 /
Procedure created.
SQL>
When the procedure is executed gives an error:
SQL> exec superuser.dblink_proc;
BEGIN superuser.dblink_proc; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SUPERUSER.DBLINK_PROC", line 3
ORA-06512: at line 1
SQL>
When the same grant command is executed it works fine.
SQL> grant create database link to test1;
Grant succeeded.
SQL>
What could I be missing? Please advise Thanks.