User and Role Management

The Data Virtuality Server has special stored procedures for user and role management.

User Management

SYSADMIN.addUser

SQL

SYSADMIN.addUser(IN name string NOT NULL, IN pwd string NOT NULL, IN role_name string NOT NULL, IN loginAllowed boolean, IN concurrentQueries integer, IN maxQueryTime integer, IN maxRows integer, IN timezone string, OUT id biginteger NOT NULL RESULT)

Example

SQL

CALL "SYSADMIN.addUser"(
    "name" => 'user1',
    "pwd" => 'password',
    "role_name" => 'connect-dv-role',
    "loginAllowed" => true,
    "concurrentQueries" => 10,
    "maxQueryTime" => 5,
    "maxRows" => 100,
    "timezone" => 'UTC'
);;

Please note that any schedules without an explicitly specified timezone will run in the timezone of the user who owns them. If that user does not have a timezone set, the schedules will run in the server’s timezone.

loginAllowed parameter available since v4.3

concurrentQueries, maxQueryTime, maxRows available since v4.8

timezone available since v4.11

For more information on query limits please refer to Resource Governor.

SYSADMIN.importUser

SQL

SYSADMIN.importUser(IN name string NOT NULL, IN encryptedPwd string NOT NULL, IN role_name string NOT NULL, IN loginAllowed boolean, IN concurrentQueries integer, IN maxQueryTime integer, IN maxRows integer, IN timezone string, OUT id biginteger NOT NULL RESULT)

Please keep in mind that this procedure:

Requires an encrypted password, not the real password;
Updates password and adds (if necessary) the indicated role for existing users;
Throws an exception if you are trying to import a "permanent" ("Admin") user.

SYSADMIN.renameUser

SQL

SYSADMIN.renameUser(IN name string NOT NULL, IN newName string NOT NULL)

SYSADMIN.deleteUser

SQL

SYSADMIN.deleteUser(IN name string NOT NULL)

SYSADMIN.changeUserPwd

SQL

SYSADMIN.changeUserPwd(IN name string NOT NULL, IN pwd, IN encryptedPwd)

SYSADMIN.getEncryptedUserPwd

SQL

SYSADMIN.getEncryptedUserPwd(IN name string NOT NULL, OUT encryptedPwd string NOT NULL RESULT)

SYSADMIN.getCurrentUser

This procedure returns the name of the current user.

SQL

SYSADMIN.getCurrentUser(OUT username string NOT NULL RESULT)

SYSADMIN.allowUserLogin

This procedure allows or disallows user login.

SQL

SYSADMIN.allowUserLogin(IN name string NOT NULL, IN loginAllowed boolean NOT NULL)

SYSADMIN.setUserTimezone

This procedure allows to set default user’s time zone.

SQL

SYSADMIN.setUserTimezone(IN name string NOT NULL, IN timezone string)

Example

SQL

CALL "SYSADMIN.setUserTimezone"(
    "name" => 'user1',
    "timezone" => 'UTC'
);;

Please note that any schedules without an explicitly specified timezone will run in the timezone of the user who owns them. If that user does not have a timezone set, the schedules will run in the server’s timezone.

SYSADMIN.allowUserLogin procedure available since v4.1

encryptedPwdproperty in SYSADMIN.changeUserPwd available since v4.1

SYSADMIN.setUserTimezone procedure available since v4.11

Role Management

SYSADMIN.addRole

SQL

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

Parameter allowCreateTempTables is deprecated since v4.1

SYSADMIN.renameRole

SQL

SYSADMIN.renameRole(IN name string NOT NULL, IN newName string NOT NULL)

SYSADMIN.deleteRole

SQL

SYSADMIN.deleteRole(IN name string NOT NULL)

SYSADMIN.addUserRole

SQL

SYSADMIN.addUserRole(IN user_name string NOT NULL, IN role_name string NOT NULL)

SYSADMIN.deleteUserRole

SQL

SYSADMIN.deleteUserRole(IN user_name string NOT NULL, IN role_name string NOT NULL)

Query Limits

SQL

SYSADMIN.setUserLimits(IN name string NOT NULL, IN concurrentQueries integer, IN maxQueryTime integer, IN maxRows integer)

Example

CODE

CALL "SYSADMIN.setUserLimits"(
    "name" => 'user1',
    "concurrentQueries" => 10,
    "maxQueryTime" => 5,
    "maxRows" => 100
);;

For more information on query limits please read Resource Governor

SYSADMIN.setUserLimits procedure available since v4.8