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.

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:

2. Processing an INSERT statement:

3. Processing an UPDATE statement:

4. Processing a DELETE statement:

5. Processing an EXEC/CALL statement:

6. Processing any function:

7. Process any ALTER or CREATE TRIGGER statements:

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:

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

See Also

Permission Management for the dedicated system procedure for setting permissions