Uploaded image for project: 'ZZZ-WSO2 Identity Server'
  1. ZZZ-WSO2 Identity Server
  2. IDENTITY-3271

Cannot save user profile when using Oracle database when the userstore is set to JDBCIdentityDataStore in identity-mgt.properties

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Highest
    • Resolution: Fixed
    • Affects Version/s: 5.0.0-GA
    • Fix Version/s: 5.1.0-M2
    • Component/s: identity-mgt
    • Labels:
    • Severity:
      Blocker
    • Estimated Complexity:
      Novice
    • Test cases added:
      Yes

      Description

      The user profile cannot be saved when an Oracle database is used and when account locking feature is enabled. (This happens when JDBCIdentityDataStore is used).

      For new users, the account locking related claim/s are not stored initially. When we try to edit the profile, we get a UI like in the attached image. (Here I've made only the account locking claim as supported by default)

      Since no value is yet given for the account locking claim, we see a text box. (This is not a big issue).

      But when we do not give any value for the text box for account locking, it gives the following error.

      [2015-05-11 11:36:53,202] ERROR

      {org.wso2.carbon.identity.mgt.store.JDBCIdentityDataStore}

      - Error while persisting user identity data in database
      java.sql.SQLException: ORA-01407: cannot update ("XXXXXXX"."IDN_IDENTITY_USER_DATA"."DATA_VALUE") to NULL

      The problem is when using JDBCIdentityDataStore, it stores the special claims such as account locking claims in the table IDN_IDENTITY_USER_DATA.

      Following is the create table statement for IDN_IDENTITY_USER_DATA table.

      CREATE TABLE IDN_IDENTITY_USER_DATA (
      TENANT_ID INTEGER DEFAULT -1234,
      USER_NAME VARCHAR(255) NOT NULL,
      DATA_KEY VARCHAR(255) NOT NULL,
      DATA_VALUE VARCHAR(255) NOT NULL,
      PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY))

      The problem is DATA_VALUE column has NOT NULL constraint.

      From code actually we are sending an empty string ("") but at Oracle database it is converted to NULL by default. This is because an empty string is treated as a null value in Oracle.

      Therefore we cannot fix this by patching the code.

      A solution is to alter the IDN_IDENTITY_USER_DATA table and remove the NOT NULL constraint for the DATA_VALUE column in existing setups.

      ALTER TABLE IDN_IDENTITY_USER_DATA MODIFY (DATA_VALUE NULL);

      When we are not using JDBCIdentityDataStore in identity-mgt.properties as the userstore this works fine because then the claims are stored in the UM_USER_ATTRIBUTE table where the UM_ATTR_VALUE column that stores claim values does not have a NOT NULL constraint.

      The actual fix is to modify the database scripts such that DATA_VALUE column of IDN_IDENTITY_USER_DATA is allowed null values

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                johann@wso2.com Johann Nallathamby
                Reporter:
                tharindue@wso2.com Tharindu Edirisinghe
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: