Migrate credential vault data using SQL and direct database operations

Migrate credential vault data using SQL and direct database operations

+
Search Tips | Advanced Search

Migrating the credential secrets involves moving two tables...

VAULT_DATA
VAULT_RESOURCES

...from the earlier version database to the current database. The table definition has not changed in WebSphere Portal V6.1, so the data does not need to be changed. However, because the database is now split into different database domains, the data also has to be split.
Use an import and export utility that is provided by the database server.
The following example procedure explains how to import and export the tables when DB2 is the database server.
Perform this procedure before any users have accessed the current system and potentially added new data to the system.
If you are migrating from V5.0.2.2 or V5.0.2.3, add the following lines to both the SQL export and import commands to avoid a data type mismatch:
    cred_segment.user_mapped>0=>cred_segment.user_mapped='Y'
    cred_segment.user_mapped=0=>cred_segment.user_mapped='N'
Procedure
Export the tables from the earlier version database.
On the earlier version database server, run...
Enter the following commands...
 connect to wpsdb user dbuser using dbuserpw 
 export to C:/temp/vault.res.customization.wp5.ixf of ixf messages C:/temp/vault.res.customization.wp5.msgtxt  select distinct vault_resources.resource_name  from vault_resources, 
     cred_slot,
     cred_segment  where vault_resources.resource_name = cred_slot.resource_name 
  and cred_slot.segment_oid = cred_segment.oid 
  and cred_segment.user_mapped > 0
 export to C:/temp/vault.res.release.wp5.ixf of ixf messages C:/temp/vault.res.release.wp5.msgtxt  select distinct vault_resources.resource_name  from vault_resources, 
     cred_slot, 
     cred_segment  where vault_resources.resource_name = cred_slot.resource_name 
  and cred_slot.segment_oid = cred_segment.oid 
  and cred_segment.user_mapped = 0 
 export to C:/temp/vault.data.customization.wp5.ixf of ixf messages C:/temp/vault.data.customization.wp5.msgtxt  select vault_data.resource_name, 
       vault_data.user_dn, 
       vault_data.userid, 
       vault_data.pwd, 
       vault_data.binary_data  from cred_segment, 
     cred_slot, 
     vault_data   where vault_data.resource_name = cred_slot.resource_name 
      and cred_slot.segment_oid = cred_segment.oid 
      and cred_segment.user_mapped > 0 
 export to C:/temp/vault.data.release.wp5.ixf of ixf messages C:/temp/vault.data.release.wp5.msgtxt  select  vault_data.resource_name, 
        vault_data.user_dn, 
        vault_data.userid, 
        vault_data.pwd, 
        vault_data.binary_data  from cred_segment, 
     cred_slot, 
     vault_data   where vault_data.resource_name = cred_slot.resource_name  and cred_slot.segment_oid = cred_segment.oid  and cred_segment.user_mapped = 0
 disconnect wpsdb
...where...

wpsdb is the database name of the earlier version database,...
dbuser is the database administrator user ID
dbuserpw is the password for this user ID
Import the data in the tables to the current database.
If the current database server is not on the same system as the earlier version database server, copy the exported IXF files to the current database server system.
On the current database server, run... on UNIX.
Enter the following commands. Type each section of text on one line:
 connect to wpsdb user dbuser using dbuserpw 
 import from C:/temp/vault.res.customization.wp5.ixf of ixf  modified by indexschema=customization messages C:/temp/vault.res.customization.wp6.msgtxt  insert into customization.vault_resources 
 import from C:/temp/vault.res.release.wp5.ixf of ixf  modified by indexschema=release messages C:/temp/vault.res.release.wp6.msgtxt  insert into release.vault_resources 
 import from C:/temp/vault.data.customization.wp5.ixf of ixf  modified by indexschema=customization messages C:/temp/vault.data.customization.wp6.msgtxt  insert into customization.vault_data 
 import from C:/temp/vault.data.release.wp5.ixf of ixf  modified by indexschema=release messages C:/temp/vault.data.release.wp6.msgtxt  insert into release.vault_data
...where...

wpsdb is the database name of the current database
dbuser is the database administrator user ID
dbuserpw is the password for this user ID

Under certain conditions, the import operation into the VAULT_RESOURCES and VAULT_DATA tables might generate errors indicating that a row with the same resource name already exists. Disregard this error. The VAULT_RESOURCES table only defines resource names for use in the credential vault. If the names already exist, there is no need to redefine them. The same is true for the VAULT_DATA table. Credentials that are already stored in the table must not be redefined. Here is an example of this type of error:
SQL0803N One or more values in the insert statement, update statement, or foreign key update caused by a delete statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "schema_name.vault_resources or vault_data" from having duplicate rows for those columns. sqlstate=23505
Enter the following commands
 select user_dn, 
       resource_name  from release.vault_data vd1 where exists 
    (select * from release.vault_data vd2 
    where lower(vd1.user_dn) = lower(vd2.user_dn) 
    and   vd1.resource_name = vd2.resource_namE
    and   vd1.user_dn < vd2.user_dn)
 select user_dn, 
       resource_name  from customization.vault_data vd1 where exists 
   (select * from customization.vault_data vd2 
    where lower(vd1.user_dn) = lower(vd2.user_dn) 
    and vd1.resource_name = vd2.resource_name
    and vd1.user_dn < vd2.user_dn)
Any value returned by these commands shows a potential user DN conflict where a conversion to lowercase would not complete successfully. Review those entries for its matching user DN and manually remove the duplicate rows. Rerun the commands in this step until no values are returned.
When the commands in the previous step do not return any values, enter the following commands to update the user DN fields to the lowercase string:
 UPDATE RELEASE.VAULT_DATA SET USER_DN = LOWER(USER_DN)
 UPDATE CUSTOMIZATION.VAULT_DATA SET USER_DN = LOWER(USER_DN)
Enter the following command:
 disconnect wpsdb
...where wpsdb is the database name of the current database.
Use the method described in the topic on setting configuration properties topic to make sure that the systemcred.dn value in the Credential Vault service configuration that represents the current version is set to the same value that was used in the earlier version. This DN is used to store system (shared) credentials. If this value is not the same, the vault does not return the credential secrets for shared slots.
Parent topic
Migrating the access control configuration

Related tasks

Migrating permissions on All Authenticated Users and All Portal User Groups
Migrating permissions on administrative resources
Migrating credential vault data using the XML configuration interface
Set service configuration properties