Access Control

Users and user groups can be used for fine-tuning access to different actions. The CData 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 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

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:

Group name	Default permissions	Scope
`admin-role`	`CRUDEAL on *` (full rights)	Virtual Database/VDB
`connect-dv-role`	`C_____A_`	Virtual Database/VDB
`dv-developer-role`	`Allows to manage replications`	Virtual Database/VDB
`export-dv-role`	`C_____A_`	Virtual Database/VDB
`odata-role`	`Allows interacting via OData protocol`	Virtual Database/VDB
`superadmin-role`	`CRUDEAL on *` (full rights)	CData Virtuality Server

dv-developer-role available since v4.1

User Rights

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

Right	Description
C	Create a table/view
R	Read contents of a table/view
U	Update the content of a table
D	Delete some table content
E	Execute a stored procedure
A	Alter a view
L	Use objecttable
	Create temporary tables (special case, see below)

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 CData 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:

SQL

"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:

SQL

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.

Restricting Login

You can restrict login with a SYSADMIN.allowUserLogin procedure

CODE

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:

CODE

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:

SQL

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:

SQL

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:

SQL

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

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

SQL

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