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

**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