Chapter 7. Data roles
Data roles, also called entitlements, are sets of permissions defined per virtual database that specify data access permissions (create, read, update, delete). Data roles use a fine-grained permission system that Data Virtualization will enforce at runtime and provide audit log entries for access violations.
Before you apply data roles, you might want to restrict source system access through the fundamental design of your virtual database. Foremost, Data Virtualization can only access source entries that are represented in imported metadata. You should narrow imported metadata to only what is necessary for use by your virtual database.
If data role validation is enabled and data roles are defined in a virtual database, then access permissions will be enforced by the Data Virtualization server. The use of data roles may be disabled system wide by removing the setting for the teiid subsystem policy-decider-module. Data roles also have built-in security functions that can be used for row-based and other authorization checks.
A virtual database that is deployed without data roles can be accessed by any authenticated user.
By default, non-hidden schema metadata is only visible over JDBC/pg if the user is permissioned in some way for the given object. OData access provides all non-hidden metadata by default. To configure JDBC/pg to also make all non-hidden schema metadata visible to all authenticated users, set the environment/system property org.teiid.metadataRequiresPermission to false.
7.1. Permissions
Permissions, or grants, control access to data in several ways. There are simple access restrictions to SELECT, UPDATE, and so forth, down to a column level.
Column or table metadata are not visible to JDBC/ODBC users unless the user has permission to read at least a single column.
You may also use permissions to filter and mask results, and constrain/check update values.
User query permissions
CREATE, READ, UPDATE, DELETE (CRUD) permissions can be set for any resource path in a VDB. A resource path can be as specific as the fully qualified name of a column or as general a top level model (schema) name. Permissions granted to a particular path apply to it and any resource paths that share the same partial name. For example, granting select to "model" will also grant select to "model.table", "model.table.column", and so on. Allowing or denying a particular action is determined by searching for permissions from the most to least specific resource paths. The first permission found with a specific allow or deny will be used. Thus, it is possible to set very general permissions at high-level resource path names and to override only as necessary at more specific resource paths.
Permission grants are only needed for resources that a role needs access to. Permissions are also applied only to the columns/tables/procedures in the user query, not to every resource that is accessed transitively through view and procedure definitions. It is important therefore to ensure that permission grants are applied consistently across models that access the same resources.
Non-visible models are accessible by user queries. To restrict user access at a model level, at least one data role should be created to enable data role checking. In turn, that role can be mapped to any authenticated user, and should not grant permissions to models that should be inaccessible.
Permissions are not applicable to the SYS and pg_catalog schemas. These metadata reporting schemas are always accessible regardless of the user. The SYSADMIN schema however may need permissions as applicable.
Permission assignment
To process a SELECT statement or a stored procedure execution, the user account requires the following access rights:
- SELECT- on the Table(s) being accessed or the procedure being called.
- SELECT- on every column referenced.
To process an INSERT statement, the user account requires the following access rights:
- INSERT- on the Table being inserted into.
- INSERT- on every column being inserted on that Table.
To process an UPDATE statement, the user account requires the following access rights:
- UPDATE- on the Table being updated.
- UPDATE- on every column being updated on that Table.
- SELECT- on every column referenced in the criteria.
To process a DELETE statement, the user account requires the following access rights:
- DELETE- on the Table being deleted.
- SELECT- on every column referenced in the criteria.
To process a EXEC/CALL statement, the user account requires the following access rights:
- EXECUTE (or SELECT)- on the Procedure being executed.
To process any function, the user account requires the following access rights:
- EXECUTE (or SELECT)- on the Function being called.
To process any ALTER or CREATE TRIGGER statement, the user account requires the following access rights:
- ALTER- on the view or procedure that is effected. INSTEAD OF Triggers (update procedures) are not yet treated as full schema objects and are instead treated as attributes of the view.
To process any OBJECTTABLE function, the user account requires the following access rights:
- LANGUAGE - specifying the language name that is allowed.
To process any statement against a Data Virtualization temporary table requires the following access rights:
- allow-create-temporary-tables attribute on any applicable role
- SELECT,INSERT,UPDATE,DELETE - against the target model/schema as needed for operations against a FOREIGN temporary table.
Row- and column-based security
Although specified in a similar way to user query CRUD permissions, row-based and column-based permissions may be used together or separately to control the data that is returned to users at a more granular and consistent level.
Specifying a condition on a GRANT for row based security has been deprecated. Specifying a condition on a GRANT is the same as specifying "CREATE POLICY policyName ON schemaName.tblName TO role USING (condition);", such that the condition applies to all operations.
A POLICY against a fully qualified table/view/procedure may specify a condition to be satisfied by the given role. The condition can be any valid boolean expression referencing the columns of the table/view/procedure. Procedure result set columns may be referenced as proc.col. The condition will act as a row-based filter and as a checked constraint for insert/update operations.
Application of row-based conditions
A condition is applied conjunctively to update/delete/select WHERE clauses against the affected resource. Those queries will therefore only ever be effective against the subset of rows that pass the condition, such as "SELECT * FROM TBL WHERE something AND condition. The condition will be present regardless of how the table/view is used in the query, whether by means of a union, join, or other operation.
Example condition
CREATE POLICY policyName ON schemaName.tblName TO superUser USING ('foo=bar');
Inserts and updates against physical tables affected by a condition are further validated so that the insert/change values must pass the condition (evaluate to true) for the insert/update to succeed — this is effectively the same a SQL constraint. This will happen for all styles of insert/update — insert with query expression, bulk insert/update, and so on. Inserts/updates against views are not checked with regards to the constraint.
You can disable the insert/update constraint check by restricting the operations that the POLICY applies to.
Example DDL non-constraint condition
CREATE POLICY readPolicyName ON schemaName.tblName FOR SELECT,DELETE TO superUser USING ('col>10');
You may of course add another POLICY to cover the INSERT and UPDATE operations should they require a different condition.
If more than one POLICY applies to the same resource, the conditions will be accumulated disjunctively via OR, that is, "(condition1) OR (condition2) …". Therefore, creating a POLICY with the condition "true" will allow users in that role to see all rows of the given resource for the given operations.
Considerations when using conditions
Be aware that non-pushdown conditions may adversely impact performance. Avoid using multiple conditions against the same resource as any non-pushdown condition will cause the entire OR statement to not be pushed down. If you need to insert permission conditions, be careful when adding an inline view, because adding them can cause performance problems if they are not compatible with your sources.
Pushdown of multi-row insert/update operations will be inhibited since the condition must be checked for each row.
You can manage permission conditions on a per-role basis, but another approach is to add condition permissions to any authenticated role. By adding permissions in this way, the conditions are generalized for anyone using the hasRole, user, and other security functions. The advantage of this latter approach is that it provides you with a static row-based policy. As a result, your entire range of query plans can be shared among your users.
How you handle null values is up to you. You can implement ISNULL checks to ensure that null values are allowed when a column is nullable.
Limitations when using conditions
- Conditions on source tables that act as check constraints must currently not contain correlated subqueries.
- Conditions may not contain aggregate or windowed functions.
- Tables and procedures referenced via subqueries will still have row-based filters and column masking applied to them.
Row-based filter conditions are enforced even for materialized view loads.
You should ensure that tables consumed to produce materialized views do not have row-based filter conditions on them that could affect the materialized view results.
Column masking
A permission against a fully qualified table/view/procedure column can also specify a mask and optionally a condition. When the query is submitted, the roles are consulted, and the relevant mask/condition information are combined to form a searched case expression to mask the values that would have been returned by the access. Unlike the CRUD allow actions defined above, the resulting masking effect is always applied — not just at the user query level. The condition and expression can be any valid SQL referencing the columns of the table/view/procedure. Procedure result set columns may be referenced as proc.col.
Application of column masks
Column masking is applied only against SELECTs. Column masking is applied logically after the affect of row-based security. However, because both views and source tables canb have row- and column-based security, the actual view-level masking can take place on top of source level masking. If the condition is specified along with the mask, then the effective mask expression affects only a subset of the rows: "CASE WHEN condition THEN mask ELSE column". Otherwise the condition is assumed to be TRUE, meaning that the mask applies to all rows.
If multiple roles specify a mask against a column, the mask order argument will determine their precedence from highest to lowest as part of a larger searched case expression. For example, a mask with the default order of 0 and a mask with an order of 1 would be combined as "CASE WHEN condition1 THEN mask1 WHEN condition0 THEN mask0 ELSE column".
Column masking considerations
Non-pushdown masking conditions/expressions can adversely impact performance, because their evaluation might inhibit pushdown of query constructs on top of the affected resource. In some circumstances the insertion of masking may require that the plan be altered with the addition of an inline view, which can result in poor performance if your sources are not compatible with the use of inline views.
In addition to managing masking on a per-role basis with the use of the order value, another approach is to specify masking in a single any authenticated role such that the conditions/expressions are generalized for all users/roles using the hasRole, user, and other such security functions. The advantage of the latter approach is that there is effectively a static masking policy in effect, such that all query plans can still be shared between users.
Column masking limitations
- If two masks have the same order value, it is not well defined what order they are applied in.
- Masks or their conditions cannot contain aggregate or windowed functions.
- Tables and procedures referenced via subqueries will still have row-based filters and column masking applied to them.
Masking is enforced even for materialized view loads.
You should ensure that tables consumed to produce materialized views do not have masking on them that could affect the materialized view results.
7.2. Role mapping
Each Data Virtualization data role can be mapped to any number of container roles or to any authenticated user.
It is possible for a user to have any number of container roles, which in turn imply a subset of Data Virtualization data roles. Each applicable Data Virtualization data role contributes cumulatively to the permissions of the user. No one role supersedes or negates the permissions of the other data roles.
