Working with DSNs

Overview of DSNs

DSN Types

Standard DSN

Creating a DSN with Administration DDL

DSN Login Credentials

Credentials Search Order (CSO) for DSN Connections

Enabling Federation Server SQL Authorization

FedSQL Pass-Through Facility

Shared Logins: Best Practices

Overview of DSNs

DSNs are resources that provide connection information for data sources accessed through SAS Federation Server. The administrator assigns permissions that determine how users connect to the data. 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. All DSNs must be associated with a data service, except for federated DSNs which are objects parented by the federation server.

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:

ADMIN DSN

The ADMIN DSN is created 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.

SQL_LOG DSN

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.

Note: 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.

Permissions for DSN

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 about Permissions.

Configuring DSNs

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 ]

Here is a DSN that uses a GROUP login:

CREATE DSN "dsn-name" 
   UNDER "data-service" 
   CONNECT 'DRIVER=Oracle;GROUP=group-name' {OPTIONS CSO PERSONAL}
   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. When creating a federated DSN, ensure that the child DSNs are not pointing to the same catalog, as this might result in a catalog conflict error. Catalog names must be unique within a connection. Here is the syntax for creating a federated DSN:

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

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.

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 is used. If an administrator does not have a personal login, but has multiple shared logins available, the connection might be disallowed.

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. This includes group-owned logins when the user does not own a login in the domain of the service to which the DSN is associated.
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 that is using the shared login.
- 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 if the search order 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.

Enabling Federation Server SQL Authorization

When Federation Server SQL Authorization 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 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 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)}

FedSQL Pass-Through Facility

The FedSQL pass-through facility enables you to connect to a data source and send SQL statements directly to that data source for execution. This facility also enables you to use the syntax of your data source, and it supports any non-ANSI standard SQL that is supported by your data source. SAS Federation Server supports FedSQL pass-through with the use of personal credentials for the connection. Shared logins are not allowed with FedSQL pass-through. See the FedSQL Reference Guide for additional information about the pass-through facility.

Shared Logins: Best Practices

Shared logins consist primarily of a login and a domain to share, and the consumers who use that login. As a best practice, the consumers will typically list one or more groups. However, a conflict can arise when a particular user is in a consumer group, either directly or indirectly, of multiple shared logins for the same domain. The following scenarios outline shared login conflicts and their resolution.

Scenario 1: Application Users

In the following scenario, an application exists which requires the use of a particular set of database credentials to access protected data.

In this example, an HR application has data content stored in Oracle and DB2.

Use the following procedure to manage credentials:

Identify all the users of the HR application. The users might have different roles or data access privileges, but they all need to access the data. These users, or subgroups, will all be placed in the group HR_USERS.
Create a shared login for each domain. In this case, the administrator would create an HR_ORACLE and HR_DB2 shared login. For both shared logins, the administrator would specify the HR_USERS group as a consumer member of the shared login. Each shared login would contain the appropriate principal and domain for the database.
Specify the GROUP option to qualify the users with the shared login, either in the DSN with the CONNECT option (CONNECT ‘group=HR_USERs’), or in a connection string that specifies a DSN (group=HR_USERS;dsn=HR1). In this case, the GROUP option would be HR_USERS.
Set authorizations on different users and groups to control which set of users can perform specific operations, for example, SELECT versus UPDATE versus DELETE. All of the users and groups should be members of the HR_USERS group.

At the time of connection, the HR_USERS group is used to identify the correct shared login for each underlying database connection. If the connecting user is a consuming member of another shared login, the GROUP value would properly identify which shared login to use.

Algorithm When Using the GROUP Option

Shared logins are initially considered candidates for outbound credentials selection if the domain and shared login key match. If the domain is empty, shared logins for any domain initially qualify. This also applies to the shared login key, which is configured in SAS Federation Server.

If the GROUP connection string option is specified (which is derived from the consumer group in the DSN configuration), then only maps where the group is a direct or indirect consumer will be considered a candidate for selection of outbound credentials. The basic algorithm selects a map based on the proximity of the specified group to the map.

Candidate Map Processing

