Temporary disable APPS users

Idea for this topic came from my original post on Oracle APPS forum where I wanted to see from their gurus if there is something behind the bonnet about this issue.

Main problem is how to temporary disable unnecessary users a large number of users and still leave fully enable application for chosen one. This is real situation in a case of big roll outs or migration events, when some real APPS users need to have 100% of application usage to continue their normal work in APPS (as testing user for an example). And the best of all you do not have to restart any part of APPS...all is going online!

Our situation was even worse, because we have APPS based on several org_id's (represented here with set_of_books_id and we had to take care of those users as well. So you may find this approach pretty universal. This example was fully tested on Oracle EBS 11.5.10.2 for more then dozen times with 100% success.

Requirements

In my praxis I have very often need to temporary save some data. For that I use two kinds of pure Oracle tables defined as:

create table xxhy_table_ch (c1 varchar2(4000) );
create table xxhy_table_num (n1 number); 

As you can see this tables has just one column (VARCHAR2 and NUMBER). Create them in "XX" schema and grant all to APPS user, as you do in usual cases.

Before any work I need to cleanup them:

truncate table xxhy_table_ch drop storage;
truncate table xxhy_table_num drop storage; 

For some less experienced DBA let me express that all scripts must be run as APPS user for what you need certain level of security.

Regardless I wrote here these two steps as required, both of them can be avoid for core functioning but I find it suitable and shown them as mine approach.

Initialization

In this example I'll use mine list2table function, which can be found on of my previous post Treat lists as table. This function will allow me to save initial list of users in plain Oracle table, leaving that all other scripts has static values. Static scripts could be executed by any Oracle related person...not APPS DBA only, what reduce single point of failure in such a situations...

Next initialization step is to insert user names, which should stay enabled, in xxhy_table_ch table. This is the only part which must be changed because not all users are the same for every event so manual editing is needed.

insert into xxhy_table_ch
  SELECT * FROM TABLE(list2table('SCOTTT,JOHNYB,TOMP2,ALENG,DJINAZ,CARLM,ROBERTD')); 

As you can see, parameter for list2table function are user names that should stay enabled so change them as you need.

For the end of initialization it is very important to save(remember) the number of enabled users before we have modify their status. For saving this value I use my second xxhy_table_num table:

INSERT INTO xxhy_table_num
  SELECT count(*) 
     from HR.PER_ALL_ASSIGNMENTS_F P, FND_USER f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE);
COMMIT;

As you can see, in my case I had 3 different org_id's (3 set_of_books_id).

EBS system users

Users whose user_id is bellow 1110 are system ones and they should remain as they were. Really you do not want to mess with them!

SQL> select user_id, user_name 
  2  from fnd_user
  3  where user_id <= 1110;

   USER_ID USER_NAME
---------- ------------------------------
        -1  ANONYMOUS
         0  SYSADMIN
         1  AUTOINSTALL
         2  INITIAL SETUP
         3  FEEDER SYSTEM
         4  CONCURRENT MANAGER
         5  APPSMGR
         6  GUEST
         7  WIZARD
      1003  IEXADMIN
      1004  IBE_GUEST
      1005  OP_SYSADMIN
      1006  OP_CUST_CARE_ADMIN
      1007  ASGUEST
      1008  IBE_ADMIN
      1009  IBEGUEST
      1010  ASGADM
      1011  MOBILEADM
      1012  IRC_EXT_GUEST
      1013  IRC_EMP_GUEST
      1030  PORTAL30
      1031  PORTAL30_SSO
      1050  XML_USER
      1070  AME_INVALID_APPROVER
      1090  MOBADM
      1091  MOBDEV

26 rows selected.

SQL> 

For those who want to be more safe, manual writing number is also a good way...
;-)

Disable users

This is script which will disable all users except mentioned one:

update fnd_user
set end_date = SYSDATE-1
where user_id in
  (select user_id
     from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE)
  )
;
COMMIT;

It is also a good idea to save this number as well but I was not doing this. How ever slightly modify script (add returning clause which you have to save in xxhy_table_num table).

Now chosen users can work(test) with no worry that some other uninvited user may start unexpected concurrent or action... When all is done reverse process can be started....

Enable users

This is reverse script as one in the beginning.

update FND_USER
set end_date = null
where user_id in
  (select user_id
     from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE)
);
COMMIT;

After this check number of enabled user which was stored in xxhy_table_num first step. For that repeat SELECT count(*) part from the beginning, which is not covered here because it's simplicity.

"Synchronize WF LOCAL tables" concurrent

For the end, last required step is to run Synchronize WF LOCAL tables concurrent program as sysadmin with System Administrator responsibility, which will synchronize WF tables according some recent changes we have made before.

While you are disabling users you have some running session and want to terminate them, you can use scripts explained in my topic "Kill session on EBS environment".

Kill active APPS sessions

If you have need to kill all running APPS session on my blog Kill session on EBS environment you may find fully qualified information for that.

Cheers!