Implement Role Management in Presto

[arhimondr]

@cawallin @martint @maciejgrzybek Please review

[arhimondr]

CREATE ROLE

Creates role in <catalog>

Syntax:

CREATE ROLE <role name> 
(WITH ADMIN <USER user_name|ROLE role_name|CURRENT_USER|CURRENT_ROLE>)? 
(IN <catalog>)?

• If <catalog> is not specified, session catalog is used
• If WITH ADMIN <grantor> is not specified, it is considered to be a current logged in user

SPI Modifications:

• ConnectorMetadata

  + void createRole(ConnectorSession session, String role, String grantor)
  

[arhimondr]

GRANT ROLE

Grants role to the specified user in <catalog>

Due to the SQL standard a role can be granted either to a user or to a another role. SQL standard also says that DBMS engine must ensure user/role name uniqueness, what is not possible in terms of Presto. As a workaround we introduce additional keywords ROLE and USER to be able to clarify the type of entity we are going to give a GRANT to. If the type of entity is not explicitly specified, the entity type is considered to be USER.

Syntax:

GRANT <role name> (, <role name>)+ 
TO <user_name|USER user_name|ROLE role_name> (, <user_name|USER user_name|ROLE role_name>)+ 
(WITH ADMIN OPTION)? 
(GRANTED BY <grantor>)? 
(IN <catalog>)?

• If <catalog> is not specified, session catalog is used
• If (GRANTED BY <grantor>) is not specified, it is considered to be a current logged in user

Examples

• GRANT role TO username
• GRANT role1, role2 TO USER username
• GRANT role TO ROLE rolename
• GRANT role1, role2, role3 TO ROLE rolename, USER username, other_username IN hive

SPI Modifications:

• ConnectorMetadata

  + void grantRole(ConnectorSession session, List<String> roles, List<String> toUsers, List<String> roRoles, boolean withAdminOption, String grantor)
  

[arhimondr]

REVOKE ROLE

Revokes role from the specified user in <catalog>

Syntax:

REVOKE (ADMIN OPTION FOR)? <role name>  (, <role name>)+ 
FROM <user_name|USER user_name|ROLE role_name> (, <user_name|USER user_name|ROLE role_name>)+ 
(GRANTED BY <grantor>)?
(IN <catalog>)?

• If <catalog> is not specified, session catalog is used
• If (GRANTED BY <grantor>) is not specified, it is considered to be a current logged in user

SPI Modifications:

• ConnectorMetadata

  + void revokeRole(ConnectorSession session, List<String> roles, List<String> fromUsers, List<String> fromRoles, boolean revokeAdminOption, String revoker)
  

Follow-up

• Support <drop behavior>

[arhimondr]

DROP ROLE

Drops role in <catalog>

Syntax:

DROP <role name>  (IN <catalog>)?

• If <catalog> is not specified, session catalog is used

SPI Modifications:

• ConnectorMetadata

  + void dropRole(ConnectorSession session, String role)
  

[arhimondr]

VIEW: ROLES

<catalog>.information_schema.roles

Lists all the roles available in <catalog>

Output Columns:

• role_name

SPI Modifications:

• ConnectorMetadata

  + List<String> listRoles(ConnectorSession session)
  

[arhimondr]

SHOW ROLE GRANT [follow-up]

Show roles granted to user in <catalog>

Syntax:

SHOW ROLE GRANT (<user_name|USER user_name|ROLE role_name>)? (FROM <catalog>)?

• If <catalog> is not specified, session catalog is used

• If <user_name|USER user_name|ROLE role_name> is not specified, session user is used

• It is shortcut for

  SELECT * 
  FROM <catalog>.information_schema.role_authorization_descriptors 
  WHERE grantee=<principal name> AND grantee_type=<principal type>
  

• Result is not recursive. Only direct assignments are being shown

[arhimondr]

SHOW ROLES [follow-up]

Shows all available roles in <catalog>

Syntax:

SHOW ROLES (FROM <catalog>)?

• If <catalog> is not specified, session catalog is used

