Understanding Data Federation and Best Practices

In a federated system, data can be acquired from a large variety of data sources, and users might not always have direct access to those systems. Even if they do, administering a large number of database credentials and setting up database privileges so that users can access data with personal credentials can be burdensome. The security capabilities of SAS Federation Server provide some alternatives that can help ease security administration.

If users already have direct access to the back-end systems and you want them to access the data under their individual or shared logins, then you can configure SAS Federation Server to access the data under the rights of the invoking user. This is done by creating invoker’s rights FedSQL views. When these views execute, any connections to back-end data require the invoking user to have proper credentials to access the data. In addition, you should ensure that SAS Federation Server security settings allow invokers to access the required data. For example, if a FedSQL view is defined to select data from an Oracle data source and a DB2 data source, the administrator needs to ensure that proper privileges are assigned to each invoker of the view on the data source, as well as the FedSQL view itself. The privileges are granted to a group, or set of groups for which the invokers are members, instead of to each individual invoker.

Because users must be able to directly access all of their data, you should secure each object for each user. Also, invoker’s rights views need to be sensitive to these security settings on underlying objects, particularly column level security. Consider a view that selects columns C1 and C2 from table T, as shown in the following example:

SELECT C1, C2 FROM T

If table T has been secured through the Federation Server such that User1 does not have SELECT privilege on the table, then when User1 selects from the view, User1 receives an error when attempting to access table T. In this case, you might consider denying SELECT privilege on the view to User1. Furthermore, if User2 has SELECT privilege on table T1 and column C1 but not on column C2, then User2 receives an error when selecting from the view. Assigning column-level security on the view that denies SELECT privilege on column C2 to User2 does not help, because the underlying view definition specifically requires access to column C2 in table T. When creating views that require data from other objects with column-level security, you might consider selecting all columns using an asterisk (*). Here is an example:

SELECT * FROM T

When the Federation Server is configured with SelectStarExpansion=VISIBLE, the * expands all columns for which the invoking user has SELECT privilege. This enables you to create a single view that can be used by all invokers, yet each invoker sees only the columns for which the invoker has SELECT privilege.

If your users do not have direct access to the back-end systems, or you want to read objects under the authorizations of a single user, then you can configure SAS Federation Server to access the data under the rights of the defining user. This is accomplished by creating definer’s rights FedSQL views. When these views are executed by a user, connections to back-end data are made under the definer of the view. The definer is actually the owner of the schema that contains the view.

In this security model, you typically deny access to the back-end data to all users except the view definer. Any column-level, table-level, or row-level security will be set on the view itself. For example, if your view is defined as SELECT C1, C2 FROM T, you should ensure that the view definer has full access to columns C1 and C2 in table T.

All other users do not require access to back-end data. And in many cases, if your users are interfacing only with exposed top-level objects of the data model, then you can deny privileges to those users on back-end objects. Individual security settings can then be consolidated on the exposed objects of the data model, which are usually FedSQL views. Then users access the views only, and no other credentials are required. This greatly simplifies the security settings in the Federation Server and reduces administration on all the back-end data sources as well.

Note that you can use a combination of approaches with both invoker’s and definer’s rights views intermingled. FedSQL views can be nested, and view types are honored. For example, you might create view V1 that is owned by Owner1 and data is accessed through the credentials of Owner1. However, if view V1 selects from view V2, which is another definer’s rights view but owned by Owner2, then all data within view V2 is accessed under the authorizations of Owner2. If view V2 selects from view V3, which is an invoker’s rights view, then any data within view V3 continues to be accessed by Owner2, who is the invoker of the view.

SAS Federation Server persists metadata for security settings in its set of system tables. The server attempts to synchronize this metadata with the actual objects that it represents. For example, if the administrator has granted SELECT privilege on table T1 to User1, and subsequently table T1 is dropped, the server will likewise drop its security metadata onto T1. If T1 is subsequently re-created, it will have no security set on the object itself. All security definitions for T1 come from its inheritance objects, including the schema, catalog, data service, and server.

If you prefer that the security metadata is retained on the server, there are a couple suggested possibilities. First, it might be that a job is dropping the table only to re-create it in a subsequent step. This is often the case when refreshing the contents of a table. Rather than dropping and re-creating the table, which might also affect indexes on the table, consider issuing a DELETE FROM TABLE command to delete all the rows from the table. In doing this, the underlying table remains intact, as well as the security metadata stored in the system tables.

A second approach is to use definer’s rights views to assist with individual object security. Often, data in back-end data sources is being manipulated (created and dropped) by a few power users only. These users require CREATE, DROP TABLE, and VIEW privileges. The CREATE privilege set applies only to container objects (schema, catalog, data service, and server) and not individual tables or views. The DROP privilege can be applied at the object level. However, if designed correctly, the DROP privilege can be used from the container object.

Business users are typically the ones requiring table-level and column-level privileges to access the data. If you choose the security model using definer's rights views, then you can place your individual privileges on the views, which do not go away when a back-end table is dropped and re-created. Back-end data can be secured by denying privileges on the container object to business users, but granting SELECT to view owners. An approach similar to this can be used to eliminate the need for table-level, column-level, and row-level security on back-end data source objects.