Password Complexity in Databases

Password complexity is one of many things that IT auditors examine to see whether a system is secure. IT policies usually state that a password must be a minimum length and include a mix of uppercase and lowercase letters, numbers, and special characters.

One special character to avoid with Oracle is the at symbol (@) because @ is used in Oracle to indicate which Oracle server to use. The full format of the SQL*Plus connect statement is:

CONNECT username/password@alias

where alias is an Oracle Net alias that stands for a server, port, and instance name.

Listing A shows the error generated when @ is part of a password: ORA-12154, "TNS: could not resolve the connect identifier specified." The @ symbol is mistaken for an Oracle Net alias, and the error occurs because the part after @ is not a valid alias.

Listing A
SQL> CREATE USER testuser1 IDENTIFIED BY "Cat"
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp;

User created.

SQL> CREATE USER testuser2 IDENTIFIED BY "H@t"
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp;

User created.

SQL> GRANT create session TO testuser1, testuser2;

Grant succeeded.

SQL> connect testuser1/cat
Connected.
SQL> connect testuser2/h@t
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Warning: You are no longer connected to ORACLE.
SQL> connect testuser2/"h@t"
Connected.
SQL>

The workaround is to enclose the password in quotes, as shown in the listing.

This behavior exists even in Oracle 10g, Release 2. Also, note that the logon for testuser1 was successful using the password "cat," even though the password is "Cat." The next major release, 11g, is scheduled to replace the password algorithm with a more secure one that permits true uppercase and lowercase letters in passwords. The @ problem may go away in that release as well. In the meantime, you can prevent users from setting passwords that contain @ by creating a password complexity function in PL/SQL and rejecting any new password if it contains the @ symbol.