Candidate maps are processed based on one of the following criteria:

If the user is not a direct or indirect member of the shared login consumer group, the map is not a candidate.
If the GROUP is not a direct or indirect consumer of the map, the map is not a candidate.
The distance from the GROUP to the map is determined by following the group member-of relationship all the way to the group that is the direct consumer. The candidate map is retained only if the distance is less than, or equal to the current minimum distance to the map. The current minimum distance is updated.

After all candidate maps have been processed, use one of the following resolutions:

If exactly one map has been retained, use the associated credentials.
If two or more maps were retained, check the closest two and use the credentials associated with the closest of the two (error if the distances are the same unresolved conflict).
If no maps have been retained, use no credentials.

Scenario 2: Organized Consuming Users

In the following scenario, the administrator has organized the users based on company organization or another classification.

The administrator wants to use this relationship so that users qualify for a particular shared login, for example:

The administrator wants to grant access to the Oracle account EXECUTIVE_USER to his most privileged users, identified by the MARKETING_EXECUTIVE group.
The administrator wants to grant access to the Oracle account MARKETING_USER to members of the marketing division in the company, identified by the MARKETING group.
The administrator wants to grant access to the Oracle account STANDARD_USER to all other known users in the system, identified by the SASUSERS group.
The administrator created groups that reflect the company's organizational chart. The MARKETING group reflects all members of the marketing organization, with the MARKETING_EXECUTIVE group included as a member of the MARKETING group.

The administrator creates shared logins for the EXECUTIVE_USER, MARKETING_USER and STANDARD_USER Oracle accounts. Next, he assigns MARKETING_EXECUTIVE, MARKETING and SASUSERS consuming groups, respectively, to these shared logins.

Then, the shared login chosen is as follows:

For members of the MARKETING_EXECUTIVE group, they would be closest to the shared login identified by that group, even though they were likewise members of the MARKETING and SASUSERS groups. Therefore, this set of users would consume the EXECUTIVE_USER Oracle account.
For members of the MARKETING group, they would be closest to the Shared Login identified by that group, even though they were likewise members of the SASUSERS group. Therefore, this set of users would consume the MARKETING_USER Oracle account.
All other known users would qualify only for the shared login identified by the SASUSERS group. Therefore, this set of users would consume the STANDARD_USER Oracle account.

In this scenario, the administrator would not use the GROUP option, since the option accepts only a single value, and no single value works for all users. The administrator would omit the GROUP option and allow a closeness algorithm to identify which shared login to use.

Algorithm When No GROUP Option is Specified

Candidate Map Processing for a User

One of the following resolutions determines candidate map processing:

If the user is not a direct or indirect consumer of the map, the map is not a candidate.
The distance from the user to the map is computed by following the group member-of relationship up to the group that is the direct consumer. The candidate map is retained if the distance is less than or equal to the current minimum distance to the map. The current minimum distance is updated.

Use one of the following resolutions after all candidate maps have been processed:

If exactly one has been retained, use the associated credentials.
If two or more were retained, check the closest two and use the credentials associated with the closest of the two (error if the distances are the same unresolved conflict).
If maps have not been retained, use no credentials.

Candidate Map Processing for SASUSERS

If SASUSERS is a direct consumer of the map, then the candidate map is retained.
If a candidate map has been retained already, return an error (unresolved conflict).
After all candidate maps have been processed, if exactly one has been retained, return OK and the associated credentials; or

Continue to Candidate Map Processing for PUBLIC.

Candidate Map Processing for PUBLIC

If SASUSERS is a direct consumer of the map, then the candidate map is retained.
If a candidate map has been retained already, return an error (unresolved conflict).
After all candidate maps have been processed, if exactly one has been retained, return OK and the associated credentials; or

Return OK but empty credentials if no candidate maps were retained.

Path Length Computation Details

If SASUSERS or PUBLIC is a member of another group, and that group is a map consumer, direct or indirect, the path length does not increment when traversing from the user to the map. For the purposes of map selection, this effectively makes placing either of these two groups in another group which is a quick way to place all users in that group.

Last updated: March 6, 2018