Object Privileges :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

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. Privileges on the catalog are inherited by the schema, table (view), and column. 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

Example:

There is a group called SALES_GROUP whose members are allowed to select most objects in the SALES_DATA data service. An administrator assigns 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, all the schemas of those catalogs, and all the associated tables and views. There is a stipulation that the SALES_GROUP is restricted from viewing any data in a single catalog of the SALES_DATA data service called EXECUTIVE_DATA. To satisfy the requirement, an administrator could then deny SELECT privilege to the SALES_GROUP on that particular catalog. As a result, members of the SALES_GROUP are not 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 exceptions are set on subordinate objects. This minimizes the number of privileges that an administrator must establish, resulting in a reduction of administration overhead. An administrator can request information about privileges held by any user or group for any object in the server hierarchy, 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 Yes. The blank fields indicate that there is no privilege inheritance for the object. Inheritance runs from right to left.

Object / Privilege	Column	DS2 Method	Row	Table/View	DS2 Pkg	DS2 Thread	Schema	Catalog	DSN	Data Service	Fed 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
EXECUTE		Yes			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/ALTER CACHE				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.

Example:

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.

Determining Effective Privileges

You can use information views to determine effective privileges for federation server objects. Use the PRIVILEGES and EFFECTIVE_PRIVILEGES to obtain a complete picture, including inheritance, for server, data services, catalogs and schemas. For tables, views, and columns, use the X_OBJECT_PRIVILEGES and X_COLUMN_PRIVILEGES information views. If the query returns any rows for the grantee, you have the complete picture of privileges, including inheritance. No further queries are needed. However, if the query does not return any rows for the grantee, then you should query schema privileges in EFFECTIVE_PRIVILEGES. The results are the appropriate privileges to use for the object/column. The INHERITED column should always be set to Y.

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. See Level Control below.
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. This also applies to DS2 threads and methods.

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

To alter a view definition, you must drop the view and re-create it. When this is done, security for the view must be reestablished. This scenario also applies to security on columns and DS2 objects. Security must be reestablished if these objects are dropped and added at a later time.