How can I prevent the users from using a company?

i96nds · #1 5th March 2002, 22:25

Let me tell you the whole story. We have a large Baan instalation, with more than 10 companies. There are people from several countries working on this server. We have 4 package combination for the live companies, so there are package combinations that have several companies on.

We need to do some cleaning periodically in the database, because the tablespace get fragmented after a while. The size of the companies is growing quite fast (with so many "hungry" users...). So, we need to dump the company, fill the new ora_storage parameters (a biger INIT), coalesce the tablespaces, upload the data.

Of course, nobody should be working in that company while we do this. But it is not easy to keep informed hundreds of people from several countries.... It happened that people were working...

The major problem is that we cannot afford that all users should be logged out! While we reorganize a company, ALL other companies should be available.

How can I find out which users are working in a certain company at a certain moment? Or, how can I find out in which company a user is at a certain moment?

I can not use the default company of the user at a certain moment because the user may have switched the company.

Let's have an example. User AAA is authorised to use the companies:

100, 200, 300.

We want to reorganize company 100, which is the default company for user AAA.

Suppose we have few super users, these can be informed not to use the company 100. The problems are with normal users.

One ideea we had was to search in the table with company authorisations (for normal users, of course) and to find all users that are authorised tu use that company, say 100. Then we create a SQL script that does a

REVOKE r_baan to AAA , and to all users in the same category. Another script will do a GRANT r_baan to AAA, after the activity of reorganizing the company is OK.

Problem: I need that user AAA should be able to use companies 200 and 300 while I reorganize 100.... This cannot be done with the previos ideea which "locks" the user at Oracle level.

Another idea was to copy the table "Authorizations by company" to another table, detele the records containing company 100, do a convert to runtime for all users. At the end, load back the table, as it was, and again Convert to DD for all users.

Disadvantage:
If user AAA has as default company company 100, and I delete his authorization for company 100, is is not quite good, since a user should be allowed to use his default company...
It is also inacceptable to change the default company for 400 users and then to set it back...
Also... I hope nobody runs the Convert to RDD in this hours of maintanance....

Any suggestion will be highly appreciated. Please feel free to correct my mistakes, or comment upon what I have posted here.

Regards,

Francesco · #2 5th March 2002, 23:33

Use the bshcmd tool.

I am not sure what different versions are out there, but bshcmd6.2 will produce the following output:

bshcmd6.2 -p -s -w 10 [bshell pid]

PID PPID PGRP SESSION TICKS FLAGS S CMP MEM
1 0 1 ottstpoledaem 69 00000060 B 100 206744

and as you can see it has a column for the company.

norwim · #3 6th March 2002, 10:53

Hi there,

what you could prepare would be this:
$BSE/lib/user contains the user-profiles, which are read at bshell start (here you find the PC and the default company for the user)
Save the complete directory, change the default companies for the users of company 100 to 200, convert to runtime. Save this complete user directory as user_no100. Continue this process, and you will have different user directories which you can activate at will (simply swap them).
This solves the problem that users still log on to the "forbidden" company when the start a bshell.
Lock the company you want to change with oracle means so that they cannot switch to the company that you reorg.
About the "hope that nobody starts convert runtime":
if you insert a record into ttadv501 (company 000) for a package combination, this should be locked for "convert to runtime DD" -
I not sure here, you have to experiment with this.

good luck

Norbert

i96nds · #4 6th March 2002, 11:25

Quote:

Lock the company you want to change with oracle means so that they cannot switch to the company that you reorg

This is really a problem for me, Norbert, I do not know how can I do this. Any suggestion? The method with REVOKE r_baan to AAA has the disadvantages I mentioned in the first note.

About converting to runtime, I meant "Convert to RDD" but for users. Table ttadv501 is only used for the tables which were changed and must be converted to RDD. Anyway, this problem can be solved, I think, if I reset the file permissions to 440 in $BSE/lib/user. In this way it should be OK. After I reorganize the company, I should set back file permissions to bsp:bsp and 660. The reorganizing should take less than few days for the biggest companies (500 GB in Oracle). As root, I should be able to convert some users to RDD, if necessary.

Francesco had a good idea, I will try to write a script that finds the PID of all active bshells, then runs bshcmd6.1 for each of them, prints out the company. In this way I will know if somebody is using a certain company at a certain moment.

Maybe the solution to my problem is obvious but I cannot see it.

victor_cleto · #5 6th March 2002, 12:14

I would use a script that would do the following to block users from company xxx:

Create $BSE/lib/user_xxx directory
Search in $BSE/lib/user for the ufiles of users using company xxx, and for each one of them do:
- copy itto $BSE/lib/user_xxx (backup)
- do a chmod 600 on the file (avoid them to change the ufile by converting themselfs to runtime
- revoke access to company xxx by removing the xxx entry from 'compnr:' (the next company would be the active one)
Note: if the user has only access to one company, then just remove the ufile (do not allow the user to log in).
Check with bshcmd6.x if the user is currently logged in, and if yes, send him a message to ask him to loggof/login again (to prevent him to change company and to update his ufile)

Before start any activity, do again a check, and if no message was sent to the users, that means that nobody is working within that company. After the work, you just copy back the files from $BSE/lib/user_xxx to $BSE/lib/user.

Pierre · #6 6th March 2002, 16:08

As an extra security what I would do is;
Go in the session Assign tables to database and assign all tables of companies 100 to an inexisting database (alternatively you can manually edit the file tabledef6.1).
Whenever BaaN will try to read the data of this company it will report an error so you can be sure that nobody will ever access the tables of company 100.
As for the ora_storage, if you are using oracle 7 than you could put the pctincrease to 100 in order to limit fragmentation. If you are using oracle 8i you could use the new functionality to have system management of the extents.

i96nds · #7 6th March 2002, 16:32

The idea that Pierre had, about tabledef6.1, is really interesting, I did not think about it. I will keep it in mind. It should work, but only for the new connections, since the old ones have the tabledef in memory. This leads to another topic:

How can I find if the currently logged users are using company 100? I did an attempt with bshcmd6.1, as Francesco suggested, but unfortunately the output is "appended" and not "overwritten" !!! (chech the file $BSE/tmp/bshell.PID after you issue two or three bshcmd6.1 -p commands...)

The problem with Pierre's idea is that I, as administrator, need to work in this company !!! I need to reorganize it, etc. I think I should use the "-d driver" option for bdbpre6.1 and bdbpost6.1, so that these utilities will not take the information from tabledef6.1.

What do you think?

Alick Wilson · #8 16th April 2002, 19:33

I am working on the same problem and came up with a similar method to Victor. This is based on unix and awk! In essence the problems encountered are:

One problem I had when looking to the non-default company is that the companies are concatenated into one. So if a user has access to 111 222 and 333 becomes "compauth:111222333" and the command 'grep compauth $USER | grep 112' will find a hit in error (where $USER is the current user file being processed). I have therefore had to use awk to split the field and then check if valid. So as not to create backups of backed up files, this script will not run if backed up files currently exist.

As to the $BSE_TEMP file being appended, we delete them prior to the script being ran. Alternatively save them elsewhere.

Problems still to conquer:

The problem with the bshcmd6.? command is if a user is logged in but not doing any processing, no logging takes place. In our situation (unlike i96nds) this is not an issue as we manually check all users and ask those in the company to log out. However, we would like to automate this checking and also post a message.

As to the creating run time, we have not tested the effect of this. We will have to enforce a process to ensure the various admin people do not use this session.