Working with Catalogs and Schemas

Databases retain a structure that contains data stored in a database. 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.

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 expose data. For 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 names for all data sources must be registered in SAS Federation Server and they must be unique within the system.

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.

If your database supports native catalogs, you can use catalog mapping to avoid duplication errors. Certain SAS Federation Server drivers, such as Netezza and ODBC, provide a connection option, CATALOG= that facilitates catalog name mapping. Using CATALOG=, you can specify an arbitrary identifier for an SQL catalog that groups logically related schemas. For databases that do not support native catalogs, any identifier is valid, for example, catalog=myodbc. For databases such as SQL Server that do support native catalogs, CATALOG= is not required. The connection defaults to CATALOG=* unless you specify a logical name for the catalog and map it to the native catalog name in the database. For example, to map the logical catalog mycat to the native catalog named newusers, use the following command: catalog=(mycat=newusers);. If a catalog name is not specified, then the native catalog name is surfaced to the users. Catalog name maps can be used only with FedSQL. They are not valid with native SQL.

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.

You can create a schema for the Base SAS, MDS, and SASHDAT data sources. The following is an example of the CREATE SCHEMA DDL statement:

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

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.

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

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. As a schema owner, certain privileges are automatically granted to the schema owner.

Additionally, an administrator 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 a 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.