Creating database links for another schema

There are situations (like after cloning databases) where you, as a DBA, want to create database links in other schema’s, for which you don’t have the password. Unfortunately the security of Oracle doesn’t allow (even SYS) you to drop/create database links for other schema’s.

One solution for this is to get the password hash value, temporarily change the password for the schema, connect as the user, create the database link, change the password for the schema back to its original value (using the password hash value of the original password).

In some situations (on a running database where users connect to the database) you cannot temporarily change the password and you’re stuck. There are however some tricks you can use to make Oracle execute the CREATE DATABASE LINK or DROP DATABASE LINK as another user. This post will describe both and you can choose the one you like the most (they will work both).

Using DBMS_IJOB

The first way of dropping/creating database links (and actually any other SQL statement) as another user is by using the undocumented DBMS_IJOB package, which lets you schedule jobs that run under as another user.

To use the DBMS_IJOB method, the following prerequisites should be met:

  • Init parameter job_queue_processes parameter is set to 1 or higher.
  • The user/schema under which the database link should be created, must have the “CREATE DATABASE LINK” privilege directly granted (not only through a role).
  • Grant the following privileges to the user you want to be able to execute statements as another user:GRANT SELECT ON dba_jobs TO <user>;
    GRANT EXECUTE ON sys.dbms_ijob TO <user>; BE VERY CAREFULL TO WHOM YOU GRANT THESE PRIVILEGES, BECAUSE THIS USER WILL BE ABLE TO RUN ALL KIND OF STATEMENTS AS ANOTHER USER!!

Use the following anonymous PL/SQL block to drop a database link and create one as another user (using DBMS_IJOB):

DECLARE
  lv_stmt	VARCHAR2(200);

  PROCEDURE run_sql_as(p_user IN VARCHAR2, p_statement IN VARCHAR2) IS
    lv_jobnr	NUMBER;
    lv_stmt		VARCHAR2(4000);
  BEGIN
	-- Make the statement usable for EXECUTE IMMEDIATE in job (replace single quote with 2 quotes)
	lv_stmt := REPLACE(p_statement,'''','''''');

	-- Get a jobid that does not exist
	SELECT MAX(job)+100 INTO lv_jobnr FROM dba_jobs;

	-- Submit a new job owned by the specified user
    SYS.DBMS_IJOB.SUBMIT (
        JOB => lv_jobnr,
        LUSER => p_user,
        PUSER => p_user,
        CUSER => p_user,
        NEXT_DATE => SYSDATE,
        INTERVAL => NULL,
        BROKEN => FALSE,
        WHAT => 'BEGIN EXECUTE IMMEDIATE '''||lv_stmt||'''; END;',
        NLSENV => 'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY''',
        ENV => HEXTORAW(0102000200000000));
    COMMIT;

	-- Run the job immediately
    SYS.DBMS_IJOB.RUN(lv_jobnr);
  EXCEPTION
	WHEN OTHERS THEN
	  DBMS_OUTPUT.PUT_LINE('Failed to execute the specified statement for user: '||p_user);
  END;	

BEGIN
  run_sql_as('SOMEUSER','DROP DATABASE LINK testlink3');
  lv_stmt := 'CREATE DATABASE LINK testlink3 CONNECT TO someuser IDENTIFIED BY somepwd USING ''somehost''';
  run_sql_as('SOMEUSER',lv_stmt);
END;
/

Using DBMS_SYS_SQL

The second way of dropping/creating database links (and actually any other SQL statement) as another user is by using the undocumented DBMS_SYS_SQL package, which lets you parse and execute SQL statements as another user.

To use the DBMS_SYS_SQL method, the following prerequisites should be met:

  • The user that needs to be able to execute the SQL statement as another user should be granted execute privilege on the DBMS_SYS_SQL package in the SYS schema.GRANT EXECUTE ON sys.dbms_sys_sql TO <user>;BE VERY CAREFULL TO WHOM YOU GRANT THESE PRIVILEGES, BECAUSE THIS USER WILL BE ABLE TO RUN ALL KIND OF STATEMENTS AS ANOTHER USER!!
  • The user that needs to be able to execute the SQL statement as another user should be granted the SELECT privilege on the dba_usersview.GRANT SELECT ON dba_users TO <user>;

Use the following anonymous PL/SQL block to drop a database link and create one as another user (using DBMS_SYS_SQL):

