Permissions

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.

Permission Resource Type

Permission resource type can be defined in two ways:

1. Adding a resource type prefix to the resource name, or
2. Setting the resourceType parameter. In this case, the resource type prefix is added to the permission resource name, and the permission is saved with this prefix in SYSADMIN.Permissions.

Supported resource types include:

• table
• view
• procedure
• function
• job

Permissions can be set for all procedures, functions, views or tables in a virtual schema or a data source using resource type.

Permissions with and without a specified resource type are stored separately in SYSADMIN.Permissions. This means that a single object can have two permissions: one with and one without a specified resource type. In this case:

• Permissions with a specified resource type take precedence and will be applied;
• Permissions without a specified resource type will only apply if the specific resource type permission is deleted.

Examples

1. Setting permissions for view schema.view_1 using resource type prefix:

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'view:schema.view_1', "permissions" => 'R');;

2. Setting permissions for the schema.proc_1 procedure using the resourceType parameter. The permission will be saved in SYSADMIN.Permissions with the resource name 'procedure:schema.proc_1':

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'schema.proc_1', "permissions" => 'RE', "resourceType" => 'procedure');;

3. Setting permissions for all procedures in schema_1:

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'procedure:schema_1', "permissions" => 'RE');;

4. role_1 has 2 permissions for proc_1 with and without the resource type prefix. In this case, the RE permission with the prefix is applied:

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'procedure:schema_1.proc_1', "permissions" => 'RE');;
CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'schema_1.proc_1', "permissions" => 'RDEA');;

Wildcards

Permissions can be set using the * wildcard. When used with a resource type, this allows permissions to be set for all procedures, functions, views, or tables.

Examples

1. Setting permissions for all objects:

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => '*', "permissions" => 'R');;

2. Setting permissions for all functions:

CALL "SYSADMIN.setPermissions"("role_name" => 'role_1', "resourceName" => 'function:*', "permissions" => 'RE');;

Assigning and Removing Permissions

Permissions on an object can be assigned or removed either by:

• An admin user;
• The owner of the object (procedure or job);
• A user with "A" (Alter) permissions on the object. This user can assign and remove only those permissions that they hold for this object.

Permissions resource type, wildcards and rules of assigning and removing permissions available since v4.9

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.

Owner and Runner (Executor) of Jobs and Procedures

Jobs and procedures have the owner and runner attributes. Schedules have the owner attribute. These attributes define access rights for jobs and procedures.

Owner

By default, the creator of a job or a procedure is its owner. The owner can be set to a different user name, even a non-existent one. In procedures, it can be set while creating or altering the procedure. Jobs are created with the default value of the owner, and the owner can be changed with the SYSADMIN.changeJobParameters procedure. Only members of admin-role can change the owner. 

Runner

By default, the runner is set to CALLER. In procedures it could be set while creating a procedure or while altering a procedure using EXECUTE AS. Jobs are created with a default value of the runAs and runAs could be changed with the SYSADMIN.changeJobParameters procedure. Only owners and members of admin-role can change the runner.

Allowed values are CALLER and OWNER.

CALLER means the permissions of the user who calls the job (the owner of the schedule that triggered the job) or the procedure will be applied to the job or the procedure. For example, if the job or the procedure contains SELECT from a PostgreSQL data source with a name pg and a table table_1 and the runner is CALLER, the user who calls the job or the procedure must have READ permission for pg.table_1

OWNER means the permissions of the job or the procedure owner will be applied to the job or the procedure. For example, if the job or the procedure contains SELECT from a PostgreSQL data source with the name pg and table table_1 and runner is OWNER - the user who calls the job (more specifically - the owner of the schedule that triggers the job) or the procedure may not have READ permission for pg.table_1 but the owner of the job or the procedure must have this permission.

Please grant jobs and procedures ALTER permissions with runner = OWNER carefully, because users that have ALTER and EXECUTE permission for such jobs and procedures can change them and execute anything on behalf of the owner and using the owner's access rights.

Owner and Runner (Executor) of jobs and procedures are available since v4.1

See Also

Permission Management for the dedicated system procedure for setting permissions