How to reject the @ symbol in the password of an Oracle Database

Password Complexity is an important aspect for IT auditors to check whether a system is secure. IT policies often point out that the password should have a minimum length and contain a mix of uppercase letters, lowercase letters, numbers, and special characters.

In Oracle, do not use special characters @ in the password, because in Oracle, the @ symbol is used to indicate which Oracle server to use. The complete SQL * Plus connection statement format is as follows:

CONNECT username/password @ alias

Alias is an Oracle Net alias, representing a server, fracture, and Instance name.

List A displays the error: ORA-12154, "TNS: the specified connection identifier cannot be resolved when the @ character is used in the password ." The @ symbol is incorrectly used as an Oracle Net alias because the part after @ is not a valid alias and thus produces an error.

List
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: cocould 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 problem also exists in the second version of Oracle 10g. At the same time, it is worth noting that testuser1 can log on successfully even if its password is "cat. in the next major release of 11 GB, Oracle plans to replace the original algorithm with a safer password algorithm. The new algorithm allows real uppercase and lowercase letters to be used in the password. At the same time, the @ problem will not occur again.

At the same time, you can create a password complexity function in PL/SQL to prevent users from setting a password containing the @ symbol. If the new password contains the @ symbol, it will be rejected.