Grant Authorization CQL Reference¶

CREATE ROLE¶

Creating a role uses the CREATE ROLE statement:

create_role_statement: CREATE ROLE [ IF NOT EXISTS ] `role_name`
                     :     [ WITH `role_options` ]
role_options: `role_option` ( AND `role_option` )*
role_option: PASSWORD '=' `string`
           :| LOGIN '=' `boolean`
           :| SUPERUSER '=' `boolean`
           :| OPTIONS '=' `map_literal`

For instance:

CREATE ROLE new_role;
CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true;
CREATE ROLE bob WITH PASSWORD = 'password_b' AND LOGIN = true AND SUPERUSER = true;
CREATE ROLE carlos WITH OPTIONS = { 'custom_option1' : 'option1_value', 'custom_option2' : 99 };

By default, roles do not possess LOGIN privileges or SUPERUSER status.

Permissions on database resources are granted to roles; types of resources include keyspaces, tables, functions, and roles themselves. Roles may be granted to other roles to create hierarchical permissions structures; in these hierarchies, permissions and SUPERUSER status are inherited, but the LOGIN privilege is not.

If a role has the LOGIN privilege, clients may identify as that role when connecting. For the duration of that connection, the client will acquire any roles and privileges granted to that role.

Only a client with the CREATE permission on the database roles resource may issue CREATE ROLE requests (see the relevant section below) unless the client is a SUPERUSER. Role management in ScyllaDB is pluggable, and custom implementations may support only a subset of the listed options.

Role names should be quoted if they contain non-alphanumeric characters.

CREATE ROLE other_role;
CREATE ROLE IF NOT EXISTS other_role;

create_role_statement: CREATE ROLE [ IF NOT EXISTS ] `role_name`
                     : WITH HASHED PASSWORD `hashed_password`
                     :     ( AND `role_option` )*
hashed_password: `string`
role_option: LOGIN '=' `boolean`
           :| SUPERUSER '=' `boolean`
           :| OPTIONS '=' `map_literal`

#include <crypt.h>
#include <stdio.h>

int main(void) {
  // Generate a hashed password using sha256.
  const char* pwd = crypt("myPassword", "$5$");
  printf("The hashed password is %s\n", pwd);
  return 0;
}

cqlsh> CREATE ROLE bob WITH HASHED PASSWORD = '$5$$QdpObVOY40UOyo.BuWdpRq0Cr/DPkw7ckBEs2NqWVn5';

ALTER ROLE¶

Altering role options uses the ALTER ROLE statement:

alter_role_statement: ALTER ROLE `role_name` WITH `role_options`

For instance:

ALTER ROLE bob WITH PASSWORD = 'PASSWORD_B' AND SUPERUSER = false;

Conditions on executing ALTER ROLE statements:

• A client must have SUPERUSER status to alter the SUPERUSER status of another role

• A client cannot alter the SUPERUSER status of any role it currently holds

• A client can only modify certain properties of the role with which it identified at login (e.g. PASSWORD)

• To modify properties of a role, the client must be granted ALTER permission on that role

DROP ROLE¶

Dropping a role uses the DROP ROLE statement:

drop_role_statement: DROP ROLE [ IF EXISTS ] `role_name`

DROP ROLE requires the client to have DROP permission on the role in question. In addition, client may not DROP the role with which it identified at login. Finally, only a client with SUPERUSER status may DROP another SUPERUSER role.

Attempting to drop a role that does not exist results in an invalid query condition unless the IF EXISTS option is used. If the option is used and the role does not exist, the statement is a no-op.

GRANT ROLE¶

Granting a role to another uses the GRANT ROLE statement:

grant_role_statement: GRANT `role_name` TO `role_name`

For instance:

GRANT report_writer TO alice;

This statement grants the report_writer role to alice. Any permissions granted to report_writer are also acquired by alice.

Roles are modelled as a directed acyclic graph, so circular grants are not permitted. The following examples result in error conditions:

GRANT role_a TO role_b;
GRANT role_b TO role_a;

GRANT role_a TO role_b;
GRANT role_b TO role_c;
GRANT role_c TO role_a;

REVOKE ROLE¶

Revoking a role uses the REVOKE ROLE statement:

revoke_role_statement: REVOKE `role_name` FROM `role_name`

For instance:

REVOKE report_writer FROM alice;

This statement revokes the report_writer role from alice. Any permissions that alice has acquired via the report_writer role are also revoked.

LIST ROLES¶

All the known roles (in the system or granted to the specific role) can be listed using the LIST ROLES statement:

list_roles_statement: LIST ROLES [ OF `role_name` ] [ NORECURSIVE ]

For instance:

LIST ROLES;

returns all known roles in the system, this requires DESCRIBE permission on the database roles resource. And:

LIST ROLES OF alice;

enumerates all roles granted to alice, including those transitively acquired. But:

LIST ROLES OF bob NORECURSIVE

