User Roles/Responsibilities Missing After 11i - 12.2.4 Upgrade for User Management due to script UMXFGAMigrationScript.sql receiving ORA-00001: unique constraint violations
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,
where fg.menu_id = fm.menu_id
and fg.object_id = fo.object_id
and INSTANCE_PK1_VALUE = wfr.name (+)
and grantee_key = adminRole
and fm.menu_name = 'UMX_OBJ_ADMIN_ROLE_PERMS'
and fo.obj_name = 'UMX_ACCESS_ROLE'
and INSTANCE_TYPE = 'INSTANCE'
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
Million Knowledge Articles and hundreds of Community platforms