Dynamic Procedure To Drop A Database Link [message #686645] |
Wed, 09 November 2022 14:03 |
wtolentino
Messages: 413 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 |
Solomon Yakobson
Messages: 3286 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 #686650 is a reply to message #686649] |
Thu, 10 November 2022 07:49 |
Solomon Yakobson
Messages: 3286 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 |
wtolentino
Messages: 413 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 |
Solomon Yakobson
Messages: 3286 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.
|
|
|