-- source include/have_ndb.inc # Test of GRANT commands # Cleanup --disable_warnings drop table if exists t1; --enable_warnings SET NAMES binary; # # Alter mysql system tables to ndb # make sure you alter all back in the end # use mysql; alter table columns_priv engine=ndb; alter table db engine=ndb; alter table func engine=ndb; alter table help_category engine=ndb; alter table help_keyword engine=ndb; alter table help_relation engine=ndb; alter table help_topic engine=ndb; alter table host engine=ndb; alter table tables_priv engine=ndb; alter table time_zone engine=ndb; alter table time_zone_leap_second engine=ndb; alter table time_zone_name engine=ndb; alter table time_zone_transition engine=ndb; alter table time_zone_transition_type engine=ndb; alter table user engine=ndb; use test; # # Test that SSL options works properly # delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; flush privileges; begin; grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; commit; show grants for mysqltest_1@localhost; begin; grant delete on mysqltest.* to mysqltest_1@localhost; commit; select * from mysql.user where user="mysqltest_1"; show grants for mysqltest_1@localhost; begin; revoke delete on mysqltest.* from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; begin; grant select on mysqltest.* to mysqltest_1@localhost require NONE; commit; show grants for mysqltest_1@localhost; begin; grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; commit; show grants for mysqltest_1@localhost; begin; revoke all privileges on mysqltest.* from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; flush privileges; # # Test that the new db privileges are stored/retrieved correctly # begin; grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; flush privileges; show grants for mysqltest_1@localhost; begin; revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; begin; grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; commit; flush privileges; show grants for mysqltest_1@localhost; begin; revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; begin; revoke all privileges on mysqltest.* from mysqltest_1@localhost; commit; delete from mysql.user where user='mysqltest_1'; flush privileges; begin; grant usage on test.* to mysqltest_1@localhost with grant option; commit; show grants for mysqltest_1@localhost; delete from mysql.user where user='mysqltest_1'; delete from mysql.db where user='mysqltest_1'; delete from mysql.tables_priv where user='mysqltest_1'; delete from mysql.columns_priv where user='mysqltest_1'; flush privileges; --error 1141 show grants for mysqltest_1@localhost; # # Test what happens when you have same table and colum level grants # create table t1 (a int); begin; GRANT select,update,insert on t1 to mysqltest_1@localhost; GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; begin; REVOKE select (a), update on t1 from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; begin; REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; begin; GRANT select,references on t1 to mysqltest_1@localhost; commit; select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; begin; grant all on test.* to mysqltest_3@localhost with grant option; revoke all on test.* from mysqltest_3@localhost; commit; show grants for mysqltest_3@localhost; begin; revoke grant option on test.* from mysqltest_3@localhost; commit; show grants for mysqltest_3@localhost; begin; grant all on test.t1 to mysqltest_2@localhost with grant option; revoke all on test.t1 from mysqltest_2@localhost; commit; show grants for mysqltest_2@localhost; begin; revoke grant option on test.t1 from mysqltest_2@localhost; commit; show grants for mysqltest_2@localhost; delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; flush privileges; drop table t1; # # Test some error conditions # begin; --error 1221 GRANT FILE on mysqltest.* to mysqltest_1@localhost; commit; select 1; -- To test that the previous command didn't cause problems # # Bug#6123: GRANT USAGE inserts useless Db row # create database mysqltest1; begin; grant usage on mysqltest1.* to test6123 identified by 'magic123'; commit; select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1"; delete from mysql.user where user='test6123'; drop database mysqltest1; # # Test for 'drop user', 'revoke privileges, grant' # create table t1 (a int); begin; grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; commit; show grants for drop_user2@localhost; begin; revoke all privileges, grant option from drop_user2@localhost; commit; drop user drop_user2@localhost; begin; grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; grant select(a) on test.t1 to drop_user@localhost; commit; show grants for drop_user@localhost; # # Bug3086 # set sql_mode=ansi_quotes; show grants for drop_user@localhost; set sql_mode=default; set sql_quote_show_create=0; show grants for drop_user@localhost; set sql_mode="ansi_quotes"; show grants for drop_user@localhost; set sql_quote_show_create=1; show grants for drop_user@localhost; set sql_mode=""; show grants for drop_user@localhost; revoke all privileges, grant option from drop_user@localhost; show grants for drop_user@localhost; drop user drop_user@localhost; begin; --error 1269 revoke all privileges, grant option from drop_user@localhost; commit; begin; grant select(a) on test.t1 to drop_user1@localhost; commit; flush privileges; begin; grant select on test.t1 to drop_user2@localhost; grant select on test.* to drop_user3@localhost; grant select on *.* to drop_user4@localhost; commit; flush privileges; # Drop user now implicitly revokes all privileges. drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; begin; --error 1269 revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; commit; flush privileges; #--error 1268 drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; drop table t1; begin; grant usage on *.* to mysqltest_1@localhost identified by "password"; grant select, update, insert on test.* to mysqltest_1@localhost; commit; show grants for mysqltest_1@localhost; drop user mysqltest_1@localhost; # # Bug #3403 Wrong encodin in SHOW GRANTS output # SET NAMES koi8r; CREATE DATABASE ��; USE ��; CREATE TABLE ��� (��� int); begin; GRANT SELECT ON ��.* TO ����@localhost; commit; SHOW GRANTS FOR ����@localhost; begin; REVOKE SELECT ON ��.* FROM ����@localhost; commit; begin; GRANT SELECT ON ��.��� TO ����@localhost; commit; SHOW GRANTS FOR ����@localhost; begin; REVOKE SELECT ON ��.��� FROM ����@localhost; commit; begin; GRANT SELECT (���) ON ��.��� TO ����@localhost; commit; SHOW GRANTS FOR ����@localhost; begin; REVOKE SELECT (���) ON ��.��� FROM ����@localhost; commit; DROP DATABASE ��; SET NAMES latin1; # # Bug #5831: REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything # USE test; CREATE TABLE t1 (a int ); CREATE TABLE t2 LIKE t1; CREATE TABLE t3 LIKE t1; CREATE TABLE t4 LIKE t1; CREATE TABLE t5 LIKE t1; CREATE TABLE t6 LIKE t1; CREATE TABLE t7 LIKE t1; CREATE TABLE t8 LIKE t1; CREATE TABLE t9 LIKE t1; CREATE TABLE t10 LIKE t1; CREATE DATABASE testdb1; CREATE DATABASE testdb2; CREATE DATABASE testdb3; CREATE DATABASE testdb4; CREATE DATABASE testdb5; CREATE DATABASE testdb6; CREATE DATABASE testdb7; CREATE DATABASE testdb8; CREATE DATABASE testdb9; CREATE DATABASE testdb10; begin; GRANT ALL ON testdb1.* TO testuser@localhost; GRANT ALL ON testdb2.* TO testuser@localhost; GRANT ALL ON testdb3.* TO testuser@localhost; GRANT ALL ON testdb4.* TO testuser@localhost; GRANT ALL ON testdb5.* TO testuser@localhost; GRANT ALL ON testdb6.* TO testuser@localhost; GRANT ALL ON testdb7.* TO testuser@localhost; GRANT ALL ON testdb8.* TO testuser@localhost; GRANT ALL ON testdb9.* TO testuser@localhost; GRANT ALL ON testdb10.* TO testuser@localhost; GRANT SELECT ON test.t1 TO testuser@localhost; GRANT SELECT ON test.t2 TO testuser@localhost; GRANT SELECT ON test.t3 TO testuser@localhost; GRANT SELECT ON test.t4 TO testuser@localhost; GRANT SELECT ON test.t5 TO testuser@localhost; GRANT SELECT ON test.t6 TO testuser@localhost; GRANT SELECT ON test.t7 TO testuser@localhost; GRANT SELECT ON test.t8 TO testuser@localhost; GRANT SELECT ON test.t9 TO testuser@localhost; GRANT SELECT ON test.t10 TO testuser@localhost; GRANT SELECT (a) ON test.t1 TO testuser@localhost; GRANT SELECT (a) ON test.t2 TO testuser@localhost; GRANT SELECT (a) ON test.t3 TO testuser@localhost; GRANT SELECT (a) ON test.t4 TO testuser@localhost; GRANT SELECT (a) ON test.t5 TO testuser@localhost; GRANT SELECT (a) ON test.t6 TO testuser@localhost; GRANT SELECT (a) ON test.t7 TO testuser@localhost; GRANT SELECT (a) ON test.t8 TO testuser@localhost; GRANT SELECT (a) ON test.t9 TO testuser@localhost; GRANT SELECT (a) ON test.t10 TO testuser@localhost; commit; begin; REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; commit; SHOW GRANTS FOR testuser@localhost; DROP USER testuser@localhost; DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; DROP DATABASE testdb1; DROP DATABASE testdb2; DROP DATABASE testdb3; DROP DATABASE testdb4; DROP DATABASE testdb5; DROP DATABASE testdb6; DROP DATABASE testdb7; DROP DATABASE testdb8; DROP DATABASE testdb9; DROP DATABASE testdb10; # # just SHOW PRIVILEGES test # SHOW PRIVILEGES; # # Alter mysql system tables back to myisam # use mysql; alter table columns_priv engine=myisam; alter table db engine=myisam; alter table func engine=myisam; alter table help_category engine=myisam; alter table help_keyword engine=myisam; alter table help_relation engine=myisam; alter table help_topic engine=myisam; alter table host engine=myisam; alter table tables_priv engine=myisam; alter table time_zone engine=myisam; alter table time_zone_leap_second engine=myisam; alter table time_zone_name engine=myisam; alter table time_zone_transition engine=myisam; alter table time_zone_transition_type engine=myisam; alter table user engine=myisam; use test; flush privileges; # End of 4.1 tests