Database Roles in CQL (Cassandra Query Language)

One uses the Cassandra Query Language (CQL) when communicating with the Apache Cassandra NoSQL database. Database roles may be defined and rights can be assigned to them using CQL, one of its characteristics. User access and permissions are managed within a database using Cassandra's roles.

This article will discuss roles in Cassandra, how they work, and give examples of creating, changing, and removing roles using CQL.

What do Cassandra's roles entail?

A role in Cassandra is a type of object that specifies a set of rights that may be bestowed upon an individual user or group of users. Access to the database's resources can be restricted via roles. They can be used to give or revoke permissions to carry out particular database tasks, such as creating or editing tables or running queries.

Three different roles are supported by Cassandra

• Superuser − This position has complete access to all database resources.

• Cassandra − This role can access the database administratively but is restricted from carrying out some tasks that call for superuser rights.

• DataStax − This role has administrative access to the database and is utilized by DataStax Enterprise, a for-profit implementation of Cassandra.

Creating Roles in CQL

In Cassandra, a new role may be created using the CREATE ROLE statement. The syntax for establishing a new role is as follows −

Syntax

CREATE ROLE role_name WITH PASSWORD = 'password' AND OPTIONS = {...};

Role_name is the name of the role that will be created, and the password is the role's password in this statement. You may define extra choices for the position, such as the ability to log in or the ability to create new roles, using the choices clause, which is optional.

Example

Here is an illustration of how to create a new job in CQL −

CREATE ROLE my_role WITH PASSWORD = 'my_password' AND LOGIN = true;

In this example, a new role with the name my_role and the password my_password is being created. Users may log in to the database using this role since the LOGIN option is set to true.

Output

Role 'my_role' created

This command creates a new role called "my_role" with the parameters "my_password" for the password and "true" for the ability to log in. In the Cassandra database, this role may now be used to provide rights to users or other roles.

Granting Privileges to Roles in CQL

The GRANT statement may be used to assign privileges to a role after it has been created. The syntax for assigning privileges to a role is as follows −

Syntax

GRANT privilege_name ON resource_name TO role_name;

The permission to be provided, the resource to which it is to be granted, and the role to which it is to be granted are all identified in this statement by the terms privilege_name, resource_name, and role_name.

Example

Here is an illustration of how to provide a role in CQL privileges −

GRANT SELECT, INSERT, UPDATE ON my_keyspace.my_table TO my_role;

In this example, we're giving the my_role role access to the my_table table in the my_keyspace keyspace for SELECT, INSERT, and UPDATE operations.

Output

The aforementioned CQL query will provide the "my_role" role access to the SELECT, INSERT, and UPDATE permissions on the "my_table" table within the "my_keyspace" keyspace.

This query will not provide any output. If the query is successful, the given role will immediately receive the requested permissions.

Modifying Roles in CQL

The ALTER ROLE statement in Cassandra may be used to change a role. The syntax for changing a role is as follows −

Syntax

ALTER ROLE role_name WITH PASSWORD = 'new_password' AND OPTIONS = {...};

role_name is the name of the role that is to be changed in this statement, and OPTIONS can be used to define new options for the role.

Example

Here is an illustration of how to change a job in CQL −

ALTER ROLE my_role WITH PASSWORD = 'new_password';

In this example, the my_role role is being modified, and its password is being changed to a new_password.

Output

This command would substitute the current password "my_role" with the new password "new_password" for the existing role. There won't be any further output produced by the query itself if it runs correctly. However, in order for any ensuing queries or actions that employ this role to require authentication to be successful, the changed password must be used.

Revoking Roles in CQL

Using the REVOKE statement, rights may also be revoked for roles in Cassandra. The following syntax is used to revoke role-specific privileges −

Syntax

REVOKE privilege_name ON resource_name FROM role_name;

The words privilege_name, resource_name, and role_name refer to the names of the respective privileges that are to be revoked from each resource and role, respectively, in this statement.

Example

Here is an illustration of withdrawing role rights in CQL −

REVOKE SELECT, INSERT, UPDATE ON my_keyspace.my_table FROM my_role;

In this example, we are revocation the my_role role's SELECT, INSERT, and UPDATE permissions on the my_table table in the my_keyspace keyspace.

Output

The specified CQL query will remove the "my_role" role's SELECT, INSERT, and UPDATE rights on the "my_table" table inside the "my_keyspace" keyspace.

This query will not provide any output. The permissions have been removed for the designated role if the query runs properly.

Deleting Roles in CQL

The DROP ROLE statement in Cassandra may be used to remove roles. The syntax for eliminating a role is as follows −

Syntax

DROP ROLE role_name;

The name of the role that is to be eliminated is role_name in this statement.

Here is an illustration of how to delete a role in CQL −

Example

DROP ROLE my_role;

In this case, we are removing the database's my_role role.

Output

The "my_role" role would be removed from the Cassandra keyspace using this query. If the role is present and the query is successful, it will be deleted and no more output will be shown. The query will still run correctly if the role does not exist, but the keyspace will remain unchanged and no output will be shown.

Conclusion

The Cassandra role system is a robust tool for controlling user access and permissions inside databases. With the help of roles, you can specify a set of permissions that may be provided to a user or a group of users and manage their access to the database's resources. In this post, we looked at utilizing CQL to create, edit, and remove roles as well as give and revoke access to those roles' rights. Working with Cassandra requires an understanding of responsibilities, which may also help you manage your database resources more effectively.