Over the weekend, I spent four long hours resolving an issue with a particular Oracle schema. It turned out to be an issue with the special characters in the password, or you can say: I did not treat special characters specially.

I was playing with an old Perl script on my Oracle 10g enterprise edition database server,  running on Redhat Enterprise Linux Advanced Server 4 (RHEL AS4). As the script was originally written for a different database, I had to modify the DBI->connect portion of the script. The script was written to produce a custom report on a schema, therefore, I needed a schema to play with. HR schema was available on my lab server, but with a password that I no longer know. I did the password reset as below

[oracle@racnode1 ~]$sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 7 01:47:07 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter user hr identified by pa$$wd;
User altered.
SQL>

So far so good! I decided to test the HR account before I updated the DBI Connect handle

[oracle@'racnode1'~]$sqlplus hr/pa$$wd
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 7 20:44:23 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name:

I was surprised that the HR user is not able to connect with the right password. I immediately connected as sysdba and started looking at the HR user privileges:

–Find roles granted to an Oracle database user
select * from dba_role_privs where grantee = ‘HR’;
–Find privileges granted to an Oracle database user
select * from dba_sys_privs where grantee = ‘HR’;
–HR user role in my database RESOURCE
select * from dba_sys_privs where grantee = ‘RESOURCE’;
–Query to check Oracle database user account status
select username, account_status, expiry_date from dba_users where username = ‘HR’;

HR user had the create session privilege and the account is not locked. I just couldn’t fathom, why I am not able to connect? After tapping my fingers on the table, sembling a nervous tick, I tried to connect as HR user from my sysdba session:

SQL> show user
USER is “SYS”
SQL> connect hr/pa$$wd
Connected.
SQL> show user
USER is “HR”
SQL>

And that works! But it does not explain why it wouldn’t work from the command prompt. This time I tried some thing different:

[oracle@'racnode1'~]$sqlplus hr/pa$$wd as sysoper
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:02:09 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL>

Even that seems to work. Upon a closer look, I realized the Sql*plus just authenticated the OS user as SYSOPER:

SQL> show user
USER is “PUBLIC” <——shows that the connected user is not HR
SQL> exit

Same logic applies to SYSDBA authentication:

[oracle@'racnode1'~]$sqlplus hr/pa$$wd as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:07:04 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is “SYS”
SQL>

Then it all occurred to me, the culprit is $$ part of my password…….I take it back, the culprit is me, I did not treat the special characters specially. In Unix $$ is a shell perameter, $$ give the process id of my shell. This time I showed some ’special treatment’ to the specail characters:

[oracle@'racnode1'~]$sqlplus hr/’pa$$wd’
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:32:13 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is “HR”
SQL>

Voila…..I finally figured it out! I was happy to have resolved it, but kinda felt stupid that I spent four hours!