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 08, 2022
Applies to:
Oracle User Management - Version 12.2.4 to 12.2.4 [Release 12.2]Information in this document applies to any platform.
Symptoms
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 = 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...
assign_roleSet_to_adminRole
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
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |