Starting with version R11M03, Viewpoint Administrator’s new Advanced Database Security module, in conjunction with the Viewpoint Universal Driver, can define row-level security to limit file access. In addition, the easy-to-use interface can create simple rules to restrict access to libraries, files and fields too. All rules can be applied to group, user, and *PUBLIC profiles.
If you are using Sequel prior to R11M03, record level security can be configured using one of the two methods presented below depending on whether or not users have access to design Viewpoint Views. Each user’s default, Allow Viewpoint Design Mode, in the Viewpoint Administrator is set to allow or limit access to this ability.
This method is for the environment where users are not allowed to design views. In this scenario users need to display System i data from a browser or Viewpoint and automatically retrieve a set of records based on their System i user profile. This is accomplished using the process described below:
As an example, the file SEQUELEX/CUSTMAST contains a record for each customer. Customers can be located in any of five regions.
This simple view using the CUSTMAST file,
SELECT regon, cusno, cname, cstte, czipc, phone FROM SEQUELEX/CUSTMAST
retrieves the following records for customers in all regions, regardless of who is running the view:
|40||100200||NBCO Corporation Inc.||IL||60634-5147||312/457-182|
|20||100300||Obell Group Sales||NY||13210-0012||315/472-644|
If we want to limit a sales persons’ access to only the customer information from their sales territory, we must define security rules over the CUSTMAST file. We can accomplish this by creating a file that contains the security rules to specify which users are allowed to see which regions.
For this example, simply create a file called SECURITY1 that looks like the following:
Here, we can see user profile JIMH should only be able to retrieve customer records from region 10 and DWIGHTS should only be able to retrieve customer records for region 20, and so on.
The simple Sequel view shown here will join the data file (CUSTMAST) with the security file:
SELECT regon, cusno, cname, cadd1, cstte, czipc, phone FROM YOURLIB/SECURITY1, SEQUELEX/CUSTMAST WHERE USER=PROFILE AND REGON.CUSTMAST=REGION.SECURITY1
When run from a browser with SWI or the Viewpoint Explorer, the view will limit the records retrieved based on the user’s profile.
This method is for the environment where users are allowed to design views. In this environment, not only do we want the sales person to be limited to just the customers in her region when displaying a view, but we want to also limit her access to the data files when she is designing a view. This is accomplished using the process described below:
Create an SQL/400 View
Enter SQL/400 instructions to link the CUSTMAST file with the SECURITY1 file in a source file called SOURCE in member CUSTMASTR like so (as seen with SEU):
Columns . . . : 1 71 Edit SEQUELEX/SOURCE SEU==> CUSTMASTR FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 *************** Beginning of data ************************************* 0001.00 CREATE VIEW SEQUELEX/CUSTMASTR AS 0002.00 SELECT CUSNO, CNAME, CADD1, CSTTE, CZIPC, CPHON, CTYPE, 0003.00 CRLIM, AMTDU, OROPN, HIGHB, PAYAM, PAYMN, PAYDY, 0004.00 PAYYR, PAYCC, MTD$C, YTD$C, REGON, SALNO, DFTWH, 0005.00 CSTAT, SDATE, CPREF, PDATE 0006.00 FROM SEQUELEX/CUSTMAST, YOURLIB/SECURITY1 0007.00 WHERE USER=PROFILE AND CUSTMAST.REGON=SECURITY1.REGION ****************** End of data ****************************************
This source is used in the next step to create a join logical file linking CUSTMAST to SECURITY1.
Build the Join Logical File
At a command line, issue the command RUNSQLSTM (Run SQL Statements) to execute the instructions in the source member and build the join logical file like so:
RUNSQLSTM SRCFILE(SEQUELEX/SOURCE) SRCMBR(CUSTMASTR)
Exclude the Original File
The final step for this method (and the most important) is to use the Sequel exclusion dictionary in the ViewPoint Administrator to limit Sequel Viewpoint users’ access to the CUSTMAST file.
The following is a simple view that uses the join logical file:
SELECT regon, cusno, cname, cadd1, cstte, czipc, phone FROM SEQUELEX/CUSTMASTR
When run, the view will limit the records retrieved based on the user’s profile. More importantly, the user building the view has no access (unless granted) to the original CUSTMAST file.
Still have questions? We can help. Submit a case to Technical Support.