EBS DEV environment mass user disable/enable

Usually environment clones are used for any general purposes and sensitive data there are scrambled and as long as users are not complaining or trying to work with test instances instead of production there are no worries about access restriction. But time to time there is need for environment clone where data is not scrambled and then You have to think twice before give environment to end users. Best approach of course is restrict all and allow only  what is needed, so in this case we’re interested to disable log-in access to all users except ones who need that.

Easiest way probably is just update table fnd_user and set end_date  but this not good approach because I’m quite sure that only some knows the kitchen behind authorization and dependencies, so there is a possibility to brake something. In this case Oracle given API should be used.

--disable user
begin
   fnd_user_pkg.disableuser('USER');
end;

--enable user for 30 days
begin
   fnd_user_pkg.enableuser('USER', sysdate, sysdate+30);
end;

And now simple pl/sql block to do this as mass update

DECLARE
  -- get user list which should be disabled
  cursor c1 is
  select user_name
     from fnd_user
    where (end_date is null or end_date > sysdate) -- access all users that has no end-date or end-date is future
      and user_name not in ('SYSADMIN', 'USER1', 'USER2'); -- list of users which should not be disabledx`
BEGIN
  for c in c1 loop
    -- disable user
    fnd_user_pkg.disableuser(c.user_name);
  end loop;
  --commit changes
  commit;
END;

Both enable and disable procedures calls  fnd_user_pkg.UpdateUser which also can be used directly (described How to End-Date/Delete an Applications User [ID 1064798.1]) to do user update not just end-date but other options as well like password (unencrypted), password end date, etc. Only GUEST user cannot be updated with this package cause it’s internal user and it’s required for authorization and other stuff.

If there is need find out later users that are disabled by this pl/sql You can set end_date a very specific like 1111.11.11 11:11:11 to query later. To enable user back just user fnd_user_pkg.enableuser but be careful that with this cause after enableuser end_date should be specified and this cannot be set null (usually new user end_date value is null if not specified other) via procedures, so some information can be lost like original start_date and end_date.

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: