Providing Fine-Grained Access Using Views

Overview

You can create views that give users access to some but not all of the data within a physical table. Use the following approach:

If the physical table and its parent library are not already bound to metadata, bind them.
Create a view that excludes the rows or columns that you want to hide.
Apply the password of the underlying physical table to the view.
Set metadata-layer permissions to control who can access the view.

Column-Level Access

In this example, partial access to a customer data table is provided by creating a view and managing access to it. The view keeps the name and telephone number columns from the underlying table, but excludes the credit card number column.

options metauser="sasadm@saspw" metapass="********"
   metaserver="machine.company.com";

libname cust 'path';

proc authlib library=cust;
   create
      securedlibrary='cust'
      securedfolder='CustomerData'
      pw=secret;
quit;

proc sql; 
   create view cust.PUBLIC as
      select Name, Phone
      from cust.PRIVATE(pw=secret);
quit;

The preceding code creates a new secured library object (CustomerData) that contains two objects: a table object (called PRIVATE) and a view object (called PUBLIC).

Note: The password that is supplied to bind the library is also supplied when the PUBLIC view is defined against the PRIVATE table. In order to create a view of a metadata-bound table, you must know the password of that physical table’s parent library, and provide that password in the view definition. You can enable end users to access the view without giving them access to the underlying table. In effect, this provides selective access to the columns and rows within the underlying table.

Note: If you modify the password for the metadata-bound library, you must also update the view definition with the new password. Until you have time to redefine all of the views to use the new password, you can retain the old password in metadata by deselecting Automatically purge old library credentials in the Modify Secured Library dialog box. The old password is retained until you use the PURGE statement to remove it, or until you later modify the library with the Automatically purge old library credentials check box selected.

To complete the protection, use SAS Management Console to set metadata-layer permissions so that restricted users can access the PUBLIC view but not the PRIVATE table. For example, if only unrestricted users should access the PRIVATE table, you might use the following approach:

On the Authorization tab for the CustomerData folder, verify that the PUBLIC group is denied the ReadMetadata, WriteMetadata, WriteMemberMetadata, and Select permissions. Verify that the PRIVATE table inherits these denials.
On the Authorization tab for the PUBLIC view object, explicitly grant the ReadMetadata and Select permissions to SASUSERS.

Identity-Driven, Row-Level Access

In this example, partial access to an employee information table (HR.EMPINFO) is provided by creating a view (HR.PERSONAL) that dynamically filters rows in the underlying table. The filtering is based on each requesting user’s authenticated user ID. The filtering relies on a security associations table, which maps each user’s authenticated user ID to a corresponding employee ID.

The following code creates the identity-driven view of the employee information table. When requesting users access the view, they retrieve only those rows that match the user ID with which they authenticated to the metadata server.

proc sql; 
   create view hr.personal as
      select a.*
      from hr.empinfo(pw=secret) a,
           hr.security(where=(loginid=_METADATA_AUTHENTICATED_USERID_)) b
      where b.loginid ne '' and a.empid = b.empid;
quit;

Here are some details about the preceding code:

The code assumes that the HR libref is already established and points to a metadata-bound library that has a single password value of secret.
The reference to the EMPINFO table must supply the password (secret) in order to create the view, because the table is bound to metadata.
SECURITY is a security associations table that maps all valid _METADATA_AUTHENTICATED_USERID_ values to the primary key of the target table (the EMPID column in the EMPINFO table).

Note: As an alternative to creating a separate security associations table, you could directly add a column of _METADATA_AUTHENTICATED_USERID_ values to your target table.
_METADATA_AUTHENTICATED_USERID_ is a substitution parameter that supplies a user-specific value in each request, based on the user ID with which the requesting user authenticated to the metadata server.
The _METADATA_AUTHENTICATED_USERID_ substitution parameter is used in a WHERE clause that is expressed as a data set option.

If you want to provide broader access to certain users (for example, to enable department managers to see information about their employees), you can enhance the SECURITY table to include a column that maps employees to departments, create an additional view that exploits that mapping, and set metadata-layer permissions so that only department managers can use the new view.

Providing Fine-Grained Access Using Views

Overview

Column-Level Access

Identity-Driven, Row-Level Access

See Also