diff options
Diffstat (limited to 'mysql-test/main/grant5.test')
| -rw-r--r-- | mysql-test/main/grant5.test | 423 |
1 files changed, 423 insertions, 0 deletions
diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test new file mode 100644 index 00000000..49e0ab1a --- /dev/null +++ b/mysql-test/main/grant5.test @@ -0,0 +1,423 @@ +-- source include/not_embedded.inc + +# +# MDEV-6625 SHOW GRANTS for current_user_name@wrong_host_name +# +--error ER_NONEXISTING_GRANT +SHOW GRANTS FOR root@invalid_host; + +# +# MDEV-9580 SHOW GRANTS FOR <current_user> fails +# +create user test; +create user foo; +create role foo; +grant foo to test; +--connect (conn_1, localhost, test,,) +set role foo; +show grants for test; # user +show grants for foo; # role +--error ER_DBACCESS_DENIED_ERROR +show grants for foo@'%'; # user +--connection default +drop user test, foo; +drop role foo; + +# +# MDEV-17975 Assertion `! is_set()' or `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon REVOKE under LOCK TABLE +# +CREATE TABLE t1 (a INT); +LOCK TABLE t1 WRITE; +--error ER_TABLE_NOT_LOCKED +REVOKE EXECUTE ON PROCEDURE sp FROM u; +--error ER_TABLE_NOT_LOCKED +REVOKE PROCESS ON *.* FROM u; +DROP TABLE t1; + +# +# MDEV-23010 UPDATE privilege at Database and Table level fail to update with SELECT command denied to user +# +create database mysqltest1; +use mysqltest1; +create table t1(id int); +insert t1 values(2); +create user u1@localhost; +grant select on mysqltest1.t1 to u1@localhost; +grant update on mysqltest1.* to u1@localhost; +connect u1, localhost, u1; +update mysqltest1.t1 set id=1 where id=2; +connection default; +disconnect u1; +drop user u1@localhost; +drop database mysqltest1; + +--echo # +--echo # MDEV-22313: Neither SHOW CREATE USER nor SHOW GRANTS prints a user's default role +--echo # +CREATE ROLE test_role; +CREATE USER test_user; +GRANT test_role TO test_user; +SET DEFAULT ROLE test_role FOR test_user; +SHOW GRANTS FOR test_user; +SET DEFAULT ROLE NONE for test_user; +SHOW GRANTS FOR test_user; +connect test_user, localhost, test_user; +SET ROLE test_role; +SET DEFAULT ROLE test_role; +SHOW GRANTS; +SET DEFAULT ROLE NONE; +SHOW GRANTS; +disconnect test_user; +connection default; +DROP USER test_user; +DROP ROLE test_role; + +# +# End of 10.1 tests +# + +--echo # +--echo # MDEV-20076: SHOW GRANTS does not quote role names properly +--echo # + +create role 'role1'; +create role 'fetch'; +create role 'role-1'; +create role 'rock\'n\'roll'; +create user 'user1'@'localhost'; +create user 'fetch'@'localhost'; +create user 'user-1'@'localhost'; +create user 'O\'Brien'@'localhost'; +grant select on mysql.user to role1; +grant select on mysql.user to 'fetch'; +grant select on mysql.user to 'role-1'; +grant select on mysql.user to 'rock\'n\'roll'; +GRANT 'role1' TO 'user1'@'localhost'; +GRANT 'fetch' TO 'fetch'@'localhost'; +GRANT 'role-1' TO 'user-1'@'localhost'; +GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost'; +show grants for 'role1'; +show grants for 'fetch'; +show grants for 'role-1'; +show grants for 'rock\'n\'roll'; +show grants for 'user1'@'localhost'; +show grants for 'fetch'@'localhost'; +show grants for 'user-1'@'localhost'; +show grants for 'O\'Brien'@'localhost'; +set @save_sql_quote_show_create= @@sql_quote_show_create; +set @@sql_quote_show_create= OFF; +show grants for 'role1'; +show grants for 'fetch'; +show grants for 'role-1'; +show grants for 'rock\'n\'roll'; +show grants for 'user1'@'localhost'; +show grants for 'fetch'@'localhost'; +show grants for 'user-1'@'localhost'; +show grants for 'O\'Brien'@'localhost'; +set @@sql_quote_show_create= @save_sql_quote_show_create; +drop role 'role1'; +drop role 'fetch'; +drop role 'role-1'; +drop role 'rock\'n\'roll'; +drop user 'user1'@'localhost'; +drop user 'fetch'@'localhost'; +drop user 'user-1'@'localhost'; +drop user 'O\'Brien'@'localhost'; + +--echo # +--echo # MDEV-26080 SHOW GRANTS does not quote role names properly for DEFAULT ROLE +--echo # + +CREATE USER 'test-user'; +CREATE ROLE `r``o'l"e`; +select user from mysql.user where is_role='Y'; +GRANT `r``o'l"e` TO 'test-user'; +SET DEFAULT ROLE `r``o'l"e` FOR 'test-user'; +# it is expected that quotes won't be shown correctly +SHOW GRANTS FOR 'test-user'; +DROP ROLE `r``o'l"e`; +DROP USER 'test-user'; + +--echo # +--echo # MDEV-28548: ER_TABLEACCESS_DENIED_ERROR is missing information about DB +--echo # + +create database db1; +create user foo@localhost; +grant create on db1.* to foo@localhost; + +--connect (con1,localhost,foo,,db1) +create table t(t int); +--error ER_TABLEACCESS_DENIED_ERROR +show columns in t; +--error ER_TABLEACCESS_DENIED_ERROR +show columns in db1.t; +# CREATE_VIEW_ACL needed +--error ER_TABLEACCESS_DENIED_ERROR +create view t_v as select * from t; +# show create view needs to have SELECT_ACL and SHOW_VIEW_ACL +--error ER_TABLEACCESS_DENIED_ERROR +show create view t_v; + +create table t2(id int primary key, b int); + +# Reference non existing DB with wrong DB name +--error ER_WRONG_DB_NAME +create table t3(a int, b int, CONSTRAINT `fk_db2_db1_t1` + FOREIGN KEY (a) + REFERENCES `db1 `.t1 (a) + ON DELETE CASCADE + ON UPDATE RESTRICT); + +# Reference non-existing DB (with qualified DB name) +--error ER_TABLEACCESS_DENIED_ERROR +create table t3(a int, b int, CONSTRAINT `fk_db2_db3_t1` + FOREIGN KEY (a) + REFERENCES db3.t1 (a) + ON DELETE CASCADE + ON UPDATE RESTRICT); + +# Reference DB (with not qualified DB name) +--error ER_TABLEACCESS_DENIED_ERROR +create table t1(a int, b int, CONSTRAINT `fk_db2_db3_t1` + FOREIGN KEY (a) + REFERENCES t2 (id) + ON DELETE CASCADE + ON UPDATE RESTRICT); + +--connection default +--disconnect con1 +# Add CREATE_VIEW_ACL and SELECT_ACL +grant create view, select on db1.* to foo@localhost; + +--connect (con1,localhost,foo,,db1) +create view t_v as select * from t; +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +show create view t_v; + +--connection default +--disconnect con1 +# Add SHOW_VIEW_ACL +grant show view on db1.* to foo@localhost; + +--connect (con1,localhost,foo,,db1) +show grants; +show create view t_v; + +--connection default +--disconnect con1 +drop database db1; +drop user foo@localhost; +--echo # +--echo # MDEV-28455: CREATE TEMPORARY TABLES privilege +--echo # is insufficient for SHOW COLUMNS +--echo # + +create database db; +create user foo@localhost; +create user bar@localhost; +create user buz@localhost; +grant create temporary tables on db.* to foo@localhost; +grant create temporary tables on db.* to bar@localhost; + +--connect (con1,localhost,foo,,db) +create temporary table tmp (a int, key(a)); +show tables; +show full tables; +show table status; +show index in tmp; +show columns in tmp; +show full columns in tmp; +--echo # we don't expect to show temporary tables in information_schema.columns +select * from information_schema.columns where table_schema='db'; +--disconnect con1 + +--connect (con1,localhost,bar,,db) +# User doesn't have `select` privilege on table +--error ER_TABLEACCESS_DENIED_ERROR +show full columns in tmp; + +--disconnect con1 + +--connection default +grant select on db.* to bar@localhost; + +--connect (con1,localhost,bar,,db) +# Table doesn't exist for this session +show grants for current_user; +--error ER_NO_SUCH_TABLE +show full columns in tmp; +--disconnect con1 + +--connect (con1,localhost,buz,,) +--error ER_TABLEACCESS_DENIED_ERROR +show columns in db.tmp; +--disconnect con1 + +--connection default +# Cleanup +drop database db; +drop user foo@localhost; +drop user bar@localhost; +drop user buz@localhost; + +CREATE USER foo; +CREATE DATABASE db; +CREATE TABLE db.test_getcolpriv(col1 INT, col2 INT); + +GRANT SELECT (col1,col2) ON db.test_getcolpriv TO foo; +GRANT INSERT (col1) ON db.test_getcolpriv TO foo; + +SHOW GRANTS FOR foo; +REVOKE SELECT (col1,col2) ON db.test_getcolpriv FROM foo; +SHOW GRANTS FOR foo; +REVOKE INSERT (col1) ON db.test_getcolpriv FROM foo; + +SHOW GRANTS FOR foo; +FLUSH PRIVILEGES; +SHOW GRANTS FOR foo; + +DROP USER foo; +DROP DATABASE db; + +--echo # End of 10.3 tests + +# +# MDEV-12321 authentication plugin: SET PASSWORD support +# +error ER_PASSWD_LENGTH; +create user u1@h identified with 'mysql_native_password' using 'pwd'; +create user u1@h identified with 'mysql_native_password' using password('pwd'); +let p=`select password('pwd')`; +eval create user u2@h identified with 'mysql_native_password' using '$p'; +create user u3@h identified with 'mysql_native_password'; +error ER_PASSWD_LENGTH; +set password for u3@h = 'pwd'; +set password for u3@h = password('pwd'); +create user u4@h identified with 'mysql_native_password'; +eval set password for u4@h = '$p'; +error ER_PASSWD_LENGTH; +create user u5@h identified with 'mysql_old_password' using 'pwd'; +create user u5@h identified with 'mysql_old_password' using password('pwd'); +let p=`select old_password('pwd')`; +eval create user u6@h identified with 'mysql_old_password' using '$p'; +create user u7@h identified with 'mysql_old_password'; +error ER_PASSWD_LENGTH; +set password for u7@h = 'pwd'; +set password for u7@h = old_password('pwd'); +create user u8@h identified with 'mysql_old_password'; +eval set password for u8@h = '$p'; +sorted_result; +select user,host,plugin,authentication_string from mysql.user where host='h'; +# test with invalid entries +update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u1'; +update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u5'; +update mysql.global_priv set priv=json_set(priv, '$.plugin', 'nonexistent') where user='u8'; +flush privileges; +show create user u1@h; +show create user u2@h; +show create user u3@h; +show create user u4@h; +show create user u5@h; +show create user u6@h; +show create user u7@h; +show create user u8@h; +grant select on *.* to u1@h; +grant select on *.* to u2@h; +grant select on *.* to u3@h; +grant select on *.* to u4@h; +grant select on *.* to u5@h; +grant select on *.* to u6@h; +grant select on *.* to u7@h; +grant select on *.* to u8@h; +select user,select_priv,plugin,authentication_string from mysql.user where user like 'u_'; + +# but they still can be dropped +drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h; + +# +# MDEV-14735 better matching order for grants +# MDEV-14732 mysql.db privileges evaluated on order of grants rather than hierarchically +# MDEV-8269 Correct fix for Bug #20181776 :- ACCESS CONTROL DOESN'T MATCH MOST SPECIFIC HOST WHEN IT CONTAINS WILDCARD +# +create database mysqltest_1; +create user twg@'%' identified by 'test'; +create table mysqltest_1.t1(id int); + +# MDEV-14732 test case +grant create, drop on `mysqltest_1%`.* to twg@'%'; +grant all privileges on `mysqltest_1`.* to twg@'%'; +connect conn1,localhost,twg,test,mysqltest_1; +insert into t1 values(1); +disconnect conn1; +connection default; + +# prefix%suffix +revoke all privileges, grant option from twg@'%'; +grant create, drop on `mysqlt%`.* to twg@'%'; +grant all privileges on `mysqlt%1`.* to twg@'%'; +connect conn1,localhost,twg,test,mysqltest_1; +insert into t1 values(1); +disconnect conn1; +connection default; + +# more specific can even have a shorter prefix +revoke all privileges, grant option from twg@'%'; +grant create, drop on `mysqlt%`.* to twg@'%'; +grant all privileges on `%mysqltest_1`.* to twg@'%'; +connect conn1,localhost,twg,test,mysqltest_1; +insert into t1 values(1); +disconnect conn1; +connection default; + +drop database mysqltest_1; +drop user twg@'%'; + +# +# test the empty db case +# +insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select'); +flush privileges; +delete from mysql.tables_priv where db=''; + +# +# MDEV-21560 Assertion `grant_table || grant_table_role' failed in check_grant_all_columns +# +create database db; +create table db.t1 (a int); +insert into db.t1 values (1); +create user foo; +grant delete on db.* to foo; +--connect (con1,localhost,foo,,) +show create table db.t1; +--error ER_COLUMNACCESS_DENIED_ERROR +delete from db.t1 returning *; +--disconnect con1 +--connection default +drop database db; +drop user foo; + +# +# MDEV-23009 SIGSEGV in get_field from acl_load (on optimized builds) +# +call mtr.add_suppression('mysql.host table is damaged'); +create table mysql.host (c1 int); +insert mysql.host values (1); +--error ER_UNKNOWN_ERROR +flush privileges; +drop table mysql.host; + +--echo # +--echo # MDEV-30826 Invalid data on mysql.host segfaults the server after an upgrade to 10.4 +--echo # + +# from mysql_system_tables.sql in 10.3: +create table mysql.host (host char(60) binary default '' not null, db char(64) binary default '' not null, select_priv enum('n','y') collate utf8_general_ci default 'n' not null, insert_priv enum('n','y') collate utf8_general_ci default 'n' not null, update_priv enum('n','y') collate utf8_general_ci default 'n' not null, delete_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_priv enum('n','y') collate utf8_general_ci default 'n' not null, drop_priv enum('n','y') collate utf8_general_ci default 'n' not null, grant_priv enum('n','y') collate utf8_general_ci default 'n' not null, references_priv enum('n','y') collate utf8_general_ci default 'n' not null, index_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_tmp_table_priv enum('n','y') collate utf8_general_ci default 'n' not null, lock_tables_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, show_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, execute_priv enum('n','y') collate utf8_general_ci default 'n' not null, trigger_priv enum('n','y') collate utf8_general_ci default 'n' not null, primary key /*host*/ (host,db)) engine=myisam character set utf8 collate utf8_bin comment='host privileges; merged with database privileges'; +insert mysql.host values('10.5.0.0/255.255.0.0','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N'); +flush privileges; +drop table mysql.host; + +--echo # +--echo # End of 10.4 tests +--echo # |