Disclaimer (wow, most of my recent posts start with a disclaimer, I guess it’s bad): this post explains an UNSUPPORTED workaround for an error enforced by recent Oracle security checks. You should never use it in production! Forewarned is forearmed.
Before Oracle Database 11.2.0.4, it was possible to create a database link using the following syntax:
|
1 2 |
create database link XX connect to YY identified by values 'DEA2G0D1A57B0071057A11DA7A' using 'ZZZ'; |
It was possible to get the password hash by either selecting dbms_metadata.get_ddl for the database link or by querying directly the link$ table.
Starting with Oracle 11.2.0.4, Oracle is enforcing a check that prevents to use such syntax. Every newly created database link must have the password explicitly set.
This is clearly stated in the MOS note:
ORA-02153: Invalid VALUES Password String When Creating a Database Link Using BY VALUES With Obfuscated Password After Upgrade To 11.2.0.4 (Doc ID 1905221.1)
This is seen as a security enhancement. In my opinion, it forces also to specify clear text passwords somewhere in the scripts that create the db links. (You do not create the db links by hand in sql*plus every time you need one. Do you?)
The only exception is when using the expdp/impdp. If you expdp a schema, the dumpfile contains the password hash and the statement needed to recreate the database link (… identified by values ‘:1’), but Oracle only allows impdp to use such statement.
So, simple workaround, just create the database links on a dev/staging environment, export them using expdp and then provide your dba the dumpfile so he/she can import it and create the dblinks. Right? Not always.
There is one case where you really need of the old syntax.
- You don’t know the password
AND
- You MUST change the database link name.
As you may know, there are no ways to change a database link name (even through impdp, there is no remap_dblink or anything like that).
E.g., you need to keep the db link and intend to use it for a check BUT you want to prevent the application from using it with the old name.
Because I believe that no problems exist that cannot be solved by my Trivadis’ colleagues, I’ve checked internally. A colleague came out with a dead simple (and unsupported) solution:
Insert/update sys.link$, flush the shared_pool.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ -------------------- ------------------------------ ---------- --------- SCOTT REMOTEDB SCOTT remotedb 10-APR-15 SQL> select * from sys.link$; OWNER# NAME CTIME HOST USERID PASSWORD FLAG AUTHUSR ---------- -------------------- --------- ---------- ---------- ------------------------------ ---------- ------------------------------ AUTHPWD ------------------------------ PASSWORDX ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ AUTHPWDX ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 0 REMOTEDB 10-APR-15 remotedb SCOTT 2 061D009E40A5981668DFEE1C710CF68E20B1A4DEE898857B2C3C458C3DEA042675E6CC98CC8D7B72C2F21314D94872D32882BECDE0594B3A525E342B8958BDF37ACE0DE3CE0A4D153AF41EEAF8391A9D84924521C45BA79FF2A2 CEA78709E3BD7775DB9B79A2B4D2F742472B7B5733E142CBCBA2A73511B81F3840611737351 SQL> insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX) 2 select OWNER#, 'NEWDBLINK', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX 3 from sys.link$ where name='REMOTEDB'; 1 row created. SQL> commit; Commit complete. SQL> alter system flush shared_pool; System altered. SQL> select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ -------------------- ------------------------------ ---------- --------- SCOTT REMOTEDB SCOTT remotedb 10-APR-15 SCOTT NEWDBLINK SCOTT remotedb 10-APR-15 |
Remember, use it at your own risk (or don’t use it at all) 😉
HTH
—
Ludovico