Permissions

Overview

At times the terms permissions and privileges are used interchangeably. To clarify, permissions represent a specific ability while privileges are a combination of applying the permission to a user and an object. For example, a user’s privileges include all the permissions that were granted on various objects in SAS Federation Server.

Permission Types

Federation Server permissions are divided into two general types:
  • Administration permissions
  • SQL permissions
The following table provides a description for each of these permissions.
SAS Federation Server Permissions
Permission
Description
ADMINISTER
Controls the ability to configure the server. This privilege can be set on the server only. Users who are granted ADMINISTER privilege are automatically granted all other privileges.
ALTER TABLE
Controls the ability to add or drop columns in a table or create or drop indexes with the ALTER TABLE statement. The authorization is set on the server, data service, catalog, and schema objects.
CONNECT
Controls the ability of the user to connect, using either the DSN or data service. The authorization is set on the server, data service, and DSN.
CREATE TABLE
Controls the ability to create new tables or views with the CREATE TABLE statement. The authorization is set on the server, data service, catalog, and schema objects.
DELETE
Controls the ability to delete data with the DELETE statement or analogous method call. The authorization is set on the server, data service, catalog, and schema objects.
DROP TABLE
Controls the ability to remove tables with the DROP TABLE statement. The authorization is set on the server, data service, catalog, and schema objects.
INSERT
Controls the ability to add data with the INSERT statement or analogous method call. The authorization is set on the server, data service, catalog, schema, table, and column objects.
REFERENCES
Controls the ability to create a foreign key reference to an existing table. The authorization is set on the server, data service, catalog, schema, table, and column objects.
SELECT
Controls the ability to retrieve data with the SELECT statement. The authorization is set on the server, data service, catalog, schema, table, and column objects.
TRACE
Controls the ability of the user to enable tracing and create trace files. This privilege can be set on the server only.
UPDATE
Controls the ability to modify data with the UPDATE statement or analogous method call. The authorization is set on the server, data service, catalog, schema, table, and column objects.
CREATE DSN
Controls the ability of the user to create a DSN. The authorization can be set on the server or data service.
ALTER VIEW
Controls the ability to alter a definer's rights or invoker's rights view. The authorization can be set on the server, data service, catalog or schema objects. 1, 2, 3
CREATE VIEW
Controls the ability to create a definer's rights or invoker's rights view. The authorization can be set on the server, data service, catalog or schema objects. 1
DROP VIEW
Controls the ability to drop a definer's rights or invoker's rights view. The authorization can be set on the server, data service, catalog or schema objects. 2
CREATE CACHE
Controls the ability to create, drop and refresh a cache from a definer's rights view. The authorization can be set on the server, data service, catalog or schema objects, and on individual definer’s rights views.
ALTER CACHE
Controls the ability to enable, disable and refresh a cache. The authorization can be set on the server, data service, catalog or schema objects, and on individual definer’s rights views.
CREATE TABLESPACE
Allows a user to create tables in a schema that they do not own to implement a data cache operation. CREATE TABLESPACE applies to data cache operations. The authorization can be set on the server, data service, catalog or schema objects.
ALTER
Controls the ability to alter a view or a table. The authorization can be set on the table or view object. 1, 2
DROP
Controls the ability to drop a view or a table. The authorization can be set on the table or view object. 2
1 Because definer's rights views effectively allow a user to impersonate the schema owner, the creation of definer's rights views requires special consideration. It can be desirable to grant CREATE VIEW privilege to a user, but only for the intention of creating invoker's rights views. Likewise, if ALTER on a view allows switching invoker’s rights views to definer’s rights views, then that privilege is quite powerful as well. Only the schema owner can create definer’s rights views, along with the system user and administrator, who have all privileges. In addition, only the schema owner can issue an ALTER VIEW command to change an invoker’s rights view or a definer’s rights view. Therefore, a grant ALTER VIEW or ALTER on a view only allows a user to change the view name.
2 If the ALTER or DROP permission is explicitly set on the object, that privilege definition is honored. If the privilege is not set, the following applies: The proper ALTER or DROP privilege is searched on the container object. If the Alter privilege is on a table, ALTER TABLE is searched. If the privilege is on a view, the search target is ALTER VIEW. This applies to the DROP privilege as well.
3 ALTER VIEW is required to execute the DESCRIBE VIEW FedSQL statement. See the SAS FedSQL Reference Guide for additional information.

Administration Permissions

The administration permissions are:
  • ADMINISTER
  • CONNECT
  • CREATE DSN
  • CREATE CACHE
  • ALTER CACHE
  • CREATE TABLESPACE
  • TRACE
Administration permissions are always enforced, regardless of the ‘Federation Server SQL Authorization Enforcement’ setting for any DSN.

SQL Permissions

The SQL privileges are:
  • SELECT
  • UPDATE
  • INSERT
  • DELETE
  • REFERENCES
  • ALTER TABLE
  • ALTER VIEW
  • DROP TABLE
  • DROP VIEW
  • CREATE TABLE
  • CREATE VIEW
The enforcement of these privileges to users or groups is controlled through the ‘Federation Server SQL Authorization Enforcement’ option in the DSN. When that setting is disabled, a user connecting with that DSN can perform any SQL action on the data, regardless of what is defined in SAS Federation Server for that user. When the setting is enabled, SAS Federation Server privilege definitions are enforced for that user on that connection. The standard setting for most users will be to enable the SQL authorization enforcement. It will typically only be disabled on DSNs where certain privileged users are granted CONNECT privilege.
Copyright © SAS Institute Inc. All rights reserved.