Home » SQL & PL/SQL » SQL & PL/SQL » Grants of System Privileges Thru Stored Procedure (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.21.0.0.0)
Grants of System Privileges Thru Stored Procedure [message #689459] Tue, 09 January 2024 08:31 Go to next message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
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.
Re: Grants of System Privileges Thru Stored Procedure [message #689460 is a reply to message #689459] Tue, 09 January 2024 08:46 Go to previous messageGo to next message
John Watson
Messages: 8932
Registered: January 2010
Location: Global Village
Senior Member
You would need to have done this,
grant create database link to superuser with admin option;
for the procedure to work, has that been done?
Re: Grants of System Privileges Thru Stored Procedure [message #689461 is a reply to message #689460] Tue, 09 January 2024 09:28 Go to previous message
wtolentino
Messages: 404
Registered: March 2005
Senior Member
That works thanks so much.
Previous Topic: plsql writen test questions
Next Topic: XML Create logic
Goto Forum:
  


Current Time: Thu May 23 10:36:36 CDT 2024