SAS Scalable Performance Data (SPD) Server Table WHERE Constraints :: SAS(R) Scalable Performance Data Server 5.1: Administrator's Guide, Second Edition

Overview of Table WHERE Constraints

SPD Server table WHERE constraints enable SPD Server table owners to associate a WHERE clause with a table. This association means that the WHERE clause is applied when a user accesses the table. As a result, the user sees only the table rows that remain after the table owner's WHERE clause filter has been processed. The filtering is applied any time the table is accessed by a DATA step or by an SQL query. You can use SPD Server table WHERE constraints with SPD Server symbolic substitutions to create row-level security that filters table rows based on the values for User ID, Group ID, or ACLSPECIAL privileges. SPD Server table WHERE constraints do not effect normal SPD table ACL settings, such as Read, Write, and Column access.

Table constraints are not applied to a table that is opened for Update access. The table owner should grant Update access only to users who can modify any row of the table, delete any row of the table, or append new rows to the table.

Creating and Controlling Table WHERE Constraint

To create a table WHERE constraint, you must be the table owner. Use PROC SPDO commands to define, delete, and describe table WHERE constraints.

To add a table WHERE constraint:

constraint add <table-name>;
where <where-clause-expression>;

To describe a table WHERE constraint:

constraint describe <table-name>;

To remove a table WHERE constraint:

constraint remove <table-name>;

Table Constraint WHERE Clauses

A table constraint WHERE clause can be any WHERE clause that SPD Server can parse and interpret. The WHERE clause can use any function that SPD Server supports. Generally, the WHERE clause uses SPD Server symbolic substitution to create row-level security that is based on the values for User ID, GROUP, or ACLSPECIAL privileges. For more information, see SAS Scalable Performance Data (SPD) Server SQL Symbolic Substitution.

The table constraint WHERE clause is applied when a user accesses the table. If the WHERE clause specifies the table owner, then the WHERE clause is also applied when the owner access the table. Table owners should be careful to construct WHERE clause constraints that allow only the table owner to see all of the table rows.

Table Constraint Restrictions and Limitations

The SPD Server SQL COPY TABLE and UPDATE TABLE extensions do not function on a table that has a table constraint WHERE clause. You must use a different method (such as PROC COPY or SQL CREATE TABLE as SELECT) to copy the table. If you try to use COPY TABLE or UPDATE TABLE, the destination table contains rows that were filtered by the WHERE constraints on the source table only. The destination table does not inherit the WHERE constraints of the source table.

Example Table Constraint WHERE Clauses

Assume that the following SPD Server users exist:

User      Password    Group    ACLSPECIAL
----      --------    -----    ----------
William   worker1     group1   no
Guy       worker2     group1   no
Frank     worker3     group2   no
Ed        worker4     group2   no
Bossman   worker5     group2   no
Aclspec   worker6     group3   yes

The user Bossman creates a table that contains sensitive data. Only some users or user groups are authorized to see certain rows in the table. Bossman uses WHERE constraints to control which table rows are available to different users or user groups.

LIBNAME foo sasspds 'tmp' ... user="bossman";
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;

The following code provides Read access for all users. The code uses table WHERE constraints to determine which table rows a user or user group can read:

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

The user Bossman can create table WHERE constraints to control access to the table in the following ways:

Bossman can read any row of the table. 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;
```
Bossman or an ACLSPECIAL user can read any row of the table; any other user can read only rows where the value in the User column matches the user's group ID.
```
PROC SPDO lib=foo;
SET ACLUSER;
constraint remove employees;
constraint add employees;
 WHERE (grp= "@SPDSGRP") 
 or ("@SPDSUSR" = "BOSSMAN") 
 or ("@SPDSSPEC" = "TRUE");
constraint describe employees;
quit;
```
Bossman can read all rows. Other users can read only rows where the value in the User column matches the user's user ID, except for user Guy. User Guy can also read rows for employees from the state of California.
```
PROC SPDO lib=foo;
SET ACLUSER;
constraint remove employees; 
constraint add employees;
 WHERE (User = "@SPDSUSR") 
 or ("@SPDSUSR" = "BOSSMAN")
 or ("@SPDSUSR" = "GUY" and state = "CA");
constraint describe employees;
quit;
```
Bossman can read all rows. Other users can read only rows where the value in the User column matches the user's user ID, or rows where the value in the Salary column is less than or equal to $6,000 per month (rounded down to the nearest dollar).
```
PROC SPDO lib=foo;
SET ACLUSER;
constraint remove employees;
constraint add employees;
 WHERE (User = "@SPDSUSR")
 or ("@SPDSUSR" = "BOSSMAN")
 or (floor(salary/12) <= 6000);
constraint describe employees;
quit;
```
Bossman can read all rows. User William can read rows for employees who belong to the West region. User Ed can read rows for employees who belong to the East region. User Frank can read rows for employees who belong to the South region.
```
PROC SPDO lib=foo;
SET ACLUSER;
constraint remove employees;
constraint add employees;
 WHERE (("@SPDSUSR" = "BOSSMAN")
 or ("@SPDSUSR" = "WILLIAM" and REGION = "W") 
 or ("@SPDSUSR" = "ED" and REGION = "E")
 or ("@SPDSUSR" = "FRANK" and REGION = "S"));
constraint describe employees;
quit;
```