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.

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;

ACL Security Examples

Overview of Security Examples

If LIBACLINHERIT=YES is added to a LIBNAME definition, the ACL precedence of permission checks changes. In this case, the LIBNAME ACL is used to first give READ or WRITE access to the domain, and then to inherit ACLs for resources that are owned by the domain owner. When a user accesses resources in an owned domain by using LIBACLINHERIT=YES, the following precedence of permissions checks on the ACL resource:

If user-specific permissions are defined on the object for the accessor, the accessor gets these permissions.
If group-specific permissions are defined on the object for the accessor's group, the accessor gets these permissions.
If LIBNAME ACL permissions are defined for the accessor, the accessor gets these permissions on the object.
If LIBNAME ACL permissions are defined for the accessor's group, the accessor gets these permissions on the object.
Otherwise, the accessor gets UNIVERSAL ACLs on the resource.

The following are examples using LIBACLINHERIT:

Below is a listing of the libnames.parm files that are used in the code examples, along with a listing of users and groups in the password database.


libnames.parm:
----------------------------------
LIBNAME=d1
  pathname=/IDX1/spdsmgr/d1
  owner=admin ;
LIBNAME=d2
  pathname=/IDX1/spdsmgr/d2
  owner=prod1 ;
LIBNAME=colsec
  pathname=/IDX1/spdsmgr/colsec
  owner=boss ;
LIBNAME=onepath
  pathname=/IDX1/spdsmgr/onepath ;

Password database List:

User     Level  Entry Type  Group
-----------------------------------
ADMINGRP   0   GROUP ENTRY
GROUP1     0   GROUP ENTRY
GROUP2     0   GROUP ENTRY
GROUP3     0   GROUP ENTRY
GROUP4     0   GROUP ENTRY
PRODGRP    0   GROUP ENTRY
ADMIN1     7   user ID     ADMINGRP
ADMIN2     7   user ID     ADMINGRP
PROD1      7   user ID     PRODGRP
PROD2      7   user ID     PRODGRP
USER1      0   user ID     GROUP1
USER2      0   user ID     GROUP2
USER3      0   user ID     GROUP3
USER4      0   user ID     GROUP4
USER5      0   user ID     GROUP1
USER6      0   user ID     GROUP2
USER7      0   user ID     GROUP3
USER8      0   user ID     GROUP4
BOSS       7   user ID     ADMINGRP
EMPLOYEE   0   user ID

Domain Security

When the libname.parm option OWNER= is specified, no other user can access the domain unless the user is given permissions by the domain owner. Permissions to access a domain are given using a LIBNAME ACL statement.

The code example below uses a LIBNAME ACL statement to give access permissions to different groups.

LIBNAME d2 sasspds 'd2'
  server=zztop.5162
  user='prod1'
  password='spds123'
  IP=YES ;

/* Give permissions to LIBNAME */

PROC SPDO library=d2 ;

/* assign who owns the ACLs */

  set acluser prod1 ;

/* Give specific groups access */
/* to the domain. */

  add ACL / LIBNAME ;
  modify ACL /
  LIBNAME prodgrp=(y,y,y,y)
    group1=(y,y,n,n)
    group2=(y,n,n,n)
    group3=(y,n,n,n) ;

/* Give spedific users access to */
/* the domain */

  modify ACL /
  LIBNAME user7=(y,n,n,n)
    admin1=(y,n,n,n) ;
  list ACL _all_ ;
quit ;

The ID 'prod2' is in the group which has permissions to control the LIBNAME ACL. Any ID in that group can modify the LIBNAME ACL.

Because the ACL was created by user 'prod1', the user 'prod2' must use the user ID 'prod1' in order to modify the LIBNAME ACL. This is allowed because the group was given control. User 'prod1' still remains the owner of the LIBNAME ACL.

LIBNAME prod2d2 sasspds 'd2'
  server=zztop.5162
  user='prod1'
  password='spds123'
  IP=YES ;

PROC SPDO library=prod2d2 ;

