ACL Examples :: SAS® Scalable Performance Data Server 5.3: Administrator's Guide

Overview of Examples

This section contains examples of PROC SPDO requests that create and modify ACLs to perform common security tasks. They show the following:

how to secure a domain with a domain ACL, as well as how authorized users can modify the domain ACL
the difference in permission precedence when LIBACLINHERIT is set versus when it is not
how the user account can be used
how generic ACLs might be used
how group assignments might be made
how to bringa table or a whole domain offline in order to refresh tables
how to define and alter column security.

The examples assume that the following domains are defined in the libnames.parm parameter file:

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 ;

The examples assume that the following users exist in the password database:

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

Create a Domain ACL

When OWNER= is set on a domain in the libnames.parm parameter file, no other user besides the one specified as OWNER= can access the domain unless the user is given permissions by the owner. The domain owner can grant permissions to access a domain by defining a domain ACL. The following code creates a domain ACL for domain d2 to grant access permissions to different groups.

/* Libref d2 is assigned to connect to     */
/* domain d2 as domain owner (prod1).      */

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

/* PROC SPDO connects to libref d2. */

PROC SPDO library=d2 ;

/* The session context is set to the domain owner.  */

  set acluser prod1 ;

/* The ADD ACL statement creates the domain ACL */

  add ACL / LIBNAME ;

/* The MODIFY ACL statement modifies the domain ACL */
/* to grant group permissions to domain d2. ProdGrp  */
/* is granted full access to the domain, including */
/* ACL access.                                         */
  modify ACL /
  LIBNAME prodgrp=(y,y,y,y)
  group1=(y,y,n,n)
  group2=(y,n,n,n)
  group3=(y,n,n,n) ;

/* Specific users are given access to the domain. */
  modify ACL /
  LIBNAME user7=(y,n,n,n)
  admin1=(y,n,n,n) ;
  list ACL _all_ ;
quit ;

Connect to the Domain ACL as an Authorized User

The user Prod2 is in ProdGrp, the group that has permissions to control the domain ACL. Any user in that group can modify the domain ACL.

However, because the ACL was created by user Prod1, the user Prod2 must use the user ID Prod1 to modify the domain ACL. This is allowed because the group was given control. User Prod1 still remains the owner of the domain ACL.

/* Libref prod2d2 is created to connect to domain d2 */
/*  as user prod2 */
  libname prod2d2 sasspds 'd2'
  server=zztop.5162
  user='prod2'
  password='spds123'
  IP=YES ;

PROC SPDO library=prod2d2 ;

/* The ACLUSER statement sets the session */
/* context to 'prod1', who owns the */
/* ACL to be modified */

  set acluser prod1 ;
/* The domain ACL is modified to deny Group1 */
/* access to the domain and to give Group 4 */
/* Read-only access. */
  modify ACL /
  LIBNAME group1=(n,n,n,n)
    group4=(y,n,n,n) ;
  list ACL _all_ ;
quit ;

Modify the Domain ACL with Special Privilege

A user who has been given special privilege in the password database can also change domain ACLs. In the following example, the user Admin1 modifies the domain ACL. The user activates his or her privilege to operate under the user ID of another user by specifying the ACLSPECIAL= option in the LIBNAME statement. As in the previous example, the user Admin1 must set the session context to the user ID Prod1 to modify the ACL.

/* Libref admin1d2 is created to enable user */
/* Admin1 to connect to domain d2.   */
  libname admin1d2 sasspds 'd2'
  server=zztop.5162
  user='admin1'
  password='spds123'
  ACLSPECIAL=YES
  IP=YES ;

PROC SPDO library=admin1d2 ;
  set acluser prod1 ;

/* The MODIFY ACL statement specifies a grouplist */
/* that gives AdminGrp Read-only access to the domain */
  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 parameter file, the ACL precedence of permission checks changes for resources that belong to the domain owner. Specifying LIBACLINHERIT=YES in a domain definition extends the permissions that are defined in the domain ACL to the owner’s resources. For detailed information about LIBACLINHERIT, see Configuring Server Domains.

