In previous release an Oracle
password could have a maximum length of 30 characters.
This is still the same as before. A password is an
identifier like any other identifier in the database and
identifiers are still limited to a maximum length of 30
characters, strangely enough.
%
Now a 30 character Oracle password can contain a
mix of symbols and
multi-byte
characters! Pre-Oracle Database 11g only allows "_", "$"
and "#" symbols.
Let's try to
create a password with symbols:
SYS AS SYSDBA @ orcl
SQL> alter user lutz identified by _#new%&;
SP2-0317: expected symbol name is missing
alter user lutz identified by _#new%&
*
ERROR at line 1:
ORA-00911: invalid character
Uups, it does not work this way!
Another try, this time with single
quotes:
SYS AS SYSDBA @ orcl SQL> alter user lutz identified by
'_#new%&';
alter user lutz identified by '_#new%&'
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
%
You must use double quotes for passwords with
symbols other than _, # or $!
SYS AS SYSDBA @ orcl SQL> alter user lutz identified by
"_#new%&";
User altered.
Not all symbols can be used in 11g
passwords without special care, not even when you are
using double quotes. Let's see what happens if we use an
ampersand (&):
SYS AS SYSDBA @ orcl
SQL> alter user lutz identified by "_#new%&1234567890";
Enter value for 1234567890:
old 1: alter user lutz identified by
"_#new%&1234567890"
new 1: alter user lutz identified by "_#new%"
User altered.
This works like an exchange variable
and is caused by the default setting for DEFINE
in SQL*PLUS:
SYS AS SYSDBA @ orcl
SQL> show define
define "&" (hex 26)
% It is strongly
recommended to use the pls*sql command
password in order to change the
password and use an & as a symbol in a password.
% Beware of ?&?
in passwords!
% Comma,
backslash, double quote and the DEFINE symbol are
not allowed in passwords
Now let's check the maximum length of
passwords:
SYS AS SYSDBA @ orcl
SQL> alter user lutz identified by
"_#new%1234567890%123454667890%1";
alter user lutz identified by
"_#new%1234567890%123454667890%1234567890"
*
ERROR at line 1:
ORA-00972: identifier is too long
[oracle@rhas4 ~]$ oerr
ora 972
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 30
characters was specified.
// *Action: Specify at most 30 characters.
Case sensitive passwords
A new 11 g database uses case
sensitive passwords by default if it was created with the
database configuration assistant (DBCA). This is
implemented through the new initialization parameter
sec_case_sensitive_login.
A 10g database which is upgraded with
the database upgrade assistant (DBUA) also uses case
sensitive passwords by default but only for newly created
user accounts.
% If you create
an 11g database manually with a create database
command case sensitive passwords are
not used by DEFAULT!
SYSTEM @ orcl111
SQL> show parameter sec_case
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
sec_case_sensitive_logon
boolean TRUE
% The
default for the parameter sec_case_sensitive_login
is FALSE.
An Oracle 11g password file can also
store case sensitive passwords. The password file creation
utility evaluates the new parameter ignorecase
to allow case sensitive passwords or restrict
passwords to case insensitivity.
%
In order to create a password file with orapwd
which disallows case sensitive passwords you
must set ignorecase to N.
[oracle@rhas4 ~]$ orapwd
help=y
Usage: orapwd file=<fname> password=<password>
entries=<users> force=<y/n>
ignorecase=<y/n> nosysdba=<y/n>
where
file - name of password file (required),
password - password for SYS (optional),
entries - maximum number of distinct DBA (required),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional
Database Vault only).
There must be no spaces around the equal-to (=) character.