Oracle用户密码使用特殊符号，例如&(AND)、$(Dollar)、#(Pound)、*(Star)等

我们在管理ORACLE用户密码安全的时候总会用到各种由工具生成的密码带有特殊符号的问题，例如&、*、#、$等，但是在使用如上特殊密码往往会遇到各种错误，例如：

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user maclean_password identified by #$%^&*!;
create user maclean_password identified by #$%^&*!
*
ERROR at line 1:
ORA-00911: invalid character

[oracle@database ~]$ oerr ora 911
00911, 00000, “invalid character”
// *Cause: identifiers may not start with any ASCII character other than
// letters and numbers. $#_ are also allowed after the first
// character. Identifiers enclosed by doublequotes may contain
// any character other than a doublequote. Alternative quotes
// (q’#…#’) cannot use spaces, tabs, or carriage returns as
// delimiters. For all other contexts, consult the SQL Language
// Reference Manual.
// *Action:

注意Oracle用户的密码必须以字母或者数字开头(letters and numbers)，否则将出现ORA-00911 错误

当已经确保以字母或者数字开头的情况仍出现错误，则考虑使用双引号”将密码括起来，注意不要用中文输入法的双引号！！

SQL> create user maclean_password identified by 1#$%^&*!;
create user maclean_password identified by 1#$%^&*!
*
ERROR at line 1:
ORA-00911: invalid character

SQL> create user maclean_password identified by “1#$%^&*!”;

User created.

SQL> grant connect to maclean_password
2 ;

Grant succeeded.

SQL> conn maclean_password/”1#$%^&*!”
Connected.

还有一种可能性是在SQLPLUS或者其他IDE(例如PL/SQL DEVELOPER下)存在&(AND)符号时，客户端将&理解为变量，如：

SQL> create user maclean_password1 identified by “0000&a”;
Enter value for a:

此时只需要将define修改为非&的其他符号即可，例如：

SQL> set define +

SQL> show define
define “+” (hex 2b)

SQL> create user maclean_password1 identified by “0000&a”;

User created.

SQL> grant connect to maclean_password1;

Grant succeeded.

SQL> conn maclean_password1/”0000&a”
Connected.