Features Added in FOCUS 7.7.03

Restricting Access to Values Using WHERE Criteria

How to:

Restrict Access to Values Using WHERE Criteria

Using FOCUS DBA security, you can restrict access to specific values in a data source. The RESTRICT=VALUE attribute supports those criteria that are supported by the IF phrase. The RESTRICT=VALUE_WHERE attribute supports all criteria supported in a WHERE phrase, including comparison between fields and use of functions. The WHERE expression will be passed to a configured adapter when possible. For MODIFY, VALUE and VALUE_WHERE are synonymous and are internally converted to VALIDATE commands.

Top of page

Syntax: How to Restrict Access to Values Using WHERE Criteria

USER=password, ACCESS={R|RW|W},
   RESTRICT=VALUE_WHERE, NAME=name, 
   VALUE=expression; ,$

where:

password: Is the user password, up to 64 characters.
ACCESS={R|RW|W}: Is the type of access the user is granted: Read, Read/Write, or Write.
RESTRICT=VALUE_WHERE: Specifies that the user can have access to only those values that satisfy the expression described in the VALUE attribute.
name: Is the name of the field or segment to restrict. NAME=SYSTEM, which can only be used with value tests, restricts every segment in the data source, including descendant segments. Multiple fields or segments can be specified by issuing the RESTRICT attribute several times for one user.
VALUE=expression: Specifies an expression that describes the values to which the user has access. The expression can use all elements supported by a WHERE test in a TABLE request.

Top of page

Example: Restricting Access to Values Using VALUE_WHERE

Add the following DBA declarations to the end of the GGSALES Master File. These declarations give USER1 access to the West region and to products that start with the letter C:

END                                                            
DBA = USERD,$                                                   
USER = USER1, ACCESS = R, NAME = SALES01, RESTRICT = VALUE_WHERE,
       VALUE = REGION EQ 'West' AND PRODUCT LIKE 'C%'; ,$

The following request sets the password to USER1 and sums dollar sales and units by REGION, CATEGORY, and PRODUCT:

SET USER = USER1  
TABLE FILE GGSALES
SUM DOLLARS UNITS 
BY REGION         
BY CATEGORY       
BY PRODUCT        
END

The output only displays those regions and products that satisfy the WHERE expression in the Master File:

Region       Category     Product           Dollar Sales  Unit Sales
------       --------     -------           ------------  ----------
West         Coffee       Capuccino               915461       72831
             Food         Croissant              2425601      197022
             Gifts        Coffee Grinder          603436       48081
                          Coffee Pot              613624       47432

If the RESTRICT=VALUE_WHERE attribute is changed to a RESTRICT=VALUE attribute, the expression is not valid, the following message is generated, and the request does not execute:

(FOC002) A WORD IS NOT RECOGNIZED:  LIKE 'C%'