12.5. Assigning Access by Function (System Procedures, SQL Queries, and Default Procedures)


VoltDB Home » Documentation » Using VoltDB

12.5. Assigning Access by Function (System Procedures, SQL Queries, and Default Procedures)

It is not always convenient to assign permissions one at a time. You might want a special role for access to all user-defined stored procedures. Also, there are special capabilities available within VoltDB that are not called out individually in the schema so cannot be assigned using the CREATE PROCEDURE statement.

For these special cases VoltDB provides named permissions that you can use to assign functions as a group. For example, the ALLPROC permission grants a role access to all user-defined stored procedures so the role does not need to be granted access to each procedure individually.

Several of the special function permissions have two versions: a full access permission and a read-only permission. So, for example, DEFAULTPROC assigns access to all default procedures while DEFAULTPROCREAD allows access to only the read-only default procedures; that is, the TABLE.select procedures. Similarly, the SQL permission allows the user to execute both read and write SQL queries interactively while SQLREAD only allows read-only (SELECT) queries to be executed.

One additional functional permission is access to the read-only system procedures, such as @Statistics and @SystemInformation. This permission is special in that it does not have a name and does not need to be assigned; all authenticated users are automatically assigned read-only access to these system procedures.

Table 12.1, “Named Security Permissions” describes the named functional permissions.

Table 12.1. Named Security Permissions

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.

In the CREATE ROLE statement you enable access to these functions by including the permission name in the WITH clause. (The default, if security is enabled and the keyword is not specified, is that the role is not allowed access to the corresponding function.)

Note that the permissions are additive. So if a user is assigned one role that allows access to SQLREAD but not DEFAULTPROC, but that user is also assigned another role that allows DEFAULTPROC, the user has both permissions.

The following example assigns full access to members of the ops role, access to interactive SQL queries (and default procedures by inheritance) and all user-defined procedures to members of the developer role, and no special access beyond read-only system procedures to members of the apps role.

CREATE ROLE developer WITH sql, allproc;