Grant privileges
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 keyspacedev_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, thecassandra
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 roledb_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.