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
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Pingback: [Oracle] 在不知道密碼狀況下複製DB Link | 寰葛格的分享空間
I needed to refresh a clone, but apply a link from the old clone version to the new one. This needed a temporary cache on another database, so I did it like this.
CacheDB:
create table luser.mylink as select * from sys.link$ where 1=0;
OldCloneDB:
create table luser.mylink as select * from sys.link$ where name=’NEEDTHISLINK’;
conn luser
create database link tocache connect to luser … using ‘CacheDB’;
insert into mylink@tocache select * from mylink;
commit;
(erase the old clone)
NewCloneDB:
delete from sys.link$ where name=’NEEDTHISLINK’;
create database link tocache connect to luser … using ‘CacheDB’;
insert into link$ select * from mylink@tocache;
commit;
shutdown immediate
startup
It might also be possible to do this with export/import with a WHERE clause.
Pingback: Finding Oracle DB link password : Memory analysis | Hatem Mahmoud Oracle's blog
I did replace OH/rdbms/xml/xsl/kudblink.xsl with the 11.2.0.3 one, but this didn’t allow me to recreate the DB link the old fashioned way either…Got ORA-02153.
Hi Ludo,
Here is another unsupported alternative:
replace OH/rdbms/xml/xsl/kudblink.xsl with the 11.2.0.3 one and use dbms_metadata.get_ddl as in the good old times…
Cheers,
Franck.
Wow, that’s even worse! 😉
Pingback: How to avoid ORA-02153 when creating database links on 11.2.0.4 (the unsupported way) - Ludovico Caldara - Blogs - triBLOG