Database Roles

CQL uses database roles to represent users and group of users. Syntactically, a role is defined by:

  1. role_name ::= identifier | string

CREATE ROLE

CREATE ROLE statement:

  1. create_role_statement ::= CREATE ROLE [ IF NOT EXISTS ] role_name                          [ WITH role_options# ]role_options ::= role_option ( AND role_option)*role_option ::= PASSWORD '=' string                | HASHED PASSWORD '=' string                | LOGIN '=' boolean                | SUPERUSER '=' boolean                | OPTIONS '=' map_literal                | ACCESS TO DATACENTERS set_literal                | ACCESS TO ALL DATACENTERS                | ACCESS FROM CIDRS set_literal                | ACCESS FROM ALL CIDRS

For instance:

  1. CREATE ROLE new_role;CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true;CREATE ROLE alice WITH HASHED PASSWORD = '$2a$10$JSJEMFm6GeaW9XxT5JIheuEtPvat6i7uKbnTcxX3c1wshIIsGyUtG' 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 };CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true AND ACCESS TO DATACENTERS {'DC1', 'DC3'};CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true AND ACCESS TO ALL DATACENTERS;CREATE ROLE bob WITH LOGIN = true and PASSWORD = 'password_d' AND ACCESS FROM CIDRS { 'region1', 'region2' };CREATE ROLE hob WITH LOGIN = true and PASSWORD = 'password_c' AND ACCESS FROM ALL CIDRS;

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. 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. CREATE permission on the database roles resource may issue CREATE ROLE requests (see the relevant section), unless the client is a SUPERUSER. Role management in Cassandra 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.

Setting credentials for internal authentication

WITH PASSWORD clause to set a password for internal authentication, enclosing the password in single quotation marks. LOGIN privileges, the WITH PASSWORD clause is not necessary. WITH HASHED PASSWORD to provide the jBcrypt hashed password directly. See the hash_password tool.

Restricting connections to specific datacenters

network_authorizer has been configured, you can restrict login roles to specific datacenters with the ACCESS TO DATACENTERS clause followed by a set literal of datacenters the user can access. Not specifiying datacenters implicitly grants access to all datacenters. The clause ACCESS TO ALL DATACENTERS can be used for explicitness, but there’s no functional difference.

Restricting connections from specific CIDR groups

cidr_authorizer has been configured, you can restrict roles to login only from specific regions, aka CIDR groups, with the ACCESS FROM CIDRS clause followed by a set literal of CIDR groups the user can access from. Not specifying CIDR groups implicitly grants access from all CIDR groups. The clause ACCESS FROM ALL CIDRS can be used for explicitness, but there’s no functional difference. This clause can be also be used to remove any CIDR groups restrictions. Valid CIDR groups should be used with ACCESS FROM CIDRS clause. nodetool list-cidrgroups command can be used to see available CIDR groups in the Cluster.

Creating a role conditionally

IF NOT EXISTS option is used. If the option is used and the role exists, the statement is a no-op:

  1. CREATE ROLE other_role;CREATE ROLE IF NOT EXISTS other_role;

ALTER ROLE

ALTER ROLE statement:

  1. alter_role_statement ::= ALTER ROLE [ IF EXISTS ] role_name WITH role_options

For example:

  1. ALTER ROLE bob WITH PASSWORD = 'PASSWORD_B' AND SUPERUSER = false;ALTER ROLE bob WITH HASHED PASSWORD = '$2a$10$JSJEMFm6GeaW9XxT5JIheuEtPvat6i7uKbnTcxX3c1wshIIsGyUtG' AND SUPERUSER = false;ALTER ROLE rob WITH LOGIN = true and PASSWORD = 'password_c' AND ACCESS FROM ALL CIDRS;ALTER ROLE hob WITH LOGIN = true and PASSWORD = 'password_d' AND ACCESS FROM CIDRS { 'region1' };

IF EXISTS is used in which case the operation is a no-op. WITH HASHED PASSWORD to provide the jBcrypt hashed password directly. See the hash_password tool.

Restricting connections to specific datacenters

network_authorizer has been configured, you can restrict login roles to specific datacenters with the ACCESS TO DATACENTERS clause followed by a set literal of datacenters the user can access. To remove any data center restrictions, use the ACCESS TO ALL DATACENTERS clause.

Restricting connections from specific CIDR groups

cidr_authorizer has been configured, you can restrict roles to login only from specific regions, aka CIDR groups, with the ACCESS FROM CIDRS clause followed by a set literal of CIDR groups the user can access from. Not specifying CIDR groups implicitly grants access from all CIDR groups. The clause ACCESS FROM ALL CIDRS can be used for explicitness, but there’s no functional difference. This clause can be also be used to remove any CIDR groups restrictions. Valid CIDR groups should be used with ACCESS FROM CIDRS clause. nodetool list-cidrgroups command can be used to see available CIDR groups in the Cluster.

ALTER ROLE statements:

  • SUPERUSER status to alter the SUPERUSER status of another role
  • SUPERUSER status of any role it currently holds
  • PASSWORD)
  • ALTER permission <cql-permissions> on that role

    DROP ROLE

    DROP ROLE statement:
    1. drop_role_statement ::= DROP ROLE [ IF EXISTS ] role_name
    DROP ROLE requires the client to have DROP permission <cql-permissions> 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. IF EXISTS option is used. If the option is used and the role does not exist the statement is a no-op.

    GRANT ROLE

    GRANT ROLE statement:
    1. grant_role_statement ::= GRANT role_name TO role_name
    For example:
    1. GRANT report_writer TO alice;
    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:
    1. 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

    REVOKE ROLE statement:
    1. revoke_role_statement ::= REVOKE role_name FROM role_name
    For example:
    1. REVOKE report_writer FROM alice;
    report_writer role from alice. Any permissions that alice has acquired via the report_writer role are also revoked.

    LIST ROLES

    LIST ROLES statement:
    1. list_roles_statement ::= LIST ROLES [ OF role_name] [ NORECURSIVE ]
    For instance:
    1. LIST ROLES;
    DESCRIBE permission on the database roles resource. alice, including those transitively acquired:
    1. LIST ROLES OF alice;
    bob without including any of the transitively acquired ones:
    1. LIST ROLES OF bob NORECURSIVE;

    Users

    USER. For backward compatibility, the legacy syntax has been preserved with USER centric statements becoming synonyms for the ROLE based equivalents. In other words, creating/updating a user is just a different syntax for creating/updating a role.

    CREATE USER

    CREATE USER statement:
    1. create_user_statement ::= CREATE USER [ IF NOT EXISTS ] role_name                          [ WITH [ HASHED ] PASSWORD string ]                          [ user_option ]user_option: SUPERUSER | NOSUPERUSER
    For example:
    1. CREATE USER alice WITH PASSWORD 'password_a' SUPERUSER;CREATE USER bob WITH PASSWORD 'password_b' NOSUPERUSER;CREATE USER bob WITH HASHED PASSWORD '$2a$10$JSJEMFm6GeaW9XxT5JIheuEtPvat6i7uKbnTcxX3c1wshIIsGyUtG' NOSUPERUSER;
    CREATE USER command is equivalent to CREATE ROLE where the LOGIN option is true. So, the following pairs of statements are equivalent:
    1. CREATE USER alice WITH PASSWORD 'password_a' SUPERUSER;CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true AND SUPERUSER = true;CREATE USER IF NOT EXISTS alice WITH PASSWORD 'password_a' SUPERUSER;CREATE ROLE IF NOT EXISTS alice WITH PASSWORD = 'password_a' AND LOGIN = true AND SUPERUSER = true;CREATE USER alice WITH PASSWORD 'password_a' NOSUPERUSER;CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true AND SUPERUSER = false;CREATE USER alice WITH PASSWORD 'password_a' NOSUPERUSER;CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true;CREATE USER alice WITH PASSWORD 'password_a';CREATE ROLE alice WITH PASSWORD = 'password_a' AND LOGIN = true;CREATE ROLE rob WITH LOGIN = true and PASSWORD = 'password_c' AND ACCESS FROM ALL CIDRS;CREATE ROLE hob WITH LOGIN = true and PASSWORD = 'password_d' AND ACCESS FROM CIDRS { 'region1' };

    ALTER USER

    ALTER USER statement:
    1. alter_user_statement ::= ALTER USER [ IF EXISTS ] role_name [ WITH [ HASHED ] PASSWORD string] [ user_option]
    IF EXISTS is used in which case the operation is a no-op. For example:
    1. ALTER USER alice WITH PASSWORD 'PASSWORD_A';ALTER USER alice WITH HASHED PASSWORD '$2a$10$JSJEMFm6GeaW9XxT5JIheuEtPvat6i7uKbnTcxX3c1wshIIsGyUtG';ALTER USER bob SUPERUSER;

    DROP USER

    DROP USER statement:
    1. drop_user_statement ::= DROP USER [ IF EXISTS ] role_name

    LIST USERS

    LIST USERS statement:
    1. list_users_statement::= LIST USERS
    LIST ROLES, but only roles with theLOGIN` privilege are included in the output.

    Data Control

    Permissions

    Permissions on resources are granted to roles; there are several different types of resources in Cassandra and each type is modelled hierarchically:
  • ALL KEYSPACES → KEYSPACE → TABLE.
  • ALL FUNCTIONS → KEYSPACE → FUNCTION
  • ALL ROLES → ROLE
  • ALL MBEANS → MBEAN SELECT on a KEYSPACE automatically grants it on all TABLES in that KEYSPACE. Likewise, granting a permission on ALL FUNCTIONS grants it on every defined function, regardless of which keyspace it is scoped in. It is also possible to grant permissions on all functions scoped to a particular 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
  • EXECUTE
  • UNMASK
  • SELECT_MASKED EXECUTE is only relevant in the context of functions or mbeans; granting EXECUTE on a resource representing a table is nonsensical. Attempting to GRANT a permission on resource to which it cannot be applied results in an error response. The following illustrates which permissions can be granted on which types of resource, and which statements are enabled by that permission.

    GRANT PERMISSION

    GRANT PERMISSION statement:
    1. grant_permission_statement ::= GRANT permissions ON resource TO role_namepermissions ::= ALL [ PERMISSIONS ] | permission [ PERMISSION ]permission ::= CREATE | ALTER | DROP | SELECT | MODIFY | AUTHORIZE | DESCRIBE | EXECUTE | UNMASK | SELECT_MASKEDresource ::=    ALL KEYSPACES                | KEYSPACE keyspace_name                | [ TABLE ] table_name                | ALL ROLES                | ROLE role_name                | ALL FUNCTIONS [ IN KEYSPACE keyspace_name ]                | FUNCTION function_name '(' [ cql_type( ',' cql_type )* ] ')'                | ALL MBEANS                | ( MBEAN | MBEANS ) string
    For example:
    1. GRANT SELECT ON ALL KEYSPACES TO data_reader;
    data_reader permission to execute SELECT statements on any table across all keyspaces:
    1. GRANT MODIFY ON KEYSPACE keyspace1 TO data_writer;
    data_writer permission to perform UPDATE, INSERT, UPDATE, DELETE and TRUNCATE queries on all tables in the keyspace1 keyspace:
    1. GRANT DROP ON keyspace1.table1 TO schema_owner;
    schema_owner role permissions to DROP a specific keyspace1.table1:
    1. GRANT EXECUTE ON FUNCTION keyspace1.user_function( int ) TO report_writer;
    report_writer role permission to execute SELECT, INSERT and UPDATE queries which use the function keyspace1.user_function( int ):
    1. GRANT DESCRIBE ON ALL ROLES TO role_admin;
    role_admin role permission to view any and all roles in the system with a LIST ROLES statement.

    GRANT ALL

    GRANT ALL form is used, the appropriate set of permissions is determined automatically based on the target resource.

    Automatic Granting

    CREATE KEYSPACE, CREATE TABLE, CREATE FUNCTION, CREATE AGGREGATE or CREATE ROLE statement, the creator (the role the database user who issues the statement is identified as), is automatically granted all applicable permissions on the new resource.

    REVOKE PERMISSION

    REVOKE PERMISSION statement:
    1. revoke_permission_statement ::= REVOKE permissions ON resource FROM role_name
    For example:
    1. 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 EXECUTE ON FUNCTION keyspace1.user_function( int ) FROM report_writer;REVOKE DESCRIBE ON ALL ROLES FROM role_admin;
    SELECT permissions revoked. The following tables will be available to all authorized users regardless of their assigned role: ```
  • system_schema.keyspaces system_schema.columns system_schema.tables system.local system.peers
    1. ### LIST PERMISSIONS
    2. `LIST PERMISSIONS` statement:
    list_permissions_statement ::= LIST permissions [ ON resource] [ OF role_name[ NORECURSIVE ] ]
    1. For example:
    LIST ALL PERMISSIONS OF alice;
    1. `alice`, including those acquired transitively from any other roles:
    LIST ALL PERMISSIONS ON keyspace1.table1 OF bob;
    1. `keyspace1.table1` granted to `bob`, including those acquired transitively from any other roles. 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` or one of `bob’s roles:
    LIST SELECT PERMISSIONS OF carlos; `` carlosor any ofcarlos’s roles, limited to SELECT permissions on any resource.