Friday, November 14, 2014

Case Sensitive Passwords in Oracle 11

In version 11, Oracle passwords became case sensitive. You can see what I mean in the example below:

SQL> conn / as sysdba
Connected.
SQL> alter user system identified by manager
  2  /
 
User altered.
 
SQL> conn system/manager
Connected.
SQL> conn system/MANAGER
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL>
 
The DBA_USERS view no longer contains the encrypted password, except when the user is identified externally:
 
SQL> conn / as sysdba
Connected.
SQL> alter user system identified externally
  2  /
 
User altered.
 
SQL> select password from dba_users
  2  where username = 'SYSTEM'
  3  /
 
PASSWORD
------------------------------
EXTERNAL
 
SQL> alter user system identified by manager
  2  /
 
User altered.
 
SQL> select password from dba_users
  2  where username = 'SYSTEM'
  3  /
 
PASSWORD
------------------------------
 
SQL>
 
You can see the Oracle 10 encrypted password in the NAME column of SYS.USER$ and the Oracle 11 encrypted value of the password in the SPARE4 column of the same table:
 
SQL> select password from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
PASSWORD
------------------------------
D4DF7931AB130E37
 
SQL> select spare4 from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
SPARE4
-----------------------------------------------------------------
S:9FA0ADFDC164534A13A388167C4D75BC6C9CF35F7A9CC58F1CDBB1DC7653
 
SQL>
 
If you use the Oracle 10 encrypted value to reset the password, the Oracle 11 encrypted value disappears from the SPARE4 column:
 
SQL> alter user system identified by values 'D4DF7931AB130E37'
  2  /
 
User altered.
 
SQL> select password from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
PASSWORD
------------------------------
D4DF7931AB130E37
 
SQL> select spare4 from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
SPARE4
-----------------------------------------------------------------
 
SQL>
 
… and the password is no longer case sensitive:
 
SQL> conn system/manager
Connected.
SQL> conn system/MANAGER
Connected.
SQL>
 
Conversely, if you use the Oracle 11 encrypted value to reset the password, the Oracle 10 encrypted value disappears from the PASSWORD column:
 
SQL> alter user system identified by values
  2  'S:FA743A680B015952982BB2B501B7BC623F1AA11093AC95E58D67B18A2AC2'
  3  /
 
User altered.
 
SQL> select password from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
PASSWORD
------------------------------
 
SQL> select spare4 from sys.user$
  2  where name = 'SYSTEM'
  3  /
 
SPARE4
-----------------------------------------------------------------
S:FA743A680B015952982BB2B501B7BC623F1AA11093AC95E58D67B18A2AC2
 
SQL>
 
… and the password becomes case sensitive again:
 
SQL> conn system/manager
Connected.
SQL> conn system/MANAGER
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
SQL>

Thursday, November 13, 2014

Rollback to Savepoint Does Not Release Locks

I read that rolling back to a savepoint releases locks. This sounded reasonable so I decided to check it out in an Oracle 11.2 database. I logged in as user John (in blue) and noted my SID for future reference. Then I created a table, inserted a row, committed the change and created a savepoint. Finally I updated the row but did not commit the change, thus setting up a lock:
 
SQL> conn john/smith
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       683
 
SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
 
SQL> savepoint sp1
  2  /
 
Savepoint created.
 
SQL> update tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         2
 
SQL>
 
I logged into a new session as user Fred (in red), noted my SID again and tried to update the same table. This did nothing, as you might expect:
 
SQL> conn fred/bloggs
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
         5
 
SQL> update john.tab1 set col1 = 3
  2  /
 
I returned to John’s session and checked that I could see the lock in the DBA_WAITERS view. Then I rolled back to the savepoint and checked that the original value had reappeared in the table (it had). However, the lock was still shown in DBA_WAITERS:
 
SQL> show user
USER is "JOHN"
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
HOLDING_SESSION WAITING_SESSION
--------------- ---------------
            683               5
 
SQL> rollback to savepoint sp1
  2  /
 
Rollback complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
 
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
HOLDING_SESSION WAITING_SESSION
--------------- ---------------
            683               5
 
SQL>
 
I guessed that Oracle must still be holding some kind of lock between the two transactions. There was clearly no lock on the data any more as Donald was able to start a new session (in green) and update it with no problems:
 
SQL> conn donald/duck
Connected.
SQL> update john.tab1 set col1 = 4
  2  /
 
1 row updated.
 
SQL> select * from john.tab1
  2  /
 
      COL1
----------
         4
 
SQL> commit
  2  /
 
Commit complete.
 
SQL>
 
User John finished his transaction and the lock disappeared:
 
SQL> show user
USER is "JOHN"
SQL> commit
  2  /
 
Commit complete.
 
SQL> select holding_session, waiting_session
  2  from dba_waiters
  3  /
 
no rows selected
 
SQL>
 
… and this allowed Fred’s earlier update to finish:
 
SQL> conn fred/bloggs
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
         5
 
SQL> update john.tab1 set col1 = 3
  2  /
 
1 row updated.
 
SQL>