User and Role Management
The Data Virtuality Server has special stored procedures for user and role management.
User Management
SYSADMIN.addUser
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, OUT id biginteger NOT NULL RESULT)
Example
CALL "SYSADMIN.addUser"(
"name" => 'user1',
"pwd" => 'password',
"role_name" => 'connect-dv-role',
"loginAllowed" => true,
"concurrentQueries" => 10,
"maxQueryTime" => 5,
"maxRows" => 100
);;
loginAllowed
parameter available since v4.3
concurrentQueries
, maxQueryTime,
maxRows
available since v4.8
For more information on query limits please refer to Resource Governor.
SYSADMIN.importUser
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 OPTIONS (Annotation 'Maximal query execution time in minutes, -1 means unlimited'), IN maxRows integer, 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
SYSADMIN.renameUser(IN name string NOT NULL, IN newName string NOT NULL)
SYSADMIN.deleteUser
SYSADMIN.deleteUser(IN name string NOT NULL)
SYSADMIN.changeUserPwd
SYSADMIN.changeUserPwd(IN name string NOT NULL, IN pwd, IN encryptedPwd)
SYSADMIN.getEncryptedUserPwd
SYSADMIN.getEncryptedUserPwd(IN name string NOT NULL, OUT encryptedPwd string NOT NULL RESULT)
SYSADMIN.getCurrentUser
This procedure returns the name of the current user.
SYSADMIN.getCurrentUser(OUT username string NOT NULL RESULT)
SYSADMIN.allowUserLogin
This procedure allows or disallows user login.
SYSADMIN.allowUserLogin(IN name string NOT NULL, IN loginAllowed boolean NOT NULL)
SYSADMIN.allowUserLogin
procedure available since v4.1
encryptedPwd property in SYSADMIN.changeUserPwd available since v4.1
Role Management
SYSADMIN.addRole
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
SYSADMIN.renameRole(IN name string NOT NULL, IN newName string NOT NULL)
SYSADMIN.deleteRole
SYSADMIN.deleteRole(IN name string NOT NULL)
SYSADMIN.addUserRole
SYSADMIN.addUserRole(IN user_name string NOT NULL, IN role_name string NOT NULL)
SYSADMIN.deleteUserRole
SYSADMIN.deleteUserRole(IN user_name string NOT NULL, IN role_name string NOT NULL)
Query Limits
SYSADMIN.setUserLimits(IN name string NOT NULL, IN concurrentQueries integer, IN maxQueryTime integer, IN maxRows integer)
Example
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