Skip to Main Content
  • Questions

  • Symbols to avoid in an Oracle password

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Eric.

Asked: March 09, 2017 - 4:13 pm UTC

Last updated: August 06, 2021 - 3:28 pm UTC

Version: 11gr2

Viewed 100K+ times! This question is

You Asked

Is there a definitive list of symbols that should be avoided when creating a new password. Oracle's guidelines aren't specific http://docs.oracle.com/database/121/DBSEG/guidelines.htm#DBSEG10005 This article says don't use the @ sign http://www.techrepublic.com/blog/software-engineer/avoid-the-symbol-in-oracle-passwords/ Burleson says "Comma, backslash, double quote and the DEFINE symbol are not allowed" http://www.dba-oracle.com/t_11g_new_password_symbols.htm This article says backslash can cause problems if it's the last character of a password http://www.oradba.ch/2014/06/oracle-passwords-and-special-characters/

I guess what I really want is a list of safe symbols to use in a password that won't produce side effects.

and Chris said...

Passwords also follow the rules for naming database objects:

You can include multibyte characters in passwords.

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@).

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).


http://docs.oracle.com/database/122/SQLRF/Database-Object-Names-and-Qualifiers.htm#GUID-75337742-67FD-4EC0-985F-741C93D918DA

Though if you use one of the in-built password complexity functions or write your own these can impose their own restrictions:

http://docs.oracle.com/database/122/DBSEG/configuring-authentication.htm#DBSEG845

So as long as you wrap your password in double quotes, your password can include any character. Except double quotes themselves!

You certainly can have @, comma, backslash and the define symbol in a password:

sho define

define "&"

grant create session to u identified by "@,\/&";
conn u/"@,\/&"
select user from dual;

USER  
U 

alter user u identified by "!£@%^&*()_+=-'~:;\?><,.|/][{}#";
conn u/"!£@%^&*()_+=-'~:;\?><,.|/][{}#"
select user from dual;

USER  
U


Just make sure you're wrapping the passwords in double quotes!

You can run into issues with depending on your database characterset. MOS note describes problems where:

When the database uses a single byte character set (example: WE8ISO8859P1) the global users who have a non ASCII character in the password cannot connect to the database. The connection fails with ORA-1017. When the character set of the database is unicode ( UTF8, AL32UTF8) having a non ASCII character in the passwords of the global users does not cause any problem.

So ensure your database is in unicode or ensure you only use characters supported by your characterset.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pmdba, March 12, 2017 - 4:03 am UTC

The special characters you decide to allow or not allow in your passwords may depend in part on the tools and client operating systems that your users will be making use of. While the database can technically handle just about anything, client software may be an issue. Depending on the software, double-quotes may or may not be included around the password when it is submitted, or users may need to be reminded to place quotes themselves. Some things may work fine from within an application, or perfectly well on a web site, but not from a command-line, and different client operating systems may have different reserved characters for their own uses. You may have to experiment some to determine whether there are any "problem" characters in your own setup.

Best practice

Adrian, March 13, 2017 - 1:50 pm UTC

Just to add a swerveball to the question/answer - what is 'allowed' by Oracle may not suit your company's best practice. For example using non-repeating characters, passwords that end with '123'. I know this isn't necessarily a direct answer to the question but over 21 years in this industry I'm still amazed at easily guessed passwords being used
Chris Saxon
March 13, 2017 - 3:14 pm UTC

Great point.

If you want the database to enforce any password rules imposed by your company, you can write your own verification function:

https://oracle-base.com/articles/misc/basic-security-measures-for-oracle#password-verification-complexity

The solution that worked for me

Clay Mitchell, May 08, 2019 - 7:57 pm UTC

Overall, this was the most useful of all my searches. As I am trying to connect to sqlplus from a Linux command-line, the following did not work for me:

sqlplus XXX_user/"x@yyyzzz"@//host:port/service

This returns the TNS not found error.

However surrounding the connection string with single quotes does work:

sqlplus 'XXX_user/"x@yyyzzz"@//host:port/service'

Chris Saxon
May 10, 2019 - 8:04 am UTC

Thanks for sharing.

It worked successfully with double quotes in password

Anchal Todariya, August 06, 2021 - 12:05 pm UTC

Thanks a lot this suggestion worked for me:

By-

Clay Mitchell, May 08, 2019 - 7:57 pm UTC

SQL> connect XXX_user/"x@yyyzzz"@tns_service_name

surrounded the password with double quotes and sqlplus was able to interpret the password correctly.
Chris Saxon
August 06, 2021 - 3:28 pm UTC

Great
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.