LDAP Users, Roles, and Permissions

Users and Roles (or Groups)

Users and Roles lists are loaded from Active Directory. Lists are loaded during the first call to the relevant tables after the server start or restart (SYSADMIN.Users and SYSADMIN.Roles) and cached on the server. New users are able to log in to CData Virtuality without refreshing the cache. However, to query new users and roles, the cache must be refreshed using the SYSADMIN.refreshLdapUserCache procedure.

SQL

CALL "SYSADMIN.refreshLdapUserCache"();;

When the CData Virtuality Server is configured to read users, passwords, and roles from an LDAP domain, some features related to these objects can no longer be used in the CData Virtuality Server. In particular, handling users and roles from the CData Virtuality Server is impossible: they should be managed directly in LDAP.

For this reason, the following stored procedures are not available anymore:

SYSADMIN.addRole()
SYSADMIN.renameRole()
SYSADMIN.deleteRole()
SYSADMIN.importUser()
SYSADMIN.addUser()
SYSADMIN.renameUser()
SYSADMIN.deleteUser()
SYSADMIN.changeUserPwd()
SYSADMIN.getEncryptedUserPwd()
SYSADMIN.addUserRole()
SYSADMIN.deleteUserRole()

If they are called, this error message is shown:

UnsupportedLdapUsersHandlerOperationException. This operation is not supported when LDAP is used for the authentication.

Permissions

Permissions are handled and stored locally in dvconfig, and they can be set using the SYSADMIN.setPermissions() system procedure as with the default dvconfig-based authentication mechanism.

Special Permission to Create Temporary Tables

With the default CData Virtuality Server mechanism, the special permission to create temporary tables is normally assigned to a role, and it is set when a new role is created via SYSADMIN.addRole().

Since the CData Virtuality Server with LDAP authentication mechanism cannot handle roles, a procedure to grant or revoke that permission is also provided.

SQL

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

This procedure can be used with both authentication mechanisms and will grant/deny the appropriate permission to the role.

The main difference (but this is only a technical detail) is that if a dvconfig-based authentication is used, the special permission is set to the role and stored in the dv_roles table, as usual; if LDAP is configured for authenticating users, the special permission is stored in a new and appropriate table called dv_ldap_role_props.

Adding a Role with Minimal Permissions

Perform the following steps to create a role with minimum permissions for connecting to the CData Virtuality Server. You might not want to have all users granted admin rights.

1. Create a new group in your directory. Ensure that it is within the search scope of the configuration of the login module.

2. If necessary, create a user that shall not be an administrator.

3. Assign the user to the group. Double-check (see the screenshot):

4. Connect to the CData Virtuality Server as an administrator and go to User Management in the Studio. Check that the Server has recognized the users, roles and assignments.
If not, look at the login module configuration and the directory structure. The screenshot depicts the case where everything was correctly read from the directory:

5. Assign the role a minimal set of permissions. You can use the following script (remember to replace custom-connect-role with your role name):

SQL

BEGIN
	DECLARE string roleName='custom-connect-role';
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN', "permissions" => 'R') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getInstalledLicense', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getServerVersion', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getServerBuildInfo', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getCurrentDWH', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.refreshDataSource', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.refreshAllDataSources', "permissions" => 'RE') ;
    CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getDefaultOptionValue', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getCurrentUser', "permissions" => 'RE') ;
    CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getUserProperty', "permissions" => 'RE') ;
    CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getDataCatalogAttribute', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.reservedwords', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getCatalogs', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getViewAndProcCreationOrder', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.refreshSchema', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.getDataLineage', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.setRemark', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.refreshAllSchemas', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSADMIN.md5', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'UTILS.getJobProperties', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'UTILS.getConnectorProperties', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'UTILS.pipes_helper__status', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'INFORMATION_SCHEMA', "permissions" => 'RE') ;
	CALL SYSADMIN.setPermissions("role_name" => roleName, "resourceName" => 'SYSLOG', "permissions" => 'R') ;
END ;;

All users that belong to this group in your directory will now be able to connect.