Securing schema information

The schema information and corresponding operation information is stored in system and system_schema keyspaces. By default when DSE Unified Authentication is enabled, roles are granted full access to some tables (listed below). To limit which data in these tables a role can display, enable system_keyspaces_filtering and grant DESCRIBE permission on the keyspace. When a role executes a query against a table in the system or system_schema keyspaces, query results return only rows that match the keyspaces with enabled DESCRIBE permissions.

System and schema keyspace filtering does not apply to superusers. Superuser roles have access to all resources except those to which access is denied by a RESTRICT statement.

When system keyspace filtering is disabled, all users have the following access:

Keyspace	Table	Default access
system	local	FULL^Note
peers	FULL^Note
size_estimates	Full
available_ranges	Full
sstable_activity	NONE
IndexInfo	NONE
built_views	NONE
views_builds_in_progress	NONE
batches	NONE
paxos	NONE
peer_events	NONE
range_xfers	NONE
compaction_history	NONE
transferred_ranges	NONE
prepared_statements	NONE
repairs	NONE
nodesync_checkpoints	NONE
system_schema	aggregates	Full
columns	Full
dropped_columns	Full
hidden_columns	Full
edges (Graph)	Full
functions	Full
indexes	Full
keyspaces	Full
tables	Full
triggers	Full
types	Full
vertices (Graph)	Full
views	Full

Keyspace

Table

Default access

system

local

FULL^Note

peers

FULL^Note

size_estimates

Full

available_ranges

Full

sstable_activity

NONE

IndexInfo

NONE

built_views

NONE

views_builds_in_progress

NONE

batches

NONE

paxos

NONE

peer_events

NONE

range_xfers

NONE

compaction_history

NONE

transferred_ranges

NONE

prepared_statements

NONE

repairs

NONE

nodesync_checkpoints

NONE

system_schema

aggregates

Full

columns

Full

dropped_columns

Full

hidden_columns

Full

edges (Graph)

Full

functions

Full

indexes

Full

keyspaces

Full

tables

Full

triggers

Full

types

Full

vertices (Graph)

Full

views

Full

All roles have full access to the local and peers tables even if filtering is enabled.

By default, roles do NOT have access to data in the system_auth, system_traces, and system_distributed keyspaces. To allow access grant SELECT permission on the keyspace or table.

Example

The following uses an internal non-superuser account, martin.

Create internal login role using cqlsh:

CREATE ROLE martin WITH LOGIN = true AND PASSWORD = 'password';

Login as martin:
```
LOGIN martin
```
Count the number of tables in system_schema.tables that martin can list:
```
SELECT count(*) FROM system_schema.tables;
```
The results is the number of tables that exist cluster-wide.
```
 count
-------
    75

(1 rows)
```
Set system_keyspaces_filtering to true and restart the nodes.
Log in to cqlsh as martin:
```
cqlsh -u martin -p password
```

Count the number of tables in system_schema.tables that martin can list:

SELECT keyspace_name, count(*) AS tables
FROM system_schema.tables
GROUP BY keyspace_name;

Assuming martin is not a superuser and does not have DESCRIBE access on any other tables, the account can only list 18 tables.

 keyspace_name | tables
---------------+--------
 system_schema |     10
        system |      8

(2 rows)

To allow martin to see detailed information about cycling tables, grant DESCRIBE access on the cycling keyspace:
```
GRANT DESCRIBE ON KEYSPACE cycling TO martin;
```
Login as martin:
```
LOGIN martin
```

Verify that martin can see the tables in the cycling keyspace:

SELECT keyspace_name, count(*) AS tables
FROM system_schema.tables
GROUP BY keyspace_name;

The results show the number of tables that martin is able to list in each keyspace.

 keyspace_name | tables
---------------+--------
       cycling |     25
 system_schema |     10
        system |      8

(3 rows)