Grant privileges

YSQL
YCQL

This tutorial demonstrates how to grant privileges in YCQL using the scenario of a company with an engineering organization that has three sub-teams: developers, QA, and DB admins.

Here is what you want to achieve from a role-based access control (RBAC) perspective:

All members of engineering should be able to read data from any keyspace and table.
Developers and QA should be able to modify data in existing tables in the keyspace dev_keyspace.
QA should be able to alter the integration_tests table in the keyspace dev_keyspace.
DB admins should be able to perform all operations on any keyspace.

The exercise assumes you have enabled authentication for YCQL.

1. Create role hierarchy

Connect to the cluster using a superuser role. Use the default cassandra user and connect to the cluster using ycqlsh as follows:

$ ./bin/ycqlsh -u cassandra -p cassandra

Create a keyspace dev_keyspace.

cassandra@ycqlsh> CREATE KEYSPACE IF NOT EXISTS dev_keyspace;

Create the dev_keyspace.integration_tests table:

CREATE TABLE dev_keyspace.integration_tests (
  id UUID PRIMARY KEY,
  time TIMESTAMP,
  result BOOLEAN,
  details JSONB
);

Next, create roles engineering, developer, qa, and db_admin.

cassandra@ycqlsh> CREATE ROLE IF NOT EXISTS engineering;
                 CREATE ROLE IF NOT EXISTS developer;
                 CREATE ROLE IF NOT EXISTS qa;
                 CREATE ROLE IF NOT EXISTS db_admin;

Grant the engineering role to developer, qa, and db_admin roles, as they are all a part of the engineering organization.

cassandra@ycqlsh> GRANT engineering TO developer;
                 GRANT engineering TO qa;
                 GRANT engineering TO db_admin;

List all the roles.

cassandra@ycqlsh> SELECT role, can_login, is_superuser, member_of FROM system_auth.roles;

You should see the following output:

 role        | can_login | is_superuser | member_of
-------------+-----------+--------------+-----------------
          qa |     False |        False | ['engineering']
   developer |     False |        False | ['engineering']
 engineering |     False |        False |                []
    db_admin |     False |        False | ['engineering']
   cassandra |      True |         True |                []

(5 rows)

2. List permissions for roles

You can list all permissions granted to the various roles with the following command:

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

You should see something like the following output:

 role      | resource          | permissions
-----------+-------------------+--------------------------------------------------------------
 cassandra | roles/engineering |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra |   roles/developer |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra |          roles/qa |                               ['ALTER', 'AUTHORIZE', 'DROP']
 cassandra | data/dev_keyspace | ['ALTER', 'AUTHORIZE', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
 cassandra |    roles/db_admin |                               ['ALTER', 'AUTHORIZE', 'DROP']

(5 rows)

This shows the various permissions the cassandra role has. Because cassandra is a superuser, it has all permissions on all keyspaces, including ALTER, AUTHORIZE, and DROP on the roles you created (engineering, developer, qa, and db_admin).

Note

For the sake of brevity, the cassandra role related entries are not included in the remainder of this article.

3. Grant permissions to roles

In this section, you grant permissions to each role.

Grant read access

All members of engineering need to be able to read data from any keyspace and table. Use the GRANT SELECT command to grant SELECT (or read) access on ALL KEYSPACES to the engineering role. This can be done as follows:

cassandra@ycqlsh> GRANT SELECT ON ALL KEYSPACES TO engineering;

Verify that the engineering role has SELECT permission as follows:

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

The output should look similar to below, where the engineering role has SELECT permission on the data resource.

 role        | resource          | permissions
-------------+-------------------+--------------------------------------------------------------
 engineering |              data |                                                   ['SELECT']
 ...

Note

The resource "data" represents all keyspaces and tables.

Granting the role engineering to any other role causes all those roles to inherit the SELECT permissions. Thus, developer, qa, and db_admin all inherit the SELECT permission.

Grant data modify access

Developers and QA should be able to modify data in existing tables in the keyspace dev_keyspace. They should be able to execute statements such as INSERT, UPDATE, DELETE, or TRUNCATE to modify data on existing tables. This can be done as follows:

cassandra@ycqlsh> GRANT MODIFY ON KEYSPACE dev_keyspace TO developer;
                 GRANT MODIFY ON KEYSPACE dev_keyspace TO qa;

Verify that the developer and qa roles have the appropriate MODIFY permission by running the following command.

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

The developer and qa roles have MODIFY permissions on the keyspace data/dev_keyspace.

 role        | resource          | permissions
-------------+-------------------+--------------------------------------------------------------
          qa | data/dev_keyspace |                                                   ['MODIFY']
   developer | data/dev_keyspace |                                                   ['MODIFY']
 engineering |              data |                                                   ['SELECT']
 ...

Note

In the resource hierarchy, "data" represents all keyspaces and "data/dev_keyspace" represents one keyspace in it.

Grant alter table access

QA should be able to alter the table integration_tests in the keyspace dev_keyspace. This can be done as follows.

cassandra@ycqlsh> GRANT ALTER ON TABLE dev_keyspace.integration_tests TO qa;

Run the following command to verify the permissions.

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

The ALTER permission on the resource data/dev_keyspace/integration_tests is granted to the role qa.

 role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
   developer |                   data/dev_keyspace |                                                   ['MODIFY']
 engineering |                                data |                                                   ['SELECT']

Note

The resource "data/dev_keyspace/integration_tests" denotes the hierarchy:

All Keyspaces (data) > keyspace (dev_keyspace) > table (integration_tests)

Grant all permissions

DB admins should be able to perform all operations on any keyspace. There are two ways to achieve this:

Grant DB admins the superuser permission. Doing this gives DB admins all permissions over all roles as well.

Grant ALL permissions to the "db_admin" role. Do the following:

cassandra@ycqlsh> GRANT ALL ON ALL KEYSPACES TO db_admin;

Run the following command to verify the permissions:

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

All permissions on the resource data are granted to the role db_admin.

role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
  developer |                   data/dev_keyspace |                                                   ['MODIFY']
engineering |                                data |                                                   ['SELECT']
    db_admin |                                data | ['ALTER', 'AUTHORIZE', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
    ...

4. Revoke permissions from roles

To revoke the AUTHORIZE permission from DB admins so that they can no longer change permissions for other roles, do the following:

cassandra@ycqlsh> REVOKE AUTHORIZE ON ALL KEYSPACES FROM db_admin;

Run the following command to verify the permissions.

cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions;

You should see the following output.

 role        | resource                            | permissions
-------------+-------------------------------------+--------------------------------------------------------------
          qa |                   data/dev_keyspace |                                                   ['MODIFY']
          qa | data/dev_keyspace/integration_tests |                                                    ['ALTER']
   developer |                   data/dev_keyspace |                                                   ['MODIFY']
 engineering |                                data |                                                   ['SELECT']
    db_admin |                                data |              ['ALTER', 'CREATE', 'DROP', 'MODIFY', 'SELECT']
    ...

The AUTHORIZE permission is no longer granted to the db_admin role.