This is a particular way of applying row-level security using business views for Crystal Reports. This security is applied directly at the prompt level. A restricted user would be provided with selections that he has access to, and hence would be able to run the report for data that he is allowed to only. In this scenario we would talk about user restricted to particular plants. This document is valid for security configuration for Crystal Reports in the BO 3x AND BO 4+ world.
There are a couple of components that work together for the proper execution and application of row level security in this manner. They are:
a. Restricted group in BOE
b. Row level security table
c. Security views for each LOV
d. Command table in Business View data foundation
e. Filter in Business View data foundation
f. Rights application on the filters
g. LOV based on the command table
h. Flow of events in LOV restriction
This BOE group is required to apply the restricted LOV to the people who would be restricted. All users who need a restricted level should be part of this group; users who are not part of this group will not be restricted. There should be an entry in the row level security table for the restriction to work properly.
Important: If an entry is not present in the security table and the user is in the restricted group, the user will get an empty list in the prompt.
Where located
|
BOE CMS
|
How created
|
Using CMS, created by BOE administrator
|
Specification
|
None
|
Contents
|
User ids as members, who need restricted access
|
This table is the heart of the configuration. The actual access is defined in this table. The table contains information about the user and his area (departments etc.) of access, and the actual access (value). It contains the application/departments (FIN – Finance, ITS – Information Technology etc.) information, so that a single table can maintain for the whole enterprise environment. The restricted LOV is governed by the data available in the value column. A restricted user can only choose prompt values that are available in the value column in this table.
It has some other columns that are specifically used for administrative purposes.
Table Structure description:
Column
|
Description
|
Possible values
|
BI_AREA
|
Contains the application/department name, this is referenced in the filter defined at the business view level
|
Based on the enterprise FIN, ITS, PAP…
|
PREFERENCE
|
Area / LOV on which restriction is applied
|
Each application/department can have multiple areas of restriction. For instance Logistics user can be restricted to plant/warehouse or division etc…
|
VALUE
|
Actual area id or code by which the area could be referenced. For a plant preference the value would be plant id.
|
Plant id, division id…
|
NT_USERID
|
The network id of the user who needs to be restricted. This should be equivalent to the user id used in BOE.
|
User id
|
This table can be maintained by the BOE administrator and Application Support team.
Where located
|
Any central database accessible from other reporting databases, in our case we have created a table named BO_ROW_SECURITY
|
How created
|
Using SQL, created by DBA
|
Specification
|
History tracking can be made available using status fields in the table
|
Contents
|
Row level security definition
|
This view is created to increase the performance of the LOV. Let us take the example of plant LOV. The view creates a list of all the possible combinations of plants in the database (from a plant dimension table, in this case DIM_PLANT) and the entries available in the row level security table. This is required so that the same LOV (view) could be used for users who do not have any restriction as well. The list would have rows with NULL in nt_userid which is required to create the full list of plants from DIM_PLANT table for users who does not have any restrictions. The view also restricts the list to a particular preference, in this case to ‘plant’.
The query also takes care of hierarchical security. For instance if plants roll-up to divisions, and a user is restricted to a division (multiple plants) then this query will create the list at the plant level including all the plants in that division.
SQL text of view (SEC_PLANT):
select distinct pl.PLANT_ID as Plant_Id,
ltrim(rtrim(pl.DESCRIPTION)) as Plant_Desc,
ltrim(rtrim(pl.DESCRIPTION)) + case ltrim(rtrim(pl.DESCRIPTION)) when '' then '' else ' - ' end + ltrim(rtrim(pl.PLANT_ID)) as Plant_Id_Desc,
ltrim(rtrim(pl.PLANT_ID)) + case ltrim(rtrim(pl.DESCRIPTION)) when '' then '' else ' - ' + ltrim(rtrim(pl.DESCRIPTION)) end as Plant_Desc_Id,
se.bi_area, se.preference, se.nt_userid
from DIM_PLANT pl left outer join
(SELECT [BI_AREA],'plant' as [PREFERENCE],[VALUE] as div_value,pl.plant_id as [VALUE],[NT_USERID]
FROM [BO_ROW_SECURITY] bse, DIM_PLANT pl
where ltrim(rtrim(bse.value)) = ltrim(rtrim(pl.business_unit_id))
and bse.preference = 'division'
union all
SELECT [BI_AREA],[PREFERENCE],[VALUE] as div_value,[VALUE],[NT_USERID]
FROM [BO_ROW_SECURITY] bse
where bse.preference = 'plant'
) se
on se.value = pl.PLANT_ID and se.preference = 'plant'
Let’s assume the division and plants are defined in the system as follows:
Then the output of the view would look like (SEC_PLANT):
Let’s discuss a few important pointers on the above table:
· Lines 2 and 3 are automatically generated for joe’s access to all the plants under division CA001
· Lines 6 and 7 are automatically generated for tom’s access to all the plants under division US002
· Lines 8 to 13 are generated for all other users that have no restrictions to any plant or division
· Columns GL_Plant_Id_Desc and GL_Plant_Desc_Id are generated for different flavors of the LOV as per the group or report preference/requirement
· Columns GL_Plant_Desc, GL_Plant_Id_Desc and GL_Plant_Desc_Id are used for the LOV display. For any selection of these values, the plant id from column GL_Plant_Id is passed onto the report
Where located
|
Any database accessible globally
|
How created
|
Using SQL, created by DBA
|
Contents
|
Row level security definition combined with LOV content from master tables
|
Command table are required when it is not possible to get the required data set directly from database tables. The command table for security application is a straight forward select and the view SEC_PLANT could have been directly used, but a command table was created to keep room for future developments/changes that might affect the view. The output of command table, Plant is similar to a select * output of the sec_plant view.
SQL text of command table (BV GL_Plant):
SELECT [GL_Plant_Id]
,[GL_Plant_Desc]
,[GL_Plant_Id_Desc]
,[GL_Plant_Desc_Id]
,[bi_area]
,[preference]
,[nt_userid]
FROM [SEC_PLANT]
order by 2
Output of command table (BV GL_Plant) will be similar to the SEC_PLANT view:
Where located
|
Business View data foundation
|
How created
|
Using SQL, created by BV administrator
|
Specification
|
Can be accessed throughout
|
Contents
|
Row level security definition from security view
|
By this time we have the full data set that is required to apply a restriction on an LOV. The filter defined in business view actually applies the restriction on the data set. For a particular user this filter is either ON of OFF which is governed by his/her existence in the BOE Restricted Group. The filter uses environment variable, CurrentCEUserName to capture the user id from runtime and joins it with the dataset from the view to return rows that correspond to the particular user. The filter does not get applied for a user who is not restricted and hence sees the distinct values from the view; this distinct list matches the values in the master table.
As we have different LOVs for different preferences (plant, division…), we have to create different filters. Each filter uses the preferences part to determine the area it is running for.
Filter application in Business View Manager:
Where located
|
Business View data foundation
|
How created
|
Using Business View Manager, created by BV administrator
|
Specification
|
Can be accessed throughout
|
Contents
|
Row level security application
|
This is a very important step as it dictates when the filter is going to be applied. Rights applied to the filter can be at the user or a group level. If applied to a group, the filter is effective whenever a user in that group references it. Rights can be applied on a filter by right-clicking and opening ‘Edit Rights’. In our case, restriction rights are applied at the group level and not at individual user level.
Rights application to filter:
Where located
|
Business View data foundation
|
How created
|
Using Business View Manager, applied by BV administrator. The rights has to be manually applied to the filters in the migrated environment when an LOV is migrated for the first time
|
Specification
|
Can be accessed throughout
|
Contents
|
Row level security application at BOE group level
|
All LOVs that are created based on the table (command or normal) having reference in the filters, will have restrictive characteristics. The restriction application is not guided by the LOV, but purely is an effect of filter and the rights application of the filter. The LOV can reference lesser columns than there are in the actual table.
On a deeper level, there is actually a query that runs on the database whenever a LOV is called/ referenced in the report. Query generated in case when filter is applied:
SELECT distinct [GL_Plant_Id], [GL_Plant_Desc], [GL_Plant_Id_Desc], [GL_Plant_Desc_Id]
FROM [DM_CORPORATE].[dbo].[SEC_PLANT]
Where [bi_area] = ‘LIS’
And [preference] = ‘plant’
And [nt_userid] = <runtime value of CurrentCEUserName>
order by 2
Query generated in case when filter is not applied:
SELECT distinct [GL_Plant_Id], [GL_Plant_Desc], [GL_Plant_Id_Desc], [GL_Plant_Desc_Id]
FROM [DM_CORPORATE].[dbo].[SEC_PLANT]
order by 2
Where located
|
Business View data foundation
|
How created
|
Using Business View Manager, created by BV administrator
|
Specification
|
Can be accessed throughout
|
Contents
|
Row level security application at LOV
|
