sql: update SHOW GRANTS to include grant options #73394
Description
Once #67410 is completed, we will need to provide a way for users to see which GRANT options they have.
As the RFC describes (#72512), we will do this by adding a new column to SHOW GRANTS:
For example:
root@:26257/defaultdb> create table t (a int);
CREATE TABLE
root@:26257/defaultdb> create user u1;
CREATE ROLE
root@:26257/defaultdb> create user u2;
CREATE ROLE
root@:26257/defaultdb> create user u3;
CREATE ROLE
root@:26257/defaultdb> grant select on t to u1 with grant option;
GRANT
root@:26257/defaultdb> grant insert on t to u1;
GRANT
root@:26257/defaultdb> grant all on t to u2 with grant option;
GRANT
root@:26257/defaultdb> grant all on t to u3;
GRANT
root@:26257/defaultdb> grant select on t to u3 with grant option;
GRANT
root@:26257/defaultdb> grant insert on t to u3 with grant option;
GRANT
root@:26257/defaultdb> show grants on table t;
database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+------------------
defaultdb | public | t | admin | ALL | True
defaultdb | public | t | root | ALL | True
defaultdb | public | t | u1 | INSERT | False
defaultdb | public | t | u1 | SELECT | True
defaultdb | public | t | u2 | ALL | True
defaultdb | public | t | u3 | ALL | False
defaultdb | public | t | u3 | INSERT | True
defaultdb | public | t | u3 | SELECT | True
Code to update:
- The delegate queries in [show_grants.go(https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/delegate/show_grants.go).
- Fill in the
is_grantablecolumn forinformation_schema.table_privilegesand `information_schema.schema_privileges in information_schema.go. The column should be based on the grant option, and the code to populate it should be behind a version gate. - Add
is_grantablecolumn (or similar name) toinformation_schema.type_privilegesandcrdb_internal.cluster_database_privileges(also version gated). - NOTE: For the
is_grantablecolumn we'll need to update the logic around the ALL privilege a little. If the user does not have the ALL grant option, but they do have grant options on other privileges, we'll start showing a separate row for each grant option.
Epic CRDB-2587