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 on page 78 .

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 Configuring Data Source Access 103

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 on page 29 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” on page 29 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 on page 211 .

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.

Configuring Data Source Access 105

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 on page 205 .

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 on page 207 . 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.