Log and Lock out Users

A couple questions,

• How many users would you like to lock out of the system?
• Are you identifying the users based no username, role, etc?

You have a couple different options available. A simple PL/SQL script can be created if you have a list of the usernames you want locked. The script would perform an “ALTER USER <account> ACCOUNT LOCK;” command. When you want to unlock the accounts you have the script perform “ALTER USER <account> ACCOUNT UNLOCK;”.

If you have thousands of users you could take away the “CREATE SESSION” from their Oracle roles. This is more complex because you have to make sure you leave the ability to create a session in place for the users you want to have access.

You could just simply select the users in the 'Active Users' screen and set them to inactive. 

HI,

It would be a couple of hundred users - except for 2 off Sys Admin and 2 off Finance users.

This would be identified by username.

Would any of the options ‘kill’ any open sessions?

Try locking your colleagues account and see what happens. :) 

When applying release, ideally you want no users in the system at all so not sure why you want to allow specific users in the system.

Our process:

• Send an email out with at last 1 weeks notice when we are going to take down the LIVE system to apply a release (or you could send out a stream message - code below)

declare 
 attr_ VARCHAR2(2000);
 info_ VARCHAR2(2000);
 objid_ VARCHAR2(20);
 objversion_ VARCHAR2(100);

 CURSOR openOracleUsers IS
 select username from oracle_account where profile <> 'IFS_INTERNAL' and account_status = 'OPEN';

Begin

 FOR rec_ IN openOracleUsers LOOP 
 Client_SYS.Clear_Attr(attr_); 
 Client_SYS.Add_To_Attr('TO_USER',rec_.username,attr_);
 Client_SYS.Add_To_Attr('MESSAGE','The LIVE system will be taken down for releases between 23:00 20/11/2019 and 04:00 21/11/2019 - PLEASE ensure you are not logged in',attr_);
 Client_SYS.Add_To_Attr('STREAM_TYPE','Event',attr_);
 Client_SYS.Add_To_Attr('VISIBLE',Fnd_Boolean_API.Decode('TRUE'),attr_);
 Client_SYS.Add_To_Attr('READ',Fnd_Boolean_API.Decode('FALSE'),attr_);
 ifsapp.FND_STREAM_API.NEW__(info_,objid_,objversion_,attr_,'DO');
 commit;
 END LOOP;

end;

• Check users are out of the system by looking at the sessions and the FND_CLIENT_LOGON table.
• If they are not out, give them the benefit of the doubt and give them a call and tell them to get out the system. - If they do not leave, then proceed to kill their session (but try and read the process to see if they are doing anything important)
• Whilst the release is applying then the users will not be able to connect back to the system anyway.
• Send another email round when the release is applied to inform the users the system is back up and running.

P.s. if you wanted to lock the users out you could try this

declare

 lock_ varchar2(2000); 
 unlock_ varchar2(2000);

 CURSOR lockAccount IS
 select username from oracle_account where profile <> 'IFS_INTERNAL' and account_status = 'OPEN' and username not in ('PUT','NAMES','HERE','YOU','DO','NOT','WANT','TO','LOCK');

 CURSOR unlockAccount IS
 select username from oracle_account where trunc(lock_date) = to_date('20/11/2019','DD/MM/YYYY'); --Change date to the date the accounts were locked (KEEP IN MIND - ANY LEGITIMATE LOCKS ON THIS DATE WILL ALSO BE REVERSED!)

begin

 FOR rec_ IN lockAccount LOOP
 lock_ := 'ALTER USER "' || rec_.username || '" ACCOUNT LOCK';
 execute immediate lock_;
 END LOOP;


 /*FOR rec_ IN unlockAccount LOOP
 unlock_:= 'ALTER USER "' || rec_.username || '" ACCOUNT UNLOCK';
 execute immediate unlock_;
 END LOOP;*/

end;

There are many ways to perform this I think the easiest would be to do the following,

• Go to the Oracle Users window
• Query for Account status as OPEN
• Select all the users
• RMB → Select Lock Account