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.

4 Responses to Creating database links for another schema

Leave a Reply

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

Blue Captcha Image
Refresh

*