Row-Level Security

Introduction

Row-level security (RLS) for SAS Federation Server provides additional security on tables and views by restricting data access on a row-by-row basis. Row-level security allows selection of specific rows for a given set of users and groups. Because row-level security applies only to rows that can be selected, its implementation is a function of the SELECT privilege. The SELECT privilege can be granted without restriction, or with a predicate applied. When a predicate is applied, you are implementing row-level security because the predicate now restricts what rows are returned.

For example, an administrator might choose to grant the SELECT privilege to USER1 on table T1. In this case, USER1 is allowed to see all the data in the table. However, an administrator might allow USER1 to select only from rows where a column or set of columns meet certain criteria, such as for the northeast sales region. In this case, the GRANT statement is:

GRANT SELECT ON TABLE CATALOG.SCHEMA.T1 TO SALES 
WHERE SALES_REGION = 'NORTHEAST'

When members of the SALES group select from table T1, the predicate is automatically attached to the table. When BOB, a member of the SALES group, issues the statement,

SELECT
                     * FROM T1

, the query is effectively transformed to

SELECT
                     * FROM T1 WHERE SALES_REGION = 'NORTHEAST'

Reference Administration DDL for syntax details about the GRANT statement.

When the predicate is automatically attached to the table, it must contain a valid WHERE clause. The syntax can include sub-queries and references to other tables. However, any external data referenced in the predicate must be available on the user’s connection and the user must have the SELECT privilege to access the data.

If the data is coming from a different data source, then:

The user’s DSN must scope to that data source.
The user must have SELECT privilege on the referenced data.

For example, a user can select rows only from a table in which the user’s name is listed in the “USER NAME” column of the table. To apply this rule to all members of the SALES group, an administrator issues the following GRANT statement:

GRANT SELECT ON TABLE CATALOG.SCHEMA.T1 TO SALES WHERE 
SYSCAT.RLS.CURRENT_USER() = \"USER NAME\"

When the user BOB in the SALES group selects from the table,

SELECT * FROM
                     T1

, the query is transformed to:

SELECT * FROM T1 WHERE 'BOB' = "USER NAME"

The RLS Library and Library Reference contains details about callable functions for row-level security.

Row-Level Security Privilege Assembly Rules

Overview

The SELECT privilege for a user can be derived through group memberships. A user can be a member of multiple groups, each granting SELECT privilege with attached row-level security. One exception is the schema owner. Since a schema owner effectively has all privileges granted, group membership is not traversed for privileges at the schema level.

RLS predicates are assimilated at each level in the securable hierarchy, starting with the table or view object and progressing to the server object.

The procedure is repeated over each object in the authorization hierarchy:

the current user. If the current user has an RLS condition applied, no other RLS conditions are considered.
any groups of which the current user is a member.
followed by USERS and PUBLIC only if no other RLS conditions were discovered.

If an RLS predicate is discovered at a specific level, it is combined with the other RLS predicates at that level only, using an OR operator, and the process stops. The procedure produces predicates representing the summation of RLS privileges closest to the user. This approach gives preference to organizational security policies closer to the user over those more distant. An unconditional GRANT or DENY at the same level as an RLS predicate, will be honored and all RLS predicates will be ignored.

Example: RLS Predicate Union

In this example USER is a direct member of the user-defined groups GROUP1, GROUP2 and GROUP4. USER is also a member of the two built in groups, USERS and PUBLIC. The RLS query returned for USER is (RLS1 OR RLS4 OR RLS3).

Note: Blue nodes contain a conditional privilege; gray nodes contain NO privilege.

RLS Predicate Union

The first predicate, RLS1, is encountered at level 1 in the graph, so the remaining RLS predicates are captured at that level for the current graph, which does not include the secondary graphs, USERS and PUBLIC. The GROUP1 node is marked as visited, and its parent (“member of”) associations will not be navigated since it contributed a predicate.

After marking it as visited, the procedure skips GROUP2 since it has no assigned privileges and proceeds to GROUP4 where the node is marked as visited and RLS4 is captured and combined to the query using an OR operator. There are no more nodes at level 1, so the procedure continues with the parent associations of GROUP2.

