Is there a way to produce a list of current IFS Users as well as disabled accounts showing when the accounts were created and disabled?
This is a SOX evidence requirement for our auditors.
FND_USER doesn't hold any such dates; it just lists whether account is active or inactive.
We use Single Sign-on, so no Oracle database accounts will be associated.
Any ideas or suggestions
Best answer by Kasun Balasooriya
Hi,
You can use the oracle view dba_users (application owner has grants to this view by default). Regardless of the authentication method, any IFS applications user will have an associated oracle account. The user will be able to use the IFS applications only if the oracle account status is OPEN.
You can use the following sample query :
SELECT t.username,
t.created,
t.lock_date,
t.account_status
FROM dba_users t
WHERE t.username IN (SELECT q.identity
FROM fnd_user q
WHERE q.active = 'FALSE')
+20Hi,
You can use the oracle view dba_users (application owner has grants to this view by default). Regardless of the authentication method, any IFS applications user will have an associated oracle account. The user will be able to use the IFS applications only if the oracle account status is OPEN.
You can use the following sample query :
SELECT t.username,
t.created,
t.lock_date,
t.account_status
FROM dba_users t
WHERE t.username IN (SELECT q.identity
FROM fnd_user q
WHERE q.active = 'FALSE')
Hi,
Thanks for your response.
However this is not 100% what is required.
We are looking for a list of ALL users (regardless of status) and against each user looking for the date they were created in IFS and if inactive, the date they were made inactive.
Regards
Shaun
+19You can enable history logging against FndUser.
Go to “History Log Configuration”
Right-mouse on the tree and select “Show Foundation1 Component”.
IFS Base Functionality → FndUser → FND_USER_TAB
I recommend logging INSERT/UPDATE/DELETE on at least ACTIVE and DESCRIPTION.
This can’t regenerate history, but it will help you going forward, at least.
+1Hi,
try to get the first lines from KasunBalasooriya and use an select to get the status
SELECT t.username,
t.created,
t.lock_date,
t.account_status,
(SELECT active FROM fnd_user where identity = t.username ) Status
FROM dba_users t
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.