ACL Security Examples :: SAS(R) Scalable Performance Data Server 5.1: Administrator's Guide, Second Edition

Overview of Security Examples

Precedence of permission checks includes inheriting the permissions of the LIBNAME ACL for resources owned by the domain owner. The LIBNAME ACL is used first to grant LIBNAME access to the domain, and then to inherit ACLs to resources that belong to the domain owner. When a user attempts to access resources in a domain in which the domain owner specifies LIBACLINHERIT=YES, the following ACL precedence of permissions checks are made on the resource:

If user-specific ACLs are defined on the object for the user, the user gets these permissions.
If group-specific ACLs are defined on the object for the user’s group, the user gets these permissions.
If LIBNAME ACL permissions are defined for the user and the resource belongs to the OWNER= of the domain, then the user gets the domain LIBNAME ACL permissions on the object.
If LIBNAME ACL permissions are defined for the user’s group and the user is a member of the OWNER= group of the domain, then the user gets the LIBNAME ACL group permissions on the object.
Otherwise, the user gets UNIVERSAL ACLs on the resource.

The following listing contains the libnames.parm files that are used in the code examples, and a list 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 Examples

When you specify the libname.parm option OWNER=, 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 following code example 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 that 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 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 ;

A user who has ACL special privileges can also change the LIBNAME ACL s. In the following example , 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 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 Example

If the LIBACLINHERIT domain option is turned on for a domain in the libnames.parm file, the ACL precedence of permission checks changes for resources that belong to the domain owner. Turning on LIBACLINHERIT creates a LIBNAME ACL on the specified LIBNAME domain. For information about LIBACLINHERIT permissions and how they affect ACL precedence of permission checks, see Controlling the Precedence of Permission Checks with the LIBACLINHERIT= Option and the OWNER= Option.

The following example uses 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 Example

The SPD Server uses a general ID that is called anonymous. Any person who 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, use the psmgr utility to add a user called anonymous 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 can 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 Examples

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 grant different groups access to the domain and 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 following code enables the following actions:

Any user who is in the same group as admin can read, write, or alter tables and can modify the LIBNAME access to the domain.
Users in group1 and group2 are granted Read access to the domain.
Users in group3 and group4 are granted 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 following code give Read privileges to members of the ACL group ADMIN1 for any table that is created by the user admin1. The user admin1 has Read access to the domain.

This ACL is a good example for data marts and warehouses that do not contain sensitive data. A generic ACL gives broad access to tables in a domain. You must use generic ACLs correctly (or not at all) if you need to restrict access to sensitive data 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 following code provides the following access:

gives group1 Read access to admin1_table2.
gives admingrp Read and Control access to admin1_table2.
prevents users who are not granted specific access to admin1_table2 from reading, writing, altering, or controlling the table. The ACL in the code takes precedence over the generic Read ACL.

    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 Example

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 to individual users. This example shows how to grant access to different groups of users access to the domain owned by the user ID admin, and then extends the access to the tables. Granting permissions in this way 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 grants the following user access:

Any user ID in admingrp is grantedRead/Write/Alter access to the domain.
Any user ID in group1 or group2 is granted Read access to the domain.
Any user ID in group3 or group4 is granted 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_;

Bring a Table Offline to Refresh

The following scenario explains how to bring a table offline and refresh it:

Revoke Read access 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:
1. Revoke Read and Control access by user IDs that are in the same group. This step prevents locks during table refreshes.
2. 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 continue to run and can finish under the lock.
  
  Tip
  You can also use the special PROC SPDO operator commands to identify any users that might be running unattended jobs, and disconnect them so that 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) ;
Modify table ACLs to allow the user ID prod1 to perform table refreshes. Because user ID prod1 is part of the group 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 ;
```

After the table has been refreshed, modify the ACL to allow Read access 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 ;

You do not need to issue an ADD ACL command for prod1_table. When you delete or replace a table, you do not delete the ACLs. The ACL for that table remains until one of the following actions has occurred:

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

If one of these actions occurs, 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.

Bring a Domain Offline in Order to Refresh Tables

You can approach this type of table refresh in two ways.

You can minimize contention and table locking by revoking privileges of users and groups who will not be involved in the refresh process.

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

  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 ;

Alternatively, you can revoke Read access at the LIBNAME or domain level, which allows the IDs that are used to refresh the warehouse to have 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). This approach allows the production IDs to have full control over the tables and resources.

Note: Any user who is currently accessing the domain continues to have access until they are disconnected. This situation can cause a lock to occur. You can use the PROC SPDO special operator commands to identify the user and disconnect the process so that the refresh can occur.

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 Example

SPD Server user IDs are divided into two levels, 0 through 3 and 4 through 7. Level 4 through level 7 user IDs can log on as an SPD Server super user who can do the following tasks:

access any table
change table ACLs
disconnect users
perform administrative functions when necessary

SPD Server super users can perform database administrator functions. SPD Server super users cannot change the ownership of a table, but they can assume the identity of the table owner to do required work. This type of situation occurs when a user needs access and the table owner or domain owner is out of the office.

Before you give a user SPD Server super user status, consider the following questions:

Do you trust this user? 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.
Is this user knowledgeable about the data and the database users' needs?

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

Management has approved user4 for 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 Example

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

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;