User Roles/Responsibilities Missing After 11i - 12.2.4 Upgrade for User Management due to script UMXFGAMigrationScript.sql receiving ORA-00001: unique constraint violations

(Doc ID 2017941.1)

Last updated on DECEMBER 09, 2016

Applies to:

Oracle User Management - Version 12.2.4 to 12.2.4 [Release 12.2]
Information in this document applies to any platform.


After upgrading from 11i to 12.2.4, the user roles and responsibilities are not displayed correctly.  The UMXFGAMigrationScript.sql script is responsibile for converting the grants to roles during the upgrade.  Upgrade logs show the script completed successfully and there were no errors written to the log_message tables. Adding tracing statements to the UMX_FGA_UTILS_PKG and executing the UMXFGAMigrationScript.sql script manually shows why the Roles were not migrated.

The trace indicates the data was not properly migrated because of a unique constraint violation for the below statement:
insert into UMX_LSA_ROLE_SET_ROLES (role_set_id, role_name)
select roleSetId, INSTANCE_PK1_VALUE
       from fnd_grants fg,
      fnd_objects fo,
      fnd_menus fm,
     wf_all_roles_vl wfr
where fg.menu_id = fm.menu_id
   and fg.object_id = fo.object_id
   and INSTANCE_PK1_VALUE  = (+)
  and grantee_key = adminRole
   and fm.menu_name = 'UMX_OBJ_ADMIN_ROLE_PERMS'
  and fo.obj_name = 'UMX_ACCESS_ROLE'
   and fg.start_date <= sysdate
and nvl(fg.end_date, sysdate+1) > sysdate

Snippet from trace...
INSERT UMX LSA ROLE 1                                                                                
copy_grants_to_roleSets: UMX|AAC-M21640-CTL 1120                                                      
INSERT UMX LSA ROLE SET ROLES 1                                                                      
EXCEPTION 1 = -1 ORA-00001: unique constraint (APPLSYS.UMX_LSA_ROLE_SET_ROLES_P) violated                            


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms