Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: SHOW GRANTS inherited by virtue of role membership #97299

Closed
fabiog1901 opened this issue Feb 17, 2023 · 1 comment · Fixed by #104119
Closed

sql: SHOW GRANTS inherited by virtue of role membership #97299

fabiog1901 opened this issue Feb 17, 2023 · 1 comment · Fixed by #104119
Assignees
@postamar
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@fabiog1901
Copy link
Contributor

fabiog1901 commented Feb 17, 2023 •
edited by exalate-issue-sync bot
Loading

SHOW GRANTS currently only lists explicitly granted privileges. This FR is to extend the list to privileges inherited by role membership. Example below:

Starting point:

create database d1;
create role r1;
create user u1;
grant r1 to u1;
use d1;
create table t1 (id int);
grant all on table t1 to r1;
show grants on t1 for r1;
--   database_name | schema_name | table_name | grantee | privilege_type | is_grantable
-- ----------------+-------------+------------+---------+----------------+---------------
--   d1            | public      | t1         | r1      | ALL            |      f
-- (1 row)

Currently, it shows no grants for u1:

show grants on t1 for u1;
-- SHOW GRANTS 0

Envisioned - see last column so you have that extra info

show grants on t1 for u1;
--   database_name | schema_name | table_name | grantee | privilege_type | is_grantable   | from_role
-- ----------------+-------------+------------+---------+----------------+----------------------------
--   d1            | public      | t1         | r1      | ALL            |      f         |    r1
-- (1 row)

Slack

Jira issue: CRDB-24622

gz#16111

Epic CRDB-27601
@fabiog1901 fabiog1901 added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Feb 17, 2023
@rafiss rafiss added the T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) label Feb 17, 2023
@rafiss
Copy link
Collaborator

rafiss commented Feb 21, 2023

I think if we made the change, the grantee column might be sufficient, so there's no need to add the from_role column. grantee would show the role that was explicitly granted that privilege.

@rafiss rafiss assigned postamar and unassigned rafiss May 16, 2023
@postamar postamar mentioned this issue May 30, 2023
Merged
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 5, 2023
sql: SHOW GRANTS includes inherited roles
f70acfd 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 6, 2023
sql: SHOW GRANTS includes inherited roles
2ed81fb 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 6, 2023
sql: SHOW GRANTS includes inherited roles
7d94b00 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 6, 2023
sql: SHOW GRANTS includes inherited roles
cba1673 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
craig bot pushed a commit that referenced this issue Jun 8, 2023
Merge #104119
fcce6ba 
104119: sql: SHOW GRANTS includes inherited roles r=postamar a=postamar

  Previously, SHOW GRANTS would only list explicitly-granted privileges.
  This commit changes its output to include privileges inherited by role
  membership.
  
  Consider for example:
  
      CREATE DATABASE d1;
      CREATE ROLE r1;
      CREATE USER u1;
      GRANT r1 TO u1;
      SET database = d1;
      CREATE TABLE t1 (id INT8);
      GRANT ALL ON TABLE t1 TO r1;
  
  We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
  u1 inherits from r1.
  
  For this purpose this commit also adds a new `kv_inherited_role_members`
  virtual table in `crdb_internal` which is essentially a recursive
  self-join of `system.role_members` run by the node user.
  
  This commit also changes the output of `SHOW GRANTS ON ROLE` to list
  both explicit and implicit grantees: the former are the direct result of
  a `GRANT ... TO ...` statement, the latter comprise also all the roles
  which inherit the privileges following further role membership
  relationships. For this purpose it also uses the new virtual table and
  therefore the current user no longer requires SELECT privileges on the
  `system.role_members` table.
  
  Fixes #97299.
  
  Release note (sql change): SHOW GRANTS now lists not just privileges
  explicitly granted to each role, but also roles which inherit from
  those. SHOW GRANTS ON ROLE statements no longer require any privileges
  and also lists implicit grantees.


Co-authored-by: Marius Posta <[email protected]>
@craig craig bot closed this as completed in ab08f1d Jun 8, 2023
@postamar postamar mentioned this issue Jun 8, 2023
Merged
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 8, 2023
sql: SHOW GRANTS includes inherited roles
4a284d6 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
@postamar postamar mentioned this issue Jun 8, 2023
Merged
postamar pushed a commit to postamar/cockroach that referenced this issue Jun 8, 2023
sql: SHOW GRANTS includes inherited roles
d2d51b2 
Previously, SHOW GRANTS would only list explicitly-granted privileges.
This commit changes its output to include privileges inherited by role
membership.

Consider for example:

    CREATE DATABASE d1;
    CREATE ROLE r1;
    CREATE USER u1;
    GRANT r1 TO u1;
    SET database = d1;
    CREATE TABLE t1 (id INT8);
    GRANT ALL ON TABLE t1 TO r1;

We want `SHOW GRANTS ON TABLE t1 FOR u1` to list the ALL privilege that
u1 inherits from r1.

For this purpose this commit also adds a new `kv_inherited_role_members`
virtual table in `crdb_internal` which is essentially a recursive
self-join of `system.role_members` run by the node user.

This commit also changes the output of `SHOW GRANTS ON ROLE` to list
both explicit and implicit grantees: the former are the direct result of
a `GRANT ... TO ...` statement, the latter comprise also all the roles
which inherit the privileges following further role membership
relationships. For this purpose it also uses the new virtual table and
therefore the current user no longer requires SELECT privileges on the
`system.role_members` table.

Fixes cockroachdb#97299.

Release note (sql change): SHOW GRANTS now lists not just privileges
explicitly granted to each role, but also roles which inherit from
those. SHOW GRANTS ON ROLE statements no longer require any privileges
and also lists implicit grantees.
@rafiss rafiss mentioned this issue Apr 11, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@postamar postamar

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: SHOW GRANTS includes inherited roles postamar/cockroach
3 participants
@rafiss @postamar @fabiog1901