[MDEV-13427] INSERT permission error on 2nd level view during mysql_upgrade Created: 2017-08-02  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Scripts & Clients, Views
Affects Version/s: 5.5, 10.0, 10.1, 10.1.23, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Hartmut Holzgraefe Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: 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



 Comments   
Comment by Hartmut Holzgraefe [ 2017-08-02 ]

Priority changed to "Minor" as the error message is annoying/confusing, but the view is still usable after running mysql_upgrade

Comment by Elena Stepanova [ 2017-08-04 ]

The message is produced upon REPAIR NO_WRITE_TO_BINLOG VIEW ... which mysql_upgrade issues during operation.

MTR-friendly test case

 
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;
 
  
REPAIR NO_WRITE_TO_BINLOG VIEW `v2`;
 
 
 
# Cleanup
 
 
 
DROP VIEW v2, v1;
 
DROP TABLE t1;
 
DROP USER u1@localhost;

Generated at Thu Feb 08 08:05:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.