/* Set user ID as 'user1', who owns */
/* the ACL to be modified */

  set acluser prod1 ;
  modify ACL /
  LIBNAME group1=(n,n,n,n)
    group4=(y,n,n,n) ;
  list ACL _all_ ;
quit ;

The second way that the LIBNAME ACL can be changed is by using a user ID that has ACL Special privileges. In the example below, the user 'admin1' uses the ACLSPECIAL= statement to modify the LIBNAME ACL. As in the previous example, the user 'admin1' must use the user ID of 'prod1'.

LIBNAME admin1d2 sasspds 'd2'
  server=zztop.5162
  user='admin1'
  password='spds123'
  ACLSPECIAL=YES
  IP=YES ;

PROC SPDO library=admin1d2 ;

/* The ACLSPECIAL= statement allows */
/* the user 'admin1' to operate under */
/* the user ID 'prod1', allowing the */
/* ACLs to be modified. */

  set acluser prod1 ;
  modify ACL /
  LIBNAME admingrp=(y,n,n,n) ;
    list ACL _all_ ;
quit ;

LIBACLINHERIT

If the LIBACLINHERIT parameter file option is turned on, the ACL precedence of permission checks changes. Turning on LIBACLINHERIT creates a LIBNAME ACL on the specified LIBNAME domain. The LIBNAME ACL grants users rights to all resources within the LIBNAME domain. When a LIBNAME ACL is created for a specified LIBNAME domain, the ACL precedence of permission checks becomes:

Check user-specific permissions first. If defined, the accessor gets these permissions.
If a resource is owned by the same ACL group as the accessor, the accessor gets the resource's GROUP permissions.
LIBNAME ACL permissions are used for domains where LIBACLINHERIT is turned on.
If the resource is owned by a different ACL group than the accessor, the accessor gets the resource's UNIVERSAL permissions.

The following is an example using LIBACLINHERIT:

/* information from libnames.parm                  */
/*                                                 */
/* LIBNAME=LIBINHER                                */
/*    pathname=/IDX1/spdsmgr/spds41test/libinher   */
/*    LIBACLINHERIT=YES                            */
/*    owner=admin;                                 */
/* LIBNAME=noinher                                 */
/*    pathname=/IDX1/spdsmgr/spds41test/noinher    */
/*    owner=admin;                                 */


LIBNAME libinher sasspds 'libinher'
   server=zztop.5129
   user='admin'
   password='spds123';

LIBNAME noinher sasspds 'noinher'
   server=zztop.5129
   user='admin'
   password='spds123';

data libinher.admins_table
     noinher.admins_table ;

   do i = 1 to 10;
     output;
   end;
run;


/* Set up LIBNAME access for user anonymous */

PROC SPDO library=libinher;


/* set who will own these ACLs */

set acluser admin;


/* Add a LIBNAME ACL to d1 */

add acl / LIBNAME;


/* Modify LIBNAME ACL Domain d1     */
/* Allow users in Group 1 read-only */
/* access to the domain             */
modify acl / LIBNAME read;

  list acl _all_;
quit;


/* Set up LIBNAME access for user anonymous */

PROC SPDO library=noinher;


/* Specify who owns these ACLs */

set acluser admin ;


/* add a LIBNAME ACL to d1 */

add acl / LIBNAME ;


/* Modify LIBNAME ACL Domain d1      */
/* Allow users in Group 1 read-only  */
/* access to the domain              */

modify acl / LIBNAME read ;

  list acl _all_;
quit;

LIBNAME a_inher sasspds 'libinher'
   server=zztop.5129
   user='anonymous';
LIBNAME a_noher sasspds 'noinher'
   server=zztop.5129
   user='anonymous';

PROC PRINT data=a_inher.admins_table;
   title 'with libaclinher';
run;

PROC PRINT data=a_noher.admins_table;
   title 'without libaclinher';
run;

Anonymous User Account

The SPD Server uses a general ID that is called 'anonymous'. Any person that can connect to the server can do so using the anonymous user ID. The anonymous ID cannot be removed from the password database using the psmgr utility and the delete command. If you want to prevent anonymous user ID access, the SPD Server administrator must use the psmgr utility to add a user called, "anonymou" to the password database, and keep the password secret.

Any table that is created by the anonymous user ID can be viewed by all users who have access to that table's domain. The anonymous ID does have the ability to place ACLs on the table to limit access.

/* John logs in using the anonymous */
/* user ID and creates a table      */

     LIBNAME john sasspds 'onepath'
       server=zztop.5162
       user='anonymous'
       password='anonymous'
       IP=YES ;

     data john.anonymous_table ;
       do i = 1 to 100 ;
       output ;
       end ;
     run ;

/* Mary can also log in as anonymous  */
/* and read the table that John       */
/* created.                           */

     LIBNAME mary sasspds 'onepath'
       server=zztop.5162
       user='anonymous'
       IP=YES ;

     PROC PRINT data=mary.anonymous_table
       (obs=10) ;
       title
         'mary reading anonymous_table' ;
     run ;

/* user1 can log in and read the table */
/* that John created                   */

     LIBNAME user1 sasspds 'onepath'
       server=zztop.5162
       user='user1'
       password='spds123'
       IP=YES ;

     PROC PRINT data=user1.anonymous_table
       (obs=10) ;
       title
         'user1 reading anonymous_table' ;
     run ;

/* Tables created by user ID anonymous */
/* can have ACLs                       */

     PROC SPDO library=john ;

/* assign who owns the ACL */

     set acluser anonymous ;

/* The MODIFY statement sets an ACl so */
/* only user ID 'anonymous' can read   */
/* the table                           */

     add ACL anonymous_table ;
     modify ACL anonymous_table /
       anonymous=(y,n,n,n);

     list ACL _all_;
     quit ;

/* Now, only user ID 'anonymous' can */
/* read the table                    */

     LIBNAME user1 sasspds 'onepath'
       server=zztop.5162
       user='user1'
       password='spds123'
       IP=YES ;

     PROC PRINT data=user1.anonymous_table
       (obs=10) ;
       title
         'user1 trying to read anonymous_table' ;
     run ;

     LIBNAME mary sasspds 'onepath'
       server=zztop.5162
       user='anonymous'
       password='anonymous'
       IP=YES ;

     PROC PRINT data=mary.anonymous_table
       (obs=10) ;
       title
         'mary reading anonymous_table' ;
     run ;

/* Mary can't write to anonymous_table */

     data mary.anonymous_table ;
       do i = 1 to 100 ;
       output ;
       end ;
     run ;

Read Only Tables

A common security measure in SPD Server assigns an SPD Server ID to act as the owner of a domain and to provide control over it.

Typically, one or two user IDs administer table loads and refreshes . These user IDs can perform all the jobs that are required to create, load, refresh, update, and administer SPD Server security. Using one or two user IDs centralizes the data administration on the server. More than one ID for data administration spreads responsibility and still provides backup. The following example demonstrates how to allow different groups access to the domain, tables, and how different groups can control resources in the domain.

      LIBNAME d1 sasspds 'd1'
        server=zztop.5162
        user='admin1'
        password='spds123'
        IP=YES ;

      PROC SPDO library=d1 ;

/* assign who owns the ACLs */

      set acluser admin1 ;

/* add a LIBNAME ACL to d1  */

      add ACL / LIBNAME ;

The MODIFY statement in the code below enables the following actions:

Any user in same group as admin can read, write, or alter tables and modify the LIBNAME access to the domain.
Users in group1 and group2 receive read access to the domain.
Users in group3 and group4 receive read and write access to the domain.

      modify ACL / LIBNAME
        admingrp=(y,y,y,y)
        group1=(y,n,n,n)
        group2=(y,n,n,n)
        group3=(y,y,n,n)
        group4=(y,y,n,n) ;

      list ACL _all_;
      quit ;

/* create two tables */

      data d1.admin1_table1 ;
        do i = 1 to 100 ;
        output ;
        end ;
      run ;

/* admin1 has write priviliges to */
/* the domain                     */

      data d1.admin1_table2 ;
        do i = 1 to 100 ;
        output ;
        end ;
      run ;


