Skip to Main Content
  • Questions
  • Special Characters -- in a create user command

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, subhakar.

Asked: August 21, 2000 - 4:52 pm UTC

Last updated: July 30, 2020 - 12:25 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

How can I pass special characters into a procedure in a varchar2 variable? It comes out with an error "ORA-911 Invalid character"

For Example:

I have a procedure "create_user" to create the user and store the username and password of this user in a separate table. This procedure takes two input variables (username and password). I need to pass special characters into the password variable. So, when I pass these special characters it comes out with this ORA-911 error. Am I clear???

Thanks in advance....
Subhakar


and we said...

An Oracle password has the following rules:

o Passwords must be from 1 to 30 characters long.
o Passwords cannot contain quotation marks.
o Passwords are not case sensitive.
o A Password must begin with an alphabetic character.
o Passwords can contain only alphanumeric characters and the
underscore (_), dollar sign ($), and pound sign (#). Oracle
strongly discourages you from using $ and #..
o A Password cannot be an Oracle reserved word (eg: SELECT).

Passwords cannot have "special" characters -- they follow the same rules all Identifiers do in the database. You are passing the varchar2 in with the "special characters" -- that part is working just fine. It is the actual create user command that is failing due to an invalid character in the password OR because you have placed a semi colon at the end of the create user command.

Here is an example of the types of errors you can expect with invalid passwords:

tkyte@ORACLE8I> create or replace
procedure create_user( p_uname in varchar2, p_pw in varchar2 )
2 as
3 begin
4 execute immediate
5 'create user ' || p_uname || ' identified by ' || p_pw;
6 end;
7 /

Procedure created.

tkyte@ORACLE8I>
tkyte@ORACLE8I> exec create_user( 'test', '1wrong' );
BEGIN create_user( 'test', '1wrong' ); END;

*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
ORA-06512: at "TKYTE.CREATE_USER", line 4
ORA-06512: at line 1



tkyte@ORACLE8I> exec create_user( 'test', 'wrong;again' );
BEGIN create_user( 'test', 'wrong;again' ); END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "TKYTE.CREATE_USER", line 4
ORA-06512: at line 1


tkyte@ORACLE8I> exec create_user( 'test', 'this_is_really_wrong!' );
BEGIN create_user( 'test', 'this_is_really_wrong!' ); END;

*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at "TKYTE.CREATE_USER", line 4
ORA-06512: at line 1



The only solution is to use a valid Oracle password in the create user command.

Rating

  (25 ratings)

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

Comments

Special Characters -- in a create user command

Tim, December 01, 2004 - 5:46 pm UTC

Tom,
Good and useful information here.

However, I need to know all the special characters that can be used in an Oracle username as well. With security issues being what they are today I need to know this.

Thanks!

Tom Kyte
December 01, 2004 - 7:57 pm UTC

the sql reference guide goes into this. usernames follow the same exact rules identifiers do .

Special Characters -- in a create user command

Tim, December 02, 2004 - 12:53 pm UTC

Tom,
I found the info I was needing.

Many thanks for pointing me to the right place!!!

username

syed, January 07, 2005 - 7:50 am UTC

how to create a username and password in oracle 8i
want a Sql commands.

Tom Kyte
January 07, 2005 - 9:25 am UTC

sql commands are kept in the sql reference guide:

</code> http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-SQL <code>

peek at the "create user" command for example

Special Characters in passwords (9.2.0.6)

Jim Hill, January 11, 2005 - 7:15 pm UTC

I did a special character test using 9.2.0.6 and here's what I found for special characters in passwords:

create user bubba identified by "abc~123";
grant create session to bubba;
connect bubba/abc~123;

drop user bubba;

========================================
These special characters worked
========================================
!
%
^
@ (requires quotes around password when entering password in connect statement)
$
*
(
)
_
+
~
`
-
=
[
{
]
}
\
|
;
:
'
,
<
.
>
/ (requires quotes around password when entering password in connect statement)


=============================================
I could not find a way to make these 2 work
=============================================
&
"

NOTE: You will also want to consider any scripting implications of the special characters you use.

Special Characters in 8i and 9i passwords

Beverly Wise, January 14, 2005 - 2:06 pm UTC

To expand on Jim's tests...
(Are double quotes definitely not allowed in 9i?)


SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 14 13:56:32 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> connect system@bev1
Connected.
SQL> 
SQL> -- create user to test with
SQL> create user testme identified by password;

User created.

SQL> grant create session to testme;

Grant succeeded.

SQL> 
SQL> 
SQL> ----------------
SQL> -- characters: $_#
SQL> -- quote when creating password: optional
SQL> -- quote when connecting: optional
SQL> 
SQL> alter user testme identified by P$_#word;

User altered.

SQL> connect testme/P$_#word@bev1
Connected.
SQL> ----------------
SQL> 
SQL> ----------------
SQL> -- characters: `~!%^*()+-={}|[]\:;'<>?,.
SQL> -- quote when creating password: required
SQL> -- quote when connecting: optional
SQL> 
SQL> connect system@bev1
Connected.
SQL> alter user testme identified by "P`~!%^*()+-={}|[]\:;'<>?,.d";

User altered.

SQL> connect testme/P`~!%^*()+-={}|[]\:;'<>?,.d@bev1
Connected.
SQL> ----------------
SQL> 
SQL> ----------------
SQL> -- character: @
SQL> -- quote when creating password: required
SQL> -- quote when connecting: required
SQL> connect system@bev1
Connected.
SQL> alter user testme identified by "P@ssword";

User altered.

SQL> connect testme/"P@ssword"@bev1
Connected.
SQL> ----------------
SQL> 
SQL> ----------------
SQL> -- character: &
SQL> -- quote when creating password: required AND must set define off before creating
SQL> -- quote when connecting: optional BUT must set define off before connecting
SQL> 
SQL> connect system@bev1
Connected.
SQL> set define off
SQL> alter user testme identified by "P&ssword";

User altered.

SQL> connect testme/P&ssword@bev1
Connected.
SQL> set define on
SQL> ----------------
SQL> 
SQL> ----------------
SQL> -- character: "
SQL> -- quote when creating password: 
SQL>    -- 8i: required AND must escape
SQL>    -- 9i: double quotes not allowed in password? ORA-03001: unimplemented feature error
SQL> -- quote when connecting: 
SQL>    -- 8i: not allowed! not even if you escape. must connect unquoted
SQL>    -- 9i: (N/A - double quotes not allowed in password?)
SQL>    
SQL> 
SQL> connect system@bev1
Connected.
SQL> alter user testme identified by "P""ssword";
alter user testme identified by "P""ssword"
                                *
ERROR at line 1:
ORA-03001: unimplemented feature


SQL> connect testme/P"ssword@bev1
ERROR:
ORA-01017: invalid username; logon denied


Warning: You are no longer connected to ORACLE.
SQL> ----------------
SQL> 
SQL> 
SQL> 
SQL> connect system@bev1
Connected.
SQL> drop user testme;

User dropped.
 

Even if you can, why would you?

Kurt, January 14, 2005 - 7:36 pm UTC

Good job defining which special characters may and may not be used in Oracle passwords.

It seems like allowing all of those special characters could pose a lot of problems, especially if the application is web-based. The password must potentially pass through JavaScript, the server-side language, the webserver, the webserver operating system, the database server operating system, the database, etc.

Isn't it difficult to wrap/escape the values so that they are not inadvertently interpreted/corrupted along the way?

Do many applications support such a wide variety of characters in passwords?

Do users really use such a wide variety?

Is there such a thing as a "best practices guide" for passwords in web-based applications?

Response to: Even if you can, why would you?

Beverly, January 15, 2005 - 11:55 am UTC

Thanks. There are two issues:
1) Which special characters are allowed by Oracle (the range of possibility)?
2) What special characters are better off disallowed (best practices)?

RE #1 - 9i and 10g documentation both state "Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks." So I think the answer to my question is, no, double quotes are not allowed in 9i and 10g (I can use them in passwords in 8i). Nonetheless I was just seeking confirmation in case there is an exception/workaround to this.

RE #2 - I would agree that by allowing such a variety of special characters the potential for code injection along all those points increases. Unfortunately, we have a client that insists we must allow all the special characters in my test above for their web-based applications. I believe their logic was that by allowing more characters in the password, the password becomes that much more difficult to crack. My personal belief is that they are potentially decreasing the security on their applications. Further comments/ suggestions/ recommendations are welcome.

Double quotes in 9i passwords

Mike, January 26, 2005 - 12:03 pm UTC

Just to throw a spanner in the works, I managed to get a double quotation mark into a password in 9.2.0.6. Here's how:


SQL*Plus: Release 9.2.0.6.0 - Production on Wed Jan 26 16:48:03 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys@abc as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

SQL> create user blah identified by blah;

User created.

SQL> grant create session to blah;

Grant succeeded.

SQL> conn blah/blah@abc
Connected.
SQL> password
Changing password for BLAH
Old password:
New password:        <-- type b"lah at this prompt
Retype new password: <-- type b"lah at this prompt
Password changed
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
SQL> conn blah/b"lah@abc
Connected.
SQL>


I'm not advocating it's use here. I just thought I'd demonstrate that it appears to be possible.

Any comments? 

Strong Recommendation?

mhthomas, May 12, 2005 - 8:58 am UTC

Quoting:
"Passwords can contain only alphanumeric characters and the
underscore (_), dollar sign ($), and pound sign (#). Oracle
strongly discourages you from using $ and #.."

Please point me to the doc-link where oracle strongly discourages $ and # characters in passwords?



Tom Kyte
May 12, 2005 - 12:38 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements9a.htm#27571 <code>

in schema object names period.

I guess it could be said that for passwords - it is not totally true. the $ and # were there most likely in order to prevent clashes with oracle builtin tablenames and such which are full of $ and #. so, it probably is safe to say it would not really apply if you look at the intent behind the recommendation.

what about £ in column fields

Mahomed Suria, September 21, 2005 - 8:51 am UTC

I inserted a "£" character in a string field, but when I query , it shows up as an "#". How can I get the "£" displayed correctly?. 



SQL> insert into tst1 values('£ - POUND SIGN');

1 row created.

SQL> select * from tst1
  2  ;

TXT1
--------------------------------
# - POUND SIGN

 

Tom Kyte
September 21, 2005 - 7:21 pm UTC

check charactersets - make sure your characterset can even support that character.

use DUMP to see what is really in there.

it could be that your client characterset ( NLS_LANG ) is different from the database and in the conversion - poof, it goes away.

it could be that your characterset does not support the pound-sign at all.

it could be that your terminal isn't capable of displaying that normally (eg, you are using telnet and it is ascii but this isn't an ascii character or something)

Followup

Mahomed Suria, September 26, 2005 - 8:49 am UTC

Thanks Tom,

Needed to set environment variable
NLS_LANG="AMERICAN_UNITED KINGDOM.WE8ISO8859P1"


Help

Raj, January 19, 2006 - 4:21 am UTC

Hi Tom,
In a column i have some non-printable char's...How will i know which is the non-printable char?

Please do reply.
Bye!

Tom Kyte
January 19, 2006 - 12:37 pm UTC

you tell me, what defines "non printable" to you.

Special characters further

Melissa, March 17, 2006 - 5:32 pm UTC

I am trying to loop though databases using db links to delete rows. I keep receiving the ORA-911 'invalid character' error for my '@' even though my execution statement is straight from Oracle Documentation :) I know there is a setting to turn define off for & would this be a reason why? I tested it out and still recieved the error but is there a different setting? I'm running currently on 8i on a Linux box, could this be the cause? Here is my example SQL. THANKS!


FOR X IN (SELECT db_name
FROM ( SELECT 'TEST' AS db_name FROM DUAL
UNION
SELECT 'DEV' AS db_name FROM DUAL))
LOOP
EXECUTE IMMEDIATE 'DELETE FROM USERS@' ||
X.db_name
|| ' WHERE lower(email)=lower('''
|| sf_email || ''');';
END LOOP;

Tom Kyte
March 17, 2006 - 6:10 pm UTC

ugh, missing binds :(


are you sure the invalid character is for the @, I'm sure it isn't :)

I'm quite sure it is the ';' at the end of the delete.


for x in ( ... )
loop
execute immediate 'delete from users@' || x.db_name ||
' where lower(email) = :x ' using lower(sf_email);
end loop;


do you really need that lower(email), you know what that IMPLIES right (unless you have an index on lower(email)....)

So...

A reader, March 17, 2006 - 9:06 pm UTC

Why am I able to issued:

SQL> create user test identified by "1test";
User created.

grant connect to  test ;
Grant succeeded.

SQL> connect test/1test
Connected. 

Tom Kyte
March 18, 2006 - 4:32 pm UTC

passwords are always case insensitive (folded to upper case actually) and by using the "quoted identifier" - you can start passwords (or any identifier really) with special characters.

I tgry to login in a forum.

Grace karali, July 09, 2006 - 10:00 am UTC

I try to login in a forum but i can because they dont axept my password,they wand alpanumeric caracters only .can you help me, can you finds me a password with these caracters please?

Tom Kyte
July 09, 2006 - 1:44 pm UTC

eh? any forum in particular?

Alexander the ok, May 10, 2007 - 2:50 pm UTC

Tom,

My question is a waste of your talent, but I don't understand this:

SQL> begin
  2  /* start program
  3  ####
SP2-0042: unknown command "###" - rest of line ignored.
  3  */
  4  null;
  5  end;
  6  /

PL/SQL procedure successfully completed.


A developer has some pound signs in their comments like that in one of their packages. I just don't know what is special about them in that context.

The documentation does not mention this in it's blurb about comment restrictions. Thanks as always.
Tom Kyte
May 11, 2007 - 11:03 am UTC


SP2-0545: SET command requires an argument.
ops$tkyte%ORA10GR2> begin
  2  # hello
SP2-0042: unknown command "hello" - rest of line ignored.


it is not a comment restriction, it is a sqlplus'ism (an old old thing). line starting with #, "special" (has to be column 1)

ops$tkyte%ORA10GR2> select
  2  # column foo format a5 heading hey
  2  dummy foo from dual;

hey
-----
X

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select
  2  # column foo format a15 heading there
  2  dummy foo from dual;

there
---------------
X


Alexander the ok, May 11, 2007 - 11:26 am UTC

I didn't think it was a sqlplus thing because that only seems to happen inside pl/sql blocks.
Tom Kyte
May 11, 2007 - 11:50 am UTC

umm, i used SELECT to demonstrate.....

so, not true.

Alexander the ok, May 11, 2007 - 12:06 pm UTC

Right, I understand that now.

Any sqlplus system variables I can set to avoid this behavior? Doesn't look like it.

passwords

A reader, November 13, 2008 - 4:37 pm UTC


Special characters

A reader, October 10, 2012 - 3:00 pm UTC

Tom,
In 11gr2, Oracle has come a long way to even allowing the use of case sensitive passwords. Still I cannot use special characters like @!* etc. in a password. This seems to be a serious limitation. Do you happen to know why Oracle does not allow use of these characters?

Thanks...

Tom Kyte
October 10, 2012 - 4:36 pm UTC

ops$tkyte%ORA11GR2> alter user ops$tkyte identified by "hello@*!there";

User altered.

ops$tkyte%ORA11GR2> connect ops$tkyte/"hello@*!there"
Connected.



be careful with @! many things will think "this is a tnsnames thing", that is why I had to quote it to hide the @ from sqlplus - it would parse it and try to connect to a database !there



ops$tkyte%ORA11GR2> alter user ops$tkyte identified by "hello*there";

User altered.

ops$tkyte%ORA11GR2> connect ops$tkyte/hello*there
Connected.
ops$tkyte%ORA11GR2> 




Special characters

A reader, October 11, 2012 - 8:24 am UTC

Thanks Tom. Every time I ask a question on your forum, I learn something new about Oracle.

Once I have a password with a @ how can I login or change it?

Andrew, January 03, 2013 - 6:30 am UTC

I logged in with SQL*plus and changed my password to something with a @
How can I now start SQL*plus with the new password?

--------------------

The only way I could find was by logging in with a different account [with no @ in the password] and then, once "in" SQL*plus, issuing
SQL>CONNECT user/"pass"

[what I mean to say is that when I tried using "" around my password on the shell command to start sqlplus

C:\>sqlplus user/"pass"@database

it did NOT stop sqlplus finding the @ in my password & assuming the database name was immediately after it.

And I could not find a way to start SQL*plus in a "disconnected" state

And I could not find a way to give just the database name to SQL*plus, so it would prompt for user & password instead of parsing them from command line]

Is there any way other than using another account [either on the same or a different database,] or some other standard tool besides SQL*plus for changing passwords?

Thanks in advance!

Tom Kyte
January 04, 2013 - 3:19 pm UTC

$ sqlplus /nolog


will get you into plus without a username/password disconnected.


Also, remember the " is a special character - you might have to escape it, on Linux for example:

$ sqlplus b/\"X@Y\"

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 4 16:17:33 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

b%ORA11GR2> 


would work - whereas sqlplus b/"X@Y" would not - since the shell would have interpreted the "" before sqlplus does.

Andrew, January 07, 2013 - 3:22 am UTC

Thank you so much Tom - I can confirm

sqlplus user/\"p@ss/word\"@database

works in Windows also. Probably what I wasn't expecting is that usually double quotes are *not* escaped on a Windows command line.

As for /NOLOG - I have no explanation how I missed that in the middle of the help... Thanks again.

I swear I have searched and searched for both these things to no avail; clearly the problem lies between my keyboard and screen...

thanks

A reader, April 29, 2015 - 6:24 pm UTC

thanks this is useful

How do you change a wrong format password?

Muhammad Anil Babur, July 29, 2020 - 12:39 pm UTC

Thanks for mentioning the password rules, I had set sys password starting with number and with special character, including @$. Is there any way to reset it to normal, because now I cannot login via sqlplus.
Regards.
Connor McDonald
July 30, 2020 - 12:25 am UTC

If you can get local access to the box, you can do:

sqlplus / as sysdba

If you cannot, then you need to carefully escape the characters so that it is indeed passed correctly across the line.

Alternatively, try loading into a wallet - see here for an example

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/