Implementing row security by company

[jimmymac]

Greetings,

We currently use role based security where we have multiple roles set up for AP, GL, Fixed Assets, and Address Book. Roles are defined to allow certain menu options, and users of course are defined to have certain roles. Through this method, our users get access to what they need to do.

Now the fun part. We current have a dozen or so operating companies (company records in the F0010) but have no security around company.

We have acquired another company that we will be converted to our system, and there will be another several companies (say 4) being processed by our E1 9.0 system. The talk is that we may be asked to implement security to allow some users only access to the current 12 companies, and others only access to the 4 new companies.

It would see that the solution then would be to implement row security by company. For example, our current role GASPV we would modify to not allow users with that roles to View, Add, Change, Delete data for the new companies, but only access the existing 12 companyies. Then we would create a new role GASPVNEW identical to GASPV except that it would have row security defined to only allow acces to the new 4 companies and not the existing 12.

What I am seeking is any comments or experiences in implementing security in this way. Again out ultimate requirement is to be able to secure some companies from some users, and others from others. Also, if any one has other suggestoins on how to secure by company, we are all ears.

Thanks to all. Jim.

 

[cncjunior1]

The biggest impact may be performance. If you see or hear of performance impacts after putting row security in then you will want to run some database tools to see if there are any new indexes you will want to create. You'll probably have to create indexes on heavily used tables with the CO field in them. What row security will really do in the background is just add a WHERE CO BETWEEN in the SQLs it generates with the values you specify they can see/update.

 

[jstooge]

I think there are two things you'll need to consider: the increased amount of administration (someone has to add, change, remove these things) and performance. I've been to companies who have more business unit row security than F0911 records, but you would really have to have a lot to incredibly ding performance. It just changes the SQL the middleware sends so it's doing a more complicated query.

I think most of us in the consulting world would always say don't do it if you can get away with it, but the truth, most larger companies have no choice.

 

[gregglarkin]

Jim

You can do row level security to do that segregation. I've seen that implimented in a bunch of places. I suggest you bring in a consultant to help you design that. Otherwise it can be a big headache down the road.

Or you can go a different approach - create a new pathcode. You can keep your current users in PD900. Create a new pathcode NEWPD900. Keep the code the same across the pathcode, just seperate the business data. Grant users access to which ever pathcode makes sense for them to be in.

If you need to have some reports that role up the results for all of the companies, use a third party tool like Cognos to do the rollup. From an auditing perspective, that method is very clean. It also makes reporting easy because the business data isn't getting mashed together.

Food for thought. That's a higher level business decision, not one for us techie guys to decide. But this is a good opportunity to put that option on the table. It is pretty difficult to seperate a company out of JDE once their business data is combined. Not impossible, but it's not pretty. The seperate pathcode concept is easier to impliment.

There are pros and cons for combining business data. There are equally as many pros and cons for keeping them seperate. My recommendation, find a good business partner with both functional and technical consultants. Set up some workshops with your management team and the consultants and map out your plan. Spending some money up front on a good strategic plan will reap many benefits down the road.

- Gregg

 

[peterbruce]

Gregg,

Wouldn't a new environment using the same pathcode and separate business data (control data may or may not need to be separate) be less work?

Now to be really confusing (and create a lot of unnecessary work) you could create a "total" environment with the business data full of "virtual tables" - database views combining the tables of the same name from the other environments.

That would be a nightmare to set up and maintain!

 

[Jaise James]

Hi,
Your requirement sounds like BU Security requirement. You can certainly do this using BU security. Do you have range of company that might need to be seen by everyone E.g. Parent Company or in case you are hub method of accounting, they may need access to Hub Company to post any batches etc.
You might need an individual or individuals who are very knowledgeable. This is a very involved project. You need someone who understands your business, JD Edwards from application/security perceptive, how your company's numbers (Company number/BU and anything else like Item number) are segregated under different ranges of Company's BU and finally the performance impact. Depending on your company, there might be further requirements

I have implemented this in my company and it took a while to get all the information in place, but it works extremely well. In fact if implemented correctly, you should see performance improvement as now it looks for limited record set. As someone has suggested, get yourself a good consultant, if you don't have a good in-house resource already

 

[dylan]

I have seen this implemented at several companies. The key is to have set up users by ranges of companies So:

UserA (or RoleA) has access to 00002 through 00041
UserB (or RoleB) has access to 00042 through 00050

If there are gaps in the company row security, the SQL statements tend to get big, so instead of:

SELECT * FROM PRODDTA.F0911 WHERE GLCO BETWEEN ('00001' AND '00041') OR GLCO BETWEEN ('00060' AND '00064')

you end up with something like this:

SELECT * FROM PRODDTA.F0911 WHERE GLCO = '00001' OR GLCO = '00004' OR GLCO = '00064' OR GLCO = '00064'

You may also want to create table indexes that include the CO field for performance reasons. You may also run into issues related to company security in some applications. Duplicate invoice check in P0411 is one such example. UserA, who has access to company 00001 creates a voucher for supplier 123 with invoice# ABC. UserB, who has access to company 00002 (and no access to 00001) can now create a voucher for the same supplier with the same invoice# now. JDE checks F0411 for duplicate invoices, but when the query runs for UserB, it will not find the voucher created by UserA due to company security.
Hope this helps.

 

[Luke Phillips]

I have put a lot of time in recently to reporting on row security - both for BUs and companies, and in this process I am finding that performance is largely not an issue.

I would suggest the main issue is maintenance. To get around this I would create one role per company and assign that to the users. Where you have a user who needs multiple companies, I would manually merge my roles into combined roles - i.e. using the company specific roles as templates. I have created just such an (automated) using sofwtare but if you are just talking about company security you can probably achieve the same results manually using Excel. My product fixes BU ranges too which you cant do so esily manually but its easier if you are just talking about company security.