Software Development

Avoid the @ symbol in Oracle passwords


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.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.
--------------------------------------------------------------------------------------

Get Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

2 comments
magmag66
magmag66

if you've faced with this error: ORA-12532: TNS:invalid argument , while Oracle Database Instance Creation it can be because of your bad password definition for sys or system ..., etc the passwords should not contain '@' symbol , as Bob Watkins had mentioned it before. Thanks dear Bob Watkins Sincerely Roya

magmag66
magmag66

if you've faced with this error :ORA-12532: TNS:invalid argument , while Oracle Database Instance Creation it can be because of your bad password definition for sys or system ..., etc the passwords should not contain '@' symbol , as Bob Watkins had mentioned it before in this link :http://blogs.techrepublic.com.com/programming-and-development/?p=403 Thanks dear Bob Watkins Sincerely Roya