Monday, 21 March 2016

SYSADMIN Responsibility is not showing after instance refresh

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.

No comments:

Post a Comment