Configuring Data Source Access

Overview

To access data, the administrator must create and configure data services for SAS Federation Server. Data services contain connection information and driver specifics to connect with data sources such as Oracle® or Base SAS® data sets.

Relational databases provide authorization that limits the operations that can be performed on their data. As noted previously in Data Source Authorization, SAS Federation Server respects authorizations that are defined and enforced on a third-party database.

Data Services

Overview

Data services contain information that identifies the location of data tables. If a data source does not support native catalogs, SAS Federation Server enables you to define a logical catalog name to use as an SQL identifier. This allows each data source to be uniquely identified when performing heterogeneous operations.

Data services requiring logins must be associated with a domain in the Authentication Server. When users connect to the data service through a data source name, the domain name is used to retrieve user credentials associated with that data service. The credentials are then passed along to the back-end database. User credentials are stored in the Authentication Server.

Data services can also contain optional information to control SAS Federation Server driver behavior, such as locking semantics and tracing. Data services form the foundation for connectivity to a source of data, and privileges can be assigned to data services to control user access to the given data service.

Creating a Data Service

Use administration DDL or SAS Federation Server Manager to create a data service.

By default, a BASE data service is created the first time that SAS Federation Server is started. Only one BASE data service can exist in a SAS Federation Server installation, and it cannot be modified or deleted.

Native Catalog Support

When creating a data service for a data source that supports native catalogs and using the REGISTER option, the server attempts to connect to the database to acquire a list of catalogs. Credentials are required to secure the connection. If the connection cannot be made, creation of the data service will fail. The same requirement for pre-registered credentials applies when creating a data service to ODBC with native catalog support, or for any data services that support native catalogs.

Identifier Case Sensitivity

When creating an ODBC data service, the server must query the data source to acquire its identifier case sensitivity property. The identifier case sensitivity property is used to create security entries in the Federation Server system tables and is stored with the data service.

Database Login Prerequisite

Due to the requirement for a database connection described above, the following database login prerequisite applies to SQLSERVER, ODBC and ODBC_FED data services. These actions must be completed before creating the data service, and must be accomplished through the Authentication Server Manager.

A database login must be registered in the domain that will be associated with the new data service.
The domain must be registered in Authentication Server for it to be accessible when creating the data service using administration DDL or SAS Federation Server Manager.

Creating a Data Service with Administration DDL

Here is an example of the DDL statement, CREATE DATA SERVICE. See CREATE DATA SERVICE DDL for details and a complete list of options. You can also alter and drop data services using DDL.

CREATE [DATA] SERVICE data-service
TYPE data-service-type
[CATALOG [NAME] catalog-name]
[DOMAIN [NAME] domain-name]
[REGISTER [( catalog-name1 [,catalog-name2 …]) | ALL]
[register-options]]
[data-service-options]

Registering Catalogs

Use CATALOG to register a catalog name for data sources that do not support native catalogs.

Use REGISTER to register the native catalog names for those data sources that support native catalogs, such as SQL Server. If an identical catalog name is encountered, a warning message is issued and the catalog is not registered. In this case use the CREATE CATALOG DDL statement to provide a mapped name for the native name.

Driver Search Order

If specifying more than one driver in the CREATE DATA SERVICE statement, the first driver listed in the statement is the default driver used to connect to a data source if a driver is not specified in a connection string. For example, if two drivers are specified when creating a data service, the first driver is used as the default driver if driver= is not specified in a connection string. If a driver is specified in a connection string using conopts-configuration-list when creating the data service, it is used when connecting to a data source.

Catalogs and Schemas

Overview

The terms catalog and schema are defined as ANSI SQL standards and refer to the organization of data in a relational database. That is, data is contained in tables, tables are grouped into schemas, and schemas are grouped into catalogs. Catalog and schema names can be used in SQL statements to qualify table references. For example, when querying a database that supports both schemas and catalogs, you can specify a three-level identifier in the form of CATALOG.SCHEMA.TABLE-NAME.