DECLARE
  lv_stmt	VARCHAR2(200);

  PROCEDURE run_sql_as(p_user IN VARCHAR2, p_statement IN VARCHAR2) IS
    lv_userid		NUMBER;
	lv_cursor		NUMBER;
	lv_result		NUMBER;
  BEGIN
	-- Get USER_ID for specified user
	SELECT user_id INTO lv_userid FROM dba_users WHERE username = p_user;

	-- Open, parse, execute and close
	lv_cursor := sys.dbms_sys_sql.open_cursor;
	sys.dbms_sys_sql.parse_as_user(lv_cursor, p_statement, dbms_sql.native, lv_userid, TRUE);
	lv_result := sys.dbms_sys_sql.execute(lv_cursor);
	sys.dbms_sys_sql.close_cursor(lv_cursor);
  EXCEPTION
	WHEN OTHERS THEN
	  DBMS_OUTPUT.PUT_LINE('Failed to execute the specified statement for user: '||p_user);
	  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 100));
  END;

BEGIN
  run_sql_as('SOMEUSER','DROP DATABASE LINK testlink3');
  lv_stmt := 'CREATE DATABASE LINK testlink3 CONNECT TO someuser IDENTIFIED BY somepwd USING ''somehost''';
  run_sql_as('SOMEUSER',lv_stmt);
END;
/

Although this article is based on the idea of creating/dropping database links for other schema’s/users, it can also be used for other statements. So again, be carefull to whom you grant access to the DBMS_IJOB and DBMS_SYS_SQL package!

This entry was posted in Database and tagged , , , , , , , , . Bookmark the permalink.

