Access rights transfer
Access rights transfer carries the access rights of the source tables over to the objects that data marts create in the database, so that a reader of a data mart view never sees more than they may see at the source. It is switched on per data source and applies whenever a data mart view built on that source is created, recreated or linked. How views are deployed is described on the Managing data mart views page.
Switching the transfer on
The transfer is controlled by the Transfer access rights to created data mart views option on the Parameters tab of the data source (see Data sources). The option is cleared by default: the transfer requires extra privileges for the service account of the data source, so an administrator turns it on deliberately.
While the option is cleared, the object is created from the script without any access rights being granted or revoked in the database, the preview lists nothing, and materialized views remain available.
What is transferred
When the option is enabled, DataForge reads the access rights of the fact tables and dimension groups the data mart is built on and applies the equivalent rights to the object whenever it is created, recreated or linked. Only rights that hold on all source tables are carried over: a user or a role that may read one source table but not another does not receive access to the data mart object. A calculated measure is readable only by those who may read every element its formula relies on, and when several masks apply to one column, the most restrictive one is kept.
The transfer never changes the object itself: the object is created exactly from the script shown in the creation dialog, and the access rights are then placed on it.
When the access rights of the source tables change, the rights of the object are re-synchronized without recreating it.
How the access rights reach the object
| Object type | How the access rights reach the object |
|---|---|
| Regular view | Users and roles allowed on all source tables are granted read access to the view, and the view is evaluated with the rights of the person reading it, so the database itself applies the column restrictions and row-level security policies of the source tables to every reader |
| Table | Users and roles are granted read access to the table. A user or role with column restrictions at the source is granted only the columns it may read. The row-level security policies of the source tables are recreated on the table, and on MS SQL Server the masks of the source columns are added to the table columns |
| Materialized view | Unavailable while the transfer is enabled — see Materialized views below |
Row-level security on tables
Row-level security policies are recreated on tables in PostgreSQL, ClickHouse and MS SQL Server, and they are carried over from any number of protected source tables. The restrictions of different source tables apply together: a reader sees a row of the table only when the policies of every protected source table let them see it. A user or role that no policy of a protected source table admits sees no rows of the table, just as at the source.
A row-level security policy can be recreated on a table only when every field of its condition is a column shown in the data mart (in a data mart with grouping, a field the data is grouped by) and the data mart is neither of the With grouping and pivoting type nor merges several fact tables with UNION. Otherwise the users and roles that policy restricts are denied access to the object, so that they never see rows the source hides from them. A user or role with column restrictions is denied access in the same way when none of the object's columns is left readable to it, or when the columns of the object cannot be determined.
In MS SQL Server a row-level security policy is a filter function that itself decides which rows each user sees, and it applies to all users. The table receives a call of the same functions on its own columns, so their logic is carried over unchanged. A policy is carried over when every argument of its function is either a column shown in the data mart (in a data mart with grouping, a field the data is grouped by) or a constant, and the function is in the same database as the table; otherwise all users and roles are denied access to the object. To create the policy on the table, the service account of the data source needs the CREATE FUNCTION permission in the database, ALTER ANY SECURITY POLICY, ALTER on the schema of the object and REFERENCES on the functions of the source policies.
A table can hide only whole rows. When a reader may not see the dimension group record that a row is joined with, the whole row is hidden on the table, whereas a query to the source tables would return it with empty dimension group fields. The reader never sees more than at the source, but may see fewer rows.
Materialized views
While the transfer is enabled on the data source, a materialized view cannot be used: a stored snapshot of the result cannot be filtered for each reader separately. Create a regular view or a table instead.
- In the creation dialog, Materialized view is shown disabled with the tooltip "Materialized views are unavailable for a data source with permission transfer enabled: row-level access to a stored snapshot cannot be reproduced. Use a regular view or a table instead"
- For a materialized view deployed earlier, Update data mart view is disabled with an explanatory tooltip once the transfer is enabled
- Creating, linking or recreating a materialized view on such a data source — by hand or by an update rule — is refused with an error (see Errors below)
The Transferred access rights tab
The Transferred access rights tab of the data mart view details lists one row per right discovered on the source tables:
| Column | Description |
|---|---|
| Source | Source table the right was discovered on |
| Source type | Fact table or Dimension group |
| Access right | Table rights (SELECT), Column-level rights, Row-level rights or Data masking |
| Details | What the right is: "Read the table" for table rights, "Columns unavailable: …" for a column restriction, the column and its mask for masking, or the name of the policy with the rows it leaves visible — "only rows where …" or "all rows (no restriction)". A policy that cannot be carried to the object is marked "— cannot be carried to the object, these principals get no access" |
| Applies to | Who the right concerns. For table rights, the users and roles that hold them. For a row-level policy, the users and roles it restricts — a list, "all principals" or "all except: …". For a column restriction, the one user or role it restricts, so each restricted user or role has its own row. For masking, "all principals" or "all except: …", naming those exempt from the mask |
| Transfer result | Transferred or Not transferred. Shown as "-" while nothing is deployed yet |
A column restriction is Transferred when the object carries it — a regular view through the database itself, a table through column grants — and Not transferred when the user or role it restricts is denied access to the object instead. A row-level policy is Not transferred when the users and roles it restricts are denied access.
Actions on the tab:
- Check sources — re-reads the access rights from the target database, so that the list reflects the rights as they are now
- Create view or Update view — deploys the object and transfers the rights to it in the same operation
The Create view and Update view buttons on this tab are available only to the Super administrator, Administrator and Developer roles; an Analyst creates and updates the view from the view menu.
The Access rights column of the past updates calendar links to the report of the rights that were transferred by that operation (see Refreshing data mart views).
Access-rights preview
The Access rights to be transferred panel is shown above the script when a view is created or updated, and in the Recreate view dialog. It lists what the object will receive:
| Field | Contents |
|---|---|
| Users receiving access | Users who will be able to read the object |
| Roles receiving access | Roles that will be able to read the object |
| Access to a subset of columns | Each user or role that may read only some of the columns, with those columns |
| N columns with restricted access | Each column that some users or roles cannot read, with "not readable by: …" naming them |
| Masked columns | Each masked column with its mask |
| Row-level security policies | Each policy with its source table, the rows it leaves visible, the users and roles it restricts, and how it reaches the object |
How a row-level security policy reaches the object is shown next to it:
| Mode | Meaning |
|---|---|
| enforced by the database | The object is a regular view, and the database applies the source policy to every reader |
| policy created on the object | The policy is recreated on the table |
| access denied to the restricted users | The policy cannot be carried to the object, so the users and roles it restricts do not receive access |
| filters no rows, nothing to transfer | The policy lets every row through, so there is nothing to restrict |
The panel can also show:
- The warning "Security-first fallback: N users or roles will be denied access to the object because their row-level restrictions cannot be enforced on it", followed by their names
- The message "The source permissions changed since they were last applied to the object"
- "No access rights were discovered on the source tables" when there is nothing to transfer, which is also the case while the transfer is switched off on the data source
The preview is for information only: it never blocks executing the script or confirming Recreate view. If the preview cannot be computed, the panel shows "Could not compute the permission preview" with a Retry button, and the object can still be deployed.
Errors
If the access rights cannot be transferred to the object, the view goes to Update error, with the reason in the tooltip of the badge.
| Message | Cause |
|---|---|
| "Materialized views are unavailable for data sources with permission transfer enabled: row-level access to a stored snapshot cannot be reproduced on the object. Create a regular view or a table instead" | A materialized view is being created, linked or recreated — by hand or by an update rule — on a data source with the access-rights transfer enabled |
| "The service account “…” cannot read the system catalogs. Grant it access or switch off permission transfer on the data source" | The access rights of the source tables cannot be read, so they cannot be transferred |
| "The service account “…” lacks a privilege needed to apply permissions to the data mart object" | The service account of the data source cannot place the access rights on the object — for example, it lacks the permissions needed to create row-level security on an MS SQL Server table |