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 read to "model" will also grant read to "model.table", "model.table.column", etc. 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 only applied to the columns/tables/procedures in the user query - not to every resource 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.

Unlike previous versions of Teiid, 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 inaccessable.

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.

To process a SELECT statement or a stored procedure execution, the user account requires the following access rights:

• READ- on the Table(s) being accessed or the procedure being called.
• READ- on every column referenced.

To process an INSERT statement, the user account requires the following access rights:

• CREATE- on the Table being inserted into.
• CREATE- 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.
• READ- 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.
• READ- on every column referenced in the criteria.

To process a EXEC/CALL statement, the user account requires the following access rights:

• EXECUTE (or READ)- on the Procedure being executed.

To process any function, the user account requires the following access rights:

• EXECUTE (or READ)- 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 Teiid temporary table requires the following access rights:

• allow-create-temporary-tables attribute on any applicable role
• CREATE- against the target source/schema if defining a FOREIGN temporary table.

Row-based Security Conditions

A permission against a fully qualified table/view/procedure may also specify a condition. Unlike the allow actions defined above, a condition is always applied - not just at the user query level. The condition can be any valid SQL referencing the columns of the table/view/procedure. The condition will act as a row-based filter and as a checked constraint for insert/update operations.

How Conditions Are Applied

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, i.e. "SELECT * FROM TBL WHERE blah AND condition". The condition will be present regardless of how the table/view is used in the query, whether via a union, join, etc.

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, etc. Inserts/updates against views are not checked with regards to the constraint.

Within the same role or across multiple applicable roles if more than one condition applies to the same resource, the conditions will be accumulated disjunctively via OR, i.e. "(condition1) OR (condition2) ..."

Considerations When Using Conditions

Non-pushdown conditions may adversely impact performance, since their evaluation may inhibit pushdown of query constructs on top of the affected resource. Multiple conditions against the same resource should generally be avoided as any non-pushdown condition will cause the entire OR of conditions to not be pushed down.

Pushdown of multi-row insert/update operations will be inhibited since the condition must be checked for each row.

A typical approach to adding condition permissions would be in an any authenticated role such that the conditions are generalized for all users/roles using the hasRole, user, and other such functions.

Conditions are enforced even for materialized view loads. You should ensure that tables consumed to produce materialized views do not have conditions on them that could affect the materialized view results.

Conditions on source tables that act as check constraints must currently not contain subqueries.