How to Resolve ORA-01017 in Database Links

  • by

ORA-01017 in Database Links

Before troubleshooting error ORA-01017, please make sure that every portion of connection string that you provided is correct.

Let's me show you how I reproduce ORA-01017 when connecting to a remote database via a database link, then I will explain the cause of problem.

In 12c, we set the password of a user by ALTER USER.

SQL> alter user scott identified by scott;

In 9i, we created a database link for connecting to the 12c database.

SQL> create database link ora12c_scott connect to scott identified by scott using 'ORA12C';

Database link created.

Then we tested the connectivity of the database link.

SQL> select sysdate from dual@ora12c_scott;
select sysdate from dual@ora12c_scott
                         *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ORA12C_SCOTT

Although it seemed nothing wrong with the statement, we got ORA-01017 eventually.

Rationale

In 12c, IDENTIFIED BY clause treats the non-quoted password as a case-sensitive string, which is lower-cased in this case. But in 9i, IDENTIFIED BY clause treats the non-quoted password as an upper-cased one, no matter what case it is in the statement. That's the problem.

Generally speaking, non-quoted identifiers in Oracle should be recognized as upper-cased ones and quoted identifiers are regarded as whatever they are in quotation marks. So I think Oracle make the password an exception in order to comply with some security policies, which is starting from 11g.

Solution

To solve ORA-01017, we should make the password to be saved as a lower-case one in the database link. But how? Let's keep going.

First of all, we dropped the incorrect database link in the 9i database.

SQL> drop database link ora12c_scott;

Database link dropped.

Then we created the database link with the password quoted. The database link will save the password as it is in the double quotes.

SQL> create database link ora12c_scott connect to scott identified by "scott" using 'ORA12C';

Database link created.

Then we tested the database link again.

SQL> select sysdate from dual@ora12c_scott;

SYSDATE
---------
17-DEC-19

This time, we succeeded.

Please note that, IDENTIFIED BY clause treats non-quoted password as case-sensitive one starting from 11g.

Leave a Reply

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