The example code found in the Full Code tab in this note demonstrates how WHERE constraints are not honored in the Viewtable window. This behavior is incorrect. Note that the example code assumes that you have users BOSSMAN and GUY defined in the SAS® Scalable Performance Data Server password database.

When you submit the code, PROC PRINT correctly reports only one table row for user GUY in the SAS Output window, as seen in the Output tab.

However, when user GUY opens the EMPLOYEES table in the Viewtable window, the WHERE constraints are not honored, and he can see all table rows.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

libname foo sasspds 'tmp' server=localhost.5400 user='bossman' 
password='spds123' IP=YES LIBGEN=YES;

DATA foo.employees;
input user $ grp $ salary $ position $ state $ region $;
cards;
WILLIAM GROUP1 70000 Engr1 CA W
GUY GROUP1 60000 Engr1 CA W
ED GROUP1 50000 Engr2 NJ E
FRANK GROUP2 80000 Engr2 TX S
TOM GROUP2 65000 Engr3 WA W
BOSSMAN GROUP2 80000 Mgr NJ E
;
run;

/* Grant read access for PUBLIC for table EMPLOYEES */

PROC SPDO lib=foo;
SET ACLUSER;
ADD ACL employees;
MODIFY ACL employees / read;
quit;

/*
The following code creates a table WHERE constraint that allows BOSSMAN to read any
row of table EMPLOYEES, but any other user can read only rows where the value in the User column
matches the user's user ID.
*/

PROC SPDO lib=foo;
SET ACLUSER;
constraint add employees;
WHERE (User = "@SPDSUSR")
or ("@SPDSUSR" = "BOSSMAN");
constraint describe employees;
quit;

/* user GUY can read only rows where the value in the User column matches his user ID */

libname guy sasspds 'tmp' server=localhost.5400 user='guy' 
password='spds123' IP=YES LIBGEN=YES;

proc print data=guy.employees; run;

   
              Obs    user     grp      salary    position    state    region

               2     GUY     GROUP1    60000      Engr1       CA        W

A fix for this issue for Scalable Performance Data Server 4.52 is available at:

A fix for this issue for SAS Scalable Performance Data Server 4.53 is available at: