Symbolic Substitution

SPD Server SQL supports symbolic substitution of the user's User ID using @SPDSUSR, group using @SPDSGRP, and whether the user is ACL Special using @SPDSSPEC in SQL queries. When the query is parsed, @SPDSUSR will be replaced by the User ID, @SPDSGRP by the group, and @SPDSSPEC will be "true" if the user has ACL Special privileges. The right hand side of symbolic substitution statements must be in all upper case text. Consider the example, "@SPDSUSR" = "SOMEUSER".

Symbolic Substitution Row Level Security

A powerful use of symbolic substitution is deploying row level security on sensitive tables that use views. Suppose there is a sensitive table that only certain users or groups can access. The administrator can use symbolic substitution to create a single view to the table that provides restricted access based on user ID or groups. The administrator could give universal access to the view, but only users or groups that meet the symbolic substitution constraints will see the rows.

For another example, imagine a table that contains sensitive information has one column that contains group names or user IDs. The administrator can use symbolic substitution to create a single view that allows users to access only the rows that contain his user ID or group. The administrator could give universal access to the view, but each user or group would be allowed to only see their user or group rows.

Note: 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 long. If the table column that contains user IDs or group names is not at least 8 characters wide, symbolic substitution will not function.

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;