Organize Data with a Catalog

A catalog is a named collection of logically related schemas. The catalog is the first-level (top) grouping mechanism in a data organization hierarchy that qualifies schemas. At least one schema is required for each catalog.

For the BASE data service, you must create catalogs and schemas in order to make data available. For all other data services, catalogs and schemas are defined in the data source (though the data source is not required to support either catalogs or schemas), and catalog and schema names can be registered in SAS Federation Server to reflect those objects.

Catalog Registration

Catalog names for all data sources must be registered in SAS Federation Server and they must be unique within the system.

This is accomplished by using one of the following methods:

Use the CATALOG keyword on the CREATE DATA SERVICE command. Do this when the data source does not support native catalogs.
Use the REGISTER keyword on the CREATE DATA SERVICE command. Do this when the data source supports native catalogs.
Use the CREATE CATALOG command. Do this to provide a mapped name for a native catalog that cannot be registered using the REGISTER keyword because it conflicts with an existing registered catalog.

The following is a sample of the CREATE CATALOG DDL statement:

CREATE CATALOG catalog UNDER data-service
[ NATIVE NAME native-name ]
[ create-catalog-options ]

A complete list of options is shown in the CREATE CATALOG DDL statement.

Organize Data with a Schema

A schema is a data container object that groups logically related objects such as tables and views. The schema provides a unique namespace that is used along with a catalog to qualify names.

For SAS data sets, a schema identifies the physical location such as a UNIX® directory or a Windows® folder that contains a collection of tables. That is, for SAS data, the relationship between a schema and its files is similar to that of an operating system file directory and the files that are contained within that directory. For SAS data, a schema is approximately equivalent to a SAS library.

Schema Registration

Unlike catalogs, schema registration is not required for all schemas in the data source. Schemas are registered only when the administrator wants to assign an owner to the schema. Schemas are also created and maintained internally as needed by the system, such as when assigning privileges to a user or group on a schema.

The following is an example of the CREATE SCHEMA DDL statement:

CREATE SCHEMA [ catalog.schema ]
[ AUTHORIZATION|OWNER owner ]
[ create-schema-options ]

A complete list of options is shown in CREATE SCHEMA DDL.

Schema Ownership

All schemas have an owner. If an owner is not explicitly assigned to a schema, ownership defaults to the system user account. Definer’s rights views require a non-system schema owner for proper operation. The schema owner is the owner of all objects contained in the schema, though the owner has particular relevance to definer’s rights views. And as the owner, certain privileges are automatically granted to the schema owner.

Privilege Rules for Schema Ownership

Here are additional rules that apply to schema ownership:

The schema owner automatically has all SQL privileges on tables and views in the schema. They are reported with GRANTOR=ADMINISTRATOR. Also, the schema owner can alter the schema's configuration options.
Administrators can DENY a privilege on the schema, and the owner will be denied the privilege. This feature can be used to downgrade schema ownership rights. Therefore, the schema owner has no explicit privileges on the schema, but has default GRANT for privileges on schema objects.
Administrators can reverse denied privileges using GRANT. Reference #4 below.
GRANT to schema owner is equivalent to a REVOKE. The command clears any explicit denied privileges on the schema, but does not add any explicit ones. That way, when a schema owner's privilege is cleared on the schema, it defaults back to implicit GRANT.
When schema ownership changes, the previous owner receives default privileges from the schema's container, whether it be a catalog or a data service, but retains any explicitly denied privileges on that schema.
Schemas should not be owned by any user who is a system user.

Configuring DSNs

Overview

DSNs are used to expose data services to users connecting through SAS Federation Server. Typically, when a client connects to SAS Federation Server, they will specify a DSN. The administrator can assign privileges to determine which users can connect. In order to connect to a data source, a user must be granted CONNECT privilege on SAS Federation Server, a specific data service, or a specific DSN.