GROUP3 is marked as visited and predicate RLS3 is captured and combined to the query using an OR operator. The privilege has been satisfied at this point because an RLS query is available. The two graphs starting at USERS and PUBLIC are not searched.

The resulting RLS query is: RLS1 OR RLS4 OR RLS3.

The RLS Library and Library Reference

Overview

Use the RLS library to look up functions that reference your data source. The RLS library resides in the SYSCAT.RLS catalog in the Federation Server Database. The general syntax is SELECT SYSCAT.RLS.RLS_function('name'). For example:

SELECT SYSCAT.RLS.group_id('GROUP1')

RLS Functions

In addition to row-level security, RLS Library user functions can be used with other SAS Federation Server tasks such as FedSQL views and queries by including an RLS function in your SELECT statement: SYSCAT.RLS.RLS_function. Following are the callable functions for row-level security.

auth_id

Returns the authentication provider identifier for the specified user or group name.

current_user

Returns the name of the current user.

current_id

Returns an opaque authentication provider specific user identifier.

domain

Returns the name of the domain in which the current user is authenticated.

group_id

Returns the authentication provider group identifier for the specified group name.

login

Returns the domain-qualified user id that is used to authenticate the current user.

userid

Returns the SANs domain user id that is used to authenticate the current user. Note that the userid function is similar to the login function, but it is not domain-qualified.

ip_addr

Returns the client IP address of the current user’s session.

is_admin

Returns TRUE or FALSE indicating if the current user is an administrator.

is_process_user

Returns TRUE or FALSE indicating if the current user is the process user.

member_of

Returns TRUE or FALSE indicating if the current user is a member of the specified group.

groups

Returns a single group name or result set identifying the group memberships of the current user.

The following example uses RLS functions current_user and member_of to qualify users for SELECT on specific rows in the HR.EMPLOYEES table.

grant SELECT on HR.EMPLOYEES to USERS
  where (syscat.rls.current_user() in ("Name","MgrName"))
   or syscat.rls.member_of("Payroll",'DEEP')

Authenticated users must meet the following conditions:

The name of the current user matches that of the “Name” column (the row pertains to the current user).
or the name of the current user matches that of the “MgrName” column (the row pertains to the manager of the current user).
or the current user is a direct or indirect (DEEP) member of the “Payroll” group.

RLS Library Reference

Following are the data formats for the row-level security user functions described above.

WVARCHAR(n) auth_id(WVARCHAR(n) [[authorization])

Returns an authentication identifier as defined by the authentication provider, as a result of passing input for user name.

WVARCHAR(n) current_user()

Returns the name of the current user. This is the authorization identifier of the currently authenticated user, rather than the login used.

WVARCHAR(n) current_id()

Returns a user identifier as defined by the authentication provider. Typically, this is a static identifier by which the current user is known. Applications can associate this identifier with an internal organization user identifier such as an employee number or account number.

WVARCHAR(n) domain()

The name of the domain in which the current user is authenticated.

WVARCHAR(n) group_id(WVARCHAR(n) [[authorization])

Returns a group identifier as defined by the authentication provider, as a result of passing input for group name

WVARCHAR(n) login()

The login used to authenticate the current user.

WVARCHAR(n) userid( BITupn )

The domain qualified user ID. If the upn parameter is TRUE, the format of the returned user ID is user@domain. Otherwise, the format is domain\user on Windows systems and just userid on all other systems. The userid function returns the authenticated user ID as specified by the authentication service. The authentication service can reside on a different host

WVARCHAR(n) ip_addr()

Returns the client IP address of the current user’s session.

BIT is_admin()

Returns TRUE or FALSE if the current user is or is not an administrator.

BIT is_process_user()

Returns TRUE or FALSE if the current user is, or is not the process user.

BIT member_of( WVARCHAR(n) group [, WVARCHAR(n) options] )

Returns TRUE or FALSE if the current user is, or is not a member of the specified group. Can assert direct or indirect membership. The group parameter is a group name by default and a group identifier if the ‘ID’ or 'DEEP' option is present in the options string. The options string is a blank or comma separated string consisting of one or more of ‘ID’ and ‘DEEP’ option keywords. The current user must be a direct member of the specified group unless the ‘DEEP’ keyword is specified. ‘DEEP’ checks for both direct and indirect group membership. Direct membership is tested by default.

TABLE(WVARCHAR(n) group) groups( WVARCHAR(n) [[authorization] WVARCHAR(n) [, options]]

Returns a single group name or identifier column result set containing the current user’s group memberships. The available options are ‘ID’ or ‘DEEP’. Can be restricted to direct memberships only. The authorization parameter is a user or group name by default and a user or group identifier if the 'ID' option is present in the options string. The options string is a blank or comma separated string consisting of one or more of ‘ID’ and ‘DEEP’ option keywords. A deep group membership listing is returned if the ‘DEEP’ keyword is specified, the default being a shallow listing.

Note: A trusted user must be set in order for the GROUPS function to return a result set if you pass in a user other than the current user. If you pass in the current user or a group as the first argument to GROUPS trusted user is not required.

Using the ‘ID’ and ‘DEEP’ Options

This topic outlines the behavior of the ‘ID’ and ‘DEEP’ options when used with the member_of and groups RLS functions. Consider the following queries:

SYSCAT.RLS.GROUPS(user_name_or_id [, options])

Returns a group membership list for user_name_or_id.

SYSCAT.RLS.MEMBER_OF(group_name_or_id [, options])

Returns TRUE if the current user is a member of the group specified in group_name_or_id.

The following behavior applies:

user_name_or_id returns a string literal indicating the user name or user ID. If options contains ‘ID’, the argument is treated as a user ID ('6C6C9AD1E2646F0469DD6A3D1874D167') rather than a user name ('USER1').
group_name_or_ID returns a string literal indicating the group name or group ID. If options contains ‘ID’, the argument is treated as a group ID ('78319AD1E2646F0469DD6A3D1874A2F7') rather than a group name ('GROUP1').
options returns a string literal containing 'GROUP', 'ID', or both ( 'GROUP, ID'). If multiple options are specified, they can be separated in the string by a blank in single quotation marks (' ') or comma in single quotation marks (','). If options contains 'ID' the argument is treated as an ID rather than a name. If options contains ‘DEEP’, group membership is checked for direct and indirect membership.

For example, consider that USER1 is a member of GROUP1 and GROUP1 is a member of GROUP2, and USER1 runs the following queries:

Select SYSCAT.RLS.GROUP_ID('GROUP1')

Returns 'BEA892C6D4A40464C8A144D89FFE6463'

Select SYSCAT.RLS.GROUP_ID('GROUP2')

Returns '45C562900C7333C49B1706B38DBA75B0'

Select SYSCAT.RLS.CURRENT_ID()

Returns '5790EE3F6A24A7349AA2254600793411' for USER1

Select SYSCAT.RLS.MEMBER_OF('GROUP1')

Returns TRUE for USER1.

Select SYSCAT.RLS.MEMBER_OF('GROUP2')

Returns FALSE for USER1.

Select SYSCAT.RLS.MEMBER_OF('GROUP2', 'DEEP')

Returns TRUE for USER1.

Select SYSCAT.RLS.MEMBER_OF('BEA892C6D4A40464C8A144D89FFE6463', 'ID')

Returns FALSE for USER1.

Select SYSCAT.RLS.MEMBER_OF('45C562900C7333C49B1706B38DBA75B0', 'ID')

Returns TRUE for USER1.

Select SYSCAT.RLS.MEMBER_OF('BEA892C6D4A40464C8A144D89FFE6463', 'DEEP,
                                 ID')

Returns TRUE for USER1.

The following queries use the GROUPS function which returns a result set:

Select * from SYSCAT.RLS.GROUPS('USER1')

Returns a result set showing direct group membership for USER1:

"GROUP"
'USERS'
'GROUP1'
'PUBLIC'

Select * from SYSCAT.RLS.GROUPS('USER1', 'DEEP')

Using DEEP in the SELECT statement returns a result set showing direct and indirect group membership for USER1:

"GROUP"
 'USERS'
 'GROUP1'
 'GROUP2'
 'PUBLIC'