SPD Server Table WHERE Constraints

Introduction to Table WHERE Constraints

SPD Server table WHERE constraints enable SPD Server table owners to associate a WHERE clause with a table so 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 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 affect 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 only grant update access 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 Symbolic Substitution. The table constraint WHERE clause is applied any time a user accesses the table (including the table owner if the WHERE clause specifies it). Table owners should be careful to construct only WHERE clause constraints that allow 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 will not work 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 will only contain rows that were filtered by the WHERE constraints on the source table. The destination table does not inherit the WHERE constraints of the source table.

Example Table Constraint WHERE Clauses

Assume you have the following SPD Server users:

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 certain 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 and uses table WHERE constraints to determine which table rows a user or user group can see:

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 the table, 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;

The following code creates a table WHERE constraint that allows Bossman or an ACLSPECIAL user to read any row of the table, but 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;

The following code creates a table WHERE constraint that allows Bossman to 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, who 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;

The following code creates a table WHERE constraint that allows Bossman to 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;

The following code creates a table WHERE constraint that allows Bossman to read all rows. William can read rows for employees that belong to the West region. Edcan read rows for employees that belong to the East region. Frank can read rows for employees that 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;