Deactivate Application Users who were inactive for more than 60 days

Deactivate
Application Users who were inactive for more than 60 days
a.       Create a backup table with the list of
users that are inactive for more than 60 days,
create
table INACTIVE_USERS_$ORACLE_SID as
select
USER_NAME from fnd_user
where
END_DATE is null
and
LAST_LOGON_DATE < (sysdate – 60)
and
USER_NAME not in (‘ANONYMOUS’,’ASGADM’,’ASGUEST’,’AUTOINSTALL’,’BATCHUSER’,’C10000300′,’CONCURRENT
MANAGER’,’DISCOADMIN’,’FEEDER SYSTEM’,’GUEST’,’IBE_ADMIN’,’IBEGUEST’,’IBE_GUEST’,’IEXADMIN’,’INDUSTRY
DATA’,’INITIALSETUP’,’IRC_EMP_GUEST’,’IRC_EXT_GUEST’,’LATESXXTUSER1′,’MOBILEADM’,’OAMADMIN’,’OP_CUST_CARE_ADMIN’,’OP_SYSADMIN’,’ORACLE12.0.0′,’ORACLE12.1.0′,’ORACLE12.2.0′,’ORACLE12.3.0′,’ORACLE12.4.0′,’ORACLE12.5.0′,’ORACLE12.6.0′,’ORACLE12.7.0′,’ORACLE12.8.0′,’ORACLE12.9.0′,’SYSADMIN’,’UKDISCOUSER’,’WIZARD’,’XML_USER’,’XXNAO’)
order
by 1 desc;
Note : – Replace $ORACLE_SID with the db name while
creating the backup table
I have included few of the seeded users who will not be
deactivated.
b.      Run
the following PL/SQL Block to deactivate (End Date the user and its respective
responsibilities) the application user,
declare
cursor
userapps is
select
USER_NAME from fnd_user
where
END_DATE is null
and
LAST_LOGON_DATE < (sysdate – 60)
and
USER_NAME not in (‘ANONYMOUS’,’ASGADM’,’ASGUEST’,’AUTOINSTALL’,’BATCHUSER’,’C10000300′,’CONCURRENTMANAGER’,’DISCOADMIN’,’FEEDER
SYSTEM’,’GUEST’,’IBE_ADMIN’,’IBEGUEST’,’IBE_GUEST’,’IEXADMIN’,’INDUSTRY
DATA’,’INITIAL SETUP’,’IRC_EMP_GUEST’,’IRC_EXT_GUEST’,’LATESXXTUSER1′,’MOBILEADM’,
‘OAMADMIN’,’OP_CUST_CARE_ADMIN’,’OP_SYSADMIN’,’ORACLE12.0.0′,’ORACLE12.1.0′,’ORACLE12.2.0′,’ORACLE12.3.0′,’ORACLE12.4.0′,’ORACLE12.5.0′,’ORACLE12.6.0′,
‘ORACLE12.7.0′,’ORACLE12.8.0′,’ORACLE12.9.0′,’SYSADMIN’,’UKDISCOUSER’,’WIZARD’,’XML_USER’,’XXNAO’)
order
by 1 desc;
begin
for
x in userapps loop
–End
date the following users
–dbms_output.put_line(x.USER_NAME);
fnd_user_pkg.updateuser(x_user_name=>x.USER_NAME,x_owner=>’CUST’,x_end_date=>SYSDATE);
commit;
end
loop;
end;
c.       Run
the following PL/SQL Block to reactivate (Remove end date) the application users,
declare
cursor
userapps is
select
user_name from fnd_user where user_name in (select distinct user_name from
INACTIVE_USERS_$ORACLE_SID);
—-(Mention the backup table name from step a)
begin
for
x in userapps loop
fnd_user_pkg.enableuser(x.user_name);
commit;
end
loop;
end;
  • January 17, 2017 | 14 views
  • Comments