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