mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 19:06:14 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/rpl/r/rpl_do_grant.result
Alexander Barkov 36eba98817 MDEV-19123 Change default charset from latin1 to utf8mb4 
Changing the default server character set from latin1 to utf8mb4.
2024-07-11 10:21:07 +04:00

320 lines
13 KiB
Text
Raw Permalink Blame History

include/master-slave.inc
[connection master]
connection master;
create user rpl_do_grant@localhost;
grant select on *.* to rpl_do_grant@localhost;
grant drop on test.* to rpl_do_grant@localhost;
connection slave;
show grants for rpl_do_grant@localhost;
Grants for rpl_do_grant@localhost
GRANT SELECT ON *.* TO `rpl_do_grant`@`localhost`
GRANT DROP ON `test`.* TO `rpl_do_grant`@`localhost`
connection master;
set password for rpl_do_grant@localhost=password("does it work?");
connection slave;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
1
connection master;
update mysql.global_priv set priv=json_remove(priv, '$.authentication_string') where user='rpl_do_grant';
flush privileges;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
0
set sql_mode='ANSI_QUOTES';
set password for rpl_do_grant@localhost=password('does it work?');
set sql_mode='';
connection slave;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
1
connection master;
drop user rpl_do_grant@localhost;
connection slave;
connection master;
show grants for rpl_do_grant@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
connection slave;
show grants for rpl_do_grant@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
connection master;
create user rpl_do_grant@localhost;
show grants for rpl_do_grant@localhost;
Grants for rpl_do_grant@localhost
GRANT USAGE ON *.* TO `rpl_do_grant`@`localhost`
show grants for rpl_do_grant2@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
connection slave;
show grants for rpl_do_grant@localhost;
Grants for rpl_do_grant@localhost
GRANT USAGE ON *.* TO `rpl_do_grant`@`localhost`
show grants for rpl_do_grant2@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
connection master;
rename user rpl_do_grant@localhost to rpl_do_grant2@localhost;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection master;
grant DELETE,INSERT on mysqltest1.* to rpl_do_grant2@localhost;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
GRANT INSERT, DELETE ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
GRANT INSERT, DELETE ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
connection master;
revoke DELETE on mysqltest1.* from rpl_do_grant2@localhost;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
GRANT INSERT ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
GRANT INSERT ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
connection master;
revoke all privileges, grant option from rpl_do_grant2@localhost;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection master;
drop user rpl_do_grant2@localhost;
show grants for rpl_do_grant2@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
connection slave;
show grants for rpl_do_grant2@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
connection master;
call mtr.add_suppression("Slave: Operation DROP USER failed for 'create_rout_db'@'localhost' error.* 1396");
connection slave;
connection master;
DROP DATABASE IF EXISTS bug42217_db;
CREATE DATABASE bug42217_db;
GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
connection slave;
connection master;
connect create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,;
connect create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,;
connection create_rout_db_master;
USE bug42217_db;
CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
BEGIN
RETURN "INSIDE upgrade_del_func()";
END//
connection master;
USE bug42217_db;
SELECT * FROM mysql.procs_priv;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
SELECT upgrade_del_func();
upgrade_del_func()
INSIDE upgrade_del_func()
connection slave;
SELECT * FROM mysql.procs_priv;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
SHOW GRANTS FOR 'create_rout_db'@'localhost';
Grants for create_rout_db@localhost
GRANT USAGE ON *.* TO `create_rout_db`@`localhost` IDENTIFIED BY PASSWORD '*08792480350CBA057BDE781B9DF183B263934601'
GRANT CREATE ROUTINE ON `bug42217_db`.* TO `create_rout_db`@`localhost` WITH GRANT OPTION
GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO `create_rout_db`@`localhost`
USE bug42217_db;
SHOW CREATE FUNCTION upgrade_del_func;
Function sql_mode Create Function character_set_client collation_connection Database Collation
upgrade_del_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
BEGIN
RETURN "INSIDE upgrade_del_func()";
END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
SELECT upgrade_del_func();
upgrade_del_func()
INSIDE upgrade_del_func()
"Check whether the definer user will be able to execute the replicated routine on slave"
connection create_rout_db_slave;
USE bug42217_db;
SHOW CREATE FUNCTION upgrade_del_func;
Function sql_mode Create Function character_set_client collation_connection Database Collation
upgrade_del_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
BEGIN
RETURN "INSIDE upgrade_del_func()";
END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
SELECT upgrade_del_func();
upgrade_del_func()
INSIDE upgrade_del_func()
connection slave;
DELETE FROM mysql.procs_priv;
FLUSH PRIVILEGES;
USE bug42217_db;
"Can't execute the replicated routine on slave like before after procs privilege is deleted "
SELECT upgrade_del_func();
ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func'
"Test the user who creates a function on master doesn't exist on slave."
"Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
DROP USER 'create_rout_db'@'localhost';
connection create_rout_db_master;
CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
BEGIN
RETURN "INSIDE upgrade_alter_func()";
END//
connection master;
SELECT upgrade_alter_func();
upgrade_alter_func()
INSIDE upgrade_alter_func()
connection slave;
SHOW CREATE FUNCTION upgrade_alter_func;
Function sql_mode Create Function character_set_client collation_connection Database Collation
upgrade_alter_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
BEGIN
RETURN "INSIDE upgrade_alter_func()";
END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
"Should no privilege record for upgrade_alter_func in mysql.procs_priv"
SELECT * FROM mysql.procs_priv;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
SELECT upgrade_alter_func();
ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist
disconnect create_rout_db_master;
disconnect create_rout_db_slave;
connection master;
USE bug42217_db;
DROP FUNCTION upgrade_del_func;
DROP FUNCTION upgrade_alter_func;
DROP DATABASE bug42217_db;
connection slave;
connection master;
SET SQL_LOG_BIN= 0;
DROP USER 'create_rout_db'@'localhost';
SET SQL_LOG_BIN= 1;
include/rpl_reset.inc
USE test;
######## BUG#49119 #######
### i) test case from the 'how to repeat section'
connection master;
CREATE TABLE t1(c1 INT);
CREATE PROCEDURE p1() SELECT * FROM t1 |
REVOKE EXECUTE ON PROCEDURE p1 FROM 'root'@'localhost';
ERROR 42000: There is no such grant defined for user 'root' on host 'localhost' on routine 'p1'
connection slave;
connection master;
DROP TABLE t1;
DROP PROCEDURE p1;
connection slave;
### ii) Test case in which REVOKE partially succeeds
connection master;
include/rpl_reset.inc
connection master;
CREATE TABLE t1(c1 INT);
CREATE PROCEDURE p1() SELECT * FROM t1 |
CREATE USER 'user49119'@'localhost';
GRANT EXECUTE ON PROCEDURE p1 TO 'user49119'@'localhost';
##############################################################
### Showing grants for both users: root and user49119 (master)
SHOW GRANTS FOR 'user49119'@'localhost';
Grants for user49119@localhost
GRANT USAGE ON *.* TO `user49119`@`localhost`
GRANT EXECUTE ON PROCEDURE `test`.`p1` TO `user49119`@`localhost`
SHOW GRANTS FOR CURRENT_USER;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection slave;
##############################################################
### Showing grants for both users: root and user49119 (master)
SHOW GRANTS FOR 'user49119'@'localhost';
Grants for user49119@localhost
GRANT USAGE ON *.* TO `user49119`@`localhost`
GRANT EXECUTE ON PROCEDURE `test`.`p1` TO `user49119`@`localhost`
SHOW GRANTS FOR CURRENT_USER;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection master;
## This statement will make the revoke fail because root has no
## execute grant. However, it will still revoke the grant for
## user49119.
REVOKE EXECUTE ON PROCEDURE p1 FROM 'user49119'@'localhost', 'root'@'localhost';
ERROR 42000: There is no such grant defined for user 'root' on host 'localhost' on routine 'p1'
##############################################################
### Showing grants for both users: root and user49119 (master)
### after revoke statement failure
SHOW GRANTS FOR 'user49119'@'localhost';
Grants for user49119@localhost
GRANT USAGE ON *.* TO `user49119`@`localhost`
SHOW GRANTS FOR CURRENT_USER;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection slave;
#############################################################
### Showing grants for both users: root and user49119 (slave)
### after revoke statement failure (should match
SHOW GRANTS FOR 'user49119'@'localhost';
Grants for user49119@localhost
GRANT USAGE ON *.* TO `user49119`@`localhost`
SHOW GRANTS FOR CURRENT_USER;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection master;
DROP TABLE t1;
DROP PROCEDURE p1;
DROP USER 'user49119'@'localhost';
connection slave;
include/rpl_reset.inc
connection master;
grant all on *.* to foo@"1.2.3.4";
revoke all privileges, grant option from "foo";
ERROR HY000: Can't revoke all privileges for one or more of the requested users
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; grant all on *.* to foo@"1.2.3.4"
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `test`; revoke all privileges, grant option from "foo"
connection slave;
include/check_slave_no_error.inc
connection master;
DROP USER foo@"1.2.3.4";
connection slave;
# Bug#27606 GRANT statement should be replicated with DEFINER information
include/rpl_reset.inc
connection master;
GRANT SELECT, INSERT ON mysql.user TO user_bug27606@localhost;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection slave;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection master;
REVOKE SELECT ON mysql.user FROM user_bug27606@localhost;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection slave;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection master;
DROP USER user_bug27606@localhost;
select priv into @root_priv from mysql.global_priv where user='root' and host='127.0.0.1';
update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';
include/rpl_end.inc
Reference in a new issue View git blame Copy permalink