Data Control Language (DCL) is used to manage database roles and permissions. This topic describes the DCL syntax and provides examples on how to use the DCL syntax.

Manage database roles

In Lindorm Cassandra Query Language (CQL), users are defined as database roles. You can use the following syntax to define a database role: 

role_name ::=  identifier | string

Lindorm CQL and Apache CQL support similar operations on database roles. By default, database roles do not have logon permissions or superuser permissions.

Manage permissions

Lindorm CQL allows you to grant permissions on all levels of database resources. If you grant a permission on a database resource of a specified level, the permission is also automatically granted on all the resources that belong to that database resource. For example, if you grant the SELECT permission on a keyspace, the SELECT permission is also automatically granted on all the tables in the keyspace.

Note After permissions are changed, clients do not need to re-establish connections.

Lindorm CQL supports the following permissions: CREATE, ALTER, DROP, SELECT, and MODIFY.

The following table describes the resources that are supported by each permission.
Note The resources that are supported by each permission are different. If a permission is granted on a non-supported resource, an error occurs.
Permission type Supported resource Executable statement
CREATE ALL KEYSPACES CREATE KEYSPACES and CREATE TABLE
CREATE KEYSPACE CREATE TABLE
ALTER ALL KEYSPACES ALTER KEYSPACES and ALTER TABLE
ALTER KEYSPACE ALTER TABLE
DROP ALL KEYSPACES DROP KEYSPACES and DROP TABLE
DROP KEYSPACE DROP TABLE
SELECT ALL KEYSPACES SELECT KEYSPACES and SELECT TABLE
SELECT KEYSPACE SELECT TABLE
SELECT TABLE SELECT
MODIFY ALL KEYSPACES MODIFY KEYSPACES and MODIFY TABLE
MODIFY KEYSPACE MODIFY TABLE
MODIFY TABLE MODIFY

CREATE ROLE

Creates a database role.

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
Note
  • You can execute the CREATE ROLE statement to create a database role. You must specify a logon password for the database role. You can also grant logon permissions or superuser permissions to the database role.
  • By default, database roles in Lindorm CQL do not have superuser permissions.
Parameters
Parameter Description
role_name The name of the database role.
role_option
  • PASSWORD

    The password that is used by the database role to log on to the database.

    Note The PASSWORD parameter is required.
  • LOGIN

    Specifies whether the database role has logon permissions. Default value: False.

    Note If a client uses a database role that has logon permissions, the client is identified as the database role when the client is connected to the database. The client obtains all the permissions that are assigned to the database role when the client is connected.
  • SUPERUSER

    Specifies whether the database role is a superuser.

    Note A superuser has permissions to perform the following operations:
    • Creates a superuser.
    • Executes the DROP KEYSPACE, DROP TABLE, and TRUNCATE statements.
    • Grants permissions on database resources to a database role. The supported database resources include keyspaces and tables.
    • Creates a hierarchy that contains layer permissions.
Examples
CREATE ROLE role1 WITH PASSWORD = 'password_a' AND LOGIN = true;
CREATE ROLE role2 WITH PASSWORD = 'password_b' AND LOGIN = true AND SUPERUSER = true;

ALTER ROLE

Modifies a database role.

Syntax
alter_role_statement ::=  ALTER ROLE role_name WITH role_options
Note You can execute the ALTER ROLE statement to change the logon password, logon permissions, and the superuser identity that are attached to a database role.
Parameters

The parameters used in the ALTER ROLE statements are the same as the parameters used in the CREATE ROLE statement. For more information, see the Parameters section for the CREATE ROLE statement.

Examples
ALTER ROLE bob WITH PASSWORD = 'PASSWORD_B' AND SUPERUSER = false;

DROP ROLE

Deletes a database role.

Syntax
drop_role_statement ::=  DROP ROLE [ IF EXISTS ] role_name
Note
  • You can execute the DROP ROLE statement to delete a database role.
    • To delete a database role, you must have the DELETE permission on the database role.
    • You cannot delete a database role that is in the Logged On state.
    • Only a superuser can delete a database role whose role_option parameter is set to SUPERUSER.
  • The following items describe how to use the [ IF EXISTS ] clause:
    • If this clause is not specified in a DROP statement that deletes a nonexistent database role, the statement returns an error to indicate that the search condition is invalid.
    • If this clause is specified in a DROP statement that deletes a nonexistent database role, this statement is a no-op statement.
Parameters
Parameter Description
role_name The name of the database role.
Examples
DROP ROLE bob;
DROP ROLE IF EXISTS bob;

LIST ROLES

Displays all existing database roles.

Syntax
list_roles_statement ::=  LIST ROLES [ OF role_name ]
Parameters
Parameter Description
role_name The name of the database role.
Examples
LIST ROLES;// Only superusers can execute this statement.
LIST ROLES OF role1;// Displays the details of a specified database role.

GRANT PERMISSION

Grants permissions to a database role.

Syntax
grant_permission_statement ::=  GRANT permissions ON resource TO role_name
permissions                ::=  ALL [ PERMISSIONS ] | permission [ PERMISSION ]
permission                 ::=  CREATE | ALTER | DROP | SELECT | MODIFY 
resource                   ::=  ALL KEYSPACES
                               | KEYSPACE keyspace_name
                               | [ TABLE ] table_name
Parameters
Parameter Description
role_name The name of the database role.
permission
  • CREATE

    The permission to create resources.

  • ALTER

    The permission to alter resources.

  • DROP

    The permission to delete resources.

  • SELECT

    The permission to query resources.

  • MODIFY

    The permission to modify resources.

resource The resources on which the permission is granted. Valid values include ALL KEYSPACES, KEYSPACE, and TABLE.
keyspace_name The name of the keyspace.
table_name The name of the table.
Examples
GRANT SELECT ON ALL KEYSPACES TO role;
GRANT SELECT ON KEYSPACE  ks TO role;

REVOKE PERMISSION

Revokes permissions from a database role.

Syntax
revoke_permission_statement ::=  REVOKE permissions ON resource FROM role_name
Parameters
Parameter Description
role_name The name of the database role.
permission
  • CREATE

    The permission to create resources.

  • ALTER

    The permission to alter resources.

  • DROP

    The permission to delete resources.

  • SELECT

    The permission to query resources.

  • MODIFY

    The permission to modify resources.

resource The resources on which the permission is granted. Valid values include ALL KEYSPACES, KEYSPACE, and TABLE.
Examples
REVOKE SELECT ON ALL KEYSPACES FROM role;

LIST PERMISSIONS

Displays the permissions that are granted on each resource.

Syntax
list_permissions_statement ::=  LIST permissions [ ON resource ] [ OF role_name  ]
Note
  • ON resource specifies resources. If this option is not specified, the statement returns the permissions that are granted to the specified database role on all resources.
  • OF role_name specifies a database role. By default, you must specify a database role. Only superusers can display permissions when no database role is specified.
Parameters
Parameter Description
role_name The name of the database role.
permissions
  • CREATE

    The permission to create resources.

  • ALTER

    The permission to alter resources.

  • DROP

    The permission to delete resources.

  • SELECT

    The permission to query resources.

  • MODIFY

    The permission to modify resources.

resource The resources on which the permission is granted. Valid values include ALL KEYSPACES, KEYSPACE, and TABLE.
Examples
LIST ALL PERMISSIONS OF role; 
LIST ALL PERMISSIONS ON KEYSPACE kss OF role;