We are unable to see the SYSADMIN responsibilities after instance refresh. We followed the below steps to resolve this issue.
Updating wf_local_user_roles and wf_USER_ROLE_ASSIGNMENTS resolves this issue.
Step 1:
SQL> select effective_end_date from wf_local_user_roles where user_name = 'SYSADMIN';
EFFECTIVE_END_D
--------------------------
01-JAN-99
01-JAN-99
222 rows selected.
Step 2:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_local_user_roles where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE_END_DATE,'DD-MO
-----------------------------------------------------------
01-jan-99
01-jan-99
222 rows selected.
Step 3:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_USER_ROLE_ASSIGNMENTS where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE_END_DATE,'DD-MO
-----------------------------------------------------------
01-jan-99
01-jan-99
01-jan-99
243 rows selected.
Step 4:
SQL> create table wf_user_role_assignments_bkp as select * from wf_user_role_assignments;
Table created.
SQL> create table wf_local_user_Roles_mar as select * from wf_local_user_Roles;
Table created.
Step 5:
SQL> update wf_user_role_assignments
set user_end_date=null,
role_end_date=null,
assigning_Role_end_Date=null,
effective_end_Date=to_date('31-12-9999','dd-mm-yyyy')
where user_name=upper('&username');
Enter value for username: SYSADMIN
old 6: where user_name=upper('&username')
new 6: where user_name=upper('SYSADMIN')
243 rows updated.
Step 6:
SQL> update wf_local_user_Roles
set user_end_date=null,
role_end_date=null,
effective_end_Date=to_date('31-12-9999','dd-mm-yyyy')
where user_name=upper('&username');
Enter value for username: SYSADMIN
old 5: where user_name=upper('&username')
new 5: where user_name=upper('SYSADMIN')
222 rows updated.
SQL> commit;
Commit complete.
Step 7:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_USER_ROLE_ASSIGNMENTS where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE
-------------------------------
31-dec-9999
31-dec-9999
31-dec-9999
31-dec-9999
243 rows selected.
SQL> select effective_end_date from wf_local_user_roles where user_name = 'SYSADMIN';
EFFECTIVE_END_D
----------------------------
30-NOV-15
30-NOV-15
30-NOV-15
222 rows selected.
SQL> commit;
Commit complete.
Now, we are able to access all the SYSADMIN the responsibility.
Updating wf_local_user_roles and wf_USER_ROLE_ASSIGNMENTS resolves this issue.
Step 1:
SQL> select effective_end_date from wf_local_user_roles where user_name = 'SYSADMIN';
EFFECTIVE_END_D
--------------------------
01-JAN-99
01-JAN-99
222 rows selected.
Step 2:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_local_user_roles where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE_END_DATE,'DD-MO
-----------------------------------------------------------
01-jan-99
01-jan-99
222 rows selected.
Step 3:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_USER_ROLE_ASSIGNMENTS where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE_END_DATE,'DD-MO
-----------------------------------------------------------
01-jan-99
01-jan-99
01-jan-99
243 rows selected.
Step 4:
SQL> create table wf_user_role_assignments_bkp as select * from wf_user_role_assignments;
Table created.
SQL> create table wf_local_user_Roles_mar as select * from wf_local_user_Roles;
Table created.
Step 5:
SQL> update wf_user_role_assignments
set user_end_date=null,
role_end_date=null,
assigning_Role_end_Date=null,
effective_end_Date=to_date('31-12-9999','dd-mm-yyyy')
where user_name=upper('&username');
Enter value for username: SYSADMIN
old 6: where user_name=upper('&username')
new 6: where user_name=upper('SYSADMIN')
243 rows updated.
Step 6:
SQL> update wf_local_user_Roles
set user_end_date=null,
role_end_date=null,
effective_end_Date=to_date('31-12-9999','dd-mm-yyyy')
where user_name=upper('&username');
Enter value for username: SYSADMIN
old 5: where user_name=upper('&username')
new 5: where user_name=upper('SYSADMIN')
222 rows updated.
SQL> commit;
Commit complete.
Step 7:
SQL> select TO_CHAR(effective_end_date, 'dd-mon-yyyy') from wf_USER_ROLE_ASSIGNMENTS where user_name = 'SYSADMIN';
TO_CHAR(EFFECTIVE
-------------------------------
31-dec-9999
31-dec-9999
31-dec-9999
31-dec-9999
243 rows selected.
SQL> select effective_end_date from wf_local_user_roles where user_name = 'SYSADMIN';
EFFECTIVE_END_D
----------------------------
30-NOV-15
30-NOV-15
30-NOV-15
222 rows selected.
SQL> commit;
Commit complete.
Now, we are able to access all the SYSADMIN the responsibility.
No comments:
Post a Comment