Active and Inactive User List | IFS Community
Solved

Active and Inactive User List

  • 1 November 2019
  • 4 replies
  • 1582 views

Userlevel 4
Badge +9

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

icon

Best answer by Kasun Balasooriya 1 November 2019, 12:32

View original

4 replies

Userlevel 7
Badge +15

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')

 

Userlevel 4
Badge +9

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

Userlevel 7
Badge +16

You 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.

Badge +1

Hi,

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

 

Reply