Role-base User Created Non-retrievable View

[xinghua0123]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table table1(
col1 int,
col2 int,
col3 int
);

insert into table1 values (1,1,1),(2,2,2);

CREATE ROLE 'ACL-mobius-admin';

GRANT Select,Insert,Update,Delete,Create,Drop,Alter,Index,Create View,Show View ON test.* TO 'ACL-mobius-admin'@'%';

CREATE USER 'mobius-admin';

GRANT 'ACL-mobius-admin'@'%' to 'mobius-admin'@'%';

SET DEFAULT ROLE ALL TO 'mobius-admin';

##login as mobius-admin
mysql -h 127.0.0.1 -P 4000 -u mobius-admin

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| test |
+--------------------+
2 rows in set (0.01 sec)

use test;

CREATE ALGORITHM = UNDEFINED DEFINER = mobius-admin@127.0.0.1 SQL SECURITY DEFINER VIEW test_view (col1 , col2 , col3) AS SELECT * from table1;

select * from test_view;
ERROR 1356 (HY000): View 'test.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

2. What did you expect to see? (Required)

mysql> select * from test_view;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+------+

3. What did you see instead (Required)

mysql> select * from test_view;
ERROR 1356 (HY000): View 'test.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

4. What is your TiDB version? (Required)

v4.0.12

[xinghua0123]

Bug verified by @crazycs520

[bb7133]

Maybe another bug when I was trying to reproduce it:

...

(##login as mobius-admin
mysql -h 127.0.0.1 -P 4000 -u mobius-admin)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| mobius-admin@% |
+----------------+
1 row in set (0.00 sec)

mysql> CREATE ALGORITHM = UNDEFINED DEFINER = 'mobius-admin@%' SQL SECURITY DEFINER VIEW test_view (col1 , col2 , col3) AS SELECT * from table1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[bb7133]

I guess this bug is not just related to RBAC:

tidb> create user test2;
Query OK, 0 rows affected (0.03 sec)

tidb> GRANT Select,Insert,Update,Delete,Create,Drop,Alter,Index,Create View,Show View ON test.* TO 'test2'@'%';
Query OK, 0 rows affected (0.01 sec)

(##login as test2
mysql -h 127.0.0.1 -P 4000 -u test2)

use test;
select * from test_view;
(Error):
ERROR 1356 (HY000): View 'test.test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[morgo]

@bb7133 It is related to RBAC. The reason you are still seeing this error is because of the DEFINER.

Here is a testcase that can be completed in one session (no need to log back in):

use test;
DROP TABLE IF EXISTS table1;
DROP VIEW IF EXISTS test_view, test_view2, test_view3;
DROP USER IF EXISTS 'mobius-admin';
DROP ROLE IF EXISTS 'ACL-mobius-admin';

create table table1(
col1 int,
col2 int,
col3 int
);

insert into table1 values (1,1,1),(2,2,2);
CREATE ROLE 'ACL-mobius-admin';
CREATE USER 'mobius-admin';
CREATE USER 'mobius-admin-no-role';
GRANT Select,Insert,Update,Delete,Create,Drop,Alter,Index,Create View,Show View ON test.* TO 'ACL-mobius-admin'@'%';
GRANT Select,Insert,Update,Delete,Create,Drop,Alter,Index,Create View,Show View ON test.* TO 'mobius-admin-no-role'@'%';
GRANT 'ACL-mobius-admin'@'%' to 'mobius-admin'@'%';
SET DEFAULT ROLE ALL TO 'mobius-admin';
CREATE ALGORITHM = UNDEFINED DEFINER = 'mobius-admin'@'127.0.0.1' SQL SECURITY DEFINER VIEW test_view (col1 , col2 , col3) AS SELECT * from table1;
CREATE ALGORITHM = UNDEFINED DEFINER = 'mobius-admin-no-role'@'127.0.0.1' SQL SECURITY DEFINER VIEW test_view2 (col1 , col2 , col3) AS SELECT * from table1;
CREATE VIEW test_view3 (col1 , col2 , col3) AS SELECT * from table1;

select * from test_view; # fails
select * from test_view2; # works
select * from test_view3; # works

test_view and test_view2 should have identical behavior but they do not. So it looks like the code that runs the view in the permissions of DEFINER is not picking up the DEFAULT ROLE that is associated with mobius-admin.

[morgo]

This is a bug in the privilege manager code. The SQL Security definer calls RequestVerificationWithUser, which calls RequestVerification but sets the activeRoles to nil:

tidb/privilege/privileges/privileges.go

Lines 142 to 143 in 289dcfe

	mysqlPriv := p.Handle.Get()
	return mysqlPriv.RequestVerification(nil, user.Username, user.Hostname, db, table, column, priv)

I verified outside of this example that the session is expected to preload default roles, and not the privilege manager. So the default roles need to be loaded in RequestVerificationWithUser. It's an easy enough fix.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Bug

1. Root Cause Analysis (RCA) (optional)

The privilege manager code did not correctly consider default roles in the API call RequestVerificationWithUser. This is used by views when using the security of the definer.

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

Do not use roles, but regular privileges.

5. Affected versions

[v4.0.1:v4.0.12], [v5.0.0:v5.0.1]

6. Fixed versions

master

[ti-srebot]

( AffectedVersions ) fields are empty.
The values in ( AffectedVersions ) fields are incorrect.

[ti-srebot]

( AffectedVersions ) fields are empty.
The values in ( AffectedVersions ) fields are incorrect.