Row-Level Security

Introduction

Row-level security (RLS) for SAS Federation Server provides additional granularity of security on tables and views. It allows only certain rows to be selected for a given set of users and groups. Because row-level security only applies to rows that can be selected, it is implemented as part of the SELECT privilege. The SELECT privilege can be granted without restriction, or with a predicate applied. When a predicate is applied, this is called row-level security because the predicate will restrict the rows returned.

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

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

In this case, when members of the group SALES select from table T1, the predicate is automatically attached to the table when BOB, a member of the SALES, issues the statement:

SELECT * FROM T1

His query is effectively transformed to:

SELECT * FROM T1 WHERE SALES_REGION = 'NORTHEAST'

Reference Administration DDL for syntax details about the GRANT statement.

Because 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 for 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 can issue 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 group SALES 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 will produce 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 Federation Server Database as SYSCAT.RLS. The general syntax is SELECT syscat.rls.RLS_function('name'). For example:

SELECT syscat.rls.group_id('GROUP1')

RLS Library

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.

The following table lists the callable functions for row-level security:

Function	Description
auth_id	Returns the authentication identifier.
current_user	Returns the name of the current user.
current_id	Returns an opaque authentication provider specific user identifier.
domain	The name of the domain in which the current user is authenticated.
group_id	Returns the group identifier
login	The domain-qualified login used to authenticate the current user.
userid	Similar to login. However, userid is not domain-qualified.
ip_addr	Returns the client IP address of the current user’s session.
is_admin	Returns TRUE (FALSE) if the current user is (not) an administrator.
is_process_user	Returns TRUE (FALSE) if the current user is (not) the process user.
member_of	Returns TRUE (FALSE) if the current user is (not) a member of the specified group
groups	Returns a single group name or identifier column result set containing the current user’s group memberships.

Library Reference

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 FedSQL 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. 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.

Usage Notes for the member_of and groups functions returning ‘ID’ or ‘DEEP’ member_of and groups functions:

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

Lists the groups that user_name_or_id is a member of.

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

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

user_name_or_id: a string literal indicating the user. If options contains ‘ID’ this will be treated as a user ID (for example, '6C6C9AD1E2646F0469DD6A3D1874D167'). Otherwise, this will be treated as a user name (for example, 'USER1').
group_name_or_id: a string literal indicating the group. If options contains ‘ID’ this will be treated as a user ID (ex: '78319AD1E2646F0469DD6A3D1874A2F7'), otherwise this will be treated as a user name (ex: 'GROUP1').
options: a string literal containing 'GROUP', 'ID', or both (ex: 'GROUP, ID'). If multiple options are specified, they can be separated in the string by a blank ' ' or comma ','. If options contains 'ID' the first argument is treated as an ID rather than a name. If options contains ‘DEEP’ group membership will be checked recursively.

For example, consider that USER1 is a member of GROUP1 and GROUP1 is a member of GROUP2. 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 against GROUPS return a results set:

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

 "GROUP"

 `USERS`

 `GROUP1`

`PUBLIC`

Select * from SYSCAT.RLS.GROUPS('USER1', 'DEEP') returns
   "GROUP"
   'USERS`
   `GROUP1`
   ‘GROUP2’
   `PUBLIC'