ORA-02153: invalid VALUES password string

Posted: June 10, 2014 in Database General
Tags: , , ,

Today i seen that “create user identified by values” command was not working. Below is the error message:

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 20 06:36:55 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create user myuser identified by myuser default tablespace user_data temporary tablespace temp;

User created.

SQL> set long 100000
SQL> set line 1000
SQL>
SQL> select dbms_metadata.get_ddl('USER','MYUSER') from dual;

DBMS_METADATA.GET_DDL('USER','MYUSER')
--------------------------------------------------------------------------------

   CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A
009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'
      DEFAULT TABLESPACE "USER_DATA"
      TEMPORARY TABLESPACE "TEMP"


SQL> drop user myuser cascade;

User dropped.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, what to do for running this in 10g database and here comes the new thing in password.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

See in above query there are two types of passwords in above query which are taken from user$ table. Our old 10g format password is saved in column PASSWORD and new 11g format password is stored in column SPARE4 in user$ table. Just to verify the above thing, if we closely monitor the user creation script then there is “;” in password seperating the 10g & 11g version od password. See the below code:

'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'

The shorter password is for 10g and longer one if for 11g only. Below code created user successfully in 10g database while the user creation script is captured from 11g database in above steps.

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 20 06:40:46 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> conn myuser/myuser
ERROR:
ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.

When the password version field contains both values like “10G 11G” then your password will contains both the 10g & 11g encrypted password and “sec_case_sensitive_logon” parameter is false then you can use case insensitive passwords, like I have logged in successfully with password “myuser” and “MYUSER”.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, say you created user with only 11g encrypted password (longer version of string) then you must set the “sec_case_sensitive_logon” parameter to true. Then only you will be able to login with old password as shown in below code. Notice that password_version field only contains 11G now and only spare4 column has encrypted value not the password column as in case of “10G 11G” password.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;

System altered.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER                                    11G      S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

Now, time to create use with 10g encrypted password only. Below is code having details of same. Now, password column has encrypted password while spare4 column is empty. Also, with 10g encrypted password it doesn’t matter what value of parameter “sec_case_sensitive_logon” you set, user will be able to login as in below code.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.

SQL> conn / as sysdba
Connected.
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;

System altered.

SQL> conn myuser/myuser
Connected.
SQL> conn myuser/MYUSER
Connected.

SQL> conn / as sysdba
Connected.
SQL>
SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

Hope this will help 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s