mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
320 lines
13 KiB
Text
320 lines
13 KiB
Text
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 latin1 COLLATE latin1_swedish_ci
|
|
BEGIN
|
|
RETURN "INSIDE upgrade_del_func()";
|
|
END latin1 latin1_swedish_ci latin1_swedish_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 latin1 COLLATE latin1_swedish_ci
|
|
BEGIN
|
|
RETURN "INSIDE upgrade_del_func()";
|
|
END latin1 latin1_swedish_ci latin1_swedish_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 latin1 COLLATE latin1_swedish_ci
|
|
BEGIN
|
|
RETURN "INSIDE upgrade_alter_func()";
|
|
END latin1 latin1_swedish_ci latin1_swedish_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
|