• It is shortcut for:

  SELECT * 
  FROM <catalog>.information_schema.roles
  

[arhimondr]

SET ROLE

Set the current role name for the current SQL-session context in <catalog>

Syntax:

SET ROLE <role_name|ALL|NONE> (IN <catalog>)?

• If <catalog> is not specified, session catalog is used

SPI Modifications:

• Identity

  + RoleSelectionType roleSelectionType;
  + Optional<String> selectedRole;
  

  enum RoleSelectionType {
     DEFAULT, NONE, ALL, SUBSET
  }
  

Implementation design

• When SET ROLE has not been invoked on the session - the selection of roles is connector specific.
• SET ROLE NONE
• SET ROLE ALL
• SET ROLE role. Every consequent invocation will override the previous one.

[arhimondr]

VIEW: APPLICABLE_ROLES

<catalog>.information_schema.applicable_roles

Recursively Identifies the applicable roles for the current user in <catalog>

Output Columns:

• grantee
• grantee_type - <USER|ROLE>
• role_name
• is_grantable

[arhimondr]

VIEW: ENABLED_ROLES

<catalog>.information_schema.enabled_roles

Recursively identify the enabled roles for the current SQL-session for <catalog>

Output Columns:

• role_name

[arhimondr]

SHOW CURRENT ROLES [follow-up]

Shows all available roles in <catalog>

Syntax:

SHOW CURRENT ROLES (FROM <catalog>)?

• If <catalog> is not specified, session catalog is used

• It is shortcut for:

  SELECT * 
  FROM <catalog>.information_schema.enabled_roles
  

[arhimondr]

PERMISSIONS

As Presto is only data providers aggregator, no special permissions for invoking any of the above commands must be checked explicitly by Presto. All the commands must be silently passed to the underlying connector, and the connector itself must decide whether to allow execution or not.

[cawallin]

Very thorough! I have some comments, both to pare down what we have to do to make it a smaller MVP, and additional suggestions.

• No “WITH GRANTOR” – seems like that’s a way that could be used to subvert security (e.g. you should have to log in as a particular user to issue role grants as that user)
• No WITH ADMIN OPTION
• No recursive ROLEs? Different databases have different support for that, and we can avoid it for now
• We should follow the SQL standard names, unfortunately
• Need to add a ROLE keyword to regular GRANT: e.g.
  GRANT INSERT, SELECT ON foo TO ROLE admin;
• I believe that information schema methods in ConnectorMetadata start with list*
• I don’t think we need SHOW PRINCIPALS or the APPLICABLE_ROLES view at first

[arhimondr]

No “WITH GRANTOR” – seems like that’s a way that could be used to subvert security (e.g. you should have to log in as a particular user to issue role grants as that user)

Let's assume that you are logged in as user GRANTOR that has ADMIN, MODERATOR, SUPERUSER roles granted. You want to grant DUMMY role to user GRANTEE. And you want to grant this role on behalf of your MODERATOR role. Then you do GRANT dummy TO grantee WITH GRANTOR moderator. Then in grantor field in ROLE_AUTHORIZATION_DESCRIPTORS view grantor will be MODERATOR.

As described in PERMISSION section - security is completely on underlying database. Presto just need to provide option to trigger such a statement.

I would like to introduce WITH GRANTOR in the POC version. We need grantor field in SPI. So, we don't need to modify SPI further.

No WITH ADMIN OPTION

Ditto about SPI modifications

No recursive ROLEs? Different databases have different support for that

Do you think it is better to expose separate method in SPI?

We should follow the SQL standard names, unfortunately

+1

I believe that information schema methods in ConnectorMetadata start with list*

+1

I don’t think we need SHOW PRINCIPALS or the APPLICABLE_ROLES view at first

Yes, we can implement it later. It doesn't involve any breaking changes in the SPI

Need to add a ROLE keyword to regular GRANT: e.g. GRANT INSERT, SELECT ON foo TO ROLE admin;

+1

[cawallin]

I was suggesting now allowing recursive ROLE, but it's the same amount of work so we might as well have it.

Looks good!