/* Generic ACLs allow all users to */
/* read tables created by admin1   */
/* unless a specific ACL is placed */
/* on a resource                   */

      PROC SPDO library=d1 ;

/* Assign who owna the ACLs */

      set acluser admin1 ;

The two ACL commands in the code below give read privileges to members of the ACL group 'ADMIN1' for any table that is created by admin1, who has read access to the domain.

This ACL is a good example for data marts and warehouses which DO NOT contain sensitive data. A GENERIC ACL gives broad access to tables in a domain. Generic ACLs must be used correctly (or not at all) if sensitive data needs to be restricted to specific users or groups of users.

If a table in a domain with generic ACLs is not specifically protected by its own ACL, there is a risk of allowing access by any user to sensitive data.

     add ACL / generic
       read ;
     modify ACL / generic read
       admingrp=(y,n,n,y) ;
     list ACL _all_;
     quit ;

/* Test access for a user in group1 */

    LIBNAME user1d1 sasspds 'd1'
      server=zztop.5162
      user='user1'
      password='spds123'
      IP=YES ;

    PROC PRINT data=user1d1.admin1_table1
      (obs=10) ;
      title
        'read admin1_table1 by user1' ;
    run ;

    PROC PRINT data=user1d1.admin1_table2
      (obs=10) ;
      title
        'read admin1_table2 by user1' ;
    run ;

/* Test access for a user in group2 */

    LIBNAME user2d1 sasspds 'd1'
      server=zztop.5162
      user='user2'
      password='spds123'
      IP=YES ;

    PROC PRINT data=user2d1.admin1_table1
      (obs=10) ;
      title
        'read admin1_table1 by user2' ;
    run ;

    PROC PRINT data=user2d1.admin1_table2
      (obs=10) ;
      title
        'read admin1_table2 by user2' ;
    run ;

When any ACL is placed on a specific table, that ACL takes precedence over the generic ACL. The ACL in the code below performs the following:

Gives read access of admin1_table2 to group1.
Gives the admingrp read and control of admin1_table2
Takes precedence over the generic read ACL, which prevents users that are not granted specific access to admin1_table2 from reading, writing, altering, or controling the table.

    PROC SPDO library=d1 ;

/* Assign who owns the ACLs */

    set acluser admin1 ;

/* This ACL takes precedence over the */
/* generic ACL for users that try to  */
/* access admin1_table2.              */

    add ACL admin1_table2 ;
    modify ACL admin1_table2 /
      group1=(y,n,n,n)
      admingrp=(y,n,n,y) ;
    list ACL _all_;
    quit ;

/* Test access for a user in group1 */

    LIBNAME user1d1 sasspds 'd1'
      server=zztop.5162
      user='user1'
      password='spds123'
      IP=YES ;

    PROC PRINT data=user1d1.admin1_table2
      (obs=10) ;
      title
        'read admin1_table2 by user1' ;
    run ;

/* Test access for a user in group2 */

    LIBNAME user2d1 sasspds 'd1'
      server=zztop.5162
      user='user2'
      password='spds123'
      IP=YES ;


    PROC PRINT data=user2d1.admin1_table2
      (obs=10) ;
      title
        'read admin1_table2 by user2' ;
    run ;

Domain Security and Group Access

This section of code provides an overview of SPD Server domain security and group access using PROC SPDO.

Permissions are often granted to a group of users rather than individual users. The example below shows how to provide the different groups of users access to the domain owned by the user ID "Admin", and then extends the access to the tables. This makes administration both simpler and more secure. Admin1 is the owner of the domain and can determine access to the resources. In the following example, PROC SPDO permits the following:

Any user ID in admingrp receives read/write/alter access to the domain
Any user ID in group1 or group2 receives read access to the domain
Any user ID in group3 or group4 receives read/write access to the domain

       LIBNAME d1 sasspds 'd1'
         server=zztop.5162
         user='admin'
         password='spds123
         IP=YES ;

       PROC SPDO library=d1 ;

 /* assign who owns the ACLs */

       set acluser admin ;

 /* add a LIBNAME ACL to d1 */

       add ACL / LIBNAME ;

