Fine-grained user privilege on database & table

[flaneur2020]

Summary

Currently we have got an UserPrivilege in the metastore, however it do not have the infomation on database & table yet.

We can allowing user grant privileges(like Select / Create) on a database or table.

Related: #2703 #2797

thoughts on the user grants management

we may add a struct called GrantSet or something else, which contains a list of GrantEntry, it can be used both in user and role. the SHOW GRANTS statement directly dumps the list of the GrantSet:

mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+

a GrantEntry has three levels: Global, Database and Table. the grant level is determined by the ON part in the GRANT statement.

each GrantEntry is identified by the ON and TO parts in the statement, like ("*.*", "jeffery", "localhost"), ("db1.*", "jeffery", "localhost"). if two grant statements have the same identifier, then the privileges should be merged into one Grant item:

mysql> GRANT SELECT ON *.* TO u1;
mysql> GRANT INSERT ON *.* TO u1;

mysql> SHOW GRANTS FOR 'u1';
+------------------------------------------------------------------+
| Grants for u1                                                    |
+------------------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`                              |
+------------------------------------------------------------------+

on checking an operation's privilege, we can get all the current user's grant set from the metasrv, and filter grant set by (user, database/table, hostname), the API might looks like this:

// impl Session { 
fn get_effective_grant_set(&self) -> Result<GrantSet>

// impl GrantSet

fn validate_privilege(&self, db: &str, object: &GrantObject, privilege: &PrivilegeType) -> Result<()>

please note that the grant set of the current session CAN NOT directly determined by the current user & hostname, because the active grant set may be changed by executing USE ROLE (which limits one user's effective grant set) in the current session.

this API would not change after adding RBAC: what we get is the set of effective grant set of the current user, adding an role to an user would affect this user's grant set at last, the API caller could not care about roles at all. this is why the api in session called get_effective_grant_set.

on DROP TABLE/DATABASE, the relevant GrantEntry of ALL the users needs get droped altogether. (needs some more investigation on it)

any suggestion is welcomed!

Steps

• [ISSUE-2894][PATCH-1] Add parser on GRANT .. TO database.table #2901
• Bind user to session #2779
• manage user grants in the UserManager #2955
• add more privilege types #3173
• add revoke statement #3193
• Add SHOW GRANTS statement #3262
• drop grants on database/table is getting dropped (seems mysql do not drop the relevant grants after dropping a database)
• verify privilege on creating table/database #3169
• verify privilege on write statements #3170

[flaneur2020]

/assignme

[BohuTANG]

If we follow the MySQL way, Role has privileges, the securable object is database/table, user only has a role at a time

[drmingdrmer]

How do you like to store these grant information in metasrv?
E.g., what is it like if you are gonna store these info in a kv store?

[flaneur2020]

it'd prefered to stored as a ordered flat kv format, like

(user, host, database, table) -> privilege_set

so we can list an user's grants in a prefix scan, and can drop a grant individually.

but in the eariler days, a big GrantSet json is easier to implement,

user -> GrantSet

and it would not get very big in the near future (i cant imagine how to manage a 20+ grants configs🤔)

the cons of the big JSON is that we have to deserialize the object, make modifications, and write back.

[drmingdrmer]

The firs one would be better to me 🤔

[flaneur2020]

@drmingdrmer could we make dropping a database + dropping the users' grant on this database as an atomic operation in the current code base? q.q

it'd be nice to have some transactional guarantee on it

[drmingdrmer]

@drmingdrmer could we make dropping a database + dropping the users' grant on this database as an atomic operation in the current code base? q.q

it'd be nice to have some transactional guarantee on it

Hmmm... not a big deal.