Role Management

Data roles are sets of permissions defined per virtual database and determine data access.

Thanks to this role and permission system, the CData Virtuality Server can provide audit log entries which might be useful in case of access violation.

Before applying data roles, consider restricting source system access. The CData Virtuality Server can only access source entries represented in the imported metadata.

Default Roles

The default role is admin, and the default password is admin. The admin user has all rights on any resources (including tables) in the CData Virtuality Server. All other users have no rights by default.

The default roles, permissions, and scopes are as follows:

You cannot rename the admin user, but you can set up a different password using the SYSADMIN.changeUserPwd procedure.

To check if the current user has the given data role, you can use the HASROLE function. This function can also be used in procedures or view definitions for value masking and row-level security, for example, if you need to create a procedure for retrieving and displaying data depending on the user's role.

Creating Custom Roles

The information in this section is valid for both authentication mechanisms used in the CData Virtuality Server: dvconfig-based authentication and LDAP authentication. The only difference is where the settings are stored:

• dvconfig-based authentication: dv_roles table

• LDAP authentication: dv_ldap_role_props table

To create a role in CData Virtuality, call the SYSADMIN.addRole stored procedure:

CALL "SYSADMIN.addRole"(IN name string NOT NULL, IN allowCreateTempTables boolean, OUT id biginteger NOT NULL RESULT)

Granting Permissions to a Role

After you create a role, you can grant permissions to the role by calling the SYSADMIN.setPermissions stored procedure:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'string_role_name' /* mandatory */,
    "resourceName" => 'string_resourceName' /* mandatory */,
    "permissions" => 'string_permissions' /* optional */
);;

Please note that permissions are granted to a role, not to a user. Any user with the specific role will have the permissions assigned to this role.

In this example, we have the managers role, and we want to give the users with this role the permission to read the address table in the customers schema:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'managers',
    "resourceName" => 'customers.address',
    "permissions" => 'R'
);;

The resource names can be any fully qualified:

• schema name (customers)

• table name (customers.address)

• view name (views.googleAnalyticsAndMongoDbView)

• stored procedure name (SYSADMIN.setPermissions)

If a role is given execution rights on the SYSADMIN.setPermissions stored procedure, members of that role can effectively grant/revoke rights to everybody, including themselves. This means the role indirectly gets full rights.

Restricting Login

You can restrict login with a SYSADMIN.allowUserLogin procedure:

CALL "SYSADMIN.allowUserLogin"(
    "name" => 'string_name',
    "loginAllowed" => true
);;

Prohibiting Access to a Specific Resource

 Calling SYSADMIN.setPermissions stored procedure with '' (empty string) permission value will 'deny all' for a resource:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'managers',
    "resourceName" => 'customers.address',
    "permissions" => ''
);;

Complete Removal of Rights to Resources

Calling SYSADMIN.setPermissions stored procedure with NULL permission value will delete the SYSADMIN.Permissions entry. As a result, there will be no particular permissions set for this role, and resource and parent permissions will become active:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'managers',
    "resourceName" => 'customers.address',
    "permissions" => null
);;

Special Permission to Create Temporary Tables

Operations with temporary tables require a special permission. Two of the four default roles, admin-role and superadmin-role, already have this permission. The other two (connect-dv-role and export-dv-role) do not have it. When you create your own user roles via SYSADMIN.addRole(), you can grant this role the right to create temporary tables if needed using this command:

setAllowCreateTempTables(IN role_name string NOT NULL, IN allow boolean NOT NULL)

If a role already exists and you want to check if it has this permission, you can do so using one of the following two commands:

SELECT * FROM (CALL "SYSADMIN.getCreateTempTablesPermissions"()) a;;
-- or
SELECT * FROM "SYSADMIN.getCreateTempTablesPermissions"();;

And here is how to change the setting for an existing role:

CALL sysadmin.setAllowCreateTempTables(<role name>, TRUE|FALSE);;