/* Allow any user in same group */
/* as admin to read, write, or  */
/* alter tables in the domain   */

       modify ACL / LIBNAME
         admingrp=(y,y,y,n)
         group1=(y,n,n,n)
         group2=(y,n,n,n)
         group3=(y,y,n,n)
         group4=(y,y,n,n) ;

       list ACL _all_;

       run;

/* admin1 has write privileges to */
/* the domain                     */

       data d1.admin1_table1 ;
         do i = 1 to 100 ;
         output ;
         end ;
       run ;

/* Generic ACL allows all users to */
/* read tables created by admin1   */

       PROC SPDO library=d1 ;

/* assign who owns the ACLs */

       set acluser admin1 ;

/* Modify LIBNAME for groupread     */
/* and groupwrite.  The ACL MUST    */
/* inlcude groupread if other       */
/* users  in the same group as      */
/* admin2 need to be able to read   */
/* tables that were created by      */
/* admin2.                          */

       add ACL admin1_table1 /
         generic
         read
         groupread
         groupalter ;

       list ACL _all_;
       run;

/* admin1 has write privileges to    */
/* the domain                        */

       data d1.admin1_table2 ;
         do i = 1 to 100 ;
         output ;
         end ;
       run ;

/* generic ACL allows all users to    */
/* read the tables                    */

       PROC SPDO library=d1 ;

/* assign who owns the ACLs */

       set acluser admin1 ;

/* Add a table and modify LIBNAME ACL */
/* for groupread and groupwrite. The  */
/* ACL MUST include groupread to give */
/* users in the same group as admin2  */
/* the ability to read tables created */
/* by admin2                          */

      add ACL admin1_table2 /
        group1=(y,n,n,n)
        admingrp=(y,n,n,y) ;
        list ACL _all_;
      run;

/* admin2 has write privileges to the */
/* domain                             */

      data admin2d1.admin2_table ;
        do i = 1 to 100 ;
        output ;
        end ;
      run ;

/* Admin2 must use PROC SPDO to allow */
/* users read access to the table.    */
/* The PROC SPDO example below uses   */
/* generic syntax with a read.  This  */
/* provides any user outside of the   */
/* admingrp read access to tables     */
/* that were created by acdmin2.  The */
/* groupread and groupalter allow     */
/* access by users within admingrp.   */

     PROC SPDO library=admin2d1 ;

/* Assign who owns the ACLs */

     set acluser admin2 ;

/* Modify LIBNAME ACL for groupread   */
/* and groupwrite. The ACL MUST       */
/* include groupread if other users   */
/* in the same group as admin2 need   */
/* to read tables created by admin2.  */

     add ACL / generic
       read
       groupread
       groupalter ;

     list ACL _all_;

/* admin (same group) can read the     */
/* table                               */

     PROC PRINT data=d1.admin2_table
       (obs=10) ;
       title 'read by admin' ;
     run ;

/* Admin has been given the ability to */
/* modify or replace tables created by */
/* admin2 with 'groupalter'            */

     data d1.admin2_table ;
       do i = 1 to 100 ;
       output ;
       end ;
     run ;

/* Provide other users in same group   */
/* read access to the table            */

     PROC SPDO library=admin2d1 ;

/* assign who owns the ACLs */

     set acluser user3 ;

/* Modify LIBNAME ACL for groupread    */
/* and groupwrite. The ACL MUST        */
/* include groupread if other users in */
/* the same group as admin2 are to be  */
/* able to read tables that were       */
/* created by admin2                   */

     add ACL user3_table /
       groupread ;
     list ACL _all_;

Bringing a Table Offline to Refresh

When it is time to refresh the table, the first step is to revoke read privileges to all user IDs, except the ID that will perform the refresh.

      LIBNAME d2 sasspds 'd2'
        server=zztop.5162
        user='prod1'
        password='spds123'
        IP=YES ;

This example assumes that the Table prod1_table is already loaded in the domain and that the groups who use the table have access.

PROC SPDO library=d2 ;

/* assign who will owns these ACLs */

set acluser prod1 ;

