Add SHOW PRINCIPALS for admin to see what roles were granted to users.

[lhofhansl]

Apologies if I missed something.

A user can see all roles (s)he was granted, but there's no way I could find where an admin can see the membership of all roles or role assignments for all users. Neither through a SHOW command, nor via the information_schema.

An admin can create roles, assign roles to users, but has no way to see which roles were assigned to what user.

Is that intentional or simply missing?

[lhofhansl]

Hive has: SHOW PRINCIPALS role;
Happy to do a PR if folks agree.

[kokosing]

I think it is missing. What ANSI SQL says about this? I guess there is some table defined in information_schema.

[lhofhansl]

The SQL standard does not have anything for SHOW, and each database does it differently. :(

In Postgres you issue \dg or \du in the shell.

MySQL has SHOW GRANTS [FOR user [USING role]] and MySQL also lists which roles have been granted to a user, like this:

$ SHOW GRANTS FOR u1;

GRANT SELECT ON table TO us
GRANT r1,r2 TO u1

And even:

$ SHOW GRANTS FOR u1 USING r1;

GRANT SELECT ON table TO us
GRANT SELECT ON table2 TO u1
GRANT r1,r2 TO u1

So we could "just" add the "FOR" syntax and have SHOW GRANTS also show role assignments.
That part is only accessible to an admin.

[lhofhansl]

Snowflake has SHOW GRANTS TO USER user

Presto currently has: SHOW ROLE GRANTS [ FROM catalog ], which shows the granted roles for the current user.

Perhaps the simplest (from the viewpoint of syntax at least) is to simply extend this to:
SHOW ROLE GRANTS [ FROM catalog ] [ TO USER | ROLE principal ]

That would align well with the CATALOG argument: If not specified the current catalog is used, otherwise the specified CATALOG. The TO argument would be same: Current user by default, otherwise the specified user or role.

Only an admin would be allowed to run this with TO user specified (at least when the specified user differs from the current user).

What do you folks think? Happy to do a PR if we agree.

Of course I'd have to check the metastore has an API for that. Since Hive has SHOW PRINCIPALS role I assume there would be.

So the options are:

1. Follow the Hive syntax: SHOW PRINCIPALS role
2. MySQL's SHOW GRANTS FOR user
3. Snowflake's SHOW GRANTS TO user
4. Extend Presto's SHOW ROLE GRANTS to SHOW ROLE GRANTS [ FROM catalog ] [ TO USER | ROLE principal ]

I'd vote for (4) followed by (1).

Edit: I take this back. Since only roles are actually stored in the Hive Metastore, the only real option is to discover it that way: List all roles, then list user/roles that has this role granted. Only option the Hive option (1) allows for that. It seems that's we should do. And in order to find roles for users, we should do (4) as well. So best (1) and (4).

[lhofhansl]

I verified that the Hive Metastore API has everything needed.
(can list roles for a principal, and can list principals for a role)

[lhofhansl]

Opinions? This seems to be an important gap.
@kokosing SQL standard is no help here :(

What I'm looking is two abilities for an admin:

1. See all roles granted to a specific principal (role or user).
2. Given a role, show all principals that were granted that role.

(1) Could be SHOW ROLE GRANTS [ FROM catalog ] extended by [ TO USER | ROLE principal ]
(2) Could be Hive's SHOW PRINCIPALS role

The meta point is: Most people defer these things to Hive still, I'd like to be able to do them straight through Presto.

I'm happy to start a PR. Maybe I'll start on with the proposed syntax changes so that we can have a look and try, and when that's OK fill is the access parts, tests, etc.

[lhofhansl]

Closing this.

This is now implemented via a new table information_schema.role_authorization_descriptors, which allows both the equivalent of SHOW ROLE GRANTS TO and SHOW PRINCIPALS role
See #3535