lists all roles directly granted to bob without including any of the transitively acquired ones.

CREATE USER¶

Creating a user uses the CREATE USER statement:

create_user_statement: CREATE USER [ IF NOT EXISTS ] [ WITH PASSWORD `string` ] [ `user_option` ]
user_option: SUPERUSER | NOSUPERUSER

For instance:

CREATE USER alice WITH PASSWORD 'password_a' SUPERUSER;
CREATE USER bob WITH PASSWORD 'password_b' NOSUPERUSER;

CREATE USER where the LOGIN option is true. So, the following pairs of statements are equivalent:

CREATE USER alice WITH PASSWORD 'password_a' SUPERUSER;
CREATE USER IF NOT EXISTS alice WITH PASSWORD 'password_a' SUPERUSER;
CREATE USER alice WITH PASSWORD 'password_a' NOSUPERUSER;
CREATE USER alice WITH PASSWORD 'password_a';

ALTER USER¶

Altering the options of a user uses the ALTER USER statement:

alter_user_statement: ALTER USER `user_name` [ WITH PASSWORD `string` ] [ `user_option` ]

For instance:

ALTER USER alice WITH PASSWORD 'PASSWORD_A';
ALTER USER bob SUPERUSER;

DROP USER¶

Dropping a user uses the DROP USER statement:

drop_user_statement: DROP USER [ IF EXISTS ]

LIST USERS¶

Existing users can be listed using the LIST USERS statement:

list_users_statement: LIST USERS

Permissions¶

Permissions on resources are granted to users; there are several different types of resources in ScyllaDB, and each type is modelled hierarchically:

• The hierarchy of Data resources, Keyspaces, and Tables has the structure ALL KEYSPACES -> KEYSPACE -> TABLE.

Permissions can be granted at any level of these hierarchies, and they flow downwards. So granting permission on a resource higher up the chain automatically grants that same permission on all resources lower down. For example, granting SELECT on a KEYSPACE automatically grants it on all TABLES in that KEYSPACE.

Modifications to permissions are visible to existing client sessions; that is, connections need not be re-established following permissions changes.

The full set of available permissions is:

• CREATE

• ALTER

• DROP

• SELECT

• MODIFY

• AUTHORIZE

• DESCRIBE

Attempting to GRANT permission on a resource to which it cannot be applied results in an error response. The following illustrates which permissions can be granted on which types of resources, and which statements are enabled by that permission.

GRANT PERMISSION¶

Granting permission uses the GRANT PERMISSION statement:

grant_permission_statement: GRANT `permissions` ON `resource` TO `user_name`
permissions: ALL [ PERMISSIONS ] | `permission` [ PERMISSION ]
permission: CREATE | ALTER | DROP | SELECT | MODIFY | AUTHORIZE | DESCRIBE
resource: ALL KEYSPACES
        :| KEYSPACE `keyspace_name`
        :| [ TABLE ] `table_name`
        :| ALL USERS
        :| USER `user_name`

For instance:

GRANT SELECT ON ALL KEYSPACES TO data_reader;

This gives any user with the data_reader role, permission to execute SELECT statements on any table across all keyspaces:

GRANT MODIFY ON KEYSPACE keyspace1 TO data_writer;

This gives any user with the data_writer role, permission to perform UPDATE, INSERT, DELETE, and TRUNCATE queries on all tables in the keyspace1 keyspace:

GRANT DROP ON keyspace1.table1 TO schema_owner;

This gives any user with the schema_owner role, permissions to DROP keyspace1.table1

REVOKE PERMISSION¶

Revoking permission from a user uses the REVOKE PERMISSION statement:

revoke_permission_statement: REVOKE `permissions` ON `resource` FROM `user_name`

For instance:

REVOKE SELECT ON ALL KEYSPACES FROM data_reader;
REVOKE MODIFY ON KEYSPACE keyspace1 FROM data_writer;
REVOKE DROP ON keyspace1.table1 FROM schema_owner;
REVOKE DESCRIBE ON ALL USERS FROM user_admin;

LIST PERMISSIONS¶

Listing granted permissions uses the LIST PERMISSIONS statement:

list_permissions_statement: LIST `permissions` [ ON `resource` ] [ OF `user_name` [ NORECURSIVE ] ]

For instance:

LIST ALL PERMISSIONS OF alice;

Show all permissions granted to alice, including those acquired transitively from any other users:

LIST ALL PERMISSIONS ON keyspace1.table1 OF bob;

Show all permissions on keyspace1.table1 granted to bob, including those acquired transitively from any other users. This also includes any permissions higher up the resource hierarchy, which can be applied to keyspace1.table1. For example, should bob have ALTER permission on keyspace1, that would be included in the results of this query. Adding the NORECURSIVE switch restricts the results to only those permissions which were directly granted to bob:

LIST SELECT PERMISSIONS OF carlos;

Show any permissions granted to carlos, limited to SELECT permissions on any resource.