Modify the table ACL in the following ways:

Revoke read and control by user IDs that are in the same group. This prevents locks during table refreshes.
Revoke read access by users that are in group1 through group4 to prevent locks during the refresh process.
Note: If a user is actively accessing a data table when the ACLs for that table are modified, the user continues to have access. This situation can create a table lock that prevents the table refresh from occurring. By revoking the table's read privileges before the refresh occurs, new SPD Server jobs cannot access the table.
Existing jobs will continue running and can finish under the lock. You can also use the special PROC SPDO operator commands to identify any users that might be running unattended jobs, and disconnect them so the refresh can take place.
```
      modify ACL prod1_table /
        prodgrp=(n,n,n,n)
        group1=(n,n,n,n)
        group2=(n,n,n,n)
        group3=(n,n,n,n)
        group4=(n,n,n,n) ;
```

Now, modify table ACLs to allow the user ID prod1 to perform table refreshes. Because user ID prod1 is part of prodgrp, that ID loses access to the table when the permissions are changed. Prod1, the domain and table owner, can still modify ACLs to gain access.

modify ACL prod1_table /
prod1=(y,y,y,y) ;
list ACL _all_;
quit;

Now user ID prod1 has full access to refresh the table.

data d2.prod1_table ;
do i = 1 to 100 ;
output ;
end ;
run ;

PROC SPDO library=d2 ;

/* Specify who owns the ACLs */

set acluser prod1 ;

There is no need to issue an add ACL command for prod1_table. Deleting a table or replacing a table does not delete the ACLs. The ACL for that table remains until:

The table ACL is deleted using PROC SPDO delete syntax.
The table is deleted and another user creates a table with the same name.

At that time, the ACLs have not been deleted. Deleting the table releases any rights that owner has on the table. The exception is when persistent ACLs are used.

After the table has been refreshed, the ACL can be modified to allow read access once again.

modify ACL prod1_table /
prodgrp=(y,n,n,y)
group1=(y,n,n,n)
group2=(y,n,n,n)
group3=(y,n,n,n)
group4=(y,n,n,n) ;
list ACL _all_ ;
run ;

Bringing a Domain Offline to Refresh Tables

When it is time to refresh the table(s), one approach to minimize contention and table locking is to revoke privileges of users and groups who will not be involved in the refreshing of tables in the domain.

This example assumes that the tables are already loaded in the domain and that the groups who use them have access.

  LIBNAME d2 sasspds 'd2'
    server=zztop.5162
    user='prod1'
    password='spds123'
    IP=YES ;

  PROC SPDO library=d2 ;

/* Assign who owns the ACLs */

   set acluser prod1 ;

It is possible to revoke read access at the LIBNAME or domain level, which allows the IDs that are used to refresh the warehouse complete control of resources in the domain. This example turns off all read access to the domain, except for IDs that are in the production group (prodgrp).

By doing this, the production IDs have full control over the tables and resources.

Note: Any user that is currently accessing the domain will continue to have access until they are disconnected. This can cause a lock to occur. The PROC SPDO special operator commands can be used to identify the user and disconnect the process so the refresh can take place.

modify ACL / LIBNAME
prodgrp=(y,y,y,y)
group1=(n,n,n,n)
group2=(n,n,n,n)
group3=(n,n,n,n)
group4=(n,n,n,n);
list ACL _all_ ;
run ;

/* Modify ACL for tables to be refreshed */

PROC SPDO library=d2 ;

/* set who owns the ACLs */

set acluser prod1 ;

/* Modify table ACL to revoke read and */
/* control by user IDs in same group, */
/* which prevents locks during table */
/* refreshes. */

modify ACL prod1_table /
prodgrp=(n,n,n,n);

/* Modify table ACL to allow the */
/* 'prod1' user ID to refresh the */
/* table. */

modify ACL prod1_table /
prod1=(y,y,y,y) ;
list ACL _all_;

/* refresh warehouse table(s) */

data d2.prod1_table ;
do i = 1 to 100 ;
output ;
end ;
run ;

PROC SPDO library=d2 ;

/* Assign who owns the ACLs */

