DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, January 17, 2017

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;

No comments: