Laurent Schneider

Did you ever think you could write something nobody can read ?

Submit your query before April 1st to the Oracle SQL Obfuscation Contest…

You can also vote there

My query : http://www.oraclecommunity…7305

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

While Linux x86_64 just came out, Unix Releases like HPUX, AIX and SunSolaris will probably not be available before third week of April.

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.

I will be speaking about SQL Model at Baton Rouge Oracle User Group, Thursday, March 20, 2008 from 11:30am to 1:30pm.

Check http://www.broug.org for details

I just noticed today the Web 2.0 interface of metalink :

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

I tried my chance at Le petit jeu des requêtes SQL and yesterday received my price at home.

Thanks to the author for organizing the competition

Next milestone : oraclecommunity.net obfuscation contest

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