set ACLUSER prod1 ;

/* Allow users and groups access to */
/* the domain again. */

modify ACL / LIBNAME
group1=(y,n,n,n)
group2=(y,n,n,n)
group3=(y,n,n,n)
group4=(y,n,n,n) ;

list ACL _all_ ;
run ;

ACL Special Users

SPD Server user IDs have two levels, 0 through 3 and 4 through 7. Level 4 through 7 user IDs can log in as an SPD Server 'super user' that can:

access any table
change table ACLs
disconnect users
perform administrative functions in a pinch

In many ways, SPD Server super users must be able to take on database administrator functions. The SPD Server super user cannot change the ownership of a table but they can assume the identity of the table owner to do required work. Often, this function happens in a pinch when a user needs access and the table owner or domain owner is out of the office.

The following should be considered when giving a user SPD Server super user status:

The user must be trusted, because SPD Server super users can access any data in any domain
How many SPD Server super users do you want? Limit the number in order to maintain control access.
SPD Server super users must be knowledgeable about the data and the database users' needs.

Assume the table user1_table1 is loaded, and only read permissions have been given to users in group1. User4 is a member of group4, and group4 does not have read access to the table. User1 is the owner of user1_table1 in domain d2. User1 is on vacation and user4 has been given an assignment which requires read access to the user1_table1 to create a report for management.

Management has approved user4 access to the table. The super user prod1 uses the ACLSPECIAL= option to modify the ACLs and to give user4 read access to the table.

     LIBNAME prod1d2 sasspds 'd2'
       server=zztop.5162
       user='prod1'
       password='spds123'
       aclspecial=YES
       IP=YES ;

     PROC SPDO library=prod1d2 ;

/* assign to the user to who owns   */
/* the ACL that will be modified    */

     set acluser user1 ;

/* give user ID 'user4' read access */
/* to user1_table1                  */

     modify ACL user1_table1 /
       user4=(y,n,n,n) ;
     list ACL _all_ ;
     quit;

Column-Level Security

The goal of column-level security is to allow only privileged users to access sensitive columns of tables that other users cannot read.

LIBNAME user1 sasspds 'onepath' server=zztop.5161 user='user1' 
       password='spds123';
LIBNAME user2 sasspds 'onepath' server=zztop.5161 user='user2' 
       password='spds123' aclgrp='group2';
LIBNAME user6 sasspds 'onepath' server=zztop.5161 user='user3' 
       password='spds123' aclgrp='group2';

/* generate some dummy data */
data user1.t;
id=1;
salary=2000;
run;

/* Example of only user2 in group2 */
/* being allowed to read column    */
/* salary                          */

PROC SPDO library=user1 ;

/* Assign who owns the ACLs */
set acluser;

/* Clean Up */
delete ACL t;
delete ACL t.salary;

/* Create an ACL on table t to     */
/* allow members of group2 to read */
/* table                           */

add ACL t;
modify ACL t / group2=(y,n,n,n);

/* Create an ACL on column t.salary*/
/* to only allow user2 of group2 to */
/* read the column                  */

add ACL t.salary;
modify ACL t.salary / group2=(y,n,n,n);
quit;

/* Let both users print the table */
/* Only user2 can access column    */
/* salary                           */

proc print data=user2.t;
run;

proc print data=user6.t;
run;

/* Example of every BUT user2 in */
/* group2 being allowed to read  */
/* column  salary               */

PROC SPDO library=user1 ;

/* Assign who owns the ACLs */
set acluser;

/* Clean Up Column ACL */
delete ACL t.salary;

/* Create an ACL on column t.salary*/
/* to only allow members of group2 to */
/* read the column                  */

add ACL t.salary;
modify ACL t.salary / user2=(y,n,n,n);


/* User permissions have priority over */
/* group permissions.  So now deny     */
/* user2 access to column salary       */

modify ACL t.salary / user2=(n,n,n,n);
quit;

/* Let both users print the table */
/* Only user6 can access column    */
/* salary                           */

proc print data=user2.t;
run;

proc print data=user6.t;
run;
quit;