When users asks the DBA’s to create a dblink, we would normally get back to the users asking for the dblink’s owner password to create the required db link.
The following method shall be used to create dblink without the requirement of the dbuser’s(owner of the db link) password.
These steps can e perfomed as SYS user.
a. Create a temporary procedure to create dblink, provide all the dblink information in the procedure statement.
SQL> CREATE PROCEDURE “DBLINKOWNER”.cre_db_lnk AS
BEGIN
EXECUTE IMMEDIATE ‘CREATE DATABASE LINK DBLINK_NAME.DOMAIN ‘
||’CONNECT TO USERNAME IDENTIFIED BY PASSWORD ‘
||’USING ”HOST_NAME”’;
END cre_db_lnk;
/
b. Grant required privileges to the user,
SQL>GRANT create database link TO “DBLINKOWNER”;
c. Execute the procedure, and it will create the required dblink.
SQL>execute “DBLINKOWNER”.cre_db_lnk;
d. Revoke pivileges and drop the procedure.
SQL>revoke create database link from “DBLINKOWNER”;
SQL>DROP PROCEDURE “DBLINKOWNER”.cre_db_lnk;
