Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.1.23, 10.2, 10.3, 10.4, 10.5
-
None
Description
When having two views with a DEFINER that only has SELECT privileges on the underlying table, and the second view using the first one, on running mysql_upgrade the following permission related error is printed for the 2nd level view:
Error : INSERT command denied to user 'u1'@'localhost' for column 'id' in table 't1'
|
Error : View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
error : Corrupt
|
When also giving the INSERT privilege to the DEFINER no error is raised.
Only giving SELECT privileges to the definer should be perfectly ok though, no matter how deep views are nested.
How to reproduce:
USE test;
|
DROP USER IF EXISTS u1@localhost;
|
DROP VIEW IF EXISTS v2;
|
DROP VIEW IF EXISTS v1;
|
DROP TABLE IF EXISTS t1;
|
|
|
CREATE USER u1@localhost;
|
GRANT SELECT ON test.* TO u1@localhost;
|
|
|
CREATE TABLE t1(id int primary key);
|
INSERT INTO t1 VALUES(1);
|
|
|
CREATE DEFINER=u1@localhost SQL SECURITY DEFINER VIEW v1 AS
|
SELECT * FROM t1;
|
|
|
CREATE DEFINER=u1@localhost SQL SECURITY DEFINER VIEW v2 AS
|
SELECT * FROM v1;
|
then run mysql_upgrade -f (-f to force execution even if upgrade has already been run for the current version)
Result:
Phase 1/7: Checking and upgrading mysql database
|
Processing databases
|
mysql
|
mysql.column_stats OK
|
[...]
|
mysql.user OK
|
Phase 2/7: Installing used storage engines... Skipped
|
Phase 3/7: Fixing views
|
test.v1 OK
|
test.v2
|
Error : INSERT command denied to user 'u1'@'localhost' for column 'id' in table 't1'
|
Error : View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
error : Corrupt
|
Phase 4/7: Running 'mysql_fix_privilege_tables'
|
Phase 5/7: Fixing table and database names
|
Phase 6/7: Checking and upgrading tables
|
Processing databases
|
information_schema
|
performance_schema
|
test
|
test.t1 OK
|
Phase 7/7: Running 'FLUSH PRIVILEGES'
|
OK
|