alter user identified by values in 11g

I wrote about dba_users changes in 11g .

When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords.

When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.

SQL> create user u identified by u;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> connect u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect u/u
Connected.

Per default only the proper case works

SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> connect u/U
Connected.
SQL> conn u/u
Connected.

When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior).

When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive.


SQL> select password,spare4 from user$ where name='U';

PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------
18FE58AECB6217DB
S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4

The hashes are in USER$.

SQL> alter user u identified by values '18FE58AECB6217DB';
User altered.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4';
User altered.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4;18FE58AECB6217DB';
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When using both hashes, switching back and forth to 11g mechanism is possible.

Software Configuration Manager

I just noticed today the Web 2.0 interface of metalink :

csm

The URL is http://csm.oracle.com, like Coftware Sonfiguration Manager (or maybe Configuration Software Manager).
[edit: original name is: Configuration Support Manager]

It seems nice, you can customize the portal with drag and drop, their is a blog too http://blogs.oracle.com/supportportal and a feedback button I am going to use right now!

Enjoy the new interface to read and update your SR :)

sr.png

take care of minus !

Imagine this script (10gR2) :

set echo on
select  BINARY_DOUBLE_INFINITY -
BINARY_DOUBLE_INFINITY from DUAL;

Run it and you would will get an expected result!

SQL> select BINARY_DOUBLE_INFINITY -
> BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY
----------------------
                   Inf

The issue in sqlplus is that – at the end of line means “query continues next line”. The correct answer of Inf-Inf is Nan.

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
                                          Nan