Accessing the Information Schema

A majority of views in the information schema have row-level security. Information Schema views are different from other types of base tables that can either be accessed in full or not depending on permissions. They are views into underlying data about a cluster and the objects within it.

Users have permissions to see information schema metadata about the database objects they have permissions to access. For example, if a user has permissions to access table t1 but not table t2, they will see rows containing metadata about t1 in information schema views but not t2.

Alternatively, it is possible to grant the SHOW METADATA permission, which grants a user broad access to metadata about all database objects, without requiring read or write permissions to those database objects. A few of the information schema views require an additional permission to access in full. For example, accessing MV_QUERIES requires the PROCESS permission. Accessing the pipelines views requires the INSERT permission. For more information, see the Permissions Matrix. Remember that SHOW METADATA must be set at the cluster level.

For example, use:

GRANT SHOW METADATA ON *.* to testuser;

instead of:

GRANT SHOW METADATA ON information_schema.processlist to testuser;

ERROR 1044 ER_DBACCESS_DENIED_ERROR: Access denied for user 'admin'@'%' to database 'information_schema'

Because information schema views are simply views into the internal data structure which holds the information being surfaced in a given view, most information schema views do not have an impact on memory. For the same reason, the best way to secure this data is by managing user access.

To access the information_schema, users can either SELECT data from specific view(s) with a SQL query, or use one of the SHOW Commands which will surface a subset of data from the information_schema depending on the command. The same permissions apply to any information schema data whether it is being accessed via a query or a SHOW command.