Thursday, April 24, 2014

Database Links and Case Sensitive Passwords

I connected to an Oracle 11 database:

Oracle 11: sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 23 18:22:40 2014
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
 
I created a user with a lower case password:
 
SQL> create user user1 identified by pw1
  2  /
 
User created.
 
SQL> grant create session to user1
  2  /
 
Grant succeeded.
 
SQL>
 
Then I did a couple of trial logins to check that the case sensitive password checking was working:
 
SQL> conn user1/pw1
Connected.
SQL> conn user1/PW1
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL>
 
I created a user with an upper case password:
 
SQL> conn / as sysdba
Connected.
SQL> create user user2 identified by PW2
  2  /
 
User created.
 
SQL> grant create session to user2
  2  /
 
Grant succeeded.
 
SQL>
 
Then I did two more trial logins to check that the case sensitive password checking was still working:
 
SQL> conn user2/pw2
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL> conn user2/PW2
Connected.
SQL>
 
I connected to an Oracle 9 database on a different machine:

Oracle 9: sqlplus "/ as sysdba"
 
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Apr 23 18:34:33 2014
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
 
SQL>
 
Then I connected remotely from there to the Oracle 11 database. The case of the passwords was forwarded to the remote database and the passwords were checked correctly:
 
SQL> conn user1/pw1@busdpt1
Connected.
SQL> conn user1/PW1@busdpt1
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL> conn user2/pw1@busdpt1
ERROR:
ORA-01017: invalid username/password; logon denied
 
SQL> conn user2/PW2@busdpt1
Connected.
SQL>
 
Next I created an Oracle 9 user to own some database links:
 
SQL> conn / as sysdba
Connected.
SQL> create user link_owner identified by link_owner
  2  /
 
User created.
 
SQL> grant create session, create database link
  2  to link_owner
  3  /
 
Grant succeeded.
 
SQL>
 
Then I created two database links to the Oracle 11 user with the lower case password.
One link used a lower case password and the other used an upper case password.
Neither of them worked:
 
SQL> conn link_owner/link_owner
Connected.
SQL> create database link link1
  2  connect to user1 identified by pw1
  3  using 'BUSDPT1'
  4  /
 
Database link created.
 
SQL> select * from dual@link1
  2  /
select * from dual@link1
                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from LINK1
 
SQL> create database link link2
  2  connect to user1 identified by PW1
  3  using 'BUSDPT1'
  4  /
 
Database link created.
 
SQL> select * from dual@link2
  2  /
select * from dual@link2
                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from LINK2
 
SQL>
 
Finally I created two database links to the Oracle 11 user with the upper case password.
One link used a lower case password and the other used an upper case password.
They both worked:
 
SQL> create database link link3
  2  connect to user2 identified by pw2
  3  using 'BUSDPT1'
  4  /
 
Database link created.
 
SQL> select * from dual@link3
  2  /
 
D
-
X
 
SQL> create database link link4
  2  connect to user2 identified by PW2
  3  using 'BUSDPT1'
  4  /
 
Database link created.
 
SQL> select * from dual@link4
  2  /
 
D
-
X
 
SQL>
 
This suggests to me that an Oracle 9 database sends database link passwords in upper case only. So if you want to create a link from an Oracle 9 database to an Oracle 11 database, you must connect to a remote user with an upper case password.

In Spanish 

No comments: