## File: initial00.test ## This script is the first phase of the updown privilege/grants test ## This script is specifically for Minor Upgrade scenarios ## It references features not in existance prior to the 5 branch --echo connecting as root connect (root, localhost, root,,); --echo connected as root ##################################### --echo SETTING UP FOR PRIVILEGE TESTS ##################################### --disable_warnings DROP DATABASE IF EXISTS privdb; DROP DATABASE IF EXISTS upgradedb; DROP DATABASE IF EXISTS downgradedb; DROP DATABASE IF EXISTS no_privdb; CREATE DATABASE privdb; CREATE DATABASE no_privdb; --enable_warnings SET SQL_WARNINGS=1; use privdb; --disable_warnings DROP TABLE IF EXISTS t1, t2, already_there, drop_table, delete_table; DROP VIEW IF EXISTS v1, v2, v3, v4, v5, v6, v7, v8; --enable_warnings # create objects to operate on # this table will not be used. Other users # will attempt to overwrite it. do not # delete until phase3 CREATE TABLE already_there (c1 INT); # this table will not be used. It exists so # that a user with the proper priv will be able # to drop it. CREATE TABLE drop_table (c1 INT); # these tables and views will remain until phase3 CREATE TABLE t1 (c1 INT, English CHAR(10), c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE t2 (French CHAR(10), col2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1 (c1, English) VALUES (1, 'blue'), (2, 'green'), (3, 'red'), (4, 'black'); INSERT INTO t2 (French) VALUES ('bleu'), ('vert'), ('rouge'), ('noir'); # table used in the delete tests. It must exist # so that a user can be granted a table priv to it. CREATE TABLE delete_table AS SELECT * FROM privdb.t1; CREATE VIEW v1 AS SELECT * FROM privdb.t1; DELIMITER //; Create function hello() Returns varchar(20) BEGIN Return 'Hello'; END// DELIMITER ;// DELIMITER //; CREATE PROCEDURE selAll() BEGIN SELECT * FROM privdb.t1; END// DELIMITER ;// #### CREATE USERS ################################# ################################################### # these users will remain until phase3 GRANT CREATE USER ON *.* TO 'create_user'@'localhost' IDENTIFIED BY 'create_user' WITH GRANT OPTION; #--------------------------------------------------- GRANT CREATE ON privdb.new_table TO 'create_table'@'localhost' IDENTIFIED BY 'create_table' WITH GRANT OPTION; GRANT CREATE, INSERT ON privdb.* TO 'create_db'@'localhost' IDENTIFIED BY 'create_db' WITH GRANT OPTION; GRANT CREATE, INSERT ON *.* TO 'create_all'@'localhost' IDENTIFIED BY 'create_all' WITH GRANT OPTION; #--------------------------------------------------- GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost' IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION; GRANT CREATE ROUTINE ON *.* TO 'create_rout_all'@'localhost' IDENTIFIED BY 'create_rout_all' WITH GRANT OPTION; #--------------------------------------------------- GRANT CREATE VIEW ON privdb.* TO 'create_view_db'@'localhost' IDENTIFIED BY 'create_view_db' WITH GRANT OPTION; GRANT CREATE VIEW ON *.* TO 'create_view_all'@'localhost' IDENTIFIED BY 'create_view_all' WITH GRANT OPTION; #--------------------------------------------------- GRANT CREATE TEMPORARY TABLES ON privdb.* TO 'cre_temptbl_db'@'localhost' IDENTIFIED BY 'cre_temptbl_db' WITH GRANT OPTION; GRANT CREATE TEMPORARY TABLES ON *.* TO 'cre_temptbl_all'@'localhost' IDENTIFIED BY 'cre_temptbl_all' WITH GRANT OPTION; # GRANTS to SELECT GRANT SELECT (English) ON privdb.t1 TO 'select_column'@'localhost' IDENTIFIED BY 'select_column' WITH GRANT OPTION; GRANT SELECT ON privdb.t1 TO 'select_table'@'localhost' IDENTIFIED BY 'select_table' WITH GRANT OPTION; GRANT SELECT ON privdb.* TO 'select_db'@'localhost' IDENTIFIED BY 'select_db' WITH GRANT OPTION; GRANT SELECT ON *.* TO 'select_all'@'localhost' IDENTIFIED BY 'select_all' WITH GRANT OPTION; # GRANTS to ALTER GRANT ALTER ON privdb.t1 TO 'alter_table'@'localhost' IDENTIFIED BY 'alter_table' WITH GRANT OPTION; GRANT ALTER ON privdb.* TO 'alter_db'@'localhost' IDENTIFIED BY 'alter_db' WITH GRANT OPTION; GRANT ALTER ON *.* TO 'alter_all'@'localhost' IDENTIFIED BY 'alter_all' WITH GRANT OPTION; GRANT ALTER ROUTINE ON privdb.* TO 'alter_rout_db'@'localhost' IDENTIFIED BY 'alter_rout_db' WITH GRANT OPTION; GRANT ALTER ROUTINE ON *.* TO 'alter_rout_all'@'localhost' IDENTIFIED BY 'alter_rout_all' WITH GRANT OPTION; # GRANTS to EXECUTE GRANT EXECUTE ON privdb.* TO 'execute_db'@'localhost' IDENTIFIED BY 'execute_db' WITH GRANT OPTION; GRANT EXECUTE ON *.* TO 'execute_all'@'localhost' IDENTIFIED BY 'execute_all' WITH GRANT OPTION; # GRANTS to INSERT GRANT INSERT (English) ON privdb.t1 TO 'insert_column'@'localhost' IDENTIFIED BY 'insert_column' WITH GRANT OPTION; GRANT INSERT ON privdb.t1 TO 'insert_table'@'localhost' IDENTIFIED BY 'insert_table' WITH GRANT OPTION; GRANT INSERT ON privdb.* TO 'insert_db'@'localhost' IDENTIFIED BY 'insert_db' WITH GRANT OPTION; GRANT INSERT ON *.* TO 'insert_all'@'localhost' IDENTIFIED BY 'insert_all' WITH GRANT OPTION; # GRANTS to REFERENCE GRANT REFERENCES (English) ON privdb.t1 TO 'references_col'@'localhost' IDENTIFIED BY 'references_col' WITH GRANT OPTION; GRANT REFERENCES ON privdb.t1 TO 'references_tbl'@'localhost' IDENTIFIED BY 'references_tbl' WITH GRANT OPTION; GRANT REFERENCES ON privdb.* TO 'references_db'@'localhost' IDENTIFIED BY 'references_db' WITH GRANT OPTION; GRANT REFERENCES ON *.* TO 'references_all'@'localhost' IDENTIFIED BY 'references_all' WITH GRANT OPTION; # GRANTS to UPDATE GRANT UPDATE (English) ON privdb.t1 TO 'update_column'@'localhost' IDENTIFIED BY 'update_column' WITH GRANT OPTION; GRANT UPDATE ON privdb.t1 TO 'update_table'@'localhost' IDENTIFIED BY 'update_table' WITH GRANT OPTION; GRANT UPDATE ON privdb.* TO 'update_db'@'localhost' IDENTIFIED BY 'update_db' WITH GRANT OPTION; GRANT UPDATE ON *.* TO 'update_all'@'localhost' IDENTIFIED BY 'update_all' WITH GRANT OPTION; # GRANTS to INDEX GRANT INDEX ON privdb.t1 TO 'index_table'@'localhost' IDENTIFIED BY 'index_table' WITH GRANT OPTION; GRANT INDEX ON privdb.* TO 'index_db'@'localhost' IDENTIFIED BY 'index_db' WITH GRANT OPTION; GRANT INDEX ON *.* TO 'index_all'@'localhost' IDENTIFIED BY 'index_all' WITH GRANT OPTION; # GRANTS to SHOW VIEW GRANT SHOW VIEW ON privdb.v1 TO 'show_view_tbl'@'localhost' IDENTIFIED BY 'show_view_tbl' WITH GRANT OPTION; GRANT SHOW VIEW ON privdb.* TO 'show_view_db'@'localhost' IDENTIFIED BY 'show_view_db' WITH GRANT OPTION; GRANT SHOW VIEW ON *.* TO 'show_view_all'@'localhost' IDENTIFIED BY 'show_view_all' WITH GRANT OPTION; # GRANTS to DROP GRANT DROP ON privdb.drop_table TO 'drop_table'@'localhost' IDENTIFIED BY 'drop_table' WITH GRANT OPTION; GRANT DROP ON privdb.* TO 'drop_db'@'localhost' IDENTIFIED BY 'drop_db' WITH GRANT OPTION; GRANT DROP ON *.* TO 'drop_all'@'localhost' IDENTIFIED BY 'drop_all' WITH GRANT OPTION; # GRANTS to DELETE GRANT DELETE ON privdb.delete_table TO 'delete_table'@'localhost' IDENTIFIED BY 'delete_table' WITH GRANT OPTION; GRANT DELETE ON privdb.* TO 'delete_db'@'localhost' IDENTIFIED BY 'delete_db' WITH GRANT OPTION; GRANT DELETE ON *.* TO 'delete_all'@'localhost' IDENTIFIED BY 'delete_all' WITH GRANT OPTION; # GRANTS to LOCK TABLES GRANT LOCK TABLES ON privdb.* TO 'lock_db'@'localhost' IDENTIFIED BY 'lock_db' WITH GRANT OPTION; GRANT LOCK TABLES ON *.* TO 'lock_all'@'localhost' IDENTIFIED BY 'lock_all' WITH GRANT OPTION; # GRANTS to ADMINISTRATE GRANT RELOAD ON *.* TO 'reload'@'localhost' IDENTIFIED BY 'reload' WITH GRANT OPTION; GRANT SHUTDOWN ON *.* TO 'shutdown'@'localhost' IDENTIFIED BY 'shutdown' WITH GRANT OPTION; GRANT PROCESS ON *.* TO 'process'@'localhost' IDENTIFIED BY 'process' WITH GRANT OPTION; GRANT FILE ON *.* TO 'file'@'localhost' IDENTIFIED BY 'file' WITH GRANT OPTION; GRANT SHOW DATABASES ON *.* TO 'show_db'@'localhost' IDENTIFIED BY 'show_db' WITH GRANT OPTION; GRANT SUPER ON *.* TO 'super'@'localhost' IDENTIFIED BY 'super' WITH GRANT OPTION; GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'localhost' IDENTIFIED BY 'repl_slave' WITH GRANT OPTION; GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'localhost' IDENTIFIED BY 'repl_client' WITH GRANT OPTION; # Users to be used later in the PROCESS tests GRANT SELECT ON *.* TO 'killme'@'localhost' IDENTIFIED BY 'killme'; GRANT SELECT ON *.* TO 'killme2'@'localhost'IDENTIFIED BY 'killme2'; # CREATE CONNECTIONS TO THE SERVER connect (create_user, localhost, create_user, create_user,); connect (create_table, localhost, create_table, create_table,); connect (create_db, localhost, create_db, create_db, privdb); connect (create_all, localhost, create_all, create_all, privdb); connect (create_rout_db, localhost, create_rout_db, create_rout_db, privdb); connect (create_rout_all, localhost, create_rout_all, create_rout_all,); connect (create_view_db, localhost, create_view_db, create_view_db, ); connect (create_view_all, localhost, create_view_all, create_view_all,); connect (cre_temptbl_db, localhost, cre_temptbl_db, cre_temptbl_db, privdb); connect (cre_temptbl_all, localhost, cre_temptbl_all, cre_temptbl_all,); connect (select_column, localhost, select_column, select_column, privdb); connect (select_table, localhost, select_table, select_table, privdb); connect (select_db, localhost, select_db, select_db, privdb); connect (select_all, localhost, select_all, select_all, privdb); connect (execute_db, localhost, execute_db, execute_db, privdb); connect (execute_all, localhost, execute_all, execute_all,); connect (insert_column, localhost, insert_column, insert_column,); connect (insert_table, localhost, insert_table, insert_table, ); connect (insert_db, localhost, insert_db, insert_db,); connect (insert_all, localhost, insert_all, insert_all,); connect (references_col, localhost, references_col, references_col,); connect (references_tbl, localhost, references_tbl, references_tbl, ); connect (references_db, localhost, references_db, references_db,); connect (references_all, localhost, references_all, references_all,); connect (update_column, localhost, update_column, update_column,); connect (update_table, localhost, update_table, update_table,); connect (update_db, localhost, update_db, update_db,); connect (update_all, localhost, update_all, update_all,); connect (index_table, localhost, index_table, index_table,); connect (index_db, localhost, index_db, index_db,); connect (index_all, localhost, index_all, index_all,); connect (show_view_tbl, localhost, show_view_tbl, show_view_tbl,); connect (show_view_db, localhost, show_view_db, show_view_db,); connect (show_view_all, localhost, show_view_all, show_view_all,); connect (alter_table, localhost, alter_table, alter_table, privdb); connect (alter_db, localhost, alter_db, alter_db,); connect (alter_all, localhost, alter_all, alter_all,); connect (alter_rout_db, localhost, alter_rout_db, alter_rout_db,); connect (alter_rout_all, localhost, alter_rout_all, alter_rout_all,); connect (drop_table, localhost, drop_table, drop_table,); connect (drop_db, localhost, drop_db, drop_db,); connect (drop_all, localhost, drop_all, drop_all,); connect (delete_table, localhost, delete_table, delete_table,); connect (delete_db, localhost, delete_db, delete_db,); connect (delete_all,localhost, delete_all, delete_all,); connect (lock_db, localhost, lock_db, lock_db,); connect (lock_all, localhost, lock_all, lock_all,); connect (reload, localhost, reload, reload,); connect (shutdown, localhost, shutdown, shutdown,); connect (process, localhost, process, process,); connect (file, localhost, file, file,); connect (show_db, localhost, show_db, show_db,); connect (super, localhost, super, super,); connect (repl_slave, localhost, repl_slave, repl_slave,); connect (repl_client, localhost, repl_client, repl_client,); connect (killme, localhost, killme, killme,); connect (killme2, localhost, killme2, killme2,); # TESTS Begin # The basic strategy herein will be to create different users each with # different individual Privilege Grants. Verify the existence of those # grants, and that they can basically do what they are supposed to be # able to do with those grants. # Additionally, a very unprivileged user 'none' will be granted # nothing but USAGE at the beginning of the test. Each of the other # users will in turn grant the privileges that each of them have, so # that by the end of the test, 'none' should have every available # individually grantable privilege. # permission changes take effect according to the following schedule: # PRIVILEGE RULES: # Table and column priv changes are effective on client's next request. # Database priv- effective after the next USE db_name statement. # Global privileges/passwords- effective next time the client connects. ######################## ## CREATE USER ######### ################################################################### --echo connecting as user 'create_user'; connection create_user; --echo connected; let $create_user_connection_id= `select connection_id()`; # start accumulating grants for user 'none', grant access to the server GRANT USAGE ON *.* TO 'none'@'localhost' IDENTIFIED BY 'none'; ################################################################### --echo connecting as user 'none'; connect (none, localhost, none, none,); --echo connected; # Verify USAGE GRANT for 'none' SHOW GRANTS; ######################### --echo connecting as user 'create_user'; connection create_user; --echo connected; # Grant user 'none' the CREATE USER priv GRANT CREATE USER ON *.* TO 'none'@'localhost' WITH GRANT OPTION; # close the 'none' user connection and re-open it # so that the global CREATE USER grant takes effect disconnect none; ######################### --echo connecting as user 'none'; connect (none, localhost, none, none,); --echo connected; # verify that 'none' now has CREATE USER grant SHOW GRANTS; # create 'temp_user' using the new grant GRANT USAGE ON *.* TO 'temp_user'@'localhost' IDENTIFIED BY 'temp_user'; --echo 'connecting as user \'temp_user\''; connect (temp_user, localhost, temp_user, temp_user, ); --echo 'connected'; # verify the USAGE grant for 'temp_user' SHOW GRANTS; ###################### --echo connecting as user 'none'; connection none; --echo connected; # verify that the CREATE USER grant also allows DROP USER DROP USER 'temp_user'@'localhost'; ###################### --echo connecting as root; connection root; --echo connected; # verify 'temp_user' has been dropped SELECT user, host FROM mysql.user WHERE user='temp_user'; ########################## # SELECT (COLUMN) ######## ################################################################## --echo connecting as user 'select_column'; connection select_column; --echo connected; # continue accumulating grants for the user 'none' GRANT SELECT (English) ON privdb.t1 TO 'none'@'localhost'; ################################################################## ### SELECT (COLUMN) EXCEPTION CASES ########################## # select from non-existent column --error 1054 SELECT c5 FROM privdb.t1; # don't have SELECT grant for other columns --error 1143 SELECT * FROM privdb.t1; # don't have SELECT grant for this table --error 1142 SELECT * FROM mysql.user; # don't have SELECT grant for c1 --error 1143 SELECT English FROM privdb.t1 WHERE c1<=3; # don't have SELECT grant for c1 --error 1143 SELECT English FROM privdb.t1 ORDER BY c1 DESC; ### SELECT (COLUMN) USE CASES ################################# SELECT English FROM privdb.t1; SELECT English FROM privdb.t1 ORDER BY English DESC; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the SELECT COL grant to 'none' can be used SELECT English FROM privdb.t1; ################### ########################### ## SELECT (TABLE) ######### ################################################################### --echo connecting as user 'select_table'; connection select_table; --echo connected; # continue accumulating grants for the user 'none' GRANT SELECT ON privdb.t1 TO 'none'@'localhost'; ################################################################## use privdb; ### SELECT (TABLE) EXCEPTION CASES ###################### # don't have SELECT grant for table --error 1142 SELECT * FROM privdb.t2; # don't have SELECT grant for table --error 1142 SELECT * FROM mysql.user; # table doesn't exist --error 1142 SELECT * FROM privdb.t7; ### SELECT (TABLE) USE CASES ######################## SELECT * FROM privdb.t1; SELECT * FROM privdb.t1 LIMIT 2; SELECT * FROM privdb.t1 WHERE c3<=3 ORDER BY ENGLISH DESC; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the SELECT TABLE grant to 'none' can be used SELECT * FROM privdb.t1; ################### ######################### ## SELECT (db) ########## ####################################################### --echo connecting as user 'select_db'; connection select_db; --echo connected; # continue accumulating grants for the user 'none' GRANT SELECT ON privdb.* TO 'none'@'localhost'; ####################################################### ### SELECT (db) EXCEPTION CASES ##################### # don't have access to mysql db --error 1142 SELECT * FROM mysql.user; ### SELECT (db) USE CASES ############################ SELECT * FROM privdb.t1; SELECT * FROM privdb.t2; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the SELECT TABLE grant to 'none' can be used use privdb; SELECT * from v1; SELECT English, French FROM t1 INNER JOIN t2 ON t1.c3=t2.col2; ################### ############################# ## SELECT (GLOBAL) ########## ################################################################# --echo connecting as user 'select_all'; connection select_all; --echo connected; # continue accumulating grants for the user 'none' GRANT SELECT ON *.* TO 'none'@'localhost'; ################################################################# ### SELECT (GLOBAL) USE CASES ####################### SELECT user FROM mysql.user WHERE user='select_all'; ###################### # verify the SELECT GLOBAL grant to 'none' can be used # have to close/reopen connection first disconnect none; --echo connecting as user 'none'; connect (none, localhost, none,none,); --echo connected; SELECT user FROM mysql.user WHERE user='select_all'; ###################### ########################### ## CREATE (table) ######### ############################################################## --echo connecting as user 'create_table'; connection create_table; --echo connected; let $create_table_connection_id= `select connection_id()`; # continue accumulating grants for user 'none' GRANT CREATE ON privdb.new_table TO 'none'@'localhost'; ############################################################## ### CREATE (table) EXCEPTION CASE ###### # no priv was granted for this table object --error 1142 CREATE TABLE upgradedb.no_way (c1 INT); ### CREATE (table) USE CASE ######## CREATE TABLE privdb.new_table (c1 INT); --echo connecting to user 'root'; connection root; --echo connected; SHOW TABLES LIKE 'new_table'; DROP TABLE privdb.new_table; ########################## # verify new grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; CREATE TABLE privdb.new_table (c1 INT); DESCRIBE privdb.new_table; SHOW CREATE TABLE privdb.new_table; --echo connecting to user 'root'; connection root; --echo connected; DROP TABLE privdb.new_table; ######################### ######################## ## CREATE (db) ######### ############################################################## --echo connecting as user 'create_db'; connection create_db; --echo connected; # continue accumulating grants for user 'none' ## GRANT EXCEPTION CASES ############### # don't have access to global level # NOTE: wrong error! change when fixed --error 1045 GRANT CREATE ON *.* TO 'none'@'localhost'; # try granting to a non-existant user --error 1133,1410 GRANT CREATE ON privdb.* TO 'nonone'@'localhost'; ## GRANT USE CASE #################################### GRANT CREATE, INSERT ON privdb.* TO 'none'@'localhost'; ############################################################## use privdb; ##### CREATE (db) Exception cases ############## # need access to global level to create a new DataBase --error 1044 CREATE DATABASE foo; # don't have create user privilege --error 1227 CREATE USER 'foo_user'@'localhost' IDENTIFIED BY 'foo_user'; # don't have create view privilege --error 1142 CREATE VIEW foo_view AS SELECT CURRENT_USER(); # Can't do this, the table already exists --error 1050 CREATE TABLE already_there (c1 INT, c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); # Should just get a warning now CREATE TABLE IF NOT EXISTS already_there (c1 INT, c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); # don't have a GRANT to do this. NOTE: wrong error! --error 1044 CREATE TEMPORARY TABLE foo55 (c1 INT); # don't have access to another db --error 1142 CREATE TABLE no_privdb.magoo (c1 INT); ### CREATE (db) USE CASES ##################### # This should fail following fix to 25578 --error 1142 CREATE TABLE foo LIKE t1; # Adding the SELECT grant connection default; GRANT SELECT ON privdb.t1 TO 'create_db'@'localhost'; connection create_db; # Now should work CREATE TABLE foo LIKE t1; CREATE TABLE foo2 (c1 INT, c2 CHAR(25) DEFAULT 'default value', c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE foo3 (c1 INT, c2 CHAR(25) DEFAULT 'default value', c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY) engine=memory; CREATE TABLE foo4 (c1 INT, c2 CHAR(25) DEFAULT 'default value', c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY) engine=myISAM; ### MORE CREATE (db) EXCEPTION CASES ################ # can't view table definitions --error 1142 DESCRIBE foo; # don't have DROP priv --error 1142 DROP TABLE foo; # try to rename a table - can't Alter table --error 1142 RENAME TABLE foo TO foot; # can't create index once table is defined --error 1142 CREATE INDEX French_index ON foo2(French); ######################################### ############################## --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE grant to user 'none' can be used # This table won't be dropped until the upgrade phase use privdb; CREATE TABLE time (c2 INT NOT NULL UNIQUE, c1 timestamp DEFAULT CURRENT_TIMESTAMP); ############################## # verification and cleanup --echo connecting as user 'root'; connection root; --echo connected; use privdb; SHOW CREATE TABLE foo; SHOW CREATE TABLE foo2; SHOW CREATE TABLE foo3; SHOW CREATE TABLE foo4; DROP TABLE foo, foo2, foo3, foo4, time; ############################# ## CREATE (GLOBAL) ########## ################################################################# --echo connecting as user 'create_all'; connection create_all; --echo connected; # continue accumulating grants for the user 'none' GRANT CREATE, INSERT ON *.* TO 'none'@'localhost'; ################################################################## ### CREATE (GLOBAL) EXCEPTION CASES #################### # database already exists --error 1007 CREATE DATABASE privdb; # should just get warning CREATE DATABASE IF NOT EXISTS privdb; ### CREATE (GLOBAL) USE CASES ############################ # create a database # do not drop until the upgrade phase CREATE DATABASE upgrade_drop_me_db; use upgrade_drop_me_db; # alternate syntax # do not drop until the downgrade phase CREATE SCHEMA downgrade_drop_me_db; use downgrade_drop_me_db; # create a table in a different db CREATE TABLE upgrade_drop_me_db.upgrade_drop_me_tbl (c1 INT); use upgrade_drop_me_db; SHOW TABLES; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE grant to 'none' can be used # have to close connection to none and re-open # for new global grant to take effect --error 1044 CREATE DATABASE foo_temp; disconnect none; --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; CREATE DATABASE foo_temp; USE foo_temp; CREATE TABLE temp_table SELECT * from privdb.t1; SHOW TABLES LIKE 'temp%'; #################### # clean up as root --echo connecting as user 'root'; connection root; --echo connected; DROP TABLE foo_temp.temp_table; DROP DATABASE foo_temp; ################################ ## CREATE ROUTINE (db) ######### ################################################################# --echo connecting as user 'create_rout_db'; connection create_rout_db; --echo connected; # continue accumulating grants for user 'none' GRANT CREATE ROUTINE ON privdb.* TO 'none'@'localhost'; ################################################################# ### CREATE ROUTINE (db) USE CASES ######### use privdb; # will be deleted during upgrade phase DELIMITER //; CREATE FUNCTION upgrade_del_func() RETURNS CHAR(20) BEGIN RETURN "INSIDE upgrade_del_func()"; END// # will be altered during upgrade phase CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(20) BEGIN RETURN "INSIDE upgrade_alter_func()"; END// # will be deleted during downgrade CREATE PROCEDURE downgrade_del_proc() SQL SECURITY INVOKER BEGIN SELECT c1, English, French FROM t1 JOIN t2 ON t1.c3 = t2.col2; END// # will be altered during downgrade phase CREATE PROCEDURE downgrade_alter_proc() SQL SECURITY DEFINER BEGIN SELECT c1, English, French FROM t1 JOIN t2 ON t1.c3 = t2.col2; END// # to be altered/deleted during intial phase CREATE PROCEDURE alter_proc() SQL SECURITY DEFINER BEGIN SELECT "Inside alter_proc()"; END// DELIMITER ;// ### CREATE ROUTINE(db) EXCEPTION CASES ############# # user 'create-routine' should have EXECUTE/ALTER # for routines he created # but he doesn't have a SELECT GRANT --error 1142 CALL downgrade_del_proc(); --error 1142 CALL downgrade_alter_proc(); # can't execute routines created by root # DISABLED - remove error 1305 when #21848 is resolved --error 1370, 1305 SELECT hello(); --error 1370 CALL selAll(); # can't drop routines he didn't define --error 1370 DROP FUNCTION privdb.hello; --error 1370 DROP PROCEDURE privdb.selAll; # can't create a routine in another db DELIMITER //; # OMR --error 1044 --error 1049 CREATE PROCEDURE downgradedb.bad_proc() sql security definer BEGIN SELECT CURRENT_USER(); END// DELIMITER ;// # can't ALTER something he didn't create --error 1370 ALTER PROCEDURE privdb.selAll SQL SECURITY INVOKER; ### MORE CREATE ROUTINE(db) USE CASES ############# CALL alter_proc(); SELECT upgrade_del_func(); # try to use ALTER priv ALTER PROCEDURE alter_proc SQL SECURITY INVOKER COMMENT "this is a test"; # verify the change then set it back SHOW CREATE PROCEDURE alter_proc; ALTER PROCEDURE alter_proc SQL SECURITY DEFINER; ################################## --echo connecting as user 'none'; connection none; --echo connected # verify the CREATE ROUTINE grant to 'none' use privdb; DELIMITER //; CREATE PROCEDURE none_proc() BEGIN SELECT CURRENT_USER(); END// DELIMITER ;// # should be able to call it CALL none_proc(); # and drop it DROP PROCEDURE none_proc; # but can't call anyone elses --error 1370 CALL selAll(); ################################## # DISABLED - need to remove the following block when #19857 is fixed #######################################!!!!!!!!!!!!!!!! # TBD - have to reset the passwords for these users due bug 19857 # switch to root user connection root; SET PASSWORD FOR 'create_rout_db'@'localhost' = PASSWORD('create_rout_db'); SET PASSWORD FOR 'none'@'localhost' = PASSWORD('none'); #######################################!!!!!!!!!!!!!!!! ##################################### ## CREATE ROUTINE (GLOBAL) ########## ############################################################### --echo connecting as user 'create_rout_all'; connection create_rout_all; --echo connected; # continue accumulating grants for user 'none' GRANT CREATE ROUTINE ON *.* TO 'none'@'localhost'; # close and reopen the connection to user 'none'; # so that global priv takes effect disconnect none; connect (none, localhost, none, none,); connection create_rout_all; ################################################################ ### CREATE ROUTINE (GLOBAL) USE CASES ########### DELIMITER //; CREATE PROCEDURE upgrade_drop_me_db.global_proc() BEGIN SELECT "inside global_proc()"; END// DELIMITER ;// DELIMITER //; CREATE FUNCTION upgrade_drop_me_db.global_func() RETURNS INT BEGIN RETURN 555; END// DELIMITER ;// DELIMITER //; CREATE FUNCTION upgrade_drop_me_db.drop_now() RETURNS INT BEGIN RETURN 555; END// DELIMITER ;// # should also be able to execute them CALL upgrade_drop_me_db.global_proc(); SELECT upgrade_drop_me_db.global_func(); # and alter them ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY INVOKER; SHOW CREATE PROCEDURE upgrade_drop_me_db.global_proc; ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY DEFINER; # and drop them DROP FUNCTION upgrade_drop_me_db.drop_now; # and SHOW CREATE PROCEDURE SHOW CREATE PROCEDURE upgrade_drop_me_db.global_proc; ### CREATE ROUTINE (GLOBAL) EXCEPTION CASES ####### # can't execute anyone else's routine --error 1370 CALL privdb.selAll(); # DISABLED - remove error 1305 when #21848 is resolved --error 1370, 1305 SELECT privdb.hello(); # or drop them --error 1370 DROP FUNCTION privdb.hello; ################################### # verify CREATE ROUTINE grant to user 'none' --echo connecting as user 'none'; connection none; --echo connected CREATE PROCEDURE upgrade_drop_me_db.none_proc() SELECT CURRENT_USER(); # user 'none' should be able to execute it CALL upgrade_drop_me_db.none_proc(); # and drop it # DISABLED # currently bug 20241 means that this will have to # be dropped now, because the definer will be dropped # if this is fixed comment this line and allow the # routine to live until the next phase DROP PROCEDURE upgrade_drop_me_db.none_proc; # but can't execute anyone elses --error 1370 CALL privdb.selAll(); ################################## # DISABLED - remove the next block when #19857 is fixeD --echo connecting as 'root'; connection root; --echo connected; #######################################!!!!!!!!!!!!!!!! # have to reset the passwords for these users due bug 19857 SET PASSWORD FOR 'create_rout_all'@'localhost' = PASSWORD('create_rout_all'); SET PASSWORD FOR 'none'@'localhost' = PASSWORD('none'); #######################################!!!!!!!!!!!!!!!! ############################## ## EXECUTE (db) ############## ################################################################### --echo connecting as user 'execute_db'; connection execute_db; --echo connected; # continue accumulating grants for user 'none' GRANT EXECUTE ON privdb.* TO 'none'@'localhost'; #################################################################### ## EXECUTE (db) USE CASES ############## use privdb; SELECT upgrade_del_func(); CALL alter_proc(); CALL selAll(); SELECT hello(); ## EXECUTE (db) EXCEPTION CASES ######## # don't have SELECT GRANT --error 1142 CALL downgrade_del_proc(); ################################## --echo connecting as 'root'; connection root; --echo connected; # grant SELECT so that 'execute' can call # routines containing SELECT statements GRANT SELECT ON privdb.* to 'execute_db'@'localhost'; ################################## --echo connecting as 'execute_db'; connection execute_db; --echo connected; use privdb; ## this now works CALL downgrade_del_proc(); ## but this one runs with 'create-routine's privileges --error 1142 CALL downgrade_alter_proc(); # revoke this grant again to prepare for phase2 REVOKE SELECT ON privdb.* FROM 'execute_db'@'localhost'; # drop this now as the routine DEFINER --echo connecting as user 'create_rout_db'; connection create_rout_db; --echo connected; DROP PROCEDURE privdb.alter_proc; ############################### ## EXECUTE (GLOBAL) ########### ################################################################## --echo connecting as user 'execute_all'; connection execute_all; --echo connected; # continue accumulating grants for user 'none' GRANT EXECUTE ON *.* TO 'none'@'localhost'; #################################################################### CALL upgrade_drop_me_db.global_proc(); CALL privdb.selAll(); # DISABLED - reenable the next line when #21848 is resolved #SELECT privdb.hello(); ############################## # verify EXECUTE grant to user 'none' --echo connecting as user 'none'; connection none; SHOW GRANTS; --echo connected; # have close user 'none' connection and # reconnect so that new global priv takes effect --error 1370 CALL upgrade_drop_me_db.global_proc(); disconnect none; --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; CALL upgrade_drop_me_db.global_proc(); ############################## ########################### ## CREATE VIEW (db) ####### ################################################################## --echo connecting as user 'create_view_db'; connection create_view_db; --echo connected; # continue accumulating grants for the 'none' user ################################################################# ## GRANT EXCEPTION CASES ############### # don't have access to global level NOTE: wrong error! --error 1045 GRANT CREATE VIEW ON *.* TO 'none'@'localhost'; # try granting to a non-existant user --error 1133,1410 GRANT CREATE VIEW ON privdb.* TO 'nonone'@'localhost'; ###################################################### ## GRANT USE CASE GRANT CREATE VIEW ON privdb.* TO 'none'@'localhost'; ################################################################# use privdb; ### CREATE VIEW EXCEPTION CASES ##################### # has duplicate name columns in the SELECT --error 1060 CREATE VIEW bad AS SELECT CURRENT_USER() AS FOO, CURRENT_USER() AS FOO; # same as above --error 1060 CREATE VIEW bad (foo, foo) AS SELECT CURRENT_TIME(), CURRENT_DATE(); # too many column names --error 1353 CREATE VIEW bad (foo, bar, fum) AS SELECT CURRENT_TIME(), CURRENT_DATE(); # namespace conflict with an existing table --error 1050 CREATE VIEW t1 AS SELECT CURRENT_USER(); # don't have priv to drop the view --error 1142 DROP VIEW v2; # also need to have DROP grant to use REPLACE --error 1142 CREATE OR REPLACE VIEW v2 AS SELECT * FROM t1; # need to have SELECT grant to do this --error 1142 CREATE VIEW bad AS SELECT * FROM privdb.t1; ### USE CASES ########################### CREATE VIEW v2 AS SELECT CURRENT_USER(); CREATE VIEW v3 AS SELECT pi() AS pi, SIN(radians(90)); # same as v3 but different syntax CREATE VIEW v4 (pi, SIN90) AS SELECT pi(), SIN(radians(90)); ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE VIEW grant to 'none' can be used use privdb; CREATE VIEW v5 AS SELECT CURRENT_USER(); ################### ########################################## ## CREATE VIEW - WITH SELECT GRANT (db) ## #################################################################### --echo connecting as user 'root'; connection root; --echo connected; GRANT SELECT on privdb.* TO 'create_view_db'@'localhost'; # adding SELECT GRANT so that user 'create_view_db can # create more complete views #################################################################### use privdb; CREATE VIEW v6 AS SELECT * FROM t1; CREATE VIEW v7 (Eng, FR) AS SELECT english, french from t1 JOIN t2 ON t1.c3 = t2.col2; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE/SELECT grant to user'none' use privdb; CREATE VIEW v8 AS SELECT english, french from t1 JOIN t2 ON t1.c3 = t2.col2 WHERE t1.c3 <=3; # verify all of the created views SELECT * FROM v1 WHERE c3 > 2 ORDER BY c1 DESC; # DISABLED re-enable this when #20570 fixed in both versions #SELECT * FROM v2; SELECT * FROM v3; SELECT * FROM v4; # DEBUG re-enable this when #20570 fixed in both versions #SELECT * FROM v5; SELECT * FROM v6 ORDER BY English DESC LIMIT 2; SELECT Eng from v7 ORDER BY Fr; SELECT * from v7; SELECT * FROM v8 LIMIT 1; ################### ################################ ### CREATE VIEW (global) ####### ##################################################### --echo connecting as user 'create_view_all'; connection create_view_all; --echo connected; GRANT CREATE VIEW ON *.* TO 'none'@'localhost'; # close and reopen the connection to none for new priv # to take effect disconnect none; connect (none, localhost, none, none,); connection create_view_all; ##################################################### use privdb; ### CREATE VIEW (global) EXCEPTION CASES ######### # don't have SELECT grant --error 1142 CREATE VIEW upgrade_drop_me_db.vTemp AS SELECT * FROM privdb.v8; ### CREATE VIEW (global) USE CASE ############### CREATE VIEW upgrade_drop_me_db.alter_view AS SELECT CURRENT_USER(); ############################## # verify the grant to the none user --echo connecting as user 'none'; connection none; --echo connected; CREATE VIEW upgrade_drop_me_db.vTemp AS SELECT * FROM privdb.v8; SELECT * FROM upgrade_drop_me_db.vTemp; ############################## ## verify and cleanup as root user --echo connecting as user 'root'; connection root; --echo connected; # DEBUG re-enable this when #20570 fixed in both versions #SELECT * FROM upgrade_drop_me_db.alter_view; DROP VIEW upgrade_drop_me_db.vTemp; ############################################## ### CREATE VIEW with SELECT GRANT (GLOBAL) ### ##################################################### # Granting select so that more complete Views can # be defined --echo connecting as user 'root'; connection root; --echo connected; GRANT SELECT ON *.* TO 'create_view_all'@'localhost'; # close connection and reopen so that the new priv # takes effect disconnect create_view_all; connect (create_view_all, localhost, create_view_all, create_view_all,); #################################################### CREATE VIEW downgrade_drop_me_db.global_view AS SELECT * FROM privdb.t1; ################### # verify the CREATE VIEW grant to 'none' can be used # have to close and repoen --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE VIEW grant to 'none' can be used use privdb; CREATE VIEW upgrade_drop_me_db.vTemp AS SELECT user,host FROM mysql.user; ################### # clean up as root - --echo connecting as user 'root'; connection root; --echo connected; DROP VIEW upgrade_drop_me_db.vTemp; ################################# ## CREATE TEMPORARY TABLES ###### ################################################################# --echo connecting as user 'cre_temptbl_db'; connection cre_temptbl_db; --echo connected; # continue accumulating grants for the 'none' user GRANT CREATE TEMPORARY TABLES ON privdb.* TO 'none'@'localhost'; ################################################################ use privdb; # This should fail following fix to 25578 --error 1142 CREATE TEMPORARY TABLE temp_table LIKE t1; # Adding the SELECT grant connection default; GRANT SELECT ON privdb.t1 TO 'cre_temptbl_db'@'localhost'; connection cre_temptbl_db; # Now should work CREATE TEMPORARY TABLE temp_table LIKE t1; # only way to verify it is to try to create it again --error 1050 CREATE TEMPORARY TABLE temp_table LIKE t1; # quit and log in again as create_temptable # last temp table should be gone and now # can be created again --echo disconnecting user 'cre_temptbl_db'; disconnect cre_temptbl_db; --echo disconnected; connect (cre_temptbl_db, localhost, cre_temptbl_db, cre_temptbl_db,); --echo connecting as user 'cre_temptbl_db'; connection cre_temptbl_db; --echo connected; use privdb; CREATE TEMPORARY TABLE temp_table LIKE t1; ################### --echo connecting as user 'none'; connection none; --echo connected; # verify the CREATE TEMP TABLE grant to 'none' can be used use privdb; CREATE TEMPORARY TABLE temp_table LIKE t1; --error 1050 CREATE TEMPORARY TABLE temp_table LIKE t1; SELECT * FROM temp_table; ################### ################################ ### INSERT (column) ############ ################################################################# connection insert_column; --echo connected; # continue accumulating grants for the 'none' user GRANT INSERT (English) ON privdb.t1 TO 'none'@'localhost'; ################################################################# #### INSERT (column) EXCEPTION CASES ######### # don't have INSERT (table) GRANT --error 1143 INSERT INTO privdb.t1 (c1, English) VALUES (3, 'violet'); # don't have INSERT (db) GRANT --error 1142 INSERT INTO privdb.t2 VALUES ('violet'); # don't have INSERT (all) GRANT --error 1142 INSERT INTO upgradedb.t1 VALUES ('666'); ### INSERT (column) USE CASES ############## INSERT INTO privdb.t1 (English) VALUES ('white'); ############################ --echo connecting as user 'none'; connection none; --echo connected; # verify the INSERT (column) grant to 'none' can be used INSERT INTO privdb.t1 (English) VALUES ('yellow'); SELECT * FROM privdb.t1; ############################ ################################ ### INSERT (table) ############# ################################################################# --echo connecting as user 'insert_table'; connection insert_table; --echo connected; # continue accumulating grants for the 'none' user GRANT INSERT ON privdb.t1 TO 'none'@'localhost'; ################################################################# ### INSERT (table) EXCEPTION CASES ######### # don't have INSERT (db) GRANT --error 1142 INSERT INTO privdb.t2 VALUES ('violet'); # don't have INSERT (all) GRANT --error 1142 INSERT INTO upgradedb.t1 VALUES ('666'); ### INSERT (table) USE CASES ############## INSERT INTO privdb.t1 (c1,English) VALUES (7, 'brown'); ############################ --echo connecting as user 'none'; connection none; --echo connected; # verify the INSERT (table) grant to 'none' can be used INSERT INTO privdb.t1 (c1, English) VALUES (8, 'orange'); SELECT * FROM privdb.t1; ############################ ################################ ### INSERT (db) ################ ################################################################# --echo connecting as user 'insert_db'; connection insert_db; --echo connected; # continue accumulating grants for the 'none' user GRANT INSERT ON privdb.* TO 'none'@'localhost'; ################################################################# ### INSERT (table) EXCEPTION CASES ######### # don't have INSERT (GLOBAL) GRANT --error 1142 INSERT INTO upgradedb.t1 VALUES ('666'); ### INSERT (table) USE CASES ############## INSERT INTO privdb.t2 (French) VALUES ('blanc'), ('jaune'); INSERT INTO privdb.drop_table VALUES (1),(2),(3); ############################ --echo connecting as user 'none'; connection none; --echo connected; # verify the INSERT (db) grant to 'none' can be used # the new priv should take effect after this next use statement use privdb; INSERT INTO privdb.t2 (French) VALUES ('brun'), ('orange'); SELECT * FROM privdb.t1; ############################ ################################ ### INSERT (GLOBAL) ############ ################################################################# --echo connecting as user 'insert_all'; connection insert_all; --echo connected; # continue accumulating grants for the 'none' user GRANT INSERT ON *.* TO 'none'@'localhost'; ################################################################# INSERT INTO upgrade_drop_me_db.upgrade_drop_me_tbl VALUES (66); ############################ # verify the INSERT (GLOBAL) grant to 'none' can be used # have to close and reopen the connection before the # new priv takes effect disconnect none; --echo connecting as use 'none'; connect (none, localhost, none, none,); --echo connected; INSERT INTO upgrade_drop_me_db.upgrade_drop_me_tbl VALUES (67); SELECT * FROM upgrade_drop_me_db.upgrade_drop_me_tbl; ############################ ################################ ### REFERENCES (column) ######## ################################################################# --echo connecting as user 'references_col'; connection references_col; --echo connected; # continue accumulating grants for the 'none' user GRANT REFERENCES (English) ON privdb.t1 TO 'none'@'localhost'; ################################################################# # THIS GRANT IS CURRENTLY NOT USED ################################ ### REFERENCES (table) ######### ################################################################# --echo connecting as user 'references_tbl'; connection references_tbl; --echo connected; # continue accumulating grants for the 'none' user GRANT REFERENCES ON privdb.t1 TO 'none'@'localhost'; ################################################################# # THIS GRANT IS CURRENTLY NOT USED ################################ ### REFERENCES (db) ############ ################################################################# --echo connecting as user 'references_db'; connection references_db; --echo connected; # continue accumulating grants for the 'none' user GRANT REFERENCES ON privdb.* TO 'none'@'localhost'; ################################################################# # THIS GRANT IS CURRENTLY NOT USED ################################ ### REFERENCES (GLOBAL) ######## ################################################################# --echo connecting as user 'references_all'; connection references_all; --echo connected; # continue accumulating grants for the 'none' user GRANT REFERENCES ON *.* TO 'none'@'localhost'; ################################################################# # THIS GRANT IS CURRENTLY NOT USED ################################ ### UPDATE (column) ############ ################################################################# --echo connecting as user 'update_column'; connection update_column; --echo connected; # continue accumulating grants for the 'none' user GRANT UPDATE (English) ON privdb.t1 TO 'none'@'localhost'; ################################################################# ### UPDATE (column) EXCEPTION CASES ########### # no priv for this column --error 1143 UPDATE privdb.t1 SET English = 'white' WHERE c1=1; # column c4 doesn't exist --error 1054 UPDATE privdb.t1 SET English= 'white' WHERE c4=1; ### UPDATE (column) USE CASES ############### # grant SELECT so that the user can do something --echo connecting as user 'root'; connection root; --echo connected GRANT SELECT (c1) ON privdb.t1 TO 'update_column'@'localhost'; UPDATE privdb.t1 SET English='pink' WHERE c1=1; ############################ # verify the UPDATE (column) grant to 'none' can be used --echo connecting to user 'none'; connection none; --echo connected; SELECT * FROM privdb.t1 WHERE English='pink'; UPDATE privdb.t1 SET English='white' WHERE c1=1; SELECT * FROM privdb.t1; ############################ ############################### ### UPDATE (table) ############ ################################################################# --echo connecting as user 'update_table'; connection update_table; --echo connected; # continue accumulating grants for the 'none' user GRANT UPDATE ON privdb.t1 TO 'none'@'localhost'; # grant SELECT to the UPDATE user connection root; GRANT SELECT ON privdb.t1 TO 'update_table'@'localhost'; ################################################################# --echo connecting as user 'update_table'; connection update_table; --echo connected; UPDATE privdb.t1 SET c1=5 WHERE c3=5; ########################## # verify UPDATE grant to user 'none' UPDATE privdb.t1 SET c1=6 WHERE English = 'yellow'; ######################### ################################ ### UPDATE (db) ################ ################################################################# --echo connecting as user 'update_db'; connection update_db; --echo connected; # continue accumulating grants for the 'none' user GRANT UPDATE ON privdb.* TO 'none'@'localhost'; # grant SELECT to the UPDATE user connection root; GRANT SELECT ON privdb.* TO 'update_db'@'localhost'; use privdb; ################################################################# --echo connecting as user 'update_db'; connection update_db; --echo connected UPDATE privdb.t2 SET French = 'croissant' WHERE French = 'noir'; ######################### # verify UPDATE (db) grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; SELECT * FROM privdb.t2; UPDATE privdb.t2 SET French = 'noir' WHERE French = 'croissant'; SELECT * FROM privdb.t2; ######################## ################################ ### UPDATE (GLOBAL) ############ ################################################################# --echo connecting as user 'update_all'; connection update_all; --echo connected; # continue accumulating grants for the 'none' user GRANT UPDATE ON *.* TO 'none'@'localhost'; # close connection and reconnect so that # new privs take effect disconnect none; --echo connecting as user 'none'; connect (none, localhost, none, none,); ################################################################# ################################ ### INDEX (table) ############## ################################################################# --echo connecting as user 'index_table'; connection index_table; --echo connected; # continue accumulating grants for the 'none' user GRANT INDEX ON privdb.t1 TO 'none'@'localhost'; ################################################################# ### INDEX (table) EXCEPTION CASES #### # duplicate entry present --error 1062 CREATE UNIQUE INDEX index1 ON privdb.t1 (English); --echo connecting as user 'none'; connection none; --echo connected; UPDATE privdb.t1 SET English='blue' WHERE c3=1; --echo connecting as user 'index_table'; connection index_table; --echo connected; # try that again ### INDEX (table) USE CASES ##### CREATE UNIQUE INDEX index1 ON privdb.t1 (English); DROP INDEX index1 ON privdb.t1; ####################### # verify INDEX grant to user 'none' --echo connecting as user 'none'; connection none; --echo connected; CREATE INDEX index1 ON privdb.t1 (English); DROP INDEX index1 ON privdb.t1; ####################### ############################### ### INDEX (db) ################ ################################################################# --echo connecting as user 'index_db'; connection index_db; --echo connected; # continue accumulating grants for the 'none' user GRANT INDEX ON privdb.* TO 'none'@'localhost'; use privdb; ################################################################# ### INDEX (db) EXCEPTION CASES #### # duplicate entry present --error 1072 CREATE UNIQUE INDEX index1 ON privdb.t2 (c3); ### INDEX (table) USE CASES ##### CREATE INDEX index1 ON privdb.t2 (French); DROP INDEX index1 ON privdb.t2; ####################### # verify INDEX grant to user 'none' --echo connecting as user 'none'; connection none; --echo connected; CREATE INDEX index1 ON privdb.t2 (French); DROP INDEX index1 ON privdb.t2; ####################### ################################## ### INDEX (GLOBAL) ############### ################################################################# --echo connecting as user 'index_all'; connection index_all; --echo connected; # continue accumulating grants for the 'none' user GRANT INDEX ON *.* TO 'none'@'localhost'; # close connection and reconnect so that # new privs take effect disconnect none; --echo connecting as user 'none'; connect (none, localhost, none, none,); ################################################################# ### INDEX (table) USE CASES ##### CREATE INDEX index1 ON mysql.user (select_priv); DROP INDEX index1 ON mysql.user; ####################### # verify INDEX grant to user 'none' --echo connecting as user 'none'; connection none; --echo connected; CREATE INDEX index1 ON mysql.user (select_priv); DROP INDEX index1 ON mysql.user; ####################### ################################ ### SHOW VIEW (table)########### ################################################################# --echo connecting as user 'show_view_tbl'; connection show_view_tbl; --echo connected; # continue accumulating grants for the 'none' user GRANT SHOW VIEW ON privdb.v1 TO 'none'@'localhost'; ################################################################# ### SHOW VIEW (table) EXCEPTION CASE ######## # it's asking for a SELECT GRANT but this is wrong --error 1142 SHOW CREATE VIEW privdb.v5; ### SHOW VIEW (table) USE CASE ######## SHOW CREATE VIEW privdb.v1; ####################### # verify SHOW VIEW grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; --error 1142 SHOW CREATE VIEW privdb.v5; SHOW CREATE VIEW privdb.v1; ####################### ############################ ### SHOW VIEW (db)########## ################################################################# --echo connecting as user 'show_view_db'; connection show_view_db; --echo connected; # continue accumulating grants for the 'none' user GRANT SHOW VIEW ON privdb.* TO 'none'@'localhost'; ################################################################# ### SHOW VIEW (db) EXCEPTION CASE #### --error 1142 SHOW CREATE VIEW upgradedb.upgrade_view; ### SHOW VIEW (db) USE CASES ######### --echo connecting as 'none' connection none; --echo connected CREATE USER 'temp_user2'@'localhost' IDENTIFIED BY 'temp_user2'; GRANT SHOW VIEW, SELECT ON privdb.* TO 'temp_user2'@'localhost'; connect (temp_user2, localhost, temp_user2, temp_user2, privdb); show grants; # TBD # These test are currently failing due to bug 20136 # something wrong here. The error being received states # that the user needs a SELECT grant but the invoking user # has select #SHOW CREATE VIEW pribdb.v1; #SHOW CREATE VIEW pribdb.v2; #SHOW CREATE VIEW pribdb.v3; #SHOW CREATE VIEW pribdb.v4; #SHOW CREATE VIEW pribdb.v5; --echo connecting as 'root' connection root; --echo connected REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'temp_user2'@'localhost'; DROP USER 'temp_user2'@'localhost'; ############################### ### SHOW VIEW (GLOBAL)######### ################################################################# --echo connecting as user 'show_view_all'; connection show_view_all; --echo connected; # continue accumulating grants for the 'none' user GRANT SHOW VIEW ON *.* TO 'none'@'localhost'; ################################################################# # TBD # SEE ABOVE ######################## ### ALTER (table) ###### ################################################################ --echo connecting as user 'alter_table'; connection alter_table; --echo connected; # continue accumulating grants for the 'none' user GRANT ALTER ON privdb.t1 TO 'none'@'localhost'; ################################################################ ### ALTER (table) EXCEPTION CASES #### # don't have priv for table t2 --error 1142 ALTER TABLE t2 ADD UNIQUE INDEX index1 (French); ### ALTER (table) USE CASES ######## ALTER TABLE t1 ADD UNIQUE INDEX index1 (c1); ######################## # verify ALTER table grant for user 'none' --echo connecting as user 'none'; connection none; --echo connected ALTER TABLE privdb.t1 DROP INDEX index1; ######################## ######################## ### ALTER (db)########## ################################################################# --echo connecting as user 'alter_db'; connection alter_db; --echo connected; # continue accumulating grants for the 'none' user GRANT ALTER ON privdb.* TO 'none'@'localhost'; ################################################################# ### ALTER (db) EXCEPTION CASES ##### # no grant for upgradedb --error 1044 ALTER DATABASE upgradedb COLLATE latin1_spanish_ci; ### ALTER (db) USE CASES ######## ALTER DATABASE privdb COLLATE latin1_spanish_ci; ALTER TABLE privdb.t2 ADD UNIQUE INDEX index1 (French); ########################## # verify ALTER grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; ALTER DATABASE privdb COLLATE latin1_swedish_ci; ALTER TABLE privdb.t2 DROP INDEX index1; ########################## ########################### ### ALTER (GLOBAL)######### ################################################################# --echo connecting as user 'alter_all'; connection alter_all; --echo connected; # continue accumulating grants for the 'none' user GRANT ALTER ON *.* TO 'none'@'localhost'; ################################################################# ### ALTER (GLOBAL) USE CASES ####### ALTER DATABASE upgrade_drop_me_db COLLATE latin1_spanish_ci; ######################### # verify ALTER global grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; # have to reconnect 'none' before global priv takes effect --error 1044 ALTER DATABASE upgrade_drop_me_db COLLATE latin1_swedish_ci; disconnect none; connect (none, localhost, none, none,); ALTER DATABASE upgrade_drop_me_db COLLATE latin1_swedish_ci; ######################### ################################ ### ALTER ROUTINE (db)########## ################################################################# --echo connecting as user 'alter_rout_db'; connection alter_rout_db; --echo connected; # continue accumulating grants for the 'none' user GRANT ALTER ROUTINE ON privdb.* TO 'none'@'localhost'; ################################################################# ### ALTER ROUTINE (db) EXCEPTION CASE ### # don't have a grant for upgradedb --error 1370 ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY DEFINER; ### ALTER ROUTINE (db) USE CASE ##### ALTER FUNCTION privdb.hello SQL SECURITY INVOKER; ######################## # verify ALTER ROUTINE grant to 'none' --echo connecting as user 'none'; connection none; --echo connected;: ALTER FUNCTION privdb.hello SQL SECURITY DEFINER; ######################## ################################### ### ALTER ROUTINE (GLOBAL)######### ################################################################# --echo connecting as user 'alter_rout_all'; connection alter_rout_all; --echo connected; # continue accumulating grants for the 'none' user GRANT ALTER ROUTINE ON *.* TO 'none'@'localhost'; ################################################################# # not the definer, and don't have select --error 1142 SHOW CREATE TABLE upgrade_drop_me_db.global_proc; ### ALTER ROUTINE (GLOBAL) USE CASE ########## ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY INVOKER; # DEBUG update this once #21244 fixed in both versions --replace_column 3 '*' SHOW CREATE PROCEDURE upgrade_drop_me_db.global_proc; ########################### # verify ALTER ROUTINE grant to use 'none' --echo connecting to use 'none'; connection none; --echo connected; --error 1370 ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY DEFINER; # have to close connection to use 'none' and # reopen for new global priv to take effect disconnect none; --echo connecting to use 'none'; connect (none, localhost, none, none,); --echo connected; ALTER PROCEDURE upgrade_drop_me_db.global_proc SQL SECURITY DEFINER; SHOW CREATE PROCEDURE upgrade_drop_me_db.global_proc; ########################## ######################### ### DROP (table)######### ################################################################# --echo connecting as user 'drop_table'; connection drop_table; --echo connected; # continue accumulating grants for the 'none' user GRANT DROP ON privdb.drop_table TO 'none'@'localhost'; ################################################################# ### DROP (table) EXCEPTION CASES #### # don't have DROP priv for these objects --error 1142 DROP TABLE privdb.t1; --error 1142 DROP VIEW privdb.v1; ### DROP (table) USE CASE ##### DROP TABLE privdb.drop_table; # create more tables to be dropped --echo connecting as user 'root'; connection root; --echo connected; CREATE TABLE privdb.drop_table SELECT * FROM privdb.t1; CREATE TABLE upgrade_drop_me_db.drop_table SELECT * FROM privdb.t1; CREATE TABLE upgrade_drop_me_db.another_drop_table SELECT * FROM privdb.t2; CREATE TABLE privdb.another_drop_table SELECT * FROM privdb.t2; CREATE VIEW upgrade_drop_me_db.drop_view AS SELECT * FROM privdb.t1; ############################ # verify drop grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; # wasn't given a grant for this table --error 1142 DROP TABLE privdb.another_drop_table; DROP TABLE privdb.drop_table; ############################ ######################### ### DROP (db)############ ################################################################# --echo connecting as user 'drop_db'; connection drop_db; --echo connected; # continue accumulating grants for the 'none' user GRANT DROP ON privdb.* TO 'none'@'localhost'; ################################################################# ### DROP (db) EXCEPTION CASE #### --error 1142 DROP TABLE upgrade_drop_me_db.drop_table; --error 1044 DROP DATABASE no_privdb; ### DROP (db) USE CASE ##### DROP TABLE privdb.another_drop_table; DROP VIEW privdb.v8; ############################## #verify drop grant to user 'none' --echo connecting to user 'none'; connection none; --echo connecting; --error 1142 DROP TABLE upgrade_drop_me_db.drop_table; --error 1051 DROP TABLE privdb.drop_table; DROP VIEW privdb.v7; ############################# ############################ ### DROP (GLOBAL)########### ################################################################# --echo connecting as user 'drop_all'; connection drop_all; --echo connected; # continue accumulating grants for the 'none' user GRANT DROP ON *.* TO 'none'@'localhost'; ################################################################# ### DROP (GLOBAL) USE CASES ######### DROP TABLE upgrade_drop_me_db.drop_table; DROP VIEW privdb.v6; DROP DATABASE no_privdb; ######################## # verify DROP grant to user 'none' --echo connecting to user 'none'; connection none; --echo connecting; --error 1142 DROP TABLE upgrade_drop_me_db.another_drop_table; # close connection to user 'none' so new priv takes effect disconnect none; connect (none, localhost, none, none,); DROP TABLE upgrade_drop_me_db.another_drop_table; DROP VIEW upgrade_drop_me_db.drop_view; ######################## ############################ ###DELETE (table)########### ################################################################# --echo connecting as user 'delete_table'; connection delete_table; --echo connected; # continue accumulating grants for the 'none' user GRANT DELETE ON privdb.delete_table TO 'none'@'localhost'; ################################################################# ### DELETE (table) EXCEPTION CASES ###### # no priv for this table --error 1142 DELETE FROM privdb.t1; # need SELECT grant to do this --error 1143 DELETE FROM privdb.delete_table WHERE English='white'; ### DELETE (table) USE CASES ######## DELETE FROM privdb.delete_table; ########################## # verify as user 'none' and create tables again --echo connecting to user 'none'; connection none; --echo connected; SELECT * FROM privdb.delete_table; DROP TABLE privdb.delete_table; CREATE TABLE privdb.delete_table AS SELECT * FROM privdb.t1; CREATE TABLE privdb.another_delete_table AS SELECT * FROM privdb.t2; CREATE TABLE upgrade_drop_me_db.delete_table AS SELECT * FROM privdb.t1; CREATE TABLE upgrade_drop_me_db.another_delete_table AS SELECT * FROM privdb.t2; # verify DELETE grant to user 'none' DELETE FROM privdb.delete_table WHERE English='Red'; SELECT * FROM privdb.delete_table; DELETE FROM privdb.delete_table; DROP TABLE privdb.delete_table; ######################### ########################### ### DELETE (db)############ ################################################################# --echo connecting as user 'delete_db'; connection delete_db; --echo connected; # continue accumulating grants for the 'none' user GRANT DELETE ON privdb.* TO 'none'@'localhost'; ################################################################# ### DELETE (db) EXCEPTION CASES ########### # no priv for this table --error 1142 DELETE FROM upgrade_drop_me_db.delete_table; --error 1143 DELETE FROM privdb.another_delete_table WHERE French='noir'; ### DELETE (db) USE CASES ############# DELETE FROM privdb.another_delete_table; ########################## # verify as user 'none' --echo connecting to user 'none'; connection none; --echo connected; SELECT * FROM privdb.another_delete_table; DROP TABLE privdb.another_delete_table; CREATE TABLE privdb.another_delete_table AS SELECT * FROM privdb.t2; # verify DELETE grant to user 'none' DELETE FROM privdb.another_delete_table WHERE French='noir'; SELECT * FROM privdb.another_delete_table; DELETE FROM privdb.another_delete_table; DROP TABLE privdb.another_delete_table; ######################### ############################# ### DELETE (GLOBAL)########## ################################################################# --echo connecting as user 'delete_all'; connection delete_all; --echo connected; # continue accumulating grants for the 'none' user GRANT DELETE ON *.* TO 'none'@'localhost'; ################################################################# ### DELETE (GLOBAL) EXCEPTION CASE ####### # need SELECT grant for this --error 1143 DELETE FROM upgrade_drop_me_db.delete_table WHERE English='green'; ### DELETE (GLOBAL) USE CASE ########## DELETE FROM upgrade_drop_me_db.delete_table; # verify as user 'none' --echo connecting to user 'none'; connection none; --echo connected; SELECT * FROM upgrade_drop_me_db.delete_table; # need to close connection and reopen so that new # global priv takes effect --error 1142 DELETE FROM upgrade_drop_me_db.another_delete_table WHERE French='noir'; disconnect none; --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; DELETE FROM upgrade_drop_me_db.another_delete_table WHERE French='noir'; DELETE FROM upgrade_drop_me_db.another_delete_table; SELECT * FROM upgrade_drop_me_db.another_delete_table; DROP TABLE upgrade_drop_me_db.delete_table, upgrade_drop_me_db.another_delete_table; ################################ ### LOCK TABLES (db)######### ################################################################# --echo connecting as user 'lock_db'; connection lock_db; --echo connected; # continue accumulating grants for the 'none' user GRANT LOCK TABLES ON privdb.* TO 'none'@'localhost'; ################################################################# ### LOCK TABLES (db) EXCEPTION CASE #### # need to have SELECT grant --error 1142 LOCK TABLE privdb.t1 READ; UNLOCK TABLES; # make a SELECT GRANT --echo connecting as user 'root'; connection root; --echo connected; GRANT SELECT ON privdb.t1 TO 'lock_db'@'localhost'; --echo connnecting as use 'lock_db'; connection lock_db; --echo connected; ### LOCK TABLES (db) USE CASE ######## # TBD - this needs to be enhanced to a true use case when # a way to do it is discovered LOCK TABLES privdb.t1 READ; UNLOCK TABLES; LOCK TABLES privdb.t1 WRITE; UNLOCK TABLES; # revoke SELECT so it can be re-granted in next phase REVOKE SELECT ON privdb.t1 FROM 'lock_db'@'localhost'; ######################## # verify LOCK TABLES grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; LOCK TABLES privdb.t1 READ; UNLOCK TABLES; LOCK TABLES privdb.t1 WRITE; UNLOCK TABLES; ######################## ############################### ### LOCK TABLES (GLOBAL)####### ################################################################# --echo connecting as user 'lock_all'; connection lock_all; --echo connected; # continue accumulating grants for the 'none' user GRANT LOCK TABLES ON *.* TO 'none'@'localhost'; ################################################################# ### LOCK TABLES (GLOBAL) EXCEPTION CASE #### # need to have SELECT grant --error 1142 LOCK TABLE upgrade_drop_me_db.upgrade_drop_me_tbl READ; UNLOCK TABLES; # make a SELECT GRANT --echo connecting as user 'root'; connection root; --echo connected; GRANT SELECT ON upgrade_drop_me_db.* TO 'lock_all'@'localhost'; --echo connecting to user 'lock-all'; connection lock_all; --echo connected ### LOCK TABLES (GLOBAL) USE CASE ######## # TBD - this needs to be enhanced to a true use case when # a way to do it is discovered LOCK TABLES upgrade_drop_me_db.upgrade_drop_me_tbl READ; UNLOCK TABLES; LOCK TABLES upgrade_drop_me_db.upgrade_drop_me_tbl WRITE; UNLOCK TABLES; # revoke SELECT so it can be granted again in next phase REVOKE SELECT ON upgrade_drop_me_db.* FROM 'lock_all'@'localhost'; ######################## # verify LOCK TABLES grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; # have to close/reopen connection to 'none' for # new global priv to take effect --error 1044 LOCK TABLES upgrade_drop_me_db.upgrade_drop_me_tbl READ; disconnect none; connect (none, localhost, none, none,); LOCK TABLES upgrade_drop_me_db.upgrade_drop_me_tbl READ; UNLOCK TABLES; LOCK TABLES upgrade_drop_me_db.upgrade_drop_me_tbl WRITE; UNLOCK TABLES; ######################## ######################## ### RELOAD ############# ################################################################# --echo connecting as user 'reload'; connection reload; --echo connected; # continue accumulating grants for the 'none' user GRANT RELOAD ON *.* TO 'none'@'localhost'; ################################################################# FLUSH HOSTS; FLUSH LOGS; FLUSH PRIVILEGES; FLUSH STATUS; FLUSH TABLES; ######################### ### SHUTDOWN ############ ################################################################# --echo connecting as user 'shutdown'; connection shutdown; --echo connected; # continue accumulating grants for the 'none' user GRANT SHUTDOWN ON *.* TO 'none'@'localhost'; ################################################################# # TBD Need to figure out a way to do this ######################## ### PROCESS ############ ################################################################# --echo connecting as user 'process'; connection process; --echo connected; # continue accumulating grants for the 'none' user GRANT PROCESS ON *.* TO 'none'@'localhost'; ################################################################# ### PROCESS EXCEPTION CASE ######## # only a user with SUPER can do this #REPLACE REAL CONNECTION ID WITH 3 --replace_regex / [0-9]+/ 3/ --error 1095 eval KILL $create_user_connection_id; #KILL 3; ### PROCESS USE CASE ############# # TBD unfortunately there is no way to make this test deterministic # it needs to be tested manually #--replace_column 4 HOST, 5 COMMAND, 6 TIME, 7 STATE, 8 INFO #SHOW PROCESSLIST; ######################## #verify PROCESS grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; # TBD try and make this work #--replace_column 4 HOST, 5 COMMAND, 6 TIME, 7 STATE, 8 INFO #SHOW PROCESSLIST; # close user 'none' connection so new priv takes effect disconnect none; --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; # TBD try and make this work #--replace_column 4 HOST, 5 COMMAND, 6 TIME, 7 STATE, 8 INFO #SHOW PROCESSLIST; ######################## ############################# ### FILE #################### ################################################################# --echo connecting as user 'file'; connection file; --echo connected; # continue accumulating grants for the 'none' user GRANT FILE ON *.* TO 'none'@'localhost'; # close user 'none' connection so new global priv takes effect disconnect none; ################################################################# ### FILE EXCEPTION CASES ############# # can't do this without INSERT GRANT --error 1142 LOAD DATA INFILE '../tmp/privtest-outfile' INTO TABLE privdb.t1; # can't do this without SELECT GRANT --error 1142 SELECT * FROM privdb.t1 INTO OUTFILE '../tmp/privtest-outfile'; # make these grants --echo connecting to user 'root'; connection root; --echo connected; GRANT SELECT, INSERT ON privdb.t1 TO 'file'@'localhost'; ### FILE USE CASES #################### --echo connecting to user 'file'; connection file; --echo connected; SELECT * FROM privdb.t1 INTO OUTFILE '../tmp/privtest-outfile'; --echo connecting as user 'root'; connection root; --echo connected; DELETE FROM privdb.t1; --echo connecting to user 'file'; connection file; --echo connected; LOAD DATA INFILE '../tmp/privtest-outfile' INTO TABLE privdb.t1; # revoke the extra grants to FILE for next phase REVOKE SELECT, INSERT ON privdb.t1 FROM 'file'@'localhost'; ##################################### # verify the FILE grant to user 'none' --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; SELECT * FROM privdb.t1 INTO OUTFILE '../tmp/privtest-outfile2'; DELETE FROM privdb.t1; LOAD DATA INFILE '../tmp/privtest-outfile2' INTO TABLE privdb.t1; SELECT * FROM privdb.t1; ###################################### ############################ ### SHOW DATABASES ######### ################################################################# # verify a new user can't SHOW db's prior to the grant --echo connecting as user 'none'; connection none; --echo connected; GRANT SELECT ON upgrade_drop_me_db.* TO 'show_temp'@'localhost' IDENTIFIED BY 'show_temp'; --echo connecting as 'show-temp' connect (show_temp, localhost, show_temp, show_temp,,); --echo connected # he can show this because of SELECT grant so it will be returned SHOW DATABASES like 'upgrade_drop_me_db'; # he has no grants to this so it shouldn't be returned SHOW DATABASES like 'privdb'; --echo connecting as user 'show_db'; connection show_db; --echo connected; # continue accumulating grants for the 'none' user GRANT SHOW DATABASES ON *.* TO 'none'@'localhost'; # disconnect 'none' so that new global priv takes effect disconnect none; ################################################################# ### SHOW DATABASES USE CASE ####### --echo connecting as user 'show_db'; connection show_db; --echo connected; #This user should be able to show any database SHOW DATABASES like 'mysql'; SHOW DATABASES like 'privdb'; #################### # verify that the new grant to 'none' can be passed to new user 'show_db' --echo connecting as user 'none'; connect (none, localhost, none, none,); --echo connected; GRANT SHOW DATABASES ON *.* TO show_temp; connection show_db; SHOW DATABASES LIKE 'privdb'; --echo connecting as user 'none'; connection none; --echo connected; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'show_temp'@'localhost'; DROP USER 'show_temp'@'localhost'; #################### ######################## ### SUPER ############# ################################################################# --echo connecting as user 'super'; connection super; --echo connected; # continue accumulating grants for the 'none' user GRANT SUPER ON *.* TO 'none'@'localhost'; ################################################################# #REPLACE REAL CONNECTION ID WITH 3 --replace_regex / [0-9]+/ 3/ eval KILL $create_user_connection_id; #KILL 3; ########################### # verify SUPER grant to user 'none' --echo connecting to user 'none'; connection none; --echo connected; #REPLACE REAL CONNECTION ID WITH 4 --replace_regex / [0-9]+/ 4/ --error 1095 eval KILL $create_table_connection_id; #KILL 4; # close connection to user 'none' to activate new grant disconnect none; --echo connecting to user 'none'; connect (none, localhost, none, none,); --echo connected; #REPLACE REAL CONNECTION ID WITH 4 --replace_regex / [0-9]+/ 4/ eval KILL $create_table_connection_id; #KILL 4; ########################### ############################## ### REPLICATION SLAVE ######## ################################################################# --echo connecting as user 'repl_slave'; connection repl_slave; --echo connected; # continue accumulating grants for the 'none' user GRANT REPLICATION SLAVE ON *.* TO 'none'@'localhost'; ################################################################# # TBD ############################### ### REPLICATION CLIENT ######## ################################################################# --echo connecting as user 'repl_client'; connection repl_client; --echo connected; # continue accumulating grants for the 'none' user GRANT REPLICATION CLIENT ON *.* TO 'none'@'localhost'; ################################################################# # TBD ##################################################### # IN TURN REVOKE ALL PRIVILAGES GRANTED TO user 'none' # This will validate that each user can revoke the # privileges that he has been granted # These permissions will be granted again during the # course of the upgrade phase, and then subsequently # revoked again. --echo connecting as user 'create_user'; # OBN connection create_user; connection default; --echo connected; REVOKE CREATE USER ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'select_column'; connection select_column; --echo connected; REVOKE SELECT (English) ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'select_table'; connection select_table; --echo connected; REVOKE SELECT ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'select_db'; connection select_db; --echo connected; REVOKE SELECT ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'select_all'; connection select_all; --echo connected; REVOKE SELECT ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_table'; # OBN connection create_table; connection default; --echo connected; REVOKE CREATE ON privdb.new_table FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_db'; connection create_db; --echo connected; REVOKE CREATE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_all'; connection create_all; --echo connected; REVOKE CREATE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_rout_db'; connection create_rout_db; --echo connected; REVOKE CREATE ROUTINE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_rout_all'; connection create_rout_all; --echo connected; REVOKE CREATE ROUTINE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_view_db'; connection create_view_db; --echo connected; REVOKE SELECT, CREATE VIEW ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none' connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_view_all'; connection create_view_all; --echo connected; REVOKE CREATE VIEW ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none' connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'cre_temptbl_db'; connection cre_temptbl_db; --echo connected; REVOKE CREATE TEMPORARY TABLES ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'cre_temptbl_all'; connection cre_temptbl_all; --echo connected; REVOKE CREATE TEMPORARY TABLES ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'execute_db'; connection execute_db; --echo connected; REVOKE EXECUTE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'execute_all'; connection execute_all; --echo connected; REVOKE EXECUTE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'insert_column'; connection insert_column; --echo connected; REVOKE INSERT (English) ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'insert_table'; connection insert_table; --echo connected; REVOKE INSERT ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'insert_db'; connection insert_db; --echo connected; REVOKE INSERT ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'insert_all'; connection insert_all; --echo connected; REVOKE INSERT ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'references_col'; connection references_col; --echo connected; REVOKE REFERENCES (English) ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'references_tbl'; connection references_tbl; --echo connected; REVOKE REFERENCES ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'references_db'; connection references_db; --echo connected; REVOKE REFERENCES ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'references_all'; connection references_all; --echo connected; REVOKE REFERENCES ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'update_column'; connection update_column; --echo connected; REVOKE UPDATE (English) ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'update_table'; connection update_table; --echo connected; REVOKE UPDATE ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'update_db'; connection update_db; --echo connected; REVOKE UPDATE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'update_all'; connection update_all; --echo connected; REVOKE UPDATE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'index_table'; connection index_table; --echo connected; REVOKE INDEX ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'index_db'; connection index_db; --echo connected; REVOKE INDEX ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'index_all'; connection index_all; --echo connected; REVOKE INDEX ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'show_view_tbl'; connection show_view_tbl; --echo connected; REVOKE SHOW VIEW ON privdb.v1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'show_view_db'; connection show_view_db; --echo connected; REVOKE SHOW VIEW ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'show_view_all'; connection show_view_all; --echo connected; REVOKE SHOW VIEW ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'alter_table'; connection alter_table; --echo connected; REVOKE ALTER ON privdb.t1 FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'alter_db'; connection alter_db; --echo connected; REVOKE ALTER ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'alter_all'; connection alter_all; --echo connected; REVOKE ALTER ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'alter_rout_db'; connection alter_rout_db; --echo connected; REVOKE ALTER ROUTINE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'alter_rout_all'; connection alter_rout_all; --echo connected; REVOKE ALTER ROUTINE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'drop_table'; connection drop_table; --echo connected; REVOKE DROP ON privdb.drop_table FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'drop_db'; connection drop_db; --echo connected; REVOKE DROP ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'drop_all'; connection drop_all; --echo connected; REVOKE DROP ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'delete_table'; connection delete_table; --echo connected; REVOKE DELETE ON privdb.delete_table FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'delete_db'; connection delete_db; --echo connected; REVOKE DELETE ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'delete_all'; connection delete_all; --echo connected; REVOKE DELETE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'lock_db'; connection lock_db; --echo connected; REVOKE LOCK TABLES ON privdb.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'lock_all'; connection lock_all; --echo connected; REVOKE LOCK TABLES ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'reload'; connection reload; --echo connected; REVOKE RELOAD ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'shutdown'; connection shutdown; --echo connected; REVOKE SHUTDOWN ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'process'; connection process; --echo connected; REVOKE PROCESS ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'file'; connection file; --echo connected; REVOKE FILE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'show_db'; connection show_db; --echo connected; REVOKE SHOW DATABASES ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'super'; connection super; --echo connected; REVOKE SUPER ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'repl_client'; connection repl_client; --echo connected; REVOKE REPLICATION CLIENT ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'repl_slave'; connection repl_slave; --echo connected; REVOKE REPLICATION SLAVE ON *.* FROM 'none'@'localhost'; --echo connecting as user 'none'; connection none; --echo connected; SHOW GRANTS; --echo connecting as user 'create_user'; # OBN connection create_user; connection default; --echo connected; # this probably should work but isn't - bug #20059 disconnect none; REVOKE USAGE ON *.* FROM 'none'@'localhost'; # so do this instead DROP USER 'none'@'localhost'; # alter the objects created by 'none' so that they can # still be accessed --echo connecting as 'root' connection root; --echo connected ALTER DEFINER = CURRENT_USER() VIEW privdb.v5 AS SELECT CURRENT_USER(); disconnect create_user; disconnect create_table; disconnect create_db; disconnect create_all; disconnect create_rout_db; disconnect create_rout_all; disconnect create_view_db; disconnect create_view_all; disconnect cre_temptbl_db; disconnect cre_temptbl_all; disconnect select_column; disconnect select_table; disconnect select_db; disconnect select_all; disconnect execute_db; disconnect execute_all; disconnect insert_column; disconnect insert_table; disconnect insert_db; disconnect insert_all; disconnect references_col; disconnect references_tbl; disconnect references_db; disconnect references_all; disconnect update_column; disconnect update_table; disconnect update_db; disconnect update_all; disconnect index_table; disconnect index_db; disconnect index_all; disconnect show_view_tbl; disconnect show_view_db; disconnect show_view_all; disconnect alter_table; disconnect alter_db; disconnect alter_all; disconnect alter_rout_db; disconnect alter_rout_all; disconnect drop_table; disconnect drop_db; disconnect drop_all; disconnect delete_table; disconnect delete_db; disconnect delete_all; disconnect lock_db; disconnect lock_all; disconnect reload; disconnect shutdown; disconnect process; disconnect file; disconnect show_db; disconnect super; disconnect repl_slave; disconnect repl_client; --echo ####################################################### --echo ## END TESTS FOR INITIAL STAGE ###################### --echo ## Proceeding to UPGRADE STAGE ###################### --echo #######################################################