add more privilege types

[flaneur2020]

Parent Issue

• Fine-grained user privilege on database & table #2894

Summary

currently we have 5 privilege types:

• Usage
• Create
• Select
• Insert
• Set

we'd add more production-ready privilege types in the future. but right now, the up front high priority task is to setup the framework on privileges checking. we'd make a plan about which privileges to add first, and how to cover the privilege check logic into the current code path.

some thoughts:

• do not get the privilege types list too big
• make the semantics of the few privilege types clear (more comments?)
• cover the DML & DDL operations with the privilege checking logics first, add the query operations later

steps:

• get the first-to-add privilege types list
• check the behaviour on granting SUPER on a table object
• Add more privileges #3567

[flaneur2020]

/assignme

[flaneur2020]

the privilege options in clickhouse:

https://clickhouse.com/docs/en/sql-reference/statements/grant/

[flaneur2020]

this is the privileges in tidb:

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
32 rows in set (0.00 sec)

each privilege has diferent contexts

[flaneur2020]

mysql> GRANT SUPER ON bendcloud.* TO 'test-grant';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

the privileges in the global contexts can only be granted to GLOBAL object

[flaneur2020]

a draft on the privieleges to be added in this iteration:

• Usage
• Select
• Insert
• Update
• Delete
• Create
• Drop
• Alter
• Grant
• Create User
• Create Role
• Super