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.


Method One: Users Without Design Access

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:

  1. Identify the physical file(s) over which record level security is necessary and build a security file consisting of a field for the user profile and a field that will be used to select records.
     
  2. Create a Sequel view that joins the security file with the data file.

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:

REGON CUSNO CNAME CSTTE CZIPC PHONE
40 100200 NBCO Corporation Inc. IL 60634-5147 312/457-182
20 100300 Obell Group Sales NY 13210-0012 315/472-644
40 100500 PCE Corp. IA 52665-5431 319/753-030
40 100600 UCE Corp. IL 60718-1098 312/437-611
10 100700 Xcme Corp. CA 90058-1714 213/583-171

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:

PROFILE Region
JIMH 10
DWIGHTS 20
PAMB 30
ANDYB 40
TODDP 50

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.


Method Two: Users With Design Access

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:

  1. Identify the physical file(s) over which record level security is necessary and build a security file consisting of a field for the user profile and a field that will be used to select records. (we are still working with the CUSTMAST and SECURITY1 files described in method one)
     
  2. Place SQL400 instructions to create an SQL/400 view (a join logical file) in a source member.
     
  3. Use the OS/400 command RUNSQLSTM to run the SQL/400 instructions in the source member to build the join logical file.
     
  4. Use the Sequel exclusion dictionary to exclude the underlying physical file.

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.

Last Modified On: April 21, 2017