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 unique identification of each data source when performing heterogeneous operations.

Data services that require logins must be associated with a domain in the Authentication Server. When users connect to the data service through a data source name (DSN), 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 data source and you can assign privileges that control user access to the data.

Creating a Data Service

You can use administration DDL or SAS Federation Server Manager to create a data service. A new DSN is automatically generated each time a new data service is created. This is a standard DSN given the same name as the data service. If the data service is renamed, the DSN name remains unchanged.

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 fails. 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 server’s 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]

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 when creating a data service, the first driver listed in the statement is deemed the default driver 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.

DSNs

Overview

DSNs are resources that provide connection information for data sources accessed through SAS Federation Server. The administrator assigns permissions to determine what users can connect and how they connect. For example, to connect to a data source, a user must be granted CONNECT permission on SAS Federation Server, a specific data service, or a specific DSN.

A DSN references a specific data source 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 permission is required to create a DSN. You can configure DSNs using Administration DDL statements or by using SAS Federation Server Manager.

DSN Types

Standard DSN

A standard DSN is a single-service DSN created for a 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.

Federated DSN

A federated DSN is a collection of one or more 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.

System DSNs

These system DSNs are created during installation of SAS Federation Server:

The ADMIN DSN is created automatically at server start up 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 permission to the ADMIN DSN. SAS Federation Server automatically checks user privileges when administration SQL is submitted. Users can submit administration SQL for which they have privileges, such as selecting against Information Views. Some administration SQL can be executed by the server administrator only. See the SAS FedSQL Reference Guide for details.
A SQL_LOG DSN and data service are created when SQL Logging is enabled on SAS Federation Server. At that time the server creates an EVENTS table for the purpose of capturing server activity that reflects information about SQL statements submitted by connected users. Additional information can be found in SQL Logging.

The CONNECT permission is not assigned to a DSN by default, and must be granted by the administrator or by the DSN owner, to users or groups that are connecting to data sources.

DSN Permissions

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

The permissions for standard and federated DSNs are:

CREATE DSN
ALTER or DROP DSN
CONNECT

CREATE DSN

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

The user is a system user.
The user is an administrator of the server.
The user has the CREATE DSN permission on the server. Note that this is the only way that a user who is not an administrator can create a federated DSN.
The user has the CREATE DSN permission on the data service, and the user is creating a standard DSN.

ALTER/DROP DSN

To alter or drop a DSN, one of the following conditions must be met:

The user is a system user.
The user is an administrator of the server.
The user is the owner of the DSN.

CONNECT

A user must have CONNECT permission to establish connection to a DSN. This permission is effective from the user object, inherited through the hierarchy, or acquired through group permissions. For a standard DSN, the CONNECT permission must be on (in order of inheritance):

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

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

The DSN, or
SAS Federation Server.

Permissions granted on a federated DSN override any permissions that exist for child DSNs that are contained within the federated DSN. If a user has CONNECT permission on a federated DSN, permissions on any of the child DSNs contained within (standard or federated) are ignored, even if the user is explicitly denied CONNECT on any of the child DSNs

For additional information about permission assignment, see the topic on Permissions .

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)}

Credentials Search Order (CSO) for DSN Connections

Connections made with 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).

At connection request, SAS Federation Server attempts to select a user ID and password for each data service connection based on the associated domain:

PERSONAL means the server attempts to select credentials directly owned by the user.
SHARED means the server attempts 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 is still attempted. If credentials are specified on the connection string, those will be used first. If credentials are not supplied, the server attempts to find shared logins for the user. If shared login credentials are not found, the server attempts to use personal credentials. If personal credentials are not found, the connection fails.

Also, if GROUP=groupname is specified with the DSN or supplied 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. The GROUP= option does not have any effect on personal login extraction.

DSN Login Credentials

If data services require credentials, a 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 for connecting users.

When using SAS Federation Server Manager, an administrator can specify 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). Therefore, 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 might be disallowed. See Best Practices for Setting Shared Logins for rules on how shared logins are selected.

Creating a DSN with Administration DDL

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 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. Here is the syntax for creating a federated DSN:

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

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, 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. 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. 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 permissions 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.
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 is a catalog or a data service. However, explicitly denied privileges remain in tact for the schema.
Schemas should not be owned by any user who is a system user.