The following example defines two domains: one that has LIBACLINHERIT=YES defined and one that does not. Then, it creates a table in each domain, and creates domain ACLs on each domain that enable user Anonymous to print the tables. Implement the code at your site to see the difference in how permissions are applied.

Here are statements for the libnames.parm parameter file:

LIBNAME=LIBINHER
    pathname=/IDX1/spdsmgr/spds41test/libinher 
    LIBACLINHERIT=YES                          
    owner=admin;                               
 LIBNAME=noinher                               
    pathname=/IDX1/spdsmgr/spds41test/noinher  
    owner=admin;

Here is code that creates the tables and ACLs and prints the tables:

/* Connect to libinher as admin. *
ibname libinher sasspds 'libinher'
   server=zztop.5129
   user='admin'
   password='spds123';

/* Connect to noinher as admin. */
libname noinher sasspds 'noinher'
   server=zztop.5129
   user='admin'
   password='spds123';

/* Create tables. */
data libinher.admins_table
     noinher.admins_table ;

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


/* Set up access for user anonymous.    */

/* Create domain ACL for domain libinher */
PROC SPDO library=libinher;

set acluser admin;
add acl / LIBNAME;

/* Allow users read-only */
/* access to the domain.             */
modify acl / LIBNAME read;
list acl _all_;
quit;


/* Create domain ACL for domain noinher. */
PROC SPDO library=noinher;

set acluser admin ;
add acl / LIBNAME ;

/* Allow users read-only  */
/* access to the domain.    */

modify acl / LIBNAME read ;
list acl _all_;
quit;

/* Connect to domains as user anonymous. */
libname a_inher sasspds 'libinher'
   server=zztop.5129
   user='anonymous';
libname a_noher sasspds 'noinher'
   server=zztop.5129
   user='anonymous';

/* Print the tables. */
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

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. Any table that is created by the Anonymous user can be viewed by all users who have access to that table's domain. The Anonymous user can place ACLs on a 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 the anonymous user   */
/* can have ACLs.                       */

     PROC SPDO library=john ;

/* assign who owns the ACL */

     set acluser anonymous ;

/* The MODIFY statement sets an ACL so */
/* only user '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 '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 ;

     proc print 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 ;

Domain Security and Administration Example

A common security measure assigns an SPD Server user ID to act as the owner of a domain and to provide control over it. Typically, one or two user IDs are defined to 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 user ID for data administration spreads responsibility and 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 domain 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 ACL 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 owns the ACLs. */

      set acluser admin1 ;

The two ACL commands in the following code give Read privileges to members of the ACL group AdminGrp 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 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 different groups of users access to the domain owned by the user 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 in AdminGrp is granted Read, Write, and Alter access to the domain.
Any user in Group1 or Group2 is granted Read access to the domain.
Any user in Group3 or Group4 is granted Read and 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 domain 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 domain ACL for groupread  */
/* and groupwrite.  The ACL MUST    */
/* include groupread to enable       */
/* other users  in the same group    */
/* as admin2 to be able to read      */
/* tables that were created by      */
/* admin2.                          */

       add ACL admin1_table1 /
         generic
         read
         groupread
         groupalter ;

       list ACL _all_;
       run;

/* a\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 domain 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 admin2.  The */
/* groupread and groupalter allow     */
/* access by users within admingrp.   */

     PROC SPDO library=admin2d1 ;

/* Assign who owns the ACLs. */

     set acluser admin2 ;

/* Modify domain 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 domain 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 users, except the user 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 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 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 user Prod1 to perform table refreshes. Because user Prod1 is part of the group ProdGrp, Prod1 loses access to the table when the permissions are changed. As the domain and table owner, Prod1 can still modify ACLs to gain access.
```
modify ACL prod1_table /
prod1=(y,y,y,y) ;
list ACL _all_;
quit;
```
Now user 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.
Alternatively, you can revoke Read access at the 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 operator commands to identify the user and disconnect the process so that the refresh can occur.

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 ;

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 users 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 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 ;

Special User Example

SPD Server users are divided into two levels: 0 through 3 and 4 through 7. Level 4 through level 7 users 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. SPD Server super users can access any data in any domain, so this special privilege should be given to few users and should be granted with extreme care.

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 '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 / user2=(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;