sql: SHOW RANGES should not be controlled only by role options VIEWACTIVITY or ZONECONFIG

[knz]

Is your feature request related to a problem? Please describe.

Currently SHOW RANGES (and its underlying mechanisms via crdb_internal.ranges, ranges_no_leases) is limited to users with the options VIEWACTIVITY or ZONECONFIG.

This is overly restrictive: SHOW RANGES is also useful for DBAs and application developers:

• to estimate data size on disk
• to understand hot spots and contention
• to understand query performance

Describe the solution you'd like

• The base version of the functionality should be available without a special role option.

  :warning: when the user doesn't have a special role option, the output should be restricted/filtered to only the database/tables/objects that the user has access to.

• An advanced version of the functionality could be available that can list all ranges across all database objects, with a separate role option that would also include the ability to inspect other cluster metadata such as store descriptors and other data structures. Maybe VIEWCLUSTERMETADATA?

  :warning: once this is implemented, beware to properly document it can reveal range keys and so also indexed columns. In this variant, this would be intentional (but needs documentation).

Related: #106682. Related: #103443.

Jira issue: CRDB-29677 Epic: CRDB-30635

[koorosh]

@knz , want to clarify following:

1. As I understood, proposed base and advanced versions is not mutually exclusive. Is it correct? It is okay to have both implementations?
2. Should VIEWCLUSTERMETADATA role have some limitations on information returned from SHOW RANGES (comparing to VIEWACTIVITY role)?

[knz]

(1) yes this is correct - both are possible.

(2) I don't know. Do we consider VIEWCLUSTERMETADATA to be "more" or 'less" powerful than VIEWACTIVITY? If we include range key details in the output, that will reveal the values of indexed columns. Is that acceptable for either of these role options?

maybe @j82w can chime in here.