Object Privilege Inheritance :: SAS(R) Federation Server 4.1: Administrator's Guide

About Object Privilege Inheritance

SAS Federation Server contains an inherent hierarchy of objects, in the following order:

SAS Federation Server Privilege Inheritance

Where privileges on the server are inherited by the data service, privileges on the data service are inherited by the DSN and catalog, and so on. This inheritance hierarchy allows an administrator to set general security rules on higher level objects, and then only set exceptions on the more specific (subordinate) objects.

SAS Federation Server Container Object Inheritance

For example, there is a group called SALES_GROUP whose members are allowed to select most objects in the SALES_DATA data service. An administrator can assign SELECT privilege on the SALES_DATA data service to the SALES_GROUP. The SELECT privilege is inherited on all the catalogs of the SALES_DATA data service, and all the schemas of those catalogs, and all the related tables and views. There is a stipulation that the SALES_GROUP is not allowed to see any data in a single catalog of the SALES_DATA data service called EXECUTIVE_DATA. An administrator could then deny SELECT privilege to the SALES_GROUP on that particular catalog. Members of the SALES_GROUP would then not be able to select any data from the EXECUTIVE_DATA catalog or any of its schemas. An administrator can elect to grant all privileges on the EXECUTIVE_DATA catalog to the EXECUTIVE_GROUP. An administrator can also deny SELECT privilege to any member of the EXECUTIVE_GROUP on any subordinate object of the EXECUTIVE_DATA catalog. In this way, general authorizations are defined on higher level objects, and then exceptions are set on subordinate objects. This minimizes the number of privileges that an administrator must establish, and thereby reduces administration overhead. For any object in the hierarchy of SAS Federation Server, an administrator can request information about privileges held by any user or group, including where in the hierarchy the privilege was set and who the grantee of the privilege is, which can be a group that the user is a member of, directly or indirectly.

Object Privilege Summary

The following table summarizes SAS Federation Server objects with their associated privileges. If privileges are inherited, the field is marked as ‘Yes’. The fields are blank if there is no privilege inheritance for the object. Inheritance runs from right to left.

Object / Privilege	Column	Row	Table/View	Schema	Catalog	DSN	Data Service	Federation Server
SELECT	Yes	Yes	Yes	Yes	Yes		Yes	Yes
UPDATE	Yes		Yes	Yes	Yes		Yes	Yes
INSERT	Yes		Yes	Yes	Yes		Yes	Yes
REFERENCES	Yes		Yes	Yes	Yes		Yes	Yes
DELETE	Yes		Yes	Yes	Yes		Yes	Yes
ALTER TABLE/VIEW TABLE			Yes	Yes	Yes		Yes	Yes
DROP TABLE/VIEW			Yes	Yes	Yes		Yes	Yes
CREATE TABLE/VIEW				Yes	Yes		Yes	Yes
CREATE/ALTERCACHE			Yes	Yes	Yes		Yes	Yes
CREATE TABLESPACE				Yes	Yes		Yes	Yes
ADMINISTER								Yes
TRACE								Yes
CREATE DSN ¹							Yes	Yes
CONNECT ²						Yes	Yes	Yes

Note: ¹Special DSN inheritance applies to CREATE DSN where privileges are checked for inheritance on the data service first and then on the server. ²For the CONNECT privilege, privileges are first checked on the DSN, and then checked for inheritance on the data service and finally, on the server.

User and Group Privileges

Privileges can be assigned to a user or a group on any object. Group privileges are granted and denied in the same manner as individual users. Users who are members of a group inherit the privileges from the group unless explicitly denied in the individual user account. Also, privileges set on a group that is “closer” to a user take precedence over privileges set on groups that are more distant.

For example, an administrator grants SELECT privilege on table T1 to group G1, and that is the only privilege on that table. If Bob is a direct member of group G1, then Bob has privileges to select from T1. The administrator then denies SELECT privilege on table T1 to group G10, where group G1 is a direct member of group G10. User Bob continues to have privileges to select from T1 because group G1 gives him the SELECT privilege. Group G1 is closer in relation to Bob than group G10—of which Bob is also a member—but indirectly through membership in group G1. If the administrator now denies SELECT privilege on table T1 to group G2, where Bob is also a direct member of group G2, there is a conflict in privileges. Group G1 indicates that Bob is granted SELECT privilege., However, group G2 indicates that Bob is denied SELECT privilege. In these cases where there is a conflict, the user is denied the privilege. In this example, Bob does not have SELECT privilege on table T1.

Maintaining Security Definitions for Tables, Views, or Columns

Overview

If an administrator adds security definitions to a table, view, or column and then later drops the table, view, or column, the security definitions are removed. There might be times when a batch job drops the object and then re-creates it. In this case, the administrator must reestablish security on the object.

Tables

As a suggested best practice, use the DELETE command to remove all of the rows from the table, but leave the table definition intact so that the security definitions remain with the table. Note that indexes created in the data store remain on the table as well. This practice offers an advantage if the table is merely being repopulated.

Views and Columns

There is no way to alter a view definition without dropping the view and re-creating it. In this case, security for the view needs to be reestablished. The same applies for column security. Column security must be reestablished for columns that are dropped and added at a later time.

Granting Privileges

Overview

A system user has no restrictions on SAS Federation Server and can grant any privilege to any user. A SAS Federation Server administrator can grant any privilege on the server to any user except the ADMINISTER privilege itself. In other words, an administrator cannot create other administrators. Only the system user can do this.

Besides system users and administrators, the only other user that can grant a privilege to other users is a DSN owner. This user can grant the CONNECT privilege (and only that privilege) to other users.

Grantor Precedence

The order of precedence between the three groups of users who can grant privileges is as follows:

system user
administrator
DSN owner

Privileges granted from a system user cannot be overridden by administrators, and privileges granted from an administrator cannot be overridden by a DSN owner.

Here are some examples:

A system user denies SELECT privilege to user John on the server object. An administrator cannot grant SELECT privilege to user John on the server object. An administrator can grant SELECT privilege to user John on a different object, such as a data service, catalog, or schema.
An administrator grants CONNECT privilege to group SALES_GROUP on DSN SALES_DATA. The DSN owner cannot deny CONNECT privilege to group SALES_GROUP on the same DSN.

Privilege Determination Summary

To summarize, privileges can be determined by group membership and by object hierarchy. The precise algorithm is described here:

System and administrator users are not denied access to any objects.
For other users, a specific privilege is first checked at the current object in the hierarchy, in the following order:
1. On the current object, evidence of the privilege is first searched in the identification of the specific user. If a GRANT or DENY determination is made, the status is returned for the privilege, and privilege lookup stops.
2. User privilege search continues on the current object under the first level of group membership. If any group indicates DENY, the user is denied access. If all groups indicate GRANT, then the user is granted access. If a privilege determination cannot be made, the next level of group membership is checked.
3. On the current object, the privilege is searched for under the next level of group membership, per the same rules as the previous item. If no specific determination is made, repeat for all levels of group membership.
4. If no determination is made on the current object, then privilege determination goes to the next higher level in the object hierarchy. The privilege search algorithm repeats as described, first under the identification of the specific user, and then group membership.
5. If all objects in the hierarchy are searched and no privilege determination is made, then a DENY is returned for the privilege type for the use.

Privilege Caching

Overview

Privilege caching entails capturing and reusing privileges enforced on a statement or request submitted to SAS Federation Server. This improves performance by reducing queries against internal system tables.

Privileges are cached on demand. Each time privileges on a securable for a given grantee are checked, the cache is examined initially to see whether the privilege has already been cached. If so, enforcement cost is reduced by limiting or eliminating queries against security-related system tables. Once uncached privileges are retrieved from system table queries, they are cached for subsequent use, stabilizing the cache based on actual access patterns.

Configuring Privilege Caching

Use the ALTER SERVER DDL statement to cache privileges. For example:

alter server {options xset CACHE(name 'Authorization', 
level <x level>, purge )}

The default level is 2.
The purge option frees the current cache. If privilege caching is enabled, the system dynamically builds the cache again.
Use the purge option to trim memory usage in a long-running server instance.

Level Control

0	Privilege caching is disabled. All levels are purged.
1	Schema, catalog, server, and service-level privileges are cached. Level 2 is purged.
2	Column, row, table, schema, catalog, server, and service-level privileges are cached.