A DSN references a specific data service to which it will connect and defines how SQL security is enforced. It can be configured so that SAS Federation Server enforces SQL privileges defined for the data service. The CREATE DSN privilege is required to create a DSN. Configure DSNs using DDL statements or by using SAS Federation Server Manager. For an explanation of CREATE DSN DDL syntax, refer to Administration DDL, CREATE DSN statement.

DSN Types

Standard DSN

A standard, single-service DSN is created for one particular data service and is parented to that data service. The scope is limited to one data service and contains connection information, such as server name, port, path or other connection options specific to a data service.

Auto-Created DSNs

A new DSN is automatically generated each time a new data service is created. This is a standard DSN with the same name as the data service. The DSN name remains consistent even if the data service is renamed.

SQL_LOG DSN

When SQL Logging is enabled on SAS Federation Server, a SQL_LOG DSN and data service is created automatically. Also, the server creates an EVENTS table in the SQL_LOG data service. This table contains data captured around certain activity in the server, such as information about SQL statements submitted by connected users. Additional information can be found in SQL Logging.

Federated DSN

A federated DSN is a collection of one or more standard DSNs. Unlike the standard DSN which is parented to a data service, the federated DSN is parented to the federation server itself, even if it only contains DSNs from a single data service. Federated DSNs can contain other federated DSNs. Since federated DSNs are typically used to provide access to data from multiple, disparate data sources, the FedSQL dialect is required.

The ADMIN DSN

The ADMIN DSN is created automatically at server startup and is used for the purpose of sending administration SQL to the server. The ADMIN DSN is also used to query Information Views. The SAS Federation Server Manager automatically connects with the ADMIN DSN to display information such as the registered list of data services and DSNs. Any user expected to use SAS Federation Server Manager to accomplish tasks, such as creating views and caching views, will require CONNECT privilege to the ADMIN DSN. SAS Federation Server automatically checks a user’s privileges when any administration SQL is submitted. Some administration SQL can be submitted by any user (for example, selecting against Information Views for which they have privileges), while other administration SQL can be executed by the server administrator only. See the SAS FedSQL Reference Guide for details.

CONNECT privileges are not assigned to DSNs by default, and must be granted by the administrator or by the DSN owner, to users or groups that are connecting to data sources. See the GRANT and DENY statements for additional information.

Common Configuration Options

Enabling Federation Server SQL Authorization Enforcement

When Federation Server SQL Authorization Enforcement is enabled, the FedSQL driver is also required, and the SQL dialect is automatically set to FedSQL. With FedSQL an additional layer of object-level security is enabled for the connection and SQL statements are secured before processing them. If Federation Server SQL Authorization Enforcement is disabled, object-level security is bypassed and a user is granted all privileges regardless of what the user has been granted or denied. If Federation Server SQL Authorization Enforcement is disabled, an administrator can choose either FedSQL dialect or data source (native) dialect. For example, if you are connected to Oracle, then native dialect would be SQL supported by Oracle. The SQL dialect for Base data services is always FedSQL.

Security is enabled by default for all new DSNs. However, if you need to enable SAS Federation Server security on a DSN, use DDL options with CREATE DSN and set SECURITY to YES.

Here is an example DDL statement that enables SAS Federation Server security:

CREATE DSN "DSN1" UNDER BASE
DESCRIPTION 'creating DSN1' NOPROMPT
'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA=(name="schema1_BASE")' {OPTIONS
(SECURITY YES)}

Login Credentials

If data services require credentials, the DSN can be configured to specify how database logins are retrieved. The DSN can be configured to use the personal credentials of the user, or retrieve the login from a shared login. If you are using a shared login, you can specify a consumer group from the DSN. This is required only to identify what shared login to use if multiple shared logins are available in the same domain to connecting users.

When using SAS Federation Server Manager, an administrator can have either personal credentials or a shared login to the underlying databases for the purpose of managing data services. SAS Federation Server Manager connects to a data service behind the scenes and data services use a credential search order of PERSONAL, SHARED (CSO=PERSONAL,SHARED).

