Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Procedure To Drop A Database Link (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Dynamic Procedure To Drop A Database Link [message #686645] Wed, 09 November 2022 14:03 Go to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
we had a package that creates a procedure to dynamically allow a non-owner of the database link to drop the database link. this is not the actual code but a snippet of it. so to show where the problem is coming from.

SQL> create or replace package utl.db_util_pkg as
  2    procedure drop_dblink;
  3  end db_util_pkg;
  4  /

Package created.

SQL>
SQL> create or replace package body utl.db_util_pkg as
  2    procedure drop_dblink as
  3      vStr    varchar2(4000);
  4    begin
  5      vStr := 'CREATE OR REPLACE PROCEDURE TESTUSER.DROP_DBLINK AS BEGIN EXECUTE IMMEDIATE '||
  6              ''''||'DROP DATABASE LINK TEST_DBLINK'||''''||'; END DROP_DBLINK;';
  7      dbms_output.put_line(chr(10)||vStr);
  8      execute immediate vStr;
  9      vStr := 'begin TESTUSER.DROP_DBLINK; end;';
 10      dbms_output.put_line(chr(10)||vStr);
 11      execute immediate vStr;
 12    exception
 13      when others then
 14        dbms_output.put_line(sqlerrm);
 15    end drop_dblink;
 16  end db_util_pkg;
 17  /

Package body created.
SQL> grant execute on utl.db_util_pkg to testuser;

Grant succeeded.
SQL>

when package is executed and connected/logon as testuser it is throwing the error insufficient privilege:
SQL> execute utl.db_util_pkg.drop_dblink;

CREATE OR REPLACE PROCEDURE TESTUSER.DROP_DBLINK AS BEGIN EXECUTE IMMEDIATE
'DROP DATABASE LINK TEST_DBLINK'; END DROP_DBLINK;

begin TESTUSER.DROP_DBLINK; end;
ORA-06550: line 1, column 16:
PLS-00904: insufficient privilege to access object
TESTUSER.DROP_DBLINK

PL/SQL procedure successfully completed.

SQL>
SQL>
the problem is coming from when it is attempting to execute the procedure that was dynamically created "begin TESTUSER.DROP_DBLINK; end;"


when the same dynamically created procedure is run manually it is working fine either way connected as UTL or TESTUSER:
SQL> begin TESTUSER.DROP_DBLINK; end;
  2  /

PL/SQL procedure successfully completed.

SQL>
SQL> show user;
USER is "TESTUSER"
SQL>
SQL> begin TESTUSER.DROP_DBLINK; end;
  2  /

PL/SQL procedure successfully completed.

SQL> show user;
USER is "UTL"
both TESTUSER and UTL has the 'create database privileges' privileges. i think i am missing something that i can't seem to find of what it might be.

please advise. thank you.

[Updated on: Wed, 09 November 2022 14:52]

Report message to a moderator

Re: Dynamic Procedure To Drop A Database Link [message #686647 is a reply to message #686645] Thu, 10 November 2022 05:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Most likely user TESTUSER is granted CREATE DATABASE LINK privilege via role. You need to GRANT CREATE DATABASE LINK TO UTL WITH ADMIN OPTION. Then add

execute immediate 'grant create database link to testuser';
before calling

    vStr := 'begin TESTUSER.DROP_DBLINK; end;';
    dbms_output.put_line(chr(10)||vStr);
    execute immediate vStr;
and

execute immediate 'revoke create database link from testuser';
after.

SY.
Re: Dynamic Procedure To Drop A Database Link [message #686648 is a reply to message #686647] Thu, 10 November 2022 07:05 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
thanks solomon. yes the TESTUSER had a direct grants to create a database link. still same errors.

/forum/fa/14669/0/



Re: Dynamic Procedure To Drop A Database Link [message #686649 is a reply to message #686648] Thu, 10 November 2022 07:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then most likely user UTL lacks direct privileges CREATE ANY PROCEDURE and/or EXECUTE ANY PROCEDURE.

SY.
Re: Dynamic Procedure To Drop A Database Link [message #686650 is a reply to message #686649] Thu, 10 November 2022 07:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
But what's the point of this package if TESTUSER has CREATE DATABASE LINK which means TESTUSER can simply issue DROP DATABASE LINK? Usually TESTUSER wouldn't have it an user UTL would have it with ADMIN OPTION. And package creates stored procedure in TESTUSER schema, temporarily grants CREATE DATABASE LINK to TESTUSER, executes procedure to drop database linkx, revokes CREATE DATABASE LINK from TESTUSER and drops procedure TESTUSER.DROP_DBLINK.

SY.
Re: Dynamic Procedure To Drop A Database Link [message #686651 is a reply to message #686650] Thu, 10 November 2022 10:15 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
the existing code also has procedures to create a database link. it was created as a utility to allow the application developers to build and drop database link. to explain why it was built in the first place i would not be able to explain it clearly but i know for sure that in some case which i have seen was that the need to recreate a database link that will be owned by an application schema, but they do not have direct access to it. that package was build for that situation. because it was been in there and i would not know how long it was been. i inherited it recently and just found out it was not working the way it should be. not sure also if that is working before.

i am trying to fix the code.

[Updated on: Thu, 10 November 2022 11:03]

Report message to a moderator

Re: Dynamic Procedure To Drop A Database Link [message #686653 is a reply to message #686651] Thu, 10 November 2022 17:01 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
We have similar code too. The idea is not grant developers CREATE DATABASE LINK. Creating/dropping DB link should be controlled by DBA. DBA has CREATE ANY PROCEDURE and CREATE DATABASE LINK WITH ADMIN OPTION. Developer submits request to create/drop DB link and if approved DBA creates stored procedure in corresponding schema (wrapped), temporarily grants create database link to that schema, runs procedure, drops it and revokes create database link from that schema. Wrapped because we don't want schema owner or anyone who has access to DBA_SOURCE, or has DEBUG ANY PROCEDURE, etc. even a chance (procedure exists for a short period of time) to see db link password. And we certainly don't want it to be package procedure since then db link definition along with the password will be passed as parameter and anyone who has access to GV$SQL will be able to see it.

SY.
Previous Topic: JOIN with cte
Next Topic: RegEX data parsing
Goto Forum:
  


Current Time: Thu Mar 28 04:49:46 CDT 2024