Permission Management

The CData 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 resourceType>, <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.

Parameter	Description
`role_name`	Role name
`resourceName`	Any resource path, e.g. "model", "model.table", or "model.table.column". resourceName could include a resource type prefix. Resource type prefix supported values are: `table, view, procedure, function, job`. 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 CData Virtuality role. An SSO role cannot be mapped to an SSO role, and a CData 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; To remove a role mapping, you need to map the role to an empty string to clear the existing mapping by calling `setPermissions` with `mapToRole => ''`

mapToRole parameter is available since v4.2

Optional Parameters

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

Parameter	Description
`resourceType`	Type of the resource. Supported values: `table`, `view`, `procedure`, `function`, `job`
`condition`	Defines row-based security condition
`isConstraint`	Defines 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

resourceType parameter and resource type prefixes available since v4.9

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

Parameter	Description
`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

Parameter	Description
`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`
`owner`	Name 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`
`executeAs`	Resource 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

SYSADMIN.copyPermissions

The procedure is used to copy permissions to a role.

SQL

SYSADMIN.copyPermissions(<IN string sourceRoles>, <IN string permissionIds>, <IN string targetRole>, <IN boolean createRoleIfNotExist>)

sourceRoles and permissionIds parameters can be specified both separately and together.

Parameters

Parameter	Description
`sourceRoles`	Source roles (comma-separated) whose permissions will be copied to the target role
`permissionIds`	IDs of permissions (comma-separated) that will be copied to the target role
`targetRole`	The target role to which the permissions will be copied
`createRoleIfNotExist`	If set to `TRUE`, the target role will be created if it does not exist. Default: `FALSE`

The copied permissions will be combined if source roles or source permissions have different permissions for the same object. An SQL warning will be shown in the server logs with the copied combined permissions.

Examples

1. Copy permissions from roles test_role1 and test_role2 to role target-role2 and create target-role2 if it does not exist:

SQL

CALL "SYSADMIN.copyPermissions"("sourceRoles" => 'test_role1,test_role2', "targetRole" => 'target-role2', "createRoleIfNotExist" => true);;

2. Copy permissions with IDs 81 and 85 to role target-role1 and create target-role1 if it does not exist:

SQL

CALL "SYSADMIN.copyPermissions"("permissionIds" => '81,85', "targetRole" => 'target-role1', "createRoleIfNotExist" => true);;

3. Copy permissions with IDs 81 and 85 and from role test-role1 to existing role target-role3:

SQL

CALL "SYSADMIN.copyPermissions"("sourceRoles" => 'test_role1', "permissionIds" => '81,85', "targetRole" => 'target-role3');;

SYSADMIN.copyPermissions procedure is available since v4.10