Access Control

You are looking at an older version of the documentation. The latest version is found here.

Users and user groups can be used for fine-tuning access to different actions. The Data Virtuality Server includes one user and several preconfigured groups that cannot be modified or deleted. You can add your own users and groups, though - for more detail, please refer to Data Roles.

The information in this section is valid for both authentication mechanisms used in the Data 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

Default User and User Groups

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

The default groups, their permissions, and their scopes are as follows:

User Rights

The following access rights are defined in the Data Virtuality Server:

Permissions do not apply to the metadata reporting SYS and pg_catalog schemas which are always accessible to all users. However, the SYSADMIN, SYSLOG, and UTILS schemas may need permissions as applicable.

Default Rights

The admin user has all rights on any resources (including tables) in the Data Virtuality Server. All other users have no rights by default.

Configuring Permissions

You can grant permissions to roles by calling the SYSADMIN.setPermissions stored procedure:

"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 of the fully qualified:

• schema name  - like customers
• table name - like customers.address
• view name - like views.googleAnalyticsAndMongoDbView
• stored procedure name - like SYSADMIN.setPermissions

The rights for more specific resource names override those for the less specific ones, e.g., if you prohibit reading contents of a schema, you can still enable reading contents of a particular table inside this schema.

The permissions on a particular data source's resource imply the appropriate permissions on its materialized representation in the Analytical Storage. This means that if you grant a role a read permission to a specific table, a user with this role will also be able to read the appropriate materialized table in the Analytical Storage if the SQL query is redirected there by an enabled optimization.

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.

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 his 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);;