Managing keyspace and table permissions

Provides examples on how to manage access to keyspaces and tables.

DataStax Enterprise supports role-based access control to data on transactional nodes. The GRANT and REVOKE CQL commands provide and revoke access to objects and methods. Permission is hierarchical, granting permission to a parent object automatically allows full access to all ancestors; data objects have the following structure:
ALL KEYSPACES
  • KEYSPACE keyspace_name
    • TABLE table_name
      • 'filtering_string' ROWS
DataStax Enterprise supports this CQL syntax in cqlsh to grant permissions:
GRANT permission_name ON resource_name TO role_name; 
DataStax Enterprise supports this CQL syntax in cqlsh to revoke permissions:
REVOKE permission_name ON resource_name FROM role_name; 
Where permissions that apply to each data resources type is described below:
permission_name resource_name Description
ALTER ALL KEYSPACES ALTER KEYSPACE, ALTER TABLE, ALTER TYPE, RESTRICT ROW in any keyspace.
ALTER KEYSPACE keyspace_name ALTER KEYSPACE, ALTER TABLE, ALTER TYPE, and RESTRICT ROW in specified keyspace.
ALTER TABLE table_name ALTER TABLE and RESTRICT ROW of specified table.
ALTER 'filtering_data' ROWS IN table_name ALTER
CREATE ALL KEYSPACES CREATE KEYSPACE, CREATE TABLE, CREATE FUNCTIONS, and CREATE TYPE in any keyspace.
CREATE KEYSPACE keyspace_name CREATE TABLE and CREATE TYPE in specified keyspace.
DROP ALL KEYSPACES DROP KEYSPACE, DROP TABLE, and DROP TYPE in any keyspace
DROP KEYSPACE keyspace_name DROP TABLE and DROP TYPE in specified keyspace
MODIFY ALL KEYSPACES INSERT, UPDATE, DELETE and TRUNCATE rows in any table.
MODIFY KEYSPACE keyspace_name INSERT, UPDATE, DELETE and TRUNCATE rows in any table in the specified keyspace.
MODIFY TABLE table_name INSERT, UPDATE, DELETE and TRUNCATE any rows in the specified table. See note for tables with materialized views (MVs).
MODIFY 'filtering_data' ROWS IN table_name INSERT, UPDATE, DELETE and TRUNCATE rows that match the 'filtering_data' on rows in a table that match the filtering criteria.
Note: To modify a base table that has a materialized view (MV) using an INSERT or UPDATE command if access permissions are enabled, a user must be granted MODIFY or ALL PERMISSIONS on the base table.
Note: For more details, see Access control matrix.

Procedure

  • Create a role that has all permissions in all keyspaces:
    CREATE ROLE keyspace_admin;
    GRANT ALL PERMISSIONS ON ALL KEYSPACES TO keyspace_admin;
    GRANT keyspace_admin to martin;
  • Create an administrator role for a single keyspace:
    CREATE ROLE cycling_admin;
    GRANT ALL PERMISSIONS ON KEYSPACE cycling to cycling_admin;
    GRANT cycling_admin TO sandy;
  • Create a role that can only make data changes, INSERT, UPDATE, DELETE, and TRUNCATE for any table in the keyspace cycling:
    GRANT MODIFY ON KEYSPACE cycling TO team_manager;
    GRANT team_manager to sandy;
  • Create a role that can only select data and use functions in the cycling keyspace:
    CREATE ROLE cyclist_analyst;
    GRANT SELECT ON KEYSPACE cycling TO cyclist_analist;
    GRANT EXECUTE ON ALL FUNCTIONS IN KEYSPACE cycling to cyclist_analist;
    GRANT cyclist_analist TO wilson;