Database Roles in CQL (Cassandra Query Language)

Cassandra Query Language (CQL) is the query language used to interact with Cassandra databases. Unlike traditional relational databases, Cassandra does not have a built-in concept of database roles. Instead, access control is managed at the object level, such as the keyspace, table, or column level.

Cassandra provides a set of permissions that can be granted to users or roles, such as SELECT, INSERT, ALTER, and DROP. Permissions can be granted to individual users or to roles, which are collections of users.

To create a role in Cassandra, you can use the CREATE ROLE command, followed by the name of the role and any options or permissions to be granted. For example:

This creates a new role called "analyst" with the password "password123" and grants the role login privileges.

To grant permissions to a role, you can use the GRANT command, followed by the permission and the name of the role or user to grant the permission to. For example:

This grants the SELECT permission on the "table1" table in the "keyspace1" keyspace to the "analyst" role.

To revoke permissions from a role, you can use the REVOKE command, followed by the permission and the name of the role or user to revoke the permission from. For example:

This revokes the SELECT permission on the "table1" table in the "keyspace1" keyspace from the "analyst" role.

In summary, while Cassandra does not have a concept of database roles like traditional relational databases, access control can be managed at the object level by granting and revoking permissions to users or roles.

In this article we will discuss Database Roles in Cassandra Query Language. It is very important to create different role for different type of users to provide access with a specific requirements. It is used to provide security for Database users or group of users. 
A Role name can be simply defined as following. 
 

 role_name ::= identifier | string

 

1. 
   CREATE ROLE: 
   In CQL we can create role by using the CREATE command statement. CREATE command helps in creating role for users or group of users. 
   Syntax : 
    

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
 | ACCESS TO DATACENTERS set_literal
 | ACCESS TO ALL DATACENTERS 

1. 
   source 
    

syntax :
CREATE ROLE new_role_name; 

1. 
   For example: 
   To create simple user and super user Role then used the following CQL query. 
    

CREATE ROLE Ashish WITH PASSWORD = 'pass_a' 
 AND LOGIN = true;
CREATE ROLE Rana WITH PASSWORD = 'pass_r' 
 AND LOGIN = true 
 AND SUPERUSER = true;

1. 
   To create Database Roles for user with more restrictions such that if a user only able to access specific datacenters then to create such type of Role used the following CQL query. 
    

CREATE ROLE user1 WITH OPTIONS = { 'option1' : 'option1_value', 
 'option2' : 98 };
CREATE ROLE Ashish WITH PASSWORD = 'pass_a' 
 AND LOGIN = true 
 AND ACCESS TO DATACENTERS {'DC1', 'DC4'};
CREATE ROLE Rana WITH PASSWORD = 'pass_r' 
 AND LOGIN = true 
 AND ACCESS TO ALL DATACENTERS;

1. 
   If we want to create Role conditionally then we can used the following CQL query. 
    

CREATE ROLE IF NOT EXISTS role_name; 

1. 
    
2. 
   ALTER ROLE : 
   If we want to change the existing Role which already created after that we can modify Role with ALTER ROLE statement. 
    

Syntax : 
alter_role_statement ::= ALTER ROLE role_name 
 WITH role_options 

1. 
   For instance: 
   Before Alter Role 
    

CREATE ROLE Rana WITH PASSWORD = 'pass_r' 
 AND LOGIN = true 
 AND SUPERUSER = true;

1. 
   After Alter Role 
    

ALTER ROLE Rana WITH PASSWORD = 'pass_r' 
 AND SUPERUSER = false;

1. 
    
2. 
   DROP ROLE : 
   If a user want to Drop Existing Role then we can used the following CQL query to drop the Role. 
   syntax : 
    

drop_role_statement ::= DROP ROLE [ IF EXISTS ] role_name

1. 
   For example: 
    

DROP ROLE Ashish;

1. 
    
2. 
   GRANT ROLE : 
   It is used for granting the ROLE for other uses. 
   syntax: 
    

grant_role_statement ::= GRANT role_name 
 TO role_name

1. 
   For example: 
    

GRANT user1 TO Ashish;

1. 
   This statement grants the user1 role to Ashish. Any permissions granted to user1 are also acquired by Ashish. 
    
2. 
   REVOKE ROLE : 
   If a user want to revoke database role then we can used REVOKE ROLE statement. 
   syntax: 
    

 revoke_role_statement ::= REVOKE role_name 
 FROM role_name

1. 
   For instance: 
    

REVOKE user1 FROM Ashish;

1. 
   Above CQL query statement revokes the user1 role from Ashish. Any permissions that Ashish has acquired via the user1 role are also revoked. 
    
2. 
   LIST ROLE : 
   If a user want to list all the Roles then we can used the following CQL query to list all the Roles. 
   syntax : 
    

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

1. 
   For instance: 
    

LIST ROLES;

1. 
   This CQL query statement returns all known roles in the system which requires DESCRIBE permission on the database roles resource.