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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, subhakar.

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

Answered by: Tom Kyte - Last updated: July 30, 2020 - 12:25 am UTC

Category: Database - 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.

and you rated our response

  (25 ratings)

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

Reviews

Special Characters -- in a create user command

December 01, 2004 - 5:46 pm UTC

Reviewer: Tim from Huntsville, AL

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

Followup  

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

December 02, 2004 - 12:53 pm UTC

Reviewer: Tim from Huntsville, AL

Tom,
I found the info I was needing.

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

username

January 07, 2005 - 7:50 am UTC

Reviewer: syed from India

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

Tom Kyte

Followup  

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)

January 11, 2005 - 7:15 pm UTC

Reviewer: Jim Hill from Birmingham, AL USA

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

January 14, 2005 - 2:06 pm UTC

Reviewer: Beverly Wise from Fairfax, VA

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?

January 14, 2005 - 7:36 pm UTC

Reviewer: Kurt from Springfield, VA USA

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?

January 15, 2005 - 11:55 am UTC

Reviewer: Beverly from Fairfax, VA

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

January 26, 2005 - 12:03 pm UTC

Reviewer: Mike from York, UK

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?

May 12, 2005 - 8:58 am UTC

Reviewer: mhthomas from Raleigh, NC

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

Followup  

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

September 21, 2005 - 8:51 am UTC

Reviewer: Mahomed Suria from UK

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

Followup  

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

September 26, 2005 - 8:49 am UTC

Reviewer: Mahomed Suria from UK

Thanks Tom,

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


Help

January 19, 2006 - 4:21 am UTC

Reviewer: Raj from Pune,India

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

Followup  

January 19, 2006 - 12:37 pm UTC

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

Special characters further

March 17, 2006 - 5:32 pm UTC

Reviewer: Melissa from DC

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

Followup  

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...

March 17, 2006 - 9:06 pm UTC

Reviewer: A reader

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

Followup  

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.

July 09, 2006 - 10:00 am UTC

Reviewer: Grace karali from Athens-Greece.

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

Followup  

July 09, 2006 - 1:44 pm UTC

eh? any forum in particular?

May 10, 2007 - 2:50 pm UTC

Reviewer: Alexander the ok

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

Followup  

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


May 11, 2007 - 11:26 am UTC

Reviewer: Alexander the ok

I didn't think it was a sqlplus thing because that only seems to happen inside pl/sql blocks.
Tom Kyte

Followup  

May 11, 2007 - 11:50 am UTC

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

so, not true.

May 11, 2007 - 12:06 pm UTC

Reviewer: Alexander the ok

Right, I understand that now.

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

passwords

November 13, 2008 - 4:37 pm UTC

Reviewer: A reader


Special characters

October 10, 2012 - 3:00 pm UTC

Reviewer: A reader

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

Followup  

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

October 11, 2012 - 8:24 am UTC

Reviewer: A reader

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?

January 03, 2013 - 6:30 am UTC

Reviewer: Andrew

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

Followup  

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.

January 07, 2013 - 3:22 am UTC

Reviewer: Andrew

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

April 29, 2015 - 6:24 pm UTC

Reviewer: A reader

thanks this is useful

How do you change a wrong format password?

July 29, 2020 - 12:39 pm UTC

Reviewer: Muhammad Anil Babur from Pakistan

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

Followup  

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/