Friday, January 8, 2016

Disable responsibilities to restrict EBS site access

During business events such as month-end close, only a small group of users are allowed to access EBS website. This can not be accomplished by placing it in maintenance mode. But we can disable most responsibilities to restrict and manage users' access without changing users' accounts.

-- disable responsibilities. Assume GL_SuperUser, AR_SuperUser are two special responsibilities
SQL> update FND_RESPONSIBILITY   
set end_date = sysdate   
where application_id = 30003   
and RESPONSIBILITY_ID in (select responsibility_id   
from FND_RESPONSIBILITY_TL   
where application_id = 30003   
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))   
;   
Commit;

-- After maintenance event, run sql to enable the responsibilities (without bouncing Apps services)
SQL> update FND_RESPONSIBILITY
set end_date = null
where application_id = 30003   
and RESPONSIBILITY_ID in (select responsibility_id   
from FND_RESPONSIBILITY_TL   
where application_id = 30003   
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))   
;   
Commit;

When a responsibility was created, it would be associated with an Application. Below query will find how many responsibilities were created for each application.
SQ> select a.application_id, b.application_short_name, count(*)
from FND_RESPONSIBILITY a , fnd_application b
where a.application_id = b.application_id
group by a.application_id, application_short_name
order by a.application_id;

application_id application_short_name count(*)
---------------  ----------------------------  ---------
0            FND               24
1            SYSADMIN    3
99          OAM              1
101        SQLGL           27
140        OFA               7
... ... ...
9001      MTH               2
9004      INL                 1
30003    PUGL             20     <== custom stuff
30004    PUAR             3
30005    PUFND           4

Additionally, below queries can answer questions:

1) the list of responsibilities for a user
2) the list of responsibilities for an application

SQL> SELECT user_name, frt.RESPONSIBILITY_NAME, furg.end_date, fr.application_id
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND fu.user_name = 'EBSuserName'
-- AND fr.application_id = 30003
ORDER BY 1,2;

How many users use an application with each responsibility?
SQL> SELECT fr.application_id, frt.RESPONSIBILITY_NAME, count(*)
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
group by fr.application_id, frt.RESPONSIBILITY_NAME
ORDER BY 1,2;

No comments: