Skip to main content
Skip table of contents

Permission Management

The Data Virtuality Server has a special procedure for setting permissions. Please note that permissions are only applied to new sessions (new tabs in the Data Virtuality Studio, SquirreL, etc.) and are not applied to already created sessions (already open tabs).

SYSADMIN.setPermissions

SQL
SYSADMIN.setPermissions(<IN string role_name>, <IN string resourceName>, <IN string permissions>, <IN string condition>, <IN boolean isConstaint>, <IN string mask>, <IN integer maskOrder>, <IN string mapToRole>)

Mandatory Parameters

The resourceName/permissions and mapToRole parameters cannot be applied at the same time. mapToRole could be used only to role_name parameter. Please use either resourceName/permissions or mapToRole

ParameterDescription
role_nameRole name

resourceName

Any resource path, e.g. "model", "model.table", or "model.table.column". For jobs a 'job:job_name' notation should be used

permissions

Consists of the first letters of all available types of permissions (CREATE, READ, UPDATE, DELETE, EXECUTE, ALTER, LANGUAGE).
To delete a particular permission setting created earlier, pass NULL to the permissions parameter
mapToRole

Role name to be mapped to.

Only an SSO role can be mapped to a Data Virtuality role. An SSO role cannot be mapped to an SSO role, and a Data Virtiuality role cannot be mapped to a Data Virtuality role.

Role can be mapped to another role only if this role_name role does not have any permissions assigned.

Resource permissions cannot be assigned to a role if this role is already mapped to another role

mapToRole parameter is available since v4.2

Optional Parameters

The four optional strings allow you to use row-based security settings and column masking.

ParameterDescription

condition

Defines row-based security condition
isConstraintDefines if values will be inserted/updated if the new value matches permission condition (TRUE) or all values will be inserted/updated (FALSE)

mask

Defines column mask value

maskOrder

Defines mask order if there are several masks for one column

Examples

1. Assigning CRUDEAL permissions to all resources for role example_role:

SQL
CALL SYSADMIN.setPermissions("role_name" => 'example_role', "resourceName" => '*', "permissions" => 'CRUDEAL')

2. Mapping SSO role example_role@SSO to the Data Virtuality admin-role:

SQL
CALL SYSADMIN.setPermissions("role_name" => 'example_role@SSO', "mapToRole" => 'admin-role')

All permissions are stored in the SYSADMIN.Permissions table accessible to the admin user, and every user with permission for this table.

SYSADMIN.hasPermission

The procedure is used to check if a user has a certain permission(s) for some resource.

SQL
SYSADMIN.hasPermission(<IN string username>, <IN string resource>, <IN string permissions>)

Parameters

ParameterDescription

username

User name

resource

Any resource path, e.g. "model", "model.table", or "model.table.column". For a job a 'job:job_name' notation should be used

permissions

Consists of the first letters of all available types of permissions (CREATE, READ, UPDATE, DELETE, EXECUTE, ALTER, LANGUAGE)

Here is an example to check if test_user has the READ permission for views:

SQL
CALL SYSADMIN.hasPermissions('test_user', 'views', 'R')

SYSADMIN.hasPermission procedure is available since v4.1

SYSADMIN.changeResourceOwnerAndExecutor

The procedure is used to change permissions, i.e. owner and executor for some resources. Currently supports procedures, jobs, and schedules.

SQL
SYSADMIN.changeResourceOwnerAndExecutor(<IN string name>, <IN string uuid>, <IN string resourceType>, <IN string owner>, <IN string executeAs>)

The name or uuid (for jobs and schedules) parameter, or both must be specified.

Parameters

ParameterDescription

name

Resource name

uuid

UUID of resource, valid only for jobs and schedules

resourceType

Type of resource, field is mandatory, case-insensitive. Supported values are: PROCEDURE, JOB, SCHEDULE
ownerName of the resource owner needs to be set. Owner could be set to a value that is not equal to the current user only by members of admin-role
executeAsResource runner/executor (executeAs for procedures, runAs for jobs, for schedules it is not supported). Allowed values: CALLER and OWNER. 

Here is an example to set user u1 as an owner for job test1:

SQL
CALL "SYSADMIN.changeResourceOwnerAndExecutor"(
    "name" => 'test1',
    "resourceType" => 'job',
    "owner" => 'u1'
);;

SYSADMIN.changeResourceOwnerAndExecutor procedure available since v4.5

JavaScript errors detected

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

If this problem persists, please contact our support.