As commonly known passwords should have a certain complexity. Thereby it is common to use special characters, numbers, lower and uppercase characters. Depending on the type of special characters Oracle require that the password is enclosed in double quotation marks. Oracle does provide a guideline for Securing Passwords in the Oracle® Database Security Guide. So far so well, depending on the applications or Oracle clients the enclosing doesn’t really work as expected. Some Special characters like a $ or % are used to indicate an environment variable, other special characters like /, [] or @ are used to build the connect string. But a really nasty special character is the backslash respectively the \. The backslash is in general used as escape character to change subsequent literal characters into metacharacters and vice versa. The use of the backslash in an Oracle password has some very interesting side effects :-).
Ok, lets create a test user with a complex password and a backslash \ somewhere in the password.
SQL> conn / as sysdba Connected. SQL> create user smith identified by "KT20\dft"; User created. SQL> grant create session to smith; Grant succeeded. SQL> conn smith/KT20\dft Connected. SQL> show user USER is "SMITH" SQL> conn smith/"KT20\dft" Connected. SQL> show user; USER is "SMITH" SQL> conn smith/"KT20\dft"@TDB11 Connected. SQL> show user; USER is "SMITH" SQL> conn smith/KT20\dft@TDB11 Connected.
To create the user I’ve used the double quotation marks to enclose the password. As you can see, the attempts to log on to the database have worked in any case. In contrast to the login, alter the password definitely requires some quotation as you can see below.
SQL> alter user smith identified by KT20\dft;
alter user smith identified by KT20\dft
*
ERROR at line 1:
ORA-00911: invalid character
Ok, lets put the backslash at the end of the password and try to login again.
SQL> alter user smith identified by "KT20dft\";
User altered.
SQL> conn smith/KT20dft\
Connected.
SQL> show user
USER is "SMITH"
SQL> conn smith/"KT20dft\"
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> conn smith/"KT20dft\"@TDB11
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> conn smith@TDB11
Enter password: ********
Connected.
SQL> show user
USER is "SMITH"
SQL> conn smith/KT20dft\@TDB11
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
As you can see above, the Login no longer works so smoothly. Using the username and password without any quotation does work. Any other combination with quotation or the connect identifier does not work any more. In these cases the backslash does behave as a escape character. Mmh, in this case it should be possible to escape the backslash with a second backslash isn’t it?
SQL> conn smith/"KT20dft\\" ERROR: ORA-01017: invalid username/password; logon denied
No, doesn’t work. So far I have found no way to use the backslash at the end of a password, unless the password is entered interactively. For an administration tool like SQLPlus, SQL Developer etc which is anyway used interactively it isn’t a problem. But if you would like to setup batch jobs, RMAN backup’s etc it does not work.
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jun 12 08:33:43 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN>
RMAN>
RMAN> connect target *
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-10000: error parsing target database connect string "sys/"KT20dft\"@TDB11"
RMAN> run
2> {
3>
4> allocate channel ch1 type disk;
5> backup current control file;
6> }
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 06/12/2014 08:33:43
RMAN-06171: not connected to target database
Conclusion
It is still highly recommended to use complex passwords. Although Oracle supports all types of multi-byte characters, it is useful to test some special characters before these are used in passwords. Not everything always works as you expect.