SAS Scalable Performance Data (SPD) Server SQL Symbolic Substitution :: SAS(R) Scalable Performance Data Server 5.1: Administrator's Guide, Second Edition

Overview of Symbolic Substitution

SPD Server SQL supports symbolic substitution of the following items in SQL queries:

a user ID, which is substituted by @SPDSUSR
a group, which is substituted by @SPDSGRP
a user who has ACL Special privileges, which is substituted by @SPDSSPEC

The right-hand side of symbolic substitution statement must be in all uppercase text (for example, @SPDSUSR=SOMEUSER).

Symbolic Substitution and Row-Level Security

A powerful use of symbolic substitution is to deploy row-level security on sensitive tables that use views. Suppose that only certain users or groups can access a sensitive table. You can use symbolic substitution to create a single view to the table that provides restricted access based on user ID or groups. You can grant Universal access to the view, but only users or groups that meet the symbolic substitution constraints can see the rows.

For another example, imagine a table that contains sensitive information has a column that contains group names or user IDs. You can use symbolic substitution to create a single view that allows users to access only the rows that contain their user ID or group. You can grant Universal access to the view, but each user or group is allowed to see only their user or group rows.

CAUTION:

SPD Server SQL symbolic substitution uses an 8-byte literal string (blank padded if necessary) to replace SPD Server user and SPD Server group names. Symbolic substitution will not match a column that is less than 8 characters wide. If the table column that contains user IDs or group names is not at least 8 characters wide, symbolic substitution will evaluate the WHERE- predicate on that column to be FALSE, which can result in incorrect results.

Symbolic Substitution Example

        PROC SQL;
             connect to sasspds
               (dbq="path1"
                server=host.port
                user='anonymous');

          /* queries comparing literal rows are  */
          /* only selected if the symbolic       */
          /* substitution evaluates as 'true'    */

            select *
            from connection
            to sasspds(
               select *
               from mytable
               where "@SPDSUSR" = "SOMEUSER");

            select *
            from connection
            to sasspds(
               select *
               from mytable
               where "@SPDSGRP" = "SOMEGROUP");

            select *
            from connection
            to sasspds(
               select *
               from mytable
               where "@SPDSSPEC" = "TRUE");

          /* queries based on column values will only  */
          /* select appropriate columns                */

            select *
            from connection
            to sasspds(
               select *
               from mytable
               where usercol = "@SPDSUSR");

            select *
            from connection
            to sasspds(
               select *
               from mytable
               where grpcol = "@SPDSGRP");

          /* Create a view to worktable that allows    */
          /* users FRED or BOB, groups BCD or ACD, or  */
          /* someone with ACLSPECIAL to read the table */

            execute(create view workview as
                select *
                from worktable
                where "@SPDSUSR" in ("FRED", "BOB") or
                      "@SPDSGRP" in ("BCD", "ACD") or
                      "@SPDSSPEC" = "TRUE")
            by sasspds;

          /* Create a view to worktable that allows users    */
          /* to access only rows where the column "usergrp"  */
          /* matches their group. The userID BOSS can access */
          /* any group records where the column "userid" is  */
          /* "BOSS"                                          */

            execute(create view workview as
                select *
                from worktable
                where usergrp = "@SPDSGRP" and
                ("@SPDSUSR" = "BOSS" or userid != "BOSS"))
            by sasspds;
         disconnect from sasspds;
         quit;