Tables and views in the information schema surface metadata about clusters and all the components within them. SingleStore DB supports the same
information_schema views as MySQL, as well as a variety of additional views that are specific to SingleStore. The MySQL views are often useful, but it is important to note that some are included mostly for compatibility purposes, meaning that values for some columns may always be
Accessing the Information Schema
A majority of tables in the information schema have row-level security. Information Schema tables 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. This means that users will be able to see rows in the tables for which they have access to the underlying data/objects.
To control user permissions for the information schema, you can grant read and/or write permissions on the objects for which a user needs access to the information schema data. Alternatively, it is possible to grant
SHOW METADATA on a particular information schema table, which allows access to information schema data without permissions for the related objects.
A few information schema tables do not have row-level security, and require an additional permission to access in full. In general, the exceptions are workload profiling tables, such as
MV_QUERIES, which require the
PROCESS permission, and pipelines tables, which require the
INSERT permission. For more information, see the Permissions Matrix.
Because information schema tables 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
For example, to find the five most expensive queries, run:
SELECT EXECUTION_TIME/COMMITS, QUERY_TEXT from information_schema.PLANCACHE ORDER BY EXECUTION_TIME/COMMITS DESC LIMIT 5;
In SingleStore DB, the
PROCESSLIST view also has a
PLAN_ID column that can be used to look up the query currently running on a given connection in
PLANCACHE. For example:
SELECT QUERY_TEXT FROM information_schema.PROCESSLIST as pl JOIN information_schema.PLANCACHE as pc ON pl.PLAN_ID = pc.PLAN_ID;
This is the most reliable way to find the query executing on a given connection. The
PROCESSLIST.INFO column is not always set by SingleStore DB.
If you are dealing with sensitive data and want to limit access to query information, but not the full
PROCESSLIST view, this can be controlled via the variable,
show_query_parameters. By default, this variable is set to
ON, so query parameters will be visible in the output of
information_schema.processlist. If set to
OFF, parameters will be hidden. This can be changed only at startup, not during runtime; it must be set in the memsql.cnf, followed by a system restart.