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
- TABLE table_name
- KEYSPACE keyspace_name
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_analyst; GRANT EXECUTE ON ALL FUNCTIONS IN KEYSPACE cycling to cyclist_analyst; GRANT cyclist_analyst TO wilson;