CREATE ROLE

Documentation

VoltDB Home » Documentation » Using VoltDB

CREATE ROLE

CREATE ROLE — Defines a role and the permissions associated with that role.

Synopsis

CREATE ROLE role-name [WITH permission [,...]]

Description

The CREATE ROLE statement defines a named role that can be used to assign access rights to specific procedures and functions. When security is enabled in the database configuration, the permissions assigned in the CREATE ROLE and CREATE PROCEDURE statements specify which users can access which functions.

Use the CREATE PROCEDURE statement to assign permissions to named roles for accessing specific stored procedures. The CREATE ROLE statement lets you assign certain generic permissions. The following table describes the permissions that can be assigned the WITH clause.

PermissionDescriptionInherits
DEFAULTPROCREADAccess to read-only default procedures (TABLE.select) 
DEFAULTPROCAccess to all default procedures (TABLE.select, TABLE.insert, TABLE.delete, TABLE.update, and TABLE.upsert)DEFAULTPROCREAD
SQLREADAccess to read-only ad hoc SQL queries (SELECT)DEFAULTPROCREAD
SQLAccess to all ad hoc SQL queries and default proceduresSQLREAD, DEFAULTPROC
ALLPROCAccess to all user-defined stored procedures 
ADMINFull access to all system procedures, all user-defined procedures, as well as default procedures, ad hoc SQL, and DDL statements.ALLPROC, DEFAULTPROC, SQL

Note: For backwards compatibility, the special permissions ADHOC and SYSPROC are still recognized. They are interpreted as synonyms for SQL and ADMIN, respectively.

The generic permissions are denied by default. So you must explicitly enable them for those roles that need them. For example, if users assigned to the "interactive" role need to run ad hoc queries, you must explicitly assign that permission in the CREATE ROLE statement:

CREATE ROLE interactive WITH sql;

Also note that the permissions are additive. So if a user is assigned to one role that allows access to defaultproc but not allproc, but that user also is assigned to another role that allows allproc, the user has both permissions.

Example

The following example defines three roles — admin, developer, and batch — each with a different set of permissions:

CREATE ROLE admin WITH admin;
CREATE ROLE developer WITH sql, allproc;
CREATE ROLE batch WITH defaultproc;