Skip to main content
Skip table of contents

Permissions

You are looking at an older version of the documentation. The latest version is found here.

For any resource path in a virtual database, you can set the CREATE, READ, UPDATE, DELETE, EXECUTE, ALTER, LANGUAGE (CRUDEAL) permissions. A resource path can be as specific as the fully qualified name of a column or as general as 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 a READ permission 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 the least specific resource paths. The first such permission found will be used. Thus it is possible to set very general permissions at high-level resource path names and override them as necessary at more specific resource paths.

Permission grants are only needed for resources that a role needs access to. Permissions are only applied to the columns, tables, or procedures in the user query, not to every resource accessed transitively through view and procedure definitions. Therefore, it is important to ensure that permission grants are applied consistently across models that access the same resources.

If a role's permissions overlap, the positive permission is chosen. Thus, if a user has role_1 and role_2, with role_1 having R permission for view1 and role_2 having negative permission ('') for view1, the user can see and access view1already opened tabs.

Permissions are applied to new and already created sessions (previously opened tabs) since v2.4.22

Permission Overlap

The current and old behaviour can be chosen by setting the OLD_PERMISSIONS_BEHAVIOR option. Default value: TRUE (old behaviour).

Old behaviour

If a role's permissions overlap, the positive permission is chosen. Thus, if a user has role_1 and role_2, with role_1 having R permission for view1 and role_2 having negative permission ('') for view1, the user can see and access view1.

Current behaviour

More specific permissions are chosen in case of permissions overlap. If a user has role_1 and role_2, with role_1 having R permission for all resources and role_2 having negative permission ('') for data source ds_1, the user cannot see and access ds_1.

If the user's roles have permissions for the same resources, permission for the first created role (in case of normal authentication) or the first alphabetical role (in case of LDAP authentication) is chosen. If a user has role_1 and role_2, with role_1 having R permission for view1 and role_2 being created earlier and having negative permission ('') for view1, this will work as follows:

  • Normal authentication: the user cannot see and access view1 , because role_2 has been created earlier;
  • LDAP authentication: the user can see and access view1, because role_1 comes first in alphabetical order.

Permissions do not apply 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.

Access Rights

For different actions, the user account requires different access rights. Below, we list the rights required for these actions and what the rights should apply to.

1. Processing a SELECT statement or executing a stored procedure:

RightObject
READTables being accessed or the procedure being called

READ

Every column referenced

2. Processing an INSERT statement:

RightObject

CREATE

Table being inserted into
CREATEEvery column being inserted in that table

3. Processing an UPDATE statement:

RightObject
UPDATETable being updated
UPDATEEvery column being updated in that table
READEvery column referenced in the criteria

4. Processing a DELETE statement:

RightObject
DELETETable being deleted
READEvery column referenced in the criteria

5. Processing an EXEC/CALL statement:

RightObject
EXECUTE or READ
Procedure being executed

6. Processing any function:

RightObject
EXECUTE or READ
Function being called

7. Process any ALTER or CREATE TRIGGER statements:

RightObject
ALTER
View or procedure that is affected

Please note that INSTEAD OF triggers (update procedures) are not yet regarded as full schema objects and are treated as view attributes instead.

8. Processing any OBJECTTABLE function:

RightObject
LANGUAGE
Allowed language name

Additionally, processing any statement against a temporary table requires the allowCreateTempTables attribute on any applicable role.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.