Demystifying Company and Business Unit Access
Brian Connor, Manager Security and Fraud Protection, ITIL
Business unit or company security is a frequently misunderstood component of the overall JDE security model. Many companies would like to implement data level access controls but are unsure of how to do so effectively. To understand how to do this, you need to understand what Business Unit/Company security is. Business Unit/Company security is also known as row security and is the method that is used to limit users’ access to ranges of data in the system. This setup is stored in the F00950 table as type 4 security. Row security is applied to data item alias’ in the system. An Alias is a specific component used to identify a data type. Aliases include things like Business Unit or Branch Plant (MCU), Company, (CO), or order company (KCOO). Many aliases are setup to allow row security to be applied against them, but not all of them are. To see if a specific alias has row security enabled, you can look in the F9210 table. Put in the Alias you want to look up in the FRDTAI column and then query the results. If the alias has been defined for row security, you will see the FRSCFG value = Y. If FRSCFG is blank, then the alias is not setup for row security. IN 9.2, there are 504 alias that have row security, but many more that do not.
To set an alias to work with row security, you will need to go into the DataDictionary application, and check the box to enable it.
Modes of Row Security
There are two ways row security can work in your JDE system, Exclusive or Inclusive. The default mode is Exclusive. In this mode, you define the data ranges you want to exclude from the role. Anything that is not excluded is visible to the role. In the Inclusive mode, you will define the data ranges that you want the role to have access to. However, in this mode, once you have allowed access to a range of data, all other ranges are hidden by default. There are significant differences between the two modes of row security, resulting in much better performance with Inclusive row security.
Let’s say you have a role where you want to allow access to Company 75-100, and 150-200. Using Exclusive row security, this would be defined in the system like this:
When a user with this role queries the Company master table, the SQL statement looks like this:
- Select * from PRODDTA.F0010 WHERE CCCO NOT BETWEEN ‘00000’ AND ‘00074’ AND CCCO NOT BETWEEN ‘00101’ AND ‘00149’ AND CCCO NOT BETWEEN ‘00201’ AND ‘99999’
Using Inclusive Row security, access to the same values will look like this:
When the user performs the same query against the Company master table, the SQL now looks like this:
- Select * from PRODDTA.F0010 WHERE CCCO BETWEEN ‘00075’ and ‘00100 OR CCCO BETWEEN ‘00150’ and ‘00200’
As you can see, the Inclusive mode of row security produces a much simpler SQL statement that will run faster. Multiply this by multiple users with multiple data ranges and it’s pretty easy to see why the recommended method of Row Security is Inclusive.
In addition to choosing whether to use the Inclusive or Exclusive mode of security, you need to determine whether to use named tables for each alias, or use *ALL tables. Each company is different but be aware that any given alias will exist in hundreds of tables. For example, Business Unit (MCU) exists in 1,246 tables and Company (CO) exists in 749 tables. Although you are probably not using all of the tables where the alias exists, you are still likely using dozens of the tables. While there is no one size fits all answer to what you should do, in general using *ALL tables is a simpler and more secure way of restricting the data values everywhere they are found. If you do use *ALL tables, be aware of some additional setup that is needed when applied to Alias MCU. Using *ALL for the table for MCU will cause a problem in the F41001 & F41002. To overcome the known issues, you will need to add the range values of ALL to ALL and *BLANKS to *BLANKS.
One of the common issues many companies face when implementing Row Security is how to handle data restrictions when the company utilizes intercompany transactions. A particular user may be restricted to Company 100 but will need to enter a transaction that affects Company 200. If your Row security is not configured properly, this can result in the transaction not completing and the resulting data integrity problems. To work around this limitation of Row Security, you can implement JDE’s Exclusive Application security. This security type allows you to define a specific program or UBE that will then have the row security limitations removed. In our example above, when the user completes the transaction, a UBE is submitted that will then apply the required transaction to the company they do not have access to. With Exclusive Application security, the Row Security is removed for the processing of the specific UBE, thereby allowing the transaction to complete. The transaction initiator still cannot view the result in the other company but rest assured that the transaction completed and there will not be any data integrity issues.
Before implementing Row Security, you need to have a well thought out and defined plan of action. Will you be using Inclusive security with *ALL tables and exclusive application security, or will you be using named tables or some other combination. What data are you wanting to restrict,