MDEV-23237 mariadb.sys has too many privileges

don't GRANT UPDATE ON mysql.global_priv TO mariadb.sys@localhost;

9 files changed, 4 insertions, 9 deletions

diff --git a/client/mysql_upgrade.c b/client/mysql_upgrade.c
index aa73680a3df..4f3f1ff86da 100644
--- a/client/mysql_upgrade.c
+++ b/client/mysql_upgrade.c
@@ -887,6 +887,7 @@ static const char *expected_errors[]=
   "ERROR 1290", /* RR_OPTION_PREVENTS_STATEMENT */
   "ERROR 1347", /* 'mysql.user' is not of type 'BASE TABLE' */
   "ERROR 1348", /* Column 'Show_db_priv' is not updatable */
+  "ERROR 1356", /* definer of view lack rights (UPDATE) */
   0
 };
 
diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result
index d24f53142d8..456bc251e78 100644
--- a/mysql-test/main/grant.result
+++ b/mysql-test/main/grant.result
@@ -968,7 +968,6 @@ select * from  information_schema.table_privileges;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mysqltest_8'@'%'	def	test	t1	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
-'mariadb.sys'@'localhost'	def	mysql	global_priv	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
 connect  conn5,localhost,mysqltest_8,,;
 select * from t1;
@@ -985,7 +984,6 @@ GRANT USAGE ON *.* TO `mysqltest_8`@`%`
 select * from  information_schema.table_privileges;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
-'mariadb.sys'@'localhost'	def	mysql	global_priv	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
 flush privileges;
 show grants for mysqltest_8@'';
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 697e00635eb..7e38c33e8c5 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -576,7 +576,6 @@ GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRAN
 select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
-'mariadb.sys'@'localhost'	def	mysql	global_priv	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
 drop view v1, v2, v3;
 drop table t1;
diff --git a/mysql-test/main/upgrade_MDEV-19650.result b/mysql-test/main/upgrade_MDEV-19650.result
index 386c15ae83e..7e8610683e2 100644
--- a/mysql-test/main/upgrade_MDEV-19650.result
+++ b/mysql-test/main/upgrade_MDEV-19650.result
@@ -165,7 +165,6 @@ GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
 SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'";
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
-'mariadb.sys'@'localhost'	def	mysql	global_priv	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
 # check non root
 CREATE USER 'not_root'@'localhost';
diff --git a/mysql-test/main/view_grant.result b/mysql-test/main/view_grant.result
index 263c932097d..954b57db768 100644
--- a/mysql-test/main/view_grant.result
+++ b/mysql-test/main/view_grant.result
@@ -1344,7 +1344,6 @@ connection default;
 select * from information_schema.table_privileges;
 GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
 'mariadb.sys'@'localhost'	def	mysql	global_priv	SELECT	NO
-'mariadb.sys'@'localhost'	def	mysql	global_priv	UPDATE	NO
 'mariadb.sys'@'localhost'	def	mysql	global_priv	DELETE	NO
 End of 5.0 tests.
 connection default;
diff --git a/mysql-test/suite/funcs_1/r/is_table_privileges.result b/mysql-test/suite/funcs_1/r/is_table_privileges.result
index d4f0de131d1..d7065b54788 100644
--- a/mysql-test/suite/funcs_1/r/is_table_privileges.result
+++ b/mysql-test/suite/funcs_1/r/is_table_privileges.result
@@ -56,7 +56,6 @@ SELECT table_catalog, table_schema, table_name, privilege_type
 FROM information_schema.table_privileges WHERE table_catalog IS NOT NULL;
 table_catalog	table_schema	table_name	privilege_type
 def	mysql	global_priv	SELECT
-def	mysql	global_priv	UPDATE
 def	mysql	global_priv	DELETE
 ######################################################################
 # Testcase 3.2.11.2+3.2.11.3+3.2.11.4:
diff --git a/mysql-test/suite/roles/set_role-table-column-priv.result b/mysql-test/suite/roles/set_role-table-column-priv.result
index 9e1d21bab5e..4aaa0388170 100644
--- a/mysql-test/suite/roles/set_role-table-column-priv.result
+++ b/mysql-test/suite/roles/set_role-table-column-priv.result
@@ -63,7 +63,7 @@ ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'ro
 drop user 'test_user'@'localhost';
 select * from mysql.tables_priv;
 Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
-localhost	mysql	mariadb.sys	global_priv	root@localhost	0000-00-00 00:00:00	Select,Update,Delete	
+localhost	mysql	mariadb.sys	global_priv	root@localhost	0000-00-00 00:00:00	Select,Delete	
 	mysql	test_role2	roles_mapping	root@localhost	0000-00-00 00:00:00		Select
 revoke select on mysql.roles_mapping from test_role2;
 delete from mysql.user where user like'test_%';
diff --git a/mysql-test/suite/roles/set_role-table-simple.result b/mysql-test/suite/roles/set_role-table-simple.result
index 107ca726e22..f33df34d85e 100644
--- a/mysql-test/suite/roles/set_role-table-simple.result
+++ b/mysql-test/suite/roles/set_role-table-simple.result
@@ -61,7 +61,7 @@ ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'ro
 drop user 'test_user'@'localhost';
 select * from mysql.tables_priv;
 Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
-localhost	mysql	mariadb.sys	global_priv	root@localhost	0000-00-00 00:00:00	Select,Update,Delete	
+localhost	mysql	mariadb.sys	global_priv	root@localhost	0000-00-00 00:00:00	Select,Delete	
 	mysql	test_role2	roles_mapping	root@localhost	0000-00-00 00:00:00	Select	
 revoke select on mysql.roles_mapping from test_role2;
 delete from mysql.user where user like'test_%';
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index f81aa290100..d4d16c74c11 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -116,7 +116,7 @@ CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', H
 CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(141) DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger','Delete versioning rows') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Column_priv set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin   comment='Table privileges';
 
 CREATE TEMPORARY TABLE tmp_user_sys LIKE tables_priv;
-INSERT INTO tmp_user_sys (Host,Db,User,Table_name,Grantor,Timestamp,Table_priv) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0','Select,Update,Delete');
+INSERT INTO tmp_user_sys (Host,Db,User,Table_name,Grantor,Timestamp,Table_priv) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0','Select,Delete');
 INSERT INTO tables_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_user_creation;
 DROP TABLE tmp_user_sys;