Securing schema information

Limit access to tables that contain schema and operation data.

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 and grant DESCRIBE permission on the keyspace. When a role executes a query against a table in the system or system_schema keyspaces, only rows that match the keyspaces that they have DESCRIBE permissions on are returned in the query results.

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

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


Keyspace	Table	Default access
system	size_estimates	Full
	available_ranges	Full
	local	FULL*
	peers	FULL*
	sstable_activity	NONE
	IndexInfo	NONE
	built_views	NONE
	views_builds_in_progress	NONE
system_schema	tables	Full
	triggers	Full
	views	Full
	keyspaces	Full
	dropped_columns	Full
	functions	Full
	aggregates	Full
	indexes	Full
	types	Full
	columns	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 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 can list in each keyspace.

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

(3 rows)