Federated SQL Views :: SAS® Federation Server 4.2: Administrator's Guide, Second Edition

Overview

When there is a need to view information from multiple data sources or other source types, you can create a reusable federated SQL view (FedSQL view) to deliver data from multiple relational and non-relational sources. A federated SQL view contains the information required to access database sources and can be stored separately from the data. By creating a view definition, you are storing only the instructions for where to find the data and how it is formatted, not the actual data.

Views can reduce the impact of data design changes on users. For example, you can redirect data sources or change variables that are stored in a view without changing the characteristics of the view's result. The view remains consistent even if the data source changes. To create a federated SQL view, the FedSQL dialect must be selected in the DSN.

Federated SQL Views as Data Abstraction

The concept of data abstraction is used in database systems to define user interfaces through the creation of database views. Based on the data abstraction layer concept, a federated SQL view hides the complexity of data by defining an organized data structure for presentation to an end user or calling application. The result is that a user or application can request data in the organized virtual format, without regard to the physical layout. Data is fetched from potentially many data sources, transformed into the virtual structure and returned to the user or calling application.

Invoker and Definer's Rights Views

There are two types of federated SQL views for SAS Federation Server:

Invoker's rights view: An invoker's rights view is run with the invoking user's credentials.
Definer's rights view: A definer's rights view is run with the credentials of the schema owner.

The invoker’s rights view is accessed using the current user’s authorization, credentials, and login information while the definer's rights view is accessed using the schema owner’s authorization, credentials and login information. A definer’s rights view is always associated with a schema owner.

A definer’s rights view allows security management from a single layer of data, which in turn provides for a more secured system. For example, there are 100 tables that provide data to a set of users. 10 views are created and their data is acquired from the 100 tables. The users are selecting from the 10 views to get their data. With invoker's rights views, each invoker must have access to the 100 tables. This includes setting privileges in SAS Federation Server, and ensuring that each invoker has a login to the data sources containing the 100 tables.

With definer's rights views, the data available through the view is accessed by a single user only: the schema owner. Therefore, only this user, the schema owner, needs server privileges and database logins to the data sources containing the 100 tables. View invokers do not need direct access to the underlying tables. The administrator can secure the definer's rights view using Federation Server SQL authorizations to control which users and groups have access to the view’s result set. Unless explicitly specified as a definer’s rights view, a view is created as an invoker’s rights view by default.

Here is an example of creating an invoker’s rights view:

CREATE VIEW
            view1 AS SELECT * FROM table1

Definer's rights views are required for data caching. Only a schema owner can create a definer’s rights view for a schema that he owns. A non-schema owner cannot create a definer’s rights view for a schema for which he has the SELECT privilege only. You can create a definer’s rights view using the following example syntax:

CREATE VIEW
            view1 SECURITY DEFINER AS SELECT * FROM table1

The following example alters an existing view to be an invoker's rights view or a definer's rights view. If a definer’s rights view has any associated cache, it is dropped when the view is changed to an invoker’s rights view.

ALTER VIEW 
            view1 SECURITY INVOKER

ALTER VIEW
            view1 SECURITY DEFINER

Requirements for Definer's Rights Views

Here are the requirements for definer’s rights views:

Use of definer's rights views requires a trust relationship between SAS Federation Server and SAS Metadata Server. Trust is established when the connection uses a Trusted User Account. This enables impersonation of schema owners during execution of SQL. Specifically, the capability is required to retrieve:
1. group memberships required for SQL authorization enforcement of data accessed in execution of a definer’s rights view, and
2. outbound database credentials forwarded to make transient connections used during view execution.
After defining a Trusted User Account, specify the Trusted User in the Federation Server by setting the trusted user account and password using an ALTER SERVER command:
```
ALTER SERVER {OPTIONS ( TRUSTED_USER_UID uid, TRUSTED_USER_PWD pwd )}
```
Register all database catalog names referenced from the definer's rights view.

In order for a definer's rights view to access data from a SQL Server data service (or any data service that supports native catalogs), the federation server administrator must have pre-registered the referenced catalog names.
To ease administrative burden, the federation server administrator can automatically register catalogs when creating SQL Server data services using the REGISTER keyword of the CREATE DATA SERVICE command:
```
CREATE DATA SERVICE data-service TYPE data-service-type REGISTER
```
Any user that is allowed to create, alter, or drop a view within a schema should be granted CREATE VIEW, ALTER VIEW, or DROP VIEW privilege on the schema or an object in its inheritance hierarchy. The schema owner implicitly has these privileges.
A schema owner must be assigned to the schema that the view resides in. This is the view schema owner, also referred to as the View Schema Owner (VSO). This user owns all definer's rights views within that schema, and the VSO user is used for executing the uncached view. Authorization enforcement for all SQL data accessed by the view query is performed using the identity of the owner rather than the invoker.
The VSO must have CONNECT privilege for the data service where the view is located.
The VSO must own a database login to the database in which he is the schema owner, assuming that the database requires a login.
The VSO must own database logins necessary to connect to the database accessed by the view query.
The VSO must have CONNECT privilege on all data services referenced in the view. Data service references are based on the catalog names that appear in the view query and any queries referenced indirectly from other views.
The VSO must have SELECT privilege on all tables referenced in the view.

Required Ownership for Federated SQL Views

Objects such as tables and views do not have owners in SAS Federation Server, so ownership is granted on the schema containing the view. For example,

ALTER SCHEMA LD_ORA1_SERVICE.TKTSTST1 OWNER TO USER1

A definer's rights view must be associated with a schema owner. If an invoker calls a definer view for which the schema has no owner, an error similar to the following is returned:

ERROR: Definer's security context for view "%.*s" cannot be established because 
the schema container "%.*s"."%.*s" has no configured owner.

For additional information about schema ownership, see Working with Schemas.

Creating Federated SQL Views

Overview

Views are created using the CREATE VIEW statement or command. You can create views from a single data source or multiple data sources. To create a view the user must have the CREATE VIEW privilege on the view schema, or inherited from a parent object. The CREATE VIEW privilege is not necessary for users to create views on schemas where the user is the owner of the schema.

Here is the syntax to create a FedSQL view for a single data source using invoker's rights. To specify a definer’s rights view, replace INVOKER with DEFINER:

CREATE VIEW MYVIEW SECURITY INVOKER AS SELECT * FROM 
CAT1.S1.MYTABLE T1

Create a Federated SQL View from Multiple Data Sources

You can create a FedSQL view across multiple data sources. Suppose that data resides in two separate data sources, one in Oracle and the other in DB2. Using CREATE VIEW, you can tie the two data sources together to create a single federated view of the data.

To create a FedSQL view across two data sources:

Create a data service for each of the data sources that you want to access. Make note of the catalog names associated with each of the data sources. In this example, CAT1 and CAT2 are the catalog names. Also make note of the schema within the catalog where your data resides.
```
Data source one: T1 CAT1.S1.MYTABLE
Data source two: T2 CAT2.S2.MYOTHERTABLE
```
Invoke the SQL Console window and connect to one or more data services on SAS Federation Server. The data service can be one of the two created above or any other data service associated with the SAS Federation Server that you are using. Normally, you would use a DSN to connect to the data service.
Create a statement using Submit:
```
CREATE VIEW MYVIEW SECURITY
INVOKER AS SELECT * FROM CAT1.S1.MYTABLE
T1, CAT2.S2.MYOTHERTABLE T2 WHERE T1.X=T2.X
```
This statement creates an invoker’s rights view. For definer’s rights view, replace INVOKER with DEFINER.
Grant SELECT privileges to the users or groups that will access the view.

All users with permissions can now read from the view.

Dynamic Connections

Overview

A dynamic connection is a connection made during the execution of a federated SQL view that allows access to data sources. With dynamic connections you can also connect to a data source to find DS2 functions, or execute the Data Quality Methods. Dynamic connections are created within the initial set of connections when a user connects to SAS Federation Server but does not include a connection to data referenced within a view. The dynamic connection feature allows an administrator to create views that reference data from any data service defined in SAS Federation Server, but does not require the user’s DSN to reference all the data sources in the view.

For example, an administrator creates a view V1, which references data in catalog C1 and C2, where C1 and C2 were defined through separate data services. Without dynamic connections, the administrator would need to create a federated DSN that included a connection to:

The data service where V1 was stored.
The data service containing catalog C1.
The data service containing catalog C2.

Assume the view definition in V1 was changed to reference additional data in catalogs C3 and C4, each coming from a different data service. Without dynamic connections, the administrator would need to modify the user DSN(s) to include references to the data services containing catalogs C3 and C4.

Dynamic connections ease this administration burden because the administrator can simply create a user DSN to include a connection to the data service containing the views. Any data required by the view is accessed through connections made dynamically during view execution. If the view definitions change, the user DSNs do not need to change. This feature is also very useful with data caching, as the data cache can be moved from one data service to another without requiring modifications to user DSNs.

Dynamic connections are transient, so they do not modify the capabilities of the user's original connection properties. Dynamic connections only occur within the context of federated SQL views for invoker’s or definer’s rights. They do not occur any other place in the system.

Object Privileges and Required Logins

For dynamic connections to function properly, the invoker or definer, depending on the view type, must have CONNECT privileges on the data service that is referenced by the dynamic connection. Privileges on the DSN are insufficient for dynamic connections to succeed because the underlying connection is made to the data service, and not through the DSN.

Example: USER1 connects across 2 different DSNs: BASEDSN in data service BASE and ORADSN in data service ORA1. USER1 then creates an invoker's rights view that references tables in both DSNs, and stores the view in BASEDSN. USER2 then connects to BASEDSN and issues a 'select * from VIEW'. The view will succeed only if USER2 has CONNECT privileges on the ORA1 data service.

Also, note that the invoker or definer of the view must have SELECT privileges on any data referenced in the view. Also, the invoker or definer must have the required logins to the data sources that require dynamic connections. If an invoker’s rights view requires a dynamic connection to reference data from catalog ORACAT in an Oracle data service, the invoker must have a login to the Oracle database in order to make the connection. The login can be a personal or shared login.

Secured Connections

Connections made dynamically during view execution are secured. For example, if a connection is made to an Oracle service known by the ORACAT catalog, and a FedSQL view is read (select * from ORACAT.schema.view), and the view query references another catalog (

select
                           * from DB2CAT.schema.table

...), then the dynamic connection to the DB2 service known by DB2CAT is secured. This is true even if the ORACAT connection is unsecured.

For example, if you connect via DSN=ORACLE_DSN, where the DSN is unsecured but configured to use the FEDSQL driver, the DSN might expand to a connection string like this:

DRIVER=FEDSQL;conopts=(driver=ORACLE;catalog=ORACAT;...)

The data that lives under ORACAT is accessed without additional SAS Federation Server authorization enforcement applied.

If an additional SELECT statement is made:

SELECT* from ORACAT.Schema.View.

A SELECT privilege check is not made against the columns of ORACAT.Schema.View.

Now assume that the view content is just a simple indirection that expands to this:

SELECT* from DB2CAT.Schema.Table ...

If DB2CAT's data service is configured to use the ODBC driver, the server will attempt to dynamically connect to DB2CAT using a secured connection that is equivalent to:

DRIVER=FEDSQL;conopts=(driver=ODBC;catalog=DB2CAT;...;
odbc_dsn=DB2DSN)