5 Responses to Creating database links for another schema

  1. Muthukumar Ponnambalam says:

    Though a late reply, thought of saying that DBMS_SYS_SQL method doesn’t work for me.

    Here’s the situation :

    We’ve a nightly process (this is a E-Biz 11i environment running on 9i database) that creates a clone of the production into a break-fix environment.

    As part of this procedure, we need to run certain post clone database scripts, one of which is to drop and re-create the database links in some of the schemas.

    Entire SQL should be run as APPS user (which’s the schema super user for E-Biz env.) and we’ve written a PL/SQL script that does all sorts of database stuff. (viz, profile updates, updating custom tables to change the production values, drop and re-create database links etc.)

    The only problem here is that, dropping and re-creating the database links in schemas other than APPS without having to end the Pl/SQL block.

    Here’s how my code snippet goes (this is an anonymous Pl/SQL block though) :

    declare
    procedure cr_db_link (p_link varchar2 ,
    p_connect_to varchar2 ,
    p_identified_by varchar2 ,
    p_using varchar2) is
    begin
    execute immediate ‘create database link ‘ || p_link || ‘ connect to ‘ || ……… ;
    end ;
    begin
    cr_db_link (‘link_1’ ….) ;
    cr_db_link (‘link_2’ ….) ;
    cr_db_link (‘link_3’ ….) ;

    end ;
    /

    After link_3, I want to drop and re-create the links in another schema. As Pl/SQL block doesn’t let you switch user, I tried with ‘alter session set current_schema = ‘ command and still this gave the ORA-01031 error.

    I tried the same Pl/SQL block with invoker rights, call the procedure after ‘set current_schema’ … same error.

    Now, finally I tried your above DBMS_SYS_SQL example …. no luck and same error.

    So, for now, what I do is, after completing all the SQL tasks by APPS user, I connect as the other target users and drop/re-create the links using plain SQLs.

    Thanks
    Muthu

    • marcel says:

      Hello Muthu,

      I have some questions for you that might help me figure out what is wrong:
      – What version of Oracle are you using?
      – Did you grant the “execute” permission on sys.dbms_sys_sql to the APPS user?
      – Are you using the run_sql_as procedure exactly as mentioned in my article or did you alter it a bit (if so please show me)
      – Can you show me how you are calling the run_sql_as procedure from within your PL/SQL code

      Best regards,

      Marcel

    • Ahmet says:

      Hi,

      We had the same problem,
      after executing grant create database link to target user,
      we created link successfully.

      Regards,
      Ahmet

  2. Muthukumar Ponnambalam says:

    Hello Marcel,

    Thanks a bunch for the quick reply. Here’re my answers to your questions :

    // What version of Oracle are you using?

    9.2.0.7

    // Did you grant the “execute” permission on sys.dbms_sys_sql to the APPS user?

    I haven’t explicitly, but checked and made sure APPS has that privilege. Since APPS is similar to the system account for the EBS data model, it has access to all the objects in the database including those owned by sys/system (of course, with some exceptions to the X$ kernel structures)

    Also, I’m trying to run it as another schema called SDR_PM and I explicitly granted the execute privilege to this package from SYS schema to this user and also granted the select privilege to the dba_users view.

    // Are you using the run_sql_as procedure exactly as mentioned in my article or did you alter it a bit (if so please show me) //

    I absorbed the whole idea, but slightly modified to suit my needs. Code follows.

    // Can you show me how you are calling the run_sql_as procedure from within your PL/SQL code //

    Here you go :

    ++++++++++

    declare
    procedure pl (p_message varchar2) is
    begin
    dbms_output.put_line (p_message) ;
    end ;
    procedure cr_db_link (p_user varchar2 ,
    p_link varchar2 ,
    p_connect_to varchar2 ,
    p_identified_by varchar2 ,
    p_using varchar2) is
    l_exist varchar2(1) := null ;
    l_userid number := 0;
    l_cursor number:= 0 ;
    l_result number:= 0 ;
    l_stmt varchar2(500) := null ;
    begin
    select user_id
    into l_userid
    from dba_users
    where username = p_user ;

    begin
    l_cursor := sys.dbms_sys_sql.open_cursor;
    l_stmt := ‘drop database link ‘ || p_link ;
    sys.dbms_sys_sql.parse_as_user(l_cursor, l_stmt, dbms_sql.native, l_userid) ;
    l_result := sys.dbms_sys_sql.execute(l_cursor);
    sys.dbms_sys_sql.close_cursor(l_cursor);

    exception
    when others then
    begin
    if (sqlcode = -2024) then — link not found to drop. No worries.
    pl (‘ ==> Link ‘ || p_link || ‘ not found. No issues’) ;
    else
    pl (sqlcode || ‘ – ‘ || sqlerrm) ;
    end if ;
    end ;
    end ;
    l_stmt := ‘create database link ‘ || p_link || ‘ ‘ ||
    ‘connect to ‘ || p_connect_to || ‘ ‘ ||
    ‘identified by ‘ || p_identified_by || ‘ ‘ ||
    ‘using ”’ || p_using || ”” ;
    l_cursor := sys.dbms_sys_sql.open_cursor;
    sys.dbms_sys_sql.parse_as_user(l_cursor, l_stmt, dbms_sql.native, l_userid) ;
    l_result := sys.dbms_sys_sql.execute(l_cursor);
    sys.dbms_sys_sql.close_cursor(l_cursor);

    pl (‘ * Created ‘ || p_link) ;

    exception
    when others then
    pl (sqlcode || ‘ – ‘ || sqlerrm) ;
    end ;
    begin
    cr_db_link (‘APPS’, ‘PMKR_APPS_DBL’, ‘SDR_PM’, ‘PM2012’, ‘ERPBK’) ;
    cr_db_link (‘SDR_PM’, ‘PMKR_SDR_PM_DBL’, ‘APPS’, ‘SPRING12’, ‘ERPBK’) ;
    end ;
    /

  3. Ricky says:

    To create a database link in another schema, you can do the following:
    1. Install a pl/sql procedure to create a database link in the target schema.
    2. Execute the procedure in the target schema.
    3. Drop the pl/sql procedure

    — Create lnkusr account with connect and create dblink privs.
    create user lnkusr identified by lnkpass;
    grant connect, create database link to lnkusr;

    — Install the makelnk() pl/sql procedure in the lnkusr schema.
    create procedure lnkusr.makelnk(
    lname in varchar2,
    lhost in varchar2,
    luser in varchar2,
    lpass in varchar2)
    is
    begin
    execute immediate
    ‘create database link ‘||lname||’
    connect to ‘||luser||’
    identified by ‘||lpass||’
    using ”’||lhost||””;
    end;
    /

    — Execute lnkusr.makelnk() to create the TEST_LNK database link.
    — The link is owned by lnkusr and connects back to the lnkusr schema.
    exec lnkusr.makelnk(‘TEST_LNK’, ‘&_CONNECT_IDENTIFIER.’, ‘lnkusr’, ‘lnkpass’);

    — Drop the makelnk() pl/sql procedure.
    drop procedure lnkusr.makelnk;

    — Display objects in the LNKUSR schema.
    select * from dba_objects where owner = ‘LNKUSR’;

    — Clean up the test schema.
    drop user lnkusr cascade;

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*