Note that this means:

If an administrator has both a personal and a shared login, the personal login will be chosen.
If an administrator does not have a personal login, but has multiple shared logins available, the connection may be disallowed. See Best Practices for Setting Shared Logins for rules on how shared logins are selected.

Credentials Search Order (CSO) for DSN Connections

Connections made using a DSN use a credentials search order (CSO) as specified in the DSN configuration. By default, the credentials search order is PERSONAL, SHARED. Other valid values are SHARED, (PERSONAL, SHARED) and (SHARED, PERSONAL).

The server will attempt to select a user ID and password for each data service connection request based on the domain associated with it:

PERSONAL means the server will attempt to select credentials directly owned by the user.
SHARED means the server will attempt to select credentials from a shared login of which the user is a consumer. Credentials are extracted on behalf of the user using the shared login manager's identity. Shared Logins must be configured in SAS Federation Server and defined in Authentication Server for this option to function properly. See Shared Login Manager Configuration for more information.
- If a DSN is configured as CSO(SHARED) and a shared login is not found for any of the DSN's connections, the connection will fail immediately.
- If the credentials search order is not configured on the DSN or is not CSO(SHARED), the connection will still be attempted. If credentials are specified on the connection string, those will be used first. If credentials are not supplied, the server will attempt to find shared logins for the user. If shared login credentials are not found, the server will attempt to use personal credentials. If no personal credentials are found, the connection will fail.

Also, if GROUP=groupname is specified as part of the DSN used to connect or supplied directly in the connection string, shared login selection is limited to those candidates in which the specified group is a consumer. The user must be a direct or indirect member of the group, or no shared login will be selected at all. The GROUP= option does not have any effect on personal login extraction.

DSN Permissions

DSN permissions are assigned using GRANT, REVOKE, or DENY DDL statements.

The permissions for standard and federated DSNs are:

CREATE DSN
CONNECT

See the topic on Permissions for additional information about permission assignment.

CREATE DSN

To create a DSN, one of the following must be true:

User is a System User
User is the Administrator of the server
User has CREATE DSN privilege on the server (this is the only way a non-admin can create a federated DSN)
User has CREATE DSN privilege on the data service, and the user is creating a standard DSN

ALTER/DROP DSN

In order to alter or drop a DSN, one of the following must be true:

User is a System User
User is the Administrator of the server
User is the owner of the DSN

CONNECT Permission

A user must have CONNECT permission to establish a connection to a DSN. This permission is effective from the user object, inherited through the hierarchy, or obtained though group permissions.

For a standard DSN, the CONNECT privilege must be on (in order of inheritance):

The DSN,
The parent data service of the DSN, or
The SAS Federation Server

For a federated DSN, the CONNECT privilege must be on (in order of inheritance):

The DSN
The SAS Federation Server

Note: If a user is given CONNECT permission on a federated DSN, privileges on any contained DSN (standard or federated) are ignored. If a user has CONNECT permission on a federated DSN, it does not matter if the user is explicitly denied CONNECT privilege on any child DSN.

Creating DSNs using DDL Statements

Using administration DDL, you can create standard and federated DSNs with various configuration options. For a complete list of configuration options, refer to the CREATE DSN DDL statement.

Standard DSN

Here is the DDL syntax for creating a standard DSN under a data service:

CREATE DSN dsn-name UNDER data-service
           create-dsn-options [ AS ADMINISTRATOR ]

Note: If a DSN is created by a user other than the system user or administrator, the DSN is owned by the individual user. If that user is later removed from the system, DSN ownership should be transferred to another user.

Federated DSN

Federated DSNs are objects of SAS Federation Server. Therefore, they are not created under a data service. Following is the DDL syntax for creating a federated DSN:

CREATE DSN dsn-name
             create-dsn-options
            ADD "(" dsn-name ["," ...] ")"