diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2021-12-13 16:15:21 +0100 |
|---|---|---|
| committer | Oleksandr Byelkin <sanja@mariadb.com> | 2022-06-15 15:57:17 +0200 |
| commit | 933ef02ce0781b43c9ff3e6a3881b9646f4058d2 (patch) | |
| tree | 18d3ac22859f1d89fba267ae7b3cd8d5e4837deb | |
| parent | b334d0482797c8d9d0aef43caa4579a71c14de63 (diff) | |
| download | mariadb-git-bb-10.10-MDEV-5215-2.tar.gz | |
MDEV-5215 Granted to PUBLICbb-10.10-MDEV-5215-2
30 files changed, 1058 insertions, 239 deletions
diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d1e7af6afde..7bb63f0c4e4 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -229,7 +229,7 @@ INSERT INTO t1 VALUES (1),(2); EXPLAIN UPDATE v1, mysql.user SET v1.a = v1.a + 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE global_priv index NULL PRIMARY 1149 NULL 5 Using index +1 SIMPLE global_priv index PRIMARY PRIMARY 1149 NULL 6 Using where; Using index DROP TABLE t1; DROP VIEW v1; # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index f4fc151c9f2..a84124dc41d 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -571,7 +571,7 @@ create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; select * from information_schema.views where table_schema <> 'sys'; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM -def mysql user select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` NONE YES mariadb.sys@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def mysql user select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` where `mysql`.`global_priv`.`Host` <> '' or `mysql`.`global_priv`.`User` <> 'PUBLIC' NONE YES mariadb.sys@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED def test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index 0649c9139fd..794228dddad 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -1700,7 +1700,7 @@ Phase 7/7: Running 'FLUSH PRIVILEGES' OK SHOW CREATE TABLE mysql.user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` where `mysql`.`global_priv`.`Host` <> '' or `mysql`.`global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci CREATE ROLE `aRole`; SET ROLE `aRole`; FLUSH PRIVILEGES; @@ -1876,7 +1876,7 @@ Phase 7/7: Running 'FLUSH PRIVILEGES' OK SHOW CREATE TABLE mysql.user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` where `mysql`.`global_priv`.`Host` <> '' or `mysql`.`global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci CREATE ROLE `aRole`; SET DEFAULT ROLE aRole; SHOW GRANTS; @@ -1994,7 +1994,7 @@ alter table mysql.user change authentication_string auth_string text collate utf # mysql_upgrade --force --silent 2>&1 select count(*) from mysql.global_priv; count(*) -5 +6 drop table mysql.global_priv; rename table mysql.global_priv_bak to mysql.global_priv; # diff --git a/mysql-test/main/mysqldump-system.result b/mysql-test/main/mysqldump-system.result index 559f6f29f44..42cbe7b2f01 100644 --- a/mysql-test/main/mysqldump-system.result +++ b/mysql-test/main/mysqldump-system.result @@ -55,8 +55,8 @@ CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; INSTALL PLUGIN test_plugin_server SONAME 'AUTH_TEST_PLUGIN_LIB'; INSTALL PLUGIN cleartext_plugin_server SONAME 'AUTH_TEST_PLUGIN_LIB'; CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE; -CREATE USER `root`@`localhost`; CREATE USER `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; +CREATE USER `root`@`localhost`; SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; GRANT mariadb_dump_import_role TO CURRENT_USER(); @@ -71,16 +71,16 @@ GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; /*M!100005 SET DEFAULT ROLE NONE FOR 'mariadb.sys'@'localhost' */; /*!80001 ALTER USER 'mariadb.sys'@'localhost' DEFAULT ROLE NONE */; -GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; -GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; -/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; -/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_1` TO `foobar`@`%`; GRANT `role_2` TO `foobar`@`%`; GRANT USAGE ON *.* TO `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'foobar'@'%' */; /*!80001 ALTER USER 'foobar'@'%' DEFAULT ROLE 'role_2' */; +GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_2` TO `role_1` WITH ADMIN OPTION; GRANT SHOW DATABASES ON *.* TO `role_1`; GRANT USAGE ON *.* TO `role_2`; @@ -655,19 +655,19 @@ DELIMITER ; /*!50701 DROP USER IF EXISTS 'mariadb.sys'@'localhost' */; CREATE /*M!100103 OR REPLACE */ USER `mariadb.sys`@`localhost` PASSWORD EXPIRE; DELIMITER | -/*M!100101 IF current_user()="'root'@'localhost'" THEN - SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'root'@'localhost''"; -END IF */| -DELIMITER ; -/*!50701 DROP USER IF EXISTS 'root'@'localhost' */; -CREATE /*M!100103 OR REPLACE */ USER `root`@`localhost`; -DELIMITER | /*M!100101 IF current_user()="'foobar'@'%'" THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'foobar'@'%''"; END IF */| DELIMITER ; /*!50701 DROP USER IF EXISTS 'foobar'@'%' */; CREATE /*M!100103 OR REPLACE */ USER `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; +DELIMITER | +/*M!100101 IF current_user()="'root'@'localhost'" THEN + SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT="Don't remove current user 'root'@'localhost''"; +END IF */| +DELIMITER ; +/*!50701 DROP USER IF EXISTS 'root'@'localhost' */; +CREATE /*M!100103 OR REPLACE */ USER `root`@`localhost`; SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; GRANT mariadb_dump_import_role TO CURRENT_USER(); @@ -684,16 +684,16 @@ GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; /*M!100005 SET DEFAULT ROLE NONE FOR 'mariadb.sys'@'localhost' */; /*!80001 ALTER USER 'mariadb.sys'@'localhost' DEFAULT ROLE NONE */; -GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; -GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; -/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; -/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_1` TO `foobar`@`%`; GRANT `role_2` TO `foobar`@`%`; GRANT USAGE ON *.* TO `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'foobar'@'%' */; /*!80001 ALTER USER 'foobar'@'%' DEFAULT ROLE 'role_2' */; +GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_2` TO `role_1` WITH ADMIN OPTION; GRANT SHOW DATABASES ON *.* TO `role_1`; GRANT USAGE ON *.* TO `role_2`; @@ -1260,8 +1260,8 @@ UNLOCK TABLES; INSTALL PLUGIN test_plugin_server /*M!100401 IF NOT EXISTS */ SONAME 'AUTH_TEST_PLUGIN_LIB'; INSTALL PLUGIN cleartext_plugin_server /*M!100401 IF NOT EXISTS */ SONAME 'AUTH_TEST_PLUGIN_LIB'; CREATE USER IF NOT EXISTS `mariadb.sys`@`localhost` PASSWORD EXPIRE; -CREATE USER IF NOT EXISTS `root`@`localhost`; CREATE USER IF NOT EXISTS `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; +CREATE USER IF NOT EXISTS `root`@`localhost`; SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; GRANT mariadb_dump_import_role TO CURRENT_USER(); @@ -1276,16 +1276,16 @@ GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; /*M!100005 SET DEFAULT ROLE NONE FOR 'mariadb.sys'@'localhost' */; /*!80001 ALTER USER 'mariadb.sys'@'localhost' DEFAULT ROLE NONE */; -GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; -GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; -/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; -/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_1` TO `foobar`@`%`; GRANT `role_2` TO `foobar`@`%`; GRANT USAGE ON *.* TO `foobar`@`%` IDENTIFIED VIA test_plugin_server USING 'plug_dest'; /*M!100005 SET DEFAULT ROLE 'role_2' FOR 'foobar'@'%' */; /*!80001 ALTER USER 'foobar'@'%' DEFAULT ROLE 'role_2' */; +GRANT `role_1` TO `root`@`localhost` WITH ADMIN OPTION; +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION; +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; +/*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; +/*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT `role_2` TO `role_1` WITH ADMIN OPTION; GRANT SHOW DATABASES ON *.* TO `role_1`; GRANT USAGE ON *.* TO `role_2`; diff --git a/mysql-test/main/public_basic.result b/mysql-test/main/public_basic.result new file mode 100644 index 00000000000..fa39b4b9fec --- /dev/null +++ b/mysql-test/main/public_basic.result @@ -0,0 +1,94 @@ +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +# it is not PUBLIC but an user +# (this should work as it allowed for roles for example) +create user PUBLIC; +create user PUBLIC@localhost; +GRANT SELECT on test.* to PUBLIC@localhost; +drop user PUBLIC@localhost; +drop user PUBLIC; +# preinstalled PUBLIC +GRANT SELECT on test.* to PUBLIC; +GRANT SELECT on mysql.db to PUBLIC; +select * from mysql.global_priv where user="PUBLIC" ; +Host User Priv + PUBLIC {"access":0,"version_id":VERSION,"is_role":true} +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT SELECT ON `test`.* TO `PUBLIC` +GRANT SELECT ON `mysql`.`db` TO `PUBLIC` +GRANT UPDATE on test.* to PUBLIC; +GRANT UPDATE on mysql.db to PUBLIC; +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT SELECT, UPDATE ON `test`.* TO `PUBLIC` +GRANT SELECT, UPDATE ON `mysql`.`db` TO `PUBLIC` +REVOKE SELECT on test.* from PUBLIC; +REVOKE SELECT on mysql.db from PUBLIC; +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT UPDATE ON `test`.* TO `PUBLIC` +GRANT UPDATE ON `mysql`.`db` TO `PUBLIC` +REVOKE UPDATE on test.* from PUBLIC; +REVOKE UPDATE on mysql.db from PUBLIC; +REVOKE UPDATE on test.* from PUBLIC; +ERROR 42000: There is no such grant defined for user 'PUBLIC' on host '' +REVOKE UPDATE on mysql.db from PUBLIC; +ERROR 42000: There is no such grant defined for user 'PUBLIC' on host '' on table 'db' +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +# automaticly added PUBLIC +delete from mysql.global_priv where user="PUBLIC"; +flush privileges; +select * from mysql.global_priv where user="PUBLIC" ; +Host User Priv +GRANT SELECT on test.* to PUBLIC; +GRANT SELECT on mysql.db to PUBLIC; +select * from mysql.global_priv where user="PUBLIC" ; +Host User Priv + PUBLIC {"access":0,"version_id":VERSION,"is_role":true} +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT SELECT ON `test`.* TO `PUBLIC` +GRANT SELECT ON `mysql`.`db` TO `PUBLIC` +GRANT UPDATE on test.* to PUBLIC; +GRANT UPDATE on mysql.db to PUBLIC; +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT SELECT, UPDATE ON `test`.* TO `PUBLIC` +GRANT SELECT, UPDATE ON `mysql`.`db` TO `PUBLIC` +REVOKE SELECT on test.* from PUBLIC; +REVOKE SELECT on mysql.db from PUBLIC; +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT UPDATE ON `test`.* TO `PUBLIC` +GRANT UPDATE ON `mysql`.`db` TO `PUBLIC` +REVOKE UPDATE on test.* from PUBLIC; +REVOKE UPDATE on mysql.db from PUBLIC; +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +GRANT XXXXXX TO CURRENT_USER; +ERROR OP000: Invalid role specification `XXXXXX` +# following should fail with the same error as above +GRANT PUBLIC TO CURRENT_USER; +ERROR OP000: Invalid role specification `PUBLIC` +REVOKE XXXXXX FROM CURRENT_USER; +ERROR OP000: Invalid role specification `XXXXXX` +# following should fail with the same error as above +REVOKE PUBLIC FROM CURRENT_USER; +ERROR OP000: Invalid role specification `PUBLIC` +drop role XXXXXX; +ERROR HY000: Operation DROP ROLE failed for 'XXXXXX' +# following should fail with the same error as above +drop role PUBLIC; +ERROR HY000: Operation DROP ROLE failed for PUBLIC +SET ROLE XXXXXX; +ERROR OP000: Invalid role specification `XXXXXX` +# following should fail with the same error as above +SET ROLE PUBLIC; +ERROR OP000: Invalid role specification `PUBLIC` +SET DEFAULT ROLE XXXXXX; +ERROR OP000: Invalid role specification `XXXXXX` +# following should fail with the same error as above +SET DEFAULT ROLE PUBLIC; +ERROR OP000: Invalid role specification `PUBLIC` diff --git a/mysql-test/main/public_basic.test b/mysql-test/main/public_basic.test new file mode 100644 index 00000000000..3f5993dfe97 --- /dev/null +++ b/mysql-test/main/public_basic.test @@ -0,0 +1,93 @@ +SHOW GRANTS FOR PUBLIC; + +--echo # it is not PUBLIC but an user +--echo # (this should work as it allowed for roles for example) +create user PUBLIC; +create user PUBLIC@localhost; +GRANT SELECT on test.* to PUBLIC@localhost; +drop user PUBLIC@localhost; +drop user PUBLIC; + +--echo # preinstalled PUBLIC +GRANT SELECT on test.* to PUBLIC; +GRANT SELECT on mysql.db to PUBLIC; +--replace_regex /"version_id"\:[0-9]+/"version_id":VERSION/ +select * from mysql.global_priv where user="PUBLIC" ; + +SHOW GRANTS FOR PUBLIC; + +GRANT UPDATE on test.* to PUBLIC; +GRANT UPDATE on mysql.db to PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +REVOKE SELECT on test.* from PUBLIC; +REVOKE SELECT on mysql.db from PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +REVOKE UPDATE on test.* from PUBLIC; +REVOKE UPDATE on mysql.db from PUBLIC; + +--error ER_NONEXISTING_GRANT +REVOKE UPDATE on test.* from PUBLIC; +--error ER_NONEXISTING_TABLE_GRANT +REVOKE UPDATE on mysql.db from PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +--echo # automaticly added PUBLIC +delete from mysql.global_priv where user="PUBLIC"; +flush privileges; +select * from mysql.global_priv where user="PUBLIC" ; +GRANT SELECT on test.* to PUBLIC; +GRANT SELECT on mysql.db to PUBLIC; +--replace_regex /"version_id"\:[0-9]+/"version_id":VERSION/ +select * from mysql.global_priv where user="PUBLIC" ; + +SHOW GRANTS FOR PUBLIC; + +GRANT UPDATE on test.* to PUBLIC; +GRANT UPDATE on mysql.db to PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +REVOKE SELECT on test.* from PUBLIC; +REVOKE SELECT on mysql.db from PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +REVOKE UPDATE on test.* from PUBLIC; +REVOKE UPDATE on mysql.db from PUBLIC; + +SHOW GRANTS FOR PUBLIC; + +--error ER_INVALID_ROLE +GRANT XXXXXX TO CURRENT_USER; +--echo # following should fail with the same error as above +--error ER_INVALID_ROLE +GRANT PUBLIC TO CURRENT_USER; + +--error ER_INVALID_ROLE +REVOKE XXXXXX FROM CURRENT_USER; +--echo # following should fail with the same error as above +--error ER_INVALID_ROLE +REVOKE PUBLIC FROM CURRENT_USER; +--error ER_CANNOT_USER + +drop role XXXXXX; +--echo # following should fail with the same error as above +--error ER_CANNOT_USER +drop role PUBLIC; + +--error ER_INVALID_ROLE +SET ROLE XXXXXX; +--echo # following should fail with the same error as above +--error ER_INVALID_ROLE +SET ROLE PUBLIC; + +--error ER_INVALID_ROLE +SET DEFAULT ROLE XXXXXX; +--echo # following should fail with the same error as above +--error ER_INVALID_ROLE +SET DEFAULT ROLE PUBLIC; diff --git a/mysql-test/main/public_privileges.result b/mysql-test/main/public_privileges.result new file mode 100644 index 00000000000..1f273005225 --- /dev/null +++ b/mysql-test/main/public_privileges.result @@ -0,0 +1,243 @@ +# +# Test DB/TABLE/COLUMN privileges in queries +# +SHOW GRANTS FOR PUBLIC; +Grants for PUBLIC +create user testuser; +create database testdb1; +use testdb1; +create table t1 (a int, b int); +insert into t1 values (1,2); +create database testdb2; +use testdb2; +create table t2 (a int, b int); +insert into t2 values (1,2); +create table t3 (a int, b int); +insert into t3 values (1,2); +connect testuser,localhost,testuser,,; +connection testuser; +select * from testdb1.t1; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't1' +select * from testdb2.t2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' +select b from testdb2.t3; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't3' +select a from testdb2.t3; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't3' +connection default; +GRANT SELECT ON testdb1.* to PUBLIC; +GRANT SELECT ON testdb2.t2 to PUBLIC; +GRANT SELECT (b) ON testdb2.t3 to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +select * from testdb1.t1; +a b +1 2 +select * from testdb2.t2; +a b +1 2 +select b from testdb2.t3; +b +2 +select a from testdb2.t3; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'a' in table 't3' +connection default; +SHOW PROCESSLIST; +Id User Host db Command Time State Info Progress +# root # testdb2 Query # # SHOW PROCESSLIST 0.000 +# testuser # NULL Sleep # # NULL 0.000 +connection default; +use test; +disconnect testuser; +REVOKE SELECT ON testdb1.* from PUBLIC; +REVOKE SELECT ON testdb2.t2 from PUBLIC; +REVOKE SELECT (b) ON testdb2.t3 from PUBLIC; +drop user testuser; +drop database testdb1; +drop database testdb2; +# +# test global process list privilege and EXECUTE db level +# +create user testuser; +create database testdb; +use testdb; +create procedure p1 () select 1; +connect testuser,localhost,testuser,,; +connection testuser; +SHOW PROCESSLIST; +Id User Host db Command Time State Info Progress +# testuser # NULL Query # # SHOW PROCESSLIST 0.000 +call testdb.p1(); +ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.p1' +connection default; +GRANT PROCESS ON *.* to PUBLIC; +GRANT EXECUTE ON testdb.* to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +SHOW PROCESSLIST; +Id User Host db Command Time State Info Progress +# root # testdb Sleep # # NULL 0.000 +# testuser # NULL Query # # SHOW PROCESSLIST 0.000 +call testdb.p1(); +1 +1 +connection default; +SHOW PROCESSLIST; +Id User Host db Command Time State Info Progress +# root # testdb Query # # SHOW PROCESSLIST 0.000 +# testuser # NULL Sleep # # NULL 0.000 +connection default; +use test; +disconnect testuser; +REVOKE PROCESS ON *.* from PUBLIC; +REVOKE EXECUTE ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; +# +# test DB privilege to allow USE statement +# +create user testuser; +create database testdb; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +ERROR 42000: Access denied for user 'testuser'@'%' to database 'testdb' +connection default; +GRANT LOCK TABLES ON testdb.* to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +connection default; +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; +# +# test DB privilege to allow USE statement (as above) +# test current db privileges +# +create user testuser; +create database testdb; +use testdb; +create table t1 (a int); +insert into t1 values (1); +GRANT LOCK TABLES ON testdb.* to PUBLIC; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +update t1 set a=a+1; +ERROR 42000: UPDATE command denied to user 'testuser'@'localhost' for table 't1' +connection default; +GRANT UPDATE,SELECT ON testdb.* to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +update t1 set a=a+1; +connection default; +select * from testdb.t1; +a +2 +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +REVOKE UPDATE,SELECT ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; +# +# test DB privilege to allow USE statement (as above) +# test table/column privileges in current DB +# +create user testuser; +create database testdb; +use testdb; +create table t1 (a int); +insert into t1 values (1); +create table t2 (a int, b int); +insert into t2 values (1,2); +GRANT LOCK TABLES ON testdb.* to PUBLIC; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +delete from t1; +ERROR 42000: DELETE command denied to user 'testuser'@'localhost' for table 't1' +select b from t2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' +select a from t2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' +connection default; +GRANT DELETE ON testdb.t1 to PUBLIC; +GRANT SELECT (a) ON testdb.t2 to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +use testdb; +delete from t1; +select a from t2; +a +1 +select b from t2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't2' +connection default; +select * from testdb.t1; +a +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +REVOKE DELETE ON testdb.t1 from PUBLIC; +REVOKE SELECT (a) ON testdb.t2 from PUBLIC; +drop user testuser; +drop database testdb; +# +# test function privilege +# +create user testuser; +create database testdb; +use testdb; +create function f1() returns int return 2; +connect testuser,localhost,testuser,,; +connection testuser; +alter function testdb.f1 comment "A stupid function"; +ERROR 42000: alter routine command denied to user 'testuser'@'%' for routine 'testdb.f1' +select testdb.f1(); +ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.f1' +connection default; +GRANT ALTER ROUTINE ON testdb.* to PUBLIC; +disconnect testuser; +connect testuser,localhost,testuser,,; +connection testuser; +alter function testdb.f1 comment "A stupid function"; +select testdb.f1(); +ERROR 42000: execute command denied to user 'testuser'@'%' for routine 'testdb.f1' +connection default; +use test; +disconnect testuser; +REVOKE ALTER ROUTINE ON testdb.* from PUBLIC; +drop function testdb.f1; +drop user testuser; +drop database testdb; +# +# bug with automatically added PUBLIC role +# +# automaticly added PUBLIC +delete from mysql.global_priv where user="PUBLIC"; +flush privileges; +GRANT SELECT on test.* to PUBLIC; +REVOKE SELECT on test.* from PUBLIC; +create user testuser; +create database testdb1; +use testdb1; +create table t1 (a int, b int); +insert into t1 values (1,2); +connect testuser,localhost,testuser,,; +connection testuser; +select * from testdb1.t1; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't1' +connection default; +disconnect testuser; +drop user testuser; +drop database testdb1; diff --git a/mysql-test/main/public_privileges.test b/mysql-test/main/public_privileges.test new file mode 100644 index 00000000000..a542376f05c --- /dev/null +++ b/mysql-test/main/public_privileges.test @@ -0,0 +1,293 @@ +--echo # +--echo # Test DB/TABLE/COLUMN privileges in queries +--echo # + +SHOW GRANTS FOR PUBLIC; + +create user testuser; +create database testdb1; +use testdb1; +create table t1 (a int, b int); +insert into t1 values (1,2); +create database testdb2; +use testdb2; +create table t2 (a int, b int); +insert into t2 values (1,2); +create table t3 (a int, b int); +insert into t3 values (1,2); + +connect (testuser,localhost,testuser,,); +connection testuser; +--error ER_TABLEACCESS_DENIED_ERROR +select * from testdb1.t1; +--error ER_TABLEACCESS_DENIED_ERROR +select * from testdb2.t2; +--error ER_TABLEACCESS_DENIED_ERROR +select b from testdb2.t3; +--error ER_TABLEACCESS_DENIED_ERROR +select a from testdb2.t3; + +connection default; + +GRANT SELECT ON testdb1.* to PUBLIC; +GRANT SELECT ON testdb2.t2 to PUBLIC; +GRANT SELECT (b) ON testdb2.t3 to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; +select * from testdb1.t1; +select * from testdb2.t2; +select b from testdb2.t3; +--error ER_COLUMNACCESS_DENIED_ERROR +select a from testdb2.t3; + +connection default; + +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; + +connection default; + +use test; +disconnect testuser; +REVOKE SELECT ON testdb1.* from PUBLIC; +REVOKE SELECT ON testdb2.t2 from PUBLIC; +REVOKE SELECT (b) ON testdb2.t3 from PUBLIC; +drop user testuser; +drop database testdb1; +drop database testdb2; + +--echo # +--echo # test global process list privilege and EXECUTE db level +--echo # + +create user testuser; +create database testdb; +use testdb; +create procedure p1 () select 1; + +connect (testuser,localhost,testuser,,); +connection testuser; + +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; +--error ER_PROCACCESS_DENIED_ERROR +call testdb.p1(); + +connection default; + +GRANT PROCESS ON *.* to PUBLIC; +GRANT EXECUTE ON testdb.* to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; + +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; +call testdb.p1(); + +connection default; + +--replace_column 1 # 3 # 6 # 7 # +SHOW PROCESSLIST; + +connection default; + +use test; +disconnect testuser; +REVOKE PROCESS ON *.* from PUBLIC; +REVOKE EXECUTE ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; + +--echo # +--echo # test DB privilege to allow USE statement +--echo # + +create user testuser; +create database testdb; + +connect (testuser,localhost,testuser,,); +connection testuser; + +--error ER_DBACCESS_DENIED_ERROR +use testdb; + +connection default; + +GRANT LOCK TABLES ON testdb.* to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; + +use testdb; + +connection default; + +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; + + +--echo # +--echo # test DB privilege to allow USE statement (as above) +--echo # test current db privileges +--echo # + +create user testuser; +create database testdb; +use testdb; +create table t1 (a int); +insert into t1 values (1); +GRANT LOCK TABLES ON testdb.* to PUBLIC; + +connect (testuser,localhost,testuser,,); +connection testuser; + +use testdb; +--error ER_TABLEACCESS_DENIED_ERROR +update t1 set a=a+1; + +connection default; + +GRANT UPDATE,SELECT ON testdb.* to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; + +use testdb; +update t1 set a=a+1; + +connection default; +select * from testdb.t1; + +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +REVOKE UPDATE,SELECT ON testdb.* from PUBLIC; +drop user testuser; +drop database testdb; + + +--echo # +--echo # test DB privilege to allow USE statement (as above) +--echo # test table/column privileges in current DB +--echo # + +create user testuser; +create database testdb; +use testdb; +create table t1 (a int); +insert into t1 values (1); +create table t2 (a int, b int); +insert into t2 values (1,2); +GRANT LOCK TABLES ON testdb.* to PUBLIC; + +connect (testuser,localhost,testuser,,); +connection testuser; + +use testdb; +--error ER_TABLEACCESS_DENIED_ERROR +delete from t1; +--error ER_TABLEACCESS_DENIED_ERROR +select b from t2; +--error ER_TABLEACCESS_DENIED_ERROR +select a from t2; + +connection default; + +GRANT DELETE ON testdb.t1 to PUBLIC; +GRANT SELECT (a) ON testdb.t2 to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; + +use testdb; +delete from t1; +select a from t2; +--error ER_COLUMNACCESS_DENIED_ERROR +select b from t2; + +connection default; +select * from testdb.t1; + +use test; +disconnect testuser; +REVOKE LOCK TABLES ON testdb.* from PUBLIC; +REVOKE DELETE ON testdb.t1 from PUBLIC; +REVOKE SELECT (a) ON testdb.t2 from PUBLIC; +drop user testuser; +drop database testdb; + +--echo # +--echo # test function privilege +--echo # + +create user testuser; +create database testdb; +use testdb; +create function f1() returns int return 2; + +connect (testuser,localhost,testuser,,); +connection testuser; + +--error ER_PROCACCESS_DENIED_ERROR +alter function testdb.f1 comment "A stupid function"; +--error ER_PROCACCESS_DENIED_ERROR +select testdb.f1(); + +connection default; + +GRANT ALTER ROUTINE ON testdb.* to PUBLIC; + +disconnect testuser; +connect (testuser,localhost,testuser,,); +connection testuser; + +alter function testdb.f1 comment "A stupid function"; +--error ER_PROCACCESS_DENIED_ERROR +select testdb.f1(); + +connection default; + +use test; +disconnect testuser; +REVOKE ALTER ROUTINE ON testdb.* from PUBLIC; +drop function testdb.f1; +drop user testuser; +drop database testdb; + +--echo # +--echo # bug with automatically added PUBLIC role +--echo # + +--echo # automaticly added PUBLIC +delete from mysql.global_priv where user="PUBLIC"; +flush privileges; +GRANT SELECT on test.* to PUBLIC; + +REVOKE SELECT on test.* from PUBLIC; + +create user testuser; +create database testdb1; +use testdb1; +create table t1 (a int, b int); +insert into t1 values (1,2); + +connect (testuser,localhost,testuser,,); +connection testuser; +--error ER_TABLEACCESS_DENIED_ERROR +select * from testdb1.t1; + +connection default; + +disconnect testuser; +drop user testuser; +drop database testdb1; diff --git a/mysql-test/main/sp_notembedded.result b/mysql-test/main/sp_notembedded.result index e03361598a6..239f8acb7f3 100644 --- a/mysql-test/main/sp_notembedded.result +++ b/mysql-test/main/sp_notembedded.result @@ -345,7 +345,6 @@ show grants; Grants for foo1@localhost GRANT USAGE ON *.* TO `foo1`@`localhost` IDENTIFIED BY PASSWORD '-F3A2A51A9B0F2BE2468926B4132313728C250DBF' GRANT CREATE ROUTINE ON `test`.* TO `foo1`@`localhost` -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`spfoo` TO `foo1`@`localhost` connection default; disconnect foo; drop procedure spfoo; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 22f5bfafb8e..afad7a1f396 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -592,7 +592,7 @@ explain SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL -1 SIMPLE global_priv ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 SIMPLE global_priv ALL PRIMARY NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 5b62f228b1f..760ef70af46 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -624,7 +624,7 @@ explain SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL -1 SIMPLE global_priv ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 SIMPLE global_priv ALL PRIMARY NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # diff --git a/mysql-test/main/system_mysql_db.result b/mysql-test/main/system_mysql_db.result index dcc71da3677..15b5c7adfe1 100644 --- a/mysql-test/main/system_mysql_db.result +++ b/mysql-test/main/system_mysql_db.result @@ -62,7 +62,7 @@ db CREATE TABLE `db` ( ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Database privileges' show create table user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` where `global_priv`.`Host` <> '' or `global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci show create table func; Table Create Table func CREATE TABLE `func` ( diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result index d30c2ccdffc..3339c7f8959 100644 --- a/mysql-test/main/system_mysql_db_fix40123.result +++ b/mysql-test/main/system_mysql_db_fix40123.result @@ -100,7 +100,7 @@ db CREATE TABLE `db` ( ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Database privileges' show create table user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` where `test`.`global_priv`.`Host` <> '' or `test`.`global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci show create table func; Table Create Table func CREATE TABLE `func` ( diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result index e77d79f1bd7..b0b08f73ae1 100644 --- a/mysql-test/main/system_mysql_db_fix50030.result +++ b/mysql-test/main/system_mysql_db_fix50030.result @@ -104,7 +104,7 @@ db CREATE TABLE `db` ( ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Database privileges' show create table user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` where `test`.`global_priv`.`Host` <> '' or `test`.`global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci show create table func; Table Create Table func CREATE TABLE `func` ( diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result index 0d74a7f552b..71c658b69c9 100644 --- a/mysql-test/main/system_mysql_db_fix50117.result +++ b/mysql-test/main/system_mysql_db_fix50117.result @@ -84,7 +84,7 @@ db CREATE TABLE `db` ( ) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Database privileges' show create table user; View Create View character_set_client collation_connection -user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` latin1 latin1_swedish_ci +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `user` AS select `test`.`global_priv`.`Host` AS `Host`,`test`.`global_priv`.`User` AS `User`,if(json_value(`test`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`test`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`test`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`test`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv` where `test`.`global_priv`.`Host` <> '' or `test`.`global_priv`.`User` <> 'PUBLIC' latin1 latin1_swedish_ci show create table func; Table Create Table func CREATE TABLE `func` ( diff --git a/mysql-test/main/upgrade_mdev_24363.result b/mysql-test/main/upgrade_mdev_24363.result index 50fc2d7bcc3..fb61b70d3b2 100644 --- a/mysql-test/main/upgrade_mdev_24363.result +++ b/mysql-test/main/upgrade_mdev_24363.result @@ -29,10 +29,10 @@ drop user gigi@localhost; # use mysql; set @def = (select view_definition from information_schema.views where table_name='user' and table_schema='mysql'); -set @trimmed_def = (select trim(trailing 'from `mysql`.`global_priv`' from @def)); +set @trimmed_def = (select REGEXP_REPLACE(@def, ' from .*', '')); set @newdef = (select concat(@trimmed_def, ", 'N' AS password_expired from mysql.global_priv")); set @pos = (select instr(@newdef, 'password_expired')); -create or replace view user as select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `abcsword_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` , 'N' AS password_expired from mysql.global_priv;; +create or replace view user as select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `abcsword_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time`, 'N' AS password_expired from mysql.global_priv;; create user gigi@localhost; show create user gigi@localhost; CREATE USER for gigi@localhost diff --git a/mysql-test/main/upgrade_mdev_24363.test b/mysql-test/main/upgrade_mdev_24363.test index cdb49037a40..46dfcaa3ec7 100644 --- a/mysql-test/main/upgrade_mdev_24363.test +++ b/mysql-test/main/upgrade_mdev_24363.test @@ -33,7 +33,7 @@ use mysql; # with "'N' as password_expired" and avoid listing in one more test # all the fields of the user view set @def = (select view_definition from information_schema.views where table_name='user' and table_schema='mysql'); -set @trimmed_def = (select trim(trailing 'from `mysql`.`global_priv`' from @def)); +set @trimmed_def = (select REGEXP_REPLACE(@def, ' from .*', '')); set @newdef = (select concat(@trimmed_def, ", 'N' AS password_expired from mysql.global_priv")); set @pos = (select instr(@newdef, 'password_expired')); let $viewdef = `select insert(@newdef, @pos, 3, 'abc')`; diff --git a/mysql-test/suite/perfschema/r/privilege_table_io.result b/mysql-test/suite/perfschema/r/privilege_table_io.result index 6dd885281b3..1b077395935 100644 --- a/mysql-test/suite/perfschema/r/privilege_table_io.result +++ b/mysql-test/suite/perfschema/r/privilege_table_io.result @@ -118,6 +118,7 @@ wait/io/table/sql/handler TABLE mysql global_priv fetch 1 wait/io/table/sql/handler TABLE mysql global_priv fetch 1 wait/io/table/sql/handler TABLE mysql global_priv fetch 1 wait/io/table/sql/handler TABLE mysql global_priv fetch 1 +wait/io/table/sql/handler TABLE mysql global_priv fetch 1 wait/io/table/sql/handler TABLE mysql db fetch 1 wait/io/table/sql/handler TABLE mysql proxies_priv fetch 1 wait/io/table/sql/handler TABLE mysql proxies_priv fetch 1 diff --git a/mysql-test/suite/roles/acl_statistics.result b/mysql-test/suite/roles/acl_statistics.result index c60e0297af3..58a92404caa 100644 --- a/mysql-test/suite/roles/acl_statistics.result +++ b/mysql-test/suite/roles/acl_statistics.result @@ -8,7 +8,7 @@ Acl_package_spec_grants 0 Acl_package_body_grants 0 Acl_proxy_users 2 Acl_role_grants 0 -Acl_roles 0 +Acl_roles 1 Acl_table_grants 1 Acl_users 5 SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; @@ -73,7 +73,7 @@ Acl_package_spec_grants 0 Acl_package_body_grants 0 Acl_proxy_users 3 Acl_role_grants 4 -Acl_roles 2 +Acl_roles 3 Acl_table_grants 3 Acl_users 6 SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; diff --git a/mysql-test/suite/roles/none_public.result b/mysql-test/suite/roles/none_public.result index c253ae1478b..70ca3fc8af5 100644 --- a/mysql-test/suite/roles/none_public.result +++ b/mysql-test/suite/roles/none_public.result @@ -15,8 +15,6 @@ grant public to role1; ERROR OP000: Invalid role specification `public` grant role1 to public; ERROR OP000: Invalid role specification `public` -grant select on *.* to public; -ERROR OP000: Invalid role specification `public` grant role1 to current_role; ERROR OP000: Invalid role specification `NONE` revoke none from role1; @@ -33,16 +31,11 @@ revoke select on *.* from public; ERROR OP000: Invalid role specification `public` show grants for none; ERROR OP000: Invalid role specification `none` -show grants for public; -ERROR OP000: Invalid role specification `public` create definer=none view test.v1 as select 1; ERROR OP000: Invalid role specification `none` -create definer=public view test.v1 as select 1; -ERROR OP000: Invalid role specification `public` drop role role1; -insert mysql.global_priv values ('', 'none', '{"is_role":true}'), ('', 'public', '{"is_role":true}'); +insert mysql.global_priv values ('', 'none', '{"is_role":true}'); flush privileges; Warnings: Error 1959 Invalid role specification `none` -Error 1959 Invalid role specification `public` -delete from mysql.global_priv where host=''; +delete from mysql.global_priv where host='' and user='none'; diff --git a/mysql-test/suite/roles/none_public.test b/mysql-test/suite/roles/none_public.test index a0ec2315cfc..d0ba62d226b 100644 --- a/mysql-test/suite/roles/none_public.test +++ b/mysql-test/suite/roles/none_public.test @@ -19,8 +19,9 @@ grant select on *.* to none; grant public to role1; --error ER_INVALID_ROLE grant role1 to public; ---error ER_INVALID_ROLE -grant select on *.* to public; +# PUBLIC is legal role +#--error ER_INVALID_ROLE +#grant select on *.* to public; --error ER_INVALID_ROLE grant role1 to current_role; @@ -40,16 +41,18 @@ revoke select on *.* from public; --error ER_INVALID_ROLE show grants for none; ---error ER_INVALID_ROLE -show grants for public; +# PUBLIC is legal role +#--error ER_INVALID_ROLE +#show grants for public; --error ER_INVALID_ROLE create definer=none view test.v1 as select 1; ---error ER_INVALID_ROLE -create definer=public view test.v1 as select 1; +# PUBLIC is legal role +#--error ER_INVALID_ROLE +#create definer=public view test.v1 as select 1; drop role role1; -insert mysql.global_priv values ('', 'none', '{"is_role":true}'), ('', 'public', '{"is_role":true}'); +insert mysql.global_priv values ('', 'none', '{"is_role":true}'); flush privileges; -delete from mysql.global_priv where host=''; +delete from mysql.global_priv where host='' and user='none'; diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index fd2f1c95dda..a1721913b2c 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -47,6 +47,10 @@ INSERT IGNORE INTO global_priv SELECT * FROM tmp_user_sys WHERE 0 <> @need_sys_u DROP TABLE tmp_user_sys; +-- This special "role" needed for GRAND ... TO PUBLIC +INSERT IGNORE INTO global_priv (Host,User,Priv) VALUES ('', 'PUBLIC', concat('{"access":0,"version_id":',regexp_replace(regexp_replace(@@version, '\\b\\d\\b', '0\\0'), '\\D', ''),',"is_role":true}')); + + CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT Host, User, @@ -95,7 +99,9 @@ CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role, IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role, CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time - FROM global_priv; + FROM global_priv +-- Do not show special role for GRANT TO PUBLIC + WHERE not (Host = "" and User = "PUBLIC"); -- Remember for later if user table already existed set @had_user_table= @@warning_count != 0; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 7d97b1c1b0f..f08a16f7646 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -102,7 +102,9 @@ LEX_CSTRING host_not_specified= { STRING_WITH_LEN("%") }; */ LEX_CSTRING current_user= { STRING_WITH_LEN("*current_user") }; LEX_CSTRING current_role= { STRING_WITH_LEN("*current_role") }; -LEX_CSTRING current_user_and_current_role= { STRING_WITH_LEN("*current_user_and_current_role") }; +LEX_CSTRING current_user_and_current_role= + { STRING_WITH_LEN("*current_user_and_current_role") }; +LEX_CSTRING public_name= {STRING_WITH_LEN("PUBLIC") }; static plugin_ref old_password_plugin; @@ -316,6 +318,13 @@ static bool show_table_and_column_privileges(THD *, const char *, const char *, static int show_routine_grants(THD *, const char *, const char *, const Sp_handler *sph, char *, int); +static ACL_ROLE *acl_public= NULL; + +inline privilege_t public_access() +{ + return (acl_public ? acl_public->access : NO_ACL); +} + class Grant_tables; class User_table; class Proxies_priv_table; @@ -684,7 +693,7 @@ static void rebuild_check_host(void); static void rebuild_role_grants(void); static ACL_USER *find_user_exact(const char *host, const char *user); static ACL_USER *find_user_wild(const char *host, const char *user, const char *ip= 0); -static ACL_ROLE *find_acl_role(const char *user); +static ACL_ROLE *find_acl_role(const char *user, bool allow_public); static ROLE_GRANT_PAIR *find_role_grant_pair(const LEX_CSTRING *u, const LEX_CSTRING *h, const LEX_CSTRING *r); static ACL_USER_BASE *find_acl_user_base(const char *user, const char *host); static bool update_user_table_password(THD *, const User_table&, const ACL_USER&); @@ -2135,14 +2144,32 @@ ACL_ROLE::ACL_ROLE(const char * rolename, privilege_t privileges, flags= IS_ROLE; } +enum role_name_check_result +{ + ROLE_NAME_OK= 0, + ROLE_NAME_PUBLIC, + ROLE_NAME_INVALID +}; -static bool is_invalid_role_name(const char *str) +static role_name_check_result check_role_name(const char *str, + bool public_is_ok) { - if (*str && strcasecmp(str, "PUBLIC") && strcasecmp(str, "NONE")) - return false; + if (*str) + { + if (strcasecmp(str, public_name.str) == 0) + { + if (public_is_ok) + return ROLE_NAME_PUBLIC; + else + goto error; + } + if (strcasecmp(str, "NONE") != 0) + return ROLE_NAME_OK; + } +error: my_error(ER_INVALID_ROLE, MYF(0), str); - return true; + return ROLE_NAME_INVALID; } @@ -2567,7 +2594,8 @@ static bool acl_load(THD *thd, const Grant_tables& tables) if (is_role) { - if (is_invalid_role_name(username)) + role_name_check_result result= check_role_name(username, true); + if (result == ROLE_NAME_INVALID) { thd->clear_error(); // the warning is still issued continue; @@ -2577,6 +2605,9 @@ static bool acl_load(THD *thd, const Grant_tables& tables) entry->role_grants = user.role_grants; my_init_dynamic_array(key_memory_acl_mem, &entry->parent_grantee, sizeof(ACL_USER_BASE *), 0, 8, MYF(0)); + if (result == ROLE_NAME_PUBLIC) + acl_public= entry; + my_hash_insert(&acl_roles, (uchar *)entry); continue; @@ -2632,7 +2663,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) char *db_name; db.user=safe_str(get_field(&acl_memroot, db_table.user())); const char *hostname= get_field(&acl_memroot, db_table.host()); - if (!hostname && find_acl_role(db.user)) + if (!hostname && find_acl_role(db.user, true)) hostname= ""; update_hostname(&db.host, hostname); db.db= db_name= get_field(&acl_memroot, db_table.db()); @@ -2762,6 +2793,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) void acl_free(bool end) { my_hash_free(&acl_roles); + acl_public= NULL; free_root(&acl_memroot,MYF(0)); delete_dynamic(&acl_hosts); delete_dynamic_with_callback(&acl_users, (FREE_FUNC) free_acl_user); @@ -2806,6 +2838,7 @@ bool acl_reload(THD *thd) DYNAMIC_ARRAY old_acl_hosts, old_acl_users, old_acl_proxy_users; Dynamic_array<ACL_DB> old_acl_dbs(PSI_INSTRUMENT_MEM, 0, 0); HASH old_acl_roles, old_acl_roles_mappings; + ACL_ROLE *old_acl_public; MEM_ROOT old_mem; int result; DBUG_ENTER("acl_reload"); @@ -2836,6 +2869,7 @@ bool acl_reload(THD *thd) old_acl_hosts= acl_hosts; old_acl_users= acl_users; old_acl_roles= acl_roles; + old_acl_public= acl_public; old_acl_roles_mappings= acl_roles_mappings; old_acl_proxy_users= acl_proxy_users; old_acl_dbs= acl_dbs; @@ -2860,6 +2894,7 @@ bool acl_reload(THD *thd) acl_hosts= old_acl_hosts; acl_users= old_acl_users; acl_roles= old_acl_roles; + acl_public= old_acl_public; acl_roles_mappings= old_acl_roles_mappings; acl_proxy_users= old_acl_proxy_users; acl_dbs= old_acl_dbs; @@ -3144,7 +3179,7 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, } else // Role, not User { - ACL_ROLE *acl_role= find_acl_role(user); + ACL_ROLE *acl_role= find_acl_role(user, false); if (acl_role) { res= 0; @@ -3157,6 +3192,26 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, } } + /* + PUBLIC magic: + + Note: for usual user privileges of 3 component merged together: + 1) user privileges + 2) set role privileges + 3) public privileges + But for this routine (used in Security_context::change_security_context) + only 2 component merged: + 1) user OR role privileges we are switching to + 2) public privileges + */ + if (acl_public) + { + if (ACL_DB *acl_db= acl_db_find(db, public_name.str, "", "", FALSE)) + sctx->db_access|= acl_db->access; + + sctx->master_access|= acl_public->access; + } + mysql_mutex_unlock(&acl_cache->lock); DBUG_RETURN(res); } @@ -3211,7 +3266,7 @@ static int check_user_can_set_role(THD *thd, const char *user, goto end; } - role= find_acl_role(rolename); + role= find_acl_role(rolename, false); /* According to SQL standard, the same error message must be presented */ if (role == NULL) @@ -3316,20 +3371,22 @@ int acl_setrole(THD *thd, const char *rolename, privilege_t access) /* merge the privileges */ Security_context *sctx= thd->security_ctx; sctx->master_access= access; - if (thd->db.str) - sctx->db_access= acl_get(sctx->host, sctx->ip, sctx->user, thd->db.str, FALSE); - if (!strcasecmp(rolename, "NONE")) { thd->security_ctx->priv_role[0]= 0; } else { - if (thd->db.str) - sctx->db_access|= acl_get("", "", rolename, thd->db.str, FALSE); /* mark the current role */ strmake_buf(thd->security_ctx->priv_role, rolename); } + if (thd->db.str) + sctx->db_access= acl_get_all3(sctx, thd->db.str, FALSE); + + // PUBLIC magic + if (acl_public) + sctx->master_access|= acl_public->access; + return 0; } @@ -3343,9 +3400,13 @@ static uchar* check_get_key(ACL_USER *buff, size_t *length, static void acl_update_role(const char *rolename, const privilege_t privileges) { - ACL_ROLE *role= find_acl_role(rolename); + ACL_ROLE *role= find_acl_role(rolename, true); if (role) + { role->initial_role_access= role->access= privileges; + if (strcasecmp(rolename, public_name.str) == 0) + acl_public= role; + } } @@ -3476,6 +3537,8 @@ static void acl_insert_role(const char *rolename, privilege_t privileges) sizeof(ACL_ROLE *), 0, 8, MYF(0)); my_hash_insert(&acl_roles, (uchar *)entry); + if (strcasecmp(rolename, public_name.str) == 0) + acl_public= entry; } @@ -3594,7 +3657,9 @@ privilege_t acl_get(const char *host, const char *ip, if (acl_db->host.hostname) goto exit; // Fully specified. Take it /* the host table is not used for roles */ - if ((!host || !host[0]) && !acl_db->host.hostname && find_acl_role(user)) + if ((!host || !host[0]) && + !acl_db->host.hostname && + find_acl_role(user, false)) goto exit; } @@ -3635,6 +3700,23 @@ exit: } /* + Check if there is access for the host/user, role, public on the database +*/ + +privilege_t acl_get_all3(Security_context *sctx, const char *db, + bool db_is_patern) +{ + privilege_t access= acl_get(sctx->host, sctx->ip, + sctx->priv_user, db, db_is_patern); + if (sctx->priv_role[0]) + access|= acl_get("", "", sctx->priv_role, db, db_is_patern); + if (acl_public) + access|= acl_get("", "", public_name.str, db, db_is_patern); + return access; +} + + +/* Check if there are any possible matching entries for this host NOTES @@ -3755,7 +3837,7 @@ static bool add_role_user_mapping(const char *uname, const char *hname, const char *rname) { ACL_USER_BASE *grantee= find_acl_user_base(uname, hname); - ACL_ROLE *role= find_acl_role(rname); + ACL_ROLE *role= find_acl_role(rname, true); if (grantee == NULL || role == NULL) return 1; @@ -4243,7 +4325,7 @@ bool is_acl_user(const char *host, const char *user) if (*host) // User res= find_user_exact(host, user) != NULL; else // Role - res= find_acl_role(user) != NULL; + res= find_acl_role(user, false) != NULL; mysql_mutex_unlock(&acl_cache->lock); return res; @@ -4293,7 +4375,7 @@ static ACL_USER * find_user_wild(const char *host, const char *user, const char /* Find a role with the specified name */ -static ACL_ROLE *find_acl_role(const char *role) +static ACL_ROLE *find_acl_role(const char *role, bool allow_public) { size_t length= strlen(role); DBUG_ENTER("find_acl_role"); @@ -4302,7 +4384,9 @@ static ACL_ROLE *find_acl_role(const char *role) mysql_mutex_assert_owner(&acl_cache->lock); - if (!length) + if (!length || (!allow_public && + length == public_name.length && + strcasecmp(role, public_name.str) == 0)) DBUG_RETURN(NULL); ACL_ROLE *r= (ACL_ROLE *)my_hash_search(&acl_roles, (uchar *)role, @@ -4316,7 +4400,7 @@ static ACL_USER_BASE *find_acl_user_base(const char *user, const char *host) if (*host) return find_user_exact(host, user); - return find_acl_role(user); + return find_acl_role(user, false); } @@ -4532,10 +4616,7 @@ static bool test_if_create_new_users(THD *thd) NULL, TL_WRITE); create_new_users= 1; - db_access=acl_get(sctx->host, sctx->ip, - sctx->priv_user, tl.db.str, 0); - if (sctx->priv_role[0]) - db_access|= acl_get("", "", sctx->priv_role, tl.db.str, 0); + db_access= acl_get_all3(sctx, tl.db.str, FALSE); if (!(db_access & INSERT_ACL)) { if (check_grant(thd, INSERT_ACL, &tl, FALSE, UINT_MAX, TRUE)) @@ -4827,7 +4908,7 @@ static int replace_db_table(TABLE *table, const char *db, if (!find_user_wild(combo.host.str,combo.user.str)) { /* The user could be a role, check if the user is registered as a role */ - if (!combo.host.length && !find_acl_role(combo.user.str)) + if (!combo.host.length && !find_acl_role(combo.user.str, true)) { my_message(ER_PASSWORD_NO_MATCH, ER_THD(table->in_use, ER_PASSWORD_NO_MATCH), MYF(0)); @@ -5339,7 +5420,7 @@ GRANT_NAME::GRANT_NAME(TABLE *form, bool is_routine) const char *hostname= get_field(&grant_memroot, form->field[0]); mysql_mutex_lock(&acl_cache->lock); - if (!hostname && find_acl_role(user)) + if (!hostname && find_acl_role(user, true)) hostname= ""; mysql_mutex_unlock(&acl_cache->lock); update_hostname(&host, hostname); @@ -5806,7 +5887,7 @@ static int replace_table_table(THD *thd, GRANT_TABLE *grant_table, */ if (!find_user_wild(combo.host.str,combo.user.str)) { - if (!combo.host.length && !find_acl_role(combo.user.str)) + if (!combo.host.length && !find_acl_role(combo.user.str, true)) { my_message(ER_PASSWORD_NO_MATCH, ER_THD(thd, ER_PASSWORD_NO_MATCH), MYF(0)); /* purecov: deadcode */ @@ -7113,8 +7194,9 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, error= copy_and_check_auth(Str, tmp_Str, thd) || replace_user_table(thd, tables.user_table(), Str, NO_ACL, revoke_grant, create_new_users, - MY_TEST(thd->variables.sql_mode & - MODE_NO_AUTO_CREATE_USER)); + MY_TEST(tmp_Str->is_public || + (thd->variables.sql_mode & + MODE_NO_AUTO_CREATE_USER))); if (unlikely(error)) { result= TRUE; // Remember error @@ -7205,7 +7287,7 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, } } if (Str->is_role()) - propagate_role_grants(find_acl_role(Str->user.str), + propagate_role_grants(find_acl_role(Str->user.str, true), PRIVS_TO_MERGE::TABLE_COLUMN, db_name, table_name); } @@ -7331,7 +7413,7 @@ bool mysql_routine_grant(THD *thd, TABLE_LIST *table_list, continue; } if (Str->is_role()) - propagate_role_grants(find_acl_role(Str->user.str), + propagate_role_grants(find_acl_role(Str->user.str, true), sp_privs_to_merge(sph->type()), db_name, table_name); } @@ -7469,7 +7551,7 @@ bool mysql_grant_role(THD *thd, List <LEX_USER> &list, bool revoke) mysql_rwlock_wrlock(&LOCK_grant); mysql_mutex_lock(&acl_cache->lock); - if (!(role= find_acl_role(rolename.str))) + if (!(role= find_acl_role(rolename.str, false))) { mysql_mutex_unlock(&acl_cache->lock); mysql_rwlock_unlock(&LOCK_grant); @@ -7500,7 +7582,8 @@ bool mysql_grant_role(THD *thd, List <LEX_USER> &list, bool revoke) result= 1; continue; } - if (!(role_as_user= find_acl_role(thd->security_ctx->priv_role))) + if (!(role_as_user= find_acl_role(thd->security_ctx->priv_role, + false))) { LEX_CSTRING ls= { thd->security_ctx->priv_role, strlen(thd->security_ctx->priv_role) }; @@ -7533,11 +7616,11 @@ bool mysql_grant_role(THD *thd, List <LEX_USER> &list, bool revoke) if (user->host.str) hostname= user->host; else - if ((role_as_user= find_acl_role(user->user.str))) + if ((role_as_user= find_acl_role(user->user.str, false))) hostname= empty_clex_str; else { - if (is_invalid_role_name(username.str)) + if (check_role_name(username.str, false) == ROLE_NAME_INVALID) { append_user(thd, &wrong_users, &username, &empty_clex_str); result= 1; @@ -7737,8 +7820,9 @@ bool mysql_grant(THD *thd, const char *db, List <LEX_USER> &list, replace_user_table(thd, tables.user_table(), Str, (!db ? rights : NO_ACL), revoke_grant, create_new_users, - MY_TEST(thd->variables.sql_mode & - MODE_NO_AUTO_CREATE_USER))) + MY_TEST(!Str->is_public && + (thd->variables.sql_mode & + MODE_NO_AUTO_CREATE_USER)))) result= true; else if (db) { @@ -7762,7 +7846,7 @@ bool mysql_grant(THD *thd, const char *db, List <LEX_USER> &list, result= true; } if (Str->is_role()) - propagate_role_grants(find_acl_role(Str->user.str), + propagate_role_grants(find_acl_role(Str->user.str, true), db ? PRIVS_TO_MERGE::DB : PRIVS_TO_MERGE::GLOBAL, db); } @@ -8129,8 +8213,6 @@ bool check_grant(THD *thd, privilege_t want_access, TABLE_LIST *tables, uint i; privilege_t original_want_access(want_access); bool locked= 0; - GRANT_TABLE *grant_table; - GRANT_TABLE *grant_table_role= NULL; DBUG_ENTER("check_grant"); DBUG_ASSERT(number > 0); @@ -8252,18 +8334,11 @@ bool check_grant(THD *thd, privilege_t want_access, TABLE_LIST *tables, mysql_rwlock_rdlock(&LOCK_grant); } - grant_table= table_hash_search(sctx->host, sctx->ip, - t_ref->get_db_name(), - sctx->priv_user, - t_ref->get_table_name(), - FALSE); - if (sctx->priv_role[0]) - grant_table_role= table_hash_search("", NULL, t_ref->get_db_name(), - sctx->priv_role, - t_ref->get_table_name(), - TRUE); + t_ref->grant.read(sctx, t_ref->get_db_name(), t_ref->get_table_name()); - if (!grant_table && !grant_table_role) + if (!t_ref->grant.grant_table_user && + !t_ref->grant.grant_table_role && + !t_ref->grant.grant_public) { want_access&= ~t_ref->grant.privilege; goto err; // No grants @@ -8276,19 +8351,14 @@ bool check_grant(THD *thd, privilege_t want_access, TABLE_LIST *tables, if (any_combination_will_do) continue; - t_ref->grant.grant_table_user= grant_table; // Remember for column test - t_ref->grant.grant_table_role= grant_table_role; - t_ref->grant.version= grant_version; - t_ref->grant.privilege|= grant_table ? grant_table->privs : NO_ACL; - t_ref->grant.privilege|= grant_table_role ? grant_table_role->privs : NO_ACL; + t_ref->grant.privilege|= t_ref->grant.aggregate_privs(); t_ref->grant.want_privilege= ((want_access & COL_ACLS) & ~t_ref->grant.privilege); if (!(~t_ref->grant.privilege & want_access)) continue; - if ((want_access&= ~((grant_table ? grant_table->cols : NO_ACL) | - (grant_table_role ? grant_table_role->cols : NO_ACL) | - t_ref->grant.privilege))) + if ((want_access&= ~(t_ref->grant.aggregate_cols() | + t_ref->grant.privilege))) { goto err; // impossible } @@ -8331,6 +8401,49 @@ static void check_grant_column_int(GRANT_TABLE *grant_table, const char *name, } } +inline privilege_t GRANT_INFO::aggregate_privs() +{ + return (grant_table_user ? grant_table_user->privs : NO_ACL) | + (grant_table_role ? grant_table_role->privs : NO_ACL) | + (grant_public ? grant_public->privs : NO_ACL); +} + +inline privilege_t GRANT_INFO::aggregate_cols() +{ + return (grant_table_user ? grant_table_user->cols : NO_ACL) | + (grant_table_role ? grant_table_role->cols : NO_ACL) | + (grant_public ? grant_public->cols : NO_ACL); +} + +void GRANT_INFO::refresh(const Security_context *sctx, + const char *db, const char *table) +{ + if (version != grant_version) + read(sctx, db, table); +} + +void GRANT_INFO::read(const Security_context *sctx, + const char *db, const char *table) +{ +#ifdef EMBEDDED_LIBRARY + grant_table_user= grant_table_role= grant_public= NULL; +#else + grant_table_user= + table_hash_search(sctx->host, sctx->ip, db, + sctx->priv_user, + table, FALSE); /* purecov: inspected */ + grant_table_role= + sctx->priv_role[0] ? table_hash_search("", NULL, db, + sctx->priv_role, + table, TRUE) : NULL; + grant_public= + acl_public ? table_hash_search("", NULL, db, + public_name.str, + table, TRUE) : NULL; +#endif + version= grant_version; /* purecov: inspected */ +} + /* Check column rights in given security context @@ -8364,24 +8477,14 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant, mysql_rwlock_rdlock(&LOCK_grant); /* reload table if someone has modified any grants */ - - if (grant->version != grant_version) - { - grant->grant_table_user= - table_hash_search(sctx->host, sctx->ip, db_name, - sctx->priv_user, - table_name, 0); /* purecov: inspected */ - grant->grant_table_role= - sctx->priv_role[0] ? table_hash_search("", NULL, db_name, - sctx->priv_role, - table_name, TRUE) : NULL; - grant->version= grant_version; /* purecov: inspected */ - } + grant->refresh(sctx, db_name, table_name); check_grant_column_int(grant->grant_table_user, name, (uint)length, &want_access); check_grant_column_int(grant->grant_table_role, name, (uint)length, &want_access); + check_grant_column_int(grant->grant_public, name, (uint)length, + &want_access); mysql_rwlock_unlock(&LOCK_grant); if (!want_access) @@ -8497,6 +8600,7 @@ bool check_grant_all_columns(THD *thd, privilege_t want_access_arg, GRANT_INFO *grant; GRANT_TABLE *UNINIT_VAR(grant_table); GRANT_TABLE *UNINIT_VAR(grant_table_role); + GRANT_TABLE *UNINIT_VAR(grant_public); /* Flag that gets set if privilege checking has to be performed on column level. @@ -8522,22 +8626,12 @@ bool check_grant_all_columns(THD *thd, privilege_t want_access_arg, if (want_access) { /* reload table if someone has modified any grants */ - if (grant->version != grant_version) - { - grant->grant_table_user= - table_hash_search(sctx->host, sctx->ip, db_name, - sctx->priv_user, - table_name, 0); /* purecov: inspected */ - grant->grant_table_role= - sctx->priv_role[0] ? table_hash_search("", NULL, db_name, - sctx->priv_role, - table_name, TRUE) : NULL; - grant->version= grant_version; /* purecov: inspected */ - } + grant->refresh(sctx, db_name, table_name); grant_table= grant->grant_table_user; grant_table_role= grant->grant_table_role; - if (!grant_table && !grant_table_role) + grant_public= grant->grant_public; + if (!grant_table && !grant_table_role && !grant_public) goto err; } } @@ -8560,6 +8654,15 @@ bool check_grant_all_columns(THD *thd, privilege_t want_access_arg, if (grant_column) have_access|= grant_column->rights; } + if (grant_public) + { + GRANT_COLUMN *grant_column= + column_hash_search(grant_public, field_name->str, + field_name->length); + if (grant_column) + have_access|= grant_column->rights; + + } if (have_access) using_column_privileges= TRUE; @@ -8739,6 +8842,13 @@ bool check_grant_routine(THD *thd, privilege_t want_access, table->table_name.str, sph, 0))) table->grant.privilege|= grant_proc->privs; } + if (acl_public) + { + if ((grant_proc= routine_hash_search("", NULL, table->db.str, + public_name.str, + table->table_name.str, sph, 0))) + table->grant.privilege|= grant_proc->privs; + } if (want_access & ~table->grant.privilege) { @@ -8818,27 +8928,10 @@ privilege_t get_table_grant(THD *thd, TABLE_LIST *table) { Security_context *sctx= thd->security_ctx; const char *db = table->db.str ? table->db.str : thd->db.str; - GRANT_TABLE *grant_table; - GRANT_TABLE *grant_table_role= NULL; mysql_rwlock_rdlock(&LOCK_grant); -#ifdef EMBEDDED_LIBRARY - grant_table= NULL; - grant_table_role= NULL; -#else - grant_table= table_hash_search(sctx->host, sctx->ip, db, sctx->priv_user, - table->table_name.str, 0); - if (sctx->priv_role[0]) - grant_table_role= table_hash_search("", "", db, sctx->priv_role, - table->table_name.str, 0); -#endif - table->grant.grant_table_user= grant_table; // Remember for column test - table->grant.grant_table_role= grant_table_role; - table->grant.version=grant_version; - if (grant_table) - table->grant.privilege|= grant_table->privs; - if (grant_table_role) - table->grant.privilege|= grant_table_role->privs; + table->grant.read(sctx, db, table->table_name.str); + table->grant.privilege|= table->grant.aggregate_privs(); privilege_t privilege(table->grant.privilege); mysql_rwlock_unlock(&LOCK_grant); return privilege; @@ -8869,29 +8962,19 @@ privilege_t get_column_grant(THD *thd, GRANT_INFO *grant, { GRANT_TABLE *grant_table; GRANT_TABLE *grant_table_role; + GRANT_TABLE *grant_public; GRANT_COLUMN *grant_column; privilege_t priv(NO_ACL); mysql_rwlock_rdlock(&LOCK_grant); /* reload table if someone has modified any grants */ - if (grant->version != grant_version) - { - Security_context *sctx= thd->security_ctx; - grant->grant_table_user= - table_hash_search(sctx->host, sctx->ip, - db_name, sctx->priv_user, - table_name, 0); /* purecov: inspected */ - grant->grant_table_role= - sctx->priv_role[0] ? table_hash_search("", "", db_name, - sctx->priv_role, - table_name, TRUE) : NULL; - grant->version= grant_version; /* purecov: inspected */ - } + grant->refresh(thd->security_ctx, db_name, table_name); grant_table= grant->grant_table_user; grant_table_role= grant->grant_table_role; + grant_public= grant->grant_public; - if (!grant_table && !grant_table_role) + if (!grant_table && !grant_table_role && !grant_public) priv= grant->privilege; else { @@ -8915,6 +8998,16 @@ privilege_t get_column_grant(THD *thd, GRANT_INFO *grant, priv|= (grant->privilege | grant_table_role->privs | grant_column->rights); } + if (grant_public) + { + grant_column= column_hash_search(grant_public, field_name, + (uint) strlen(field_name)); + if (!grant_column) + priv|= (grant->privilege | grant_public->privs); + else + priv|= (grant->privilege | grant_public->privs | + grant_column->rights); + } } mysql_rwlock_unlock(&LOCK_grant); return priv; @@ -9390,7 +9483,7 @@ bool mysql_show_grants(THD *thd, LEX_USER *lex_user) if (rolename) { - acl_role= find_acl_role(rolename); + acl_role= find_acl_role(rolename, true); if (acl_role) { /* get a list of all inherited roles */ @@ -9529,6 +9622,13 @@ static bool show_global_privileges(THD *thd, ACL_USER_BASE *acl_entry, want_access= ((ACL_ROLE *)acl_entry)->initial_role_access; else want_access= acl_entry->access; + + // suppress "GRANT USAGE ON *.* TO `PUBLIC`" + if (!(want_access & ~GRANT_ACL) && + acl_entry->user.length == public_name.length && + strcasecmp(acl_entry->user.str, public_name.str) == 0) + return FALSE; + if (test_all_bits(want_access, (GLOBAL_ACLS & ~ GRANT_ACL))) global.append(STRING_WITH_LEN("ALL PRIVILEGES")); else if (!(want_access & ~GRANT_ACL)) @@ -10265,7 +10365,7 @@ static int handle_grant_struct(enum enum_acl_lists struct_no, bool drop, if (struct_no == ROLE_ACL) //no need to scan the structures in this case { - acl_role= find_acl_role(user_from->user.str); + acl_role= find_acl_role(user_from->user.str, true); if (!acl_role) DBUG_RETURN(0); @@ -10620,7 +10720,7 @@ static int handle_grant_data(THD *thd, Grant_tables& tables, bool drop, if (search_only) { /* quickly search in-memory structures first */ - if (handle_as_role && find_acl_role(user_from->user.str)) + if (handle_as_role && find_acl_role(user_from->user.str, true)) DBUG_RETURN(1); // found if (!handle_as_role && find_user_exact(user_from->host.str, user_from->user.str)) @@ -10854,7 +10954,8 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list, bool handle_as_role) continue; } - if (handle_as_role && is_invalid_role_name(user_name->user.str)) + if (handle_as_role && + (check_role_name(user_name->user.str, false) == ROLE_NAME_INVALID)) { append_user(thd, &wrong_users, user_name); result= TRUE; @@ -10922,7 +11023,7 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list, bool handle_as_role) { ACL_USER_BASE *grantee= find_acl_user_base(thd->lex->definer->user.str, thd->lex->definer->host.str); - ACL_ROLE *role= find_acl_role(user_name->user.str); + ACL_ROLE *role= find_acl_role(user_name->user.str, false); /* just like with routines, views, triggers, and events we allow @@ -11014,10 +11115,15 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role) { int rc; user_name= get_current_user(thd, tmp_user_name, false); - if (!user_name) + if (!user_name || (handle_as_role && + (strcasecmp(user_name->user.str, + public_name.str) == 0))) { thd->clear_error(); - append_str(&wrong_users, STRING_WITH_LEN("CURRENT_ROLE")); + if (!user_name) + append_str(&wrong_users, STRING_WITH_LEN("CURRENT_ROLE")); + else + append_str(&wrong_users, public_name.str, public_name.length); result= TRUE; continue; } @@ -11446,7 +11552,7 @@ bool mysql_revoke_all(THD *thd, List <LEX_USER> &list) if (lex_user->is_role()) { /* this can not fail due to get_current_user already having searched for it */ - user_or_role= find_acl_role(lex_user->user.str); + user_or_role= find_acl_role(lex_user->user.str, true); } else { @@ -11967,6 +12073,8 @@ static bool set_user_salt_if_needed(ACL_USER *, int, plugin_ref) { return 0; } bool check_grant(THD *, privilege_t, TABLE_LIST *, bool, uint, bool) { return 0; } +inline privilege_t public_access() +{ return NO_ACL; } #endif /*NO_EMBEDDED_ACCESS_CHECKS */ @@ -12225,7 +12333,7 @@ bool check_role_is_granted(const char *username, if (hostname) root= find_user_exact(hostname, username); else - root= find_acl_role(username); + root= find_acl_role(username, false); LEX_CSTRING role_lex; role_lex.str= rolename; @@ -12253,7 +12361,7 @@ int fill_schema_enabled_roles(THD *thd, TABLE_LIST *tables, COND *cond) { mysql_rwlock_rdlock(&LOCK_grant); mysql_mutex_lock(&acl_cache->lock); - ACL_ROLE *acl_role= find_acl_role(thd->security_ctx->priv_role); + ACL_ROLE *acl_role= find_acl_role(thd->security_ctx->priv_role, false); if (acl_role) traverse_role_graph_down(acl_role, table, enabled_roles_insert, NULL); mysql_mutex_unlock(&acl_cache->lock); @@ -12727,11 +12835,7 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant, if (!thd->db.str || strcmp(db, thd->db.str)) { - /* db privileges */ - grant->privilege|= acl_get(sctx->host, sctx->ip, sctx->priv_user, db, 0); - /* db privileges for role */ - if (sctx->priv_role[0]) - grant->privilege|= acl_get("", "", sctx->priv_role, db, 0); + grant->privilege|= acl_get_all3(sctx, db, FALSE); } else { @@ -12740,18 +12844,8 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant, /* table privileges */ mysql_rwlock_rdlock(&LOCK_grant); - if (grant->version != grant_version) - { - grant->grant_table_user= - table_hash_search(sctx->host, sctx->ip, db, - sctx->priv_user, - table, 0); /* purecov: inspected */ - grant->grant_table_role= - sctx->priv_role[0] ? table_hash_search("", "", db, - sctx->priv_role, - table, TRUE) : NULL; - grant->version= grant_version; /* purecov: inspected */ - } + grant->refresh(sctx, db, table); + if (grant->grant_table_user != 0) { grant->privilege|= grant->grant_table_user->privs; @@ -12760,6 +12854,10 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant, { grant->privilege|= grant->grant_table_role->privs; } + if (grant->grant_public != 0) + { + grant->privilege|= grant->grant_public->privs; + } mysql_rwlock_unlock(&LOCK_grant); DBUG_PRINT("info", ("privilege 0x%llx", (longlong) grant->privilege)); @@ -12815,12 +12913,16 @@ LEX_USER *get_current_user(THD *thd, LEX_USER *user, bool lock) return dup; } - if (is_invalid_role_name(user->user.str)) + role_name_check_result result= check_role_name(user->user.str, + user->host.length == 0); + if (result == ROLE_NAME_INVALID) return 0; + if (result == ROLE_NAME_PUBLIC) + dup->is_public= true; if (lock) mysql_mutex_lock(&acl_cache->lock); - if (find_acl_role(dup->user.str)) + if (find_acl_role(dup->user.str, false) || dup->is_public) dup->host= empty_clex_str; else dup->host= host_not_specified; @@ -14429,7 +14531,7 @@ bool acl_authenticate(THD *thd, uint com_change_user_pkt_len) } #endif - sctx->master_access= acl_user->access; + sctx->master_access= (acl_user->access | public_access()); strmake_buf(sctx->priv_user, acl_user->user.str); if (acl_user->host.hostname) diff --git a/sql/sql_acl.h b/sql/sql_acl.h index 570da144b46..d1597ca9f5a 100644 --- a/sql/sql_acl.h +++ b/sql/sql_acl.h @@ -76,8 +76,8 @@ bool hostname_requires_resolving(const char *hostname); bool acl_init(bool dont_read_acl_tables); bool acl_reload(THD *thd); void acl_free(bool end=0); -privilege_t acl_get(const char *host, const char *ip, - const char *user, const char *db, my_bool db_is_pattern); +privilege_t acl_get_all3(Security_context *sctx, const char *db, + bool db_is_patern); bool acl_authenticate(THD *thd, uint com_change_user_pkt_len); bool acl_getroot(Security_context *sctx, const char *user, const char *host, const char *ip, const char *db); diff --git a/sql/sql_db.cc b/sql/sql_db.cc index 9da1ac5ca77..6b7d6375367 100644 --- a/sql/sql_db.cc +++ b/sql/sql_db.cc @@ -1778,16 +1778,13 @@ uint mysql_change_db(THD *thd, const LEX_CSTRING *new_db_name, #ifndef NO_EMBEDDED_ACCESS_CHECKS if (test_all_bits(sctx->master_access, DB_ACLS)) + { db_access= DB_ACLS; + } else { - db_access= acl_get(sctx->host, sctx->ip, sctx->priv_user, - new_db_file_name.str, FALSE) | sctx->master_access; - if (sctx->priv_role[0]) - { - /* include a possible currently set role for access */ - db_access|= acl_get("", "", sctx->priv_role, new_db_file_name.str, FALSE); - } + db_access= acl_get_all3(sctx, new_db_file_name.str, FALSE); + db_access|= sctx->master_access; } if (!force_switch && diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 0597b086b7b..9f45ff93f3a 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6759,10 +6759,7 @@ check_access(THD *thd, privilege_t want_access, { if (db && (!thd->db.str || db_is_pattern || strcmp(db, thd->db.str))) { - db_access= acl_get(sctx->host, sctx->ip, sctx->priv_user, db, - db_is_pattern); - if (sctx->priv_role[0]) - db_access|= acl_get("", "", sctx->priv_role, db, db_is_pattern); + db_access= acl_get_all3(sctx, db, db_is_pattern); } else { @@ -6807,14 +6804,7 @@ check_access(THD *thd, privilege_t want_access, } if (db && (!thd->db.str || db_is_pattern || strcmp(db, thd->db.str))) - { - db_access= acl_get(sctx->host, sctx->ip, sctx->priv_user, db, - db_is_pattern); - if (sctx->priv_role[0]) - { - db_access|= acl_get("", "", sctx->priv_role, db, db_is_pattern); - } - } + db_access= acl_get_all3(sctx, db, db_is_pattern); else db_access= sctx->db_access; DBUG_PRINT("info",("db_access: %llx want_access: %llx", diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 04bec87ca13..e66a2600446 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1402,12 +1402,8 @@ bool mysqld_show_create_db(THD *thd, LEX_CSTRING *dbname, if (test_all_bits(sctx->master_access, DB_ACLS)) db_access=DB_ACLS; else - { - db_access= acl_get(sctx->host, sctx->ip, sctx->priv_user, dbname->str, 0) | + db_access= acl_get_all3(sctx, dbname->str, FALSE) | sctx->master_access; - if (sctx->priv_role[0]) - db_access|= acl_get("", "", sctx->priv_role, dbname->str, 0); - } if (!(db_access & DB_ACLS) && check_grant_db(thd,dbname->str)) { @@ -5304,7 +5300,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) &thd->col_access, NULL, 0, 1) || (!thd->col_access && check_grant_db(thd, db_name->str))) || sctx->master_access & (DB_ACLS | SHOW_DB_ACL) || - acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, 0)) + acl_get_all3(sctx, db_name->str, 0)) #endif { Dynamic_array<LEX_CSTRING*> table_names(PSI_INSTRUMENT_MEM); @@ -5504,9 +5500,7 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond) } #ifndef NO_EMBEDDED_ACCESS_CHECKS if (sctx->master_access & (DB_ACLS | SHOW_DB_ACL) || - acl_get(sctx->host, sctx->ip, sctx->priv_user, db_name->str, false) || - (sctx->priv_role[0] ? - acl_get("", "", sctx->priv_role, db_name->str, false) : NO_ACL) || + acl_get_all3(sctx, db_name->str, false) || !check_grant_db(thd, db_name->str)) #endif { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f316a59843c..4c73c4bac97 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -17103,6 +17103,7 @@ grant_role: $$->user= $1; $$->host= empty_clex_str; $$->auth= NULL; + $$->is_public= false; if (unlikely(check_string_char_length(&$$->user, ER_USERNAME, username_char_length, diff --git a/sql/structs.h b/sql/structs.h index b36f8e6a1a0..edecbf0888b 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -254,6 +254,7 @@ struct AUTHID struct LEX_USER: public AUTHID { USER_AUTH *auth; + bool is_public; bool has_auth() { return auth && (auth->plugin.length || auth->auth_str.length || auth->pwtext.length); diff --git a/sql/table.h b/sql/table.h index 426e204e2fb..b5c17ad3d83 100644 --- a/sql/table.h +++ b/sql/table.h @@ -286,6 +286,7 @@ typedef struct st_grant_info */ GRANT_TABLE *grant_table_user; GRANT_TABLE *grant_table_role; + GRANT_TABLE *grant_public; /** @brief Used for cache invalidation when caching privilege information. @@ -332,6 +333,14 @@ typedef struct st_grant_info want_privilege(NO_ACL), orig_want_privilege(NO_ACL) { } + + void read(const Security_context *sctx, const char *db, + const char *table); + + inline void refresh(const Security_context *sctx, const char *db, + const char *table); + inline privilege_t aggregate_privs(); + inline privilege_t aggregate_cols(); } GRANT_INFO; enum tmp_table_type |