RBAC doesn't work as expect

[c4pt0r]

Bug Report

1. What did you do?

create some roles, and grant user dev to app_developer role.

CREATE DATABASE app_db;
CREATE ROLE 'app_developer', 'app_read', 'app_write';
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'password';
GRANT 'app_developer' TO 'dev'@'localhost';
FLUSH PRIVILEGES;

2. What did you expect to see?

user dev can see app_db.

3. What did you see instead?

Server version: 5.7.25-TiDB-v3.0.0-rc.1-82-g0afa56704 MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
+--------------------+
1 row in set (0.000 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

[morgo]

There is a bug here, but the test-case is not quite correct. It should be:

CREATE DATABASE newdb;
CREATE ROLE 'app_developer';
GRANT ALL ON newdb.* TO 'app_developer';
CREATE USER 'dev';
GRANT 'app_developer' TO 'dev';
SET DEFAULT ROLE app_developer TO 'dev';

The role app_developer is not applied in MySQL either unless it is a DEFAULT ROLE, or the user dev executes the statement SET ROLE app_developer. After executing this in MySQL:

mysql [localhost:8015] {dev} ((none)) > show grants;
+------------------------------------------------+
| Grants for dev@%                               |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `dev`@`%`                |
| GRANT ALL PRIVILEGES ON `newdb`.* TO `dev`@`%` |
| GRANT `app_developer`@`%` TO `dev`@`%`         |
+------------------------------------------------+
3 rows in set (0.00 sec)

mysql [localhost:8015] {dev} ((none)) > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| newdb              |
+--------------------+
2 rows in set (0.00 sec)

In TiDB:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
+--------------------+
1 row in set (0.00 sec)

mysql> set role app_developer;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
+--------------------+
1 row in set (0.01 sec)

mysql> show grants;
+----------------------------------------+
| Grants for User                        |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'dev'@'%'        |
| GRANT 'app_developer'@'%' TO 'dev'@'%' |
+----------------------------------------+
2 rows in set (0.01 sec)

[tiancaiamao]

It should have been fixed in #10261
Are you testing release-3.1 ? @c4pt0r

[morgo]

@tiancaiamao I can reproduce this on master and release-3.0:

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.0-rc.1-2-g6c9e66963
Git Commit Hash: 6c9e66963b180dec5c72e25d0ddd46f1bfbc6026
Git Branch: release-3.0
UTC Build Time: 2019-05-21 02:32:25
GoVersion: go version go1.12.1 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

[tiancaiamao]

OK, I'll take a look @morgo @c4pt0r

[c4pt0r]

@morgo I think you need FLUSH PRIVILEGES;

[c4pt0r]

It works in this version: 5.7.25-TiDB-v3.0.0-rc.1-90-gf6346a1e8

[morgo]

@morgo I think you need FLUSH PRIVILEGES;

If FLUSH PRIVILEGES is required, that is a regression of #8886

It is not supposed to be required for DCL statements, only DML directly to the base tables.

Edit: confirmed flush privileges does not affect test-case.