diff options
Diffstat (limited to 'mysql-test/main/grant4.test')
| -rw-r--r-- | mysql-test/main/grant4.test | 274 |
1 files changed, 274 insertions, 0 deletions
diff --git a/mysql-test/main/grant4.test b/mysql-test/main/grant4.test new file mode 100644 index 00000000..cda1c5fb --- /dev/null +++ b/mysql-test/main/grant4.test @@ -0,0 +1,274 @@ +--source include/not_embedded.inc + +--disable_service_connection + +# Setup database, tables and user accounts +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; +create database mysqltest_db1; +use mysqltest_db1; +create table t_column_priv_only (a int, b int); +create table t_select_priv like t_column_priv_only; +create table t_no_priv like t_column_priv_only; +grant all privileges on test.* to mysqltest_u1@localhost; +grant insert (a) on mysqltest_db1.t_column_priv_only to mysqltest_u1@localhost; +grant select on mysqltest_db1.t_select_priv to mysqltest_u1@localhost; + +--echo ** Connect as restricted user mysqltest_u1. +--echo +connect (con1,localhost,mysqltest_u1,,); +connection con1; + +######################################################################## +--echo ** Test column level privileges only. No SELECT privileges on the table. +--echo ** INSERT INTO ... VALUES ... +--echo ** Attempting to insert values to a table with only column privileges +--echo ** should work. +insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); +--echo + +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Should succeed because we have privileges (any) on at least one of the columns. +select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_column_priv_only'; +show columns from mysqltest_db1.t_column_priv_only; +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Should fail because there are no privileges on any column combination. +--error 1142 +show columns from mysqltest_db1.t_no_priv; +--echo ** However, select from I_S.COLUMNS will succeed but not show anything: +select column_name as 'Field',column_type as 'Type',is_nullable as 'Null',column_key as 'Key',column_default as 'Default',extra as 'Extra' from information_schema.columns where table_schema='mysqltest_db1' and table_name='t_no_priv'; +--echo +######################################################################### +--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. +--error 1142 +create table test.t_no_priv like mysqltest_db1.column_priv_only; +--echo +######################################################################### +--echo ** Just to be sure... SELECT also fails. +--error 1142 +select * from mysqltest_db1.t_column_priv_only; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). +--echo ** First we try and fail on a table with only one column privilege. +--error 1142 +show create table mysqltest_db1.t_column_priv_only; +--echo +######################################################################### +--echo ** Now we do the same on a table with SELECT privileges. +--echo +######################################################################### +--echo ** SHOW COLUMNS +--echo ** Success because we got some privileges on the table (SELECT_ACL) +show columns from mysqltest_db1.t_select_priv; +--echo +######################################################################### +--echo ** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. +--disable_warnings +drop table if exists test.t_duplicated; +--enable_warnings +create table test.t_duplicated like mysqltest_db1.t_select_priv; +drop table test.t_duplicated; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE will succeed because we have a privilege on all columns in the table (table-level privilege). +show create table mysqltest_db1.t_select_priv; +--echo +######################################################################### +--echo ** SHOW CREATE TABLE will fail if there is no grants at all: +--error 1142 +show create table mysqltest_db1.t_no_priv; +--echo + +connection default; + +# +# SHOW INDEX +# +use mysqltest_db1; +CREATE TABLE t5 (s1 INT); +CREATE INDEX i ON t5 (s1); +CREATE TABLE t6 (s1 INT, s2 INT); +CREATE VIEW v5 AS SELECT * FROM t5; +CREATE VIEW v6 AS SELECT * FROM t6; +CREATE VIEW v2 AS SELECT * FROM t_select_priv; +CREATE VIEW v3 AS SELECT * FROM t_select_priv; +CREATE INDEX i ON t6 (s1); +GRANT UPDATE (s2) ON t6 to mysqltest_u1@localhost; +GRANT UPDATE (s2) ON v6 to mysqltest_u1@localhost; +GRANT SHOW VIEW ON v2 to mysqltest_u1@localhost; +GRANT SHOW VIEW, SELECT ON v3 to mysqltest_u1@localhost; + +connection con1; +use mysqltest_db1; +--echo ** Connect as restricted user mysqltest_u1. +--echo ** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). +--echo ** but will return no rows +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; +# +# Bug27145 EXTRA_ACL trouble +# +--echo ** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. +--error 1142 +SHOW INDEX FROM t5; +--echo ** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. +SHOW INDEX FROM t6; + +# CHECK TABLE +--echo ** CHECK TABLE requires any privilege on any column combination and should succeed for t6: +CHECK TABLE t6; +--echo ** With no privileges access is naturally denied: +--error 1142 +CHECK TABLE t5; + +# CHECKSUM +--echo ** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: +--error 1142 +CHECKSUM TABLE t6; +--echo ** And this should work: +CHECKSUM TABLE t_select_priv; + +# SHOW CREATE VIEW +--error 1142 +SHOW CREATE VIEW v5; +--error 1142 +SHOW CREATE VIEW v6; +--error 1142 +SHOW CREATE VIEW v2; +SHOW CREATE VIEW v3; + +connection default; +disconnect con1; +drop database mysqltest_db1; +drop user mysqltest_u1@localhost; + +# +# MDEV-18241 Downgrade from 10.4 to 10.3 crashes +# +source include/switch_to_mysql_user.inc; +call mtr.add_suppression("Table 'mysql.user' doesn't exist"); +call mtr.add_suppression("'mysql.user' is not of type 'TABLE'"); +rename table mysql.user to mysql.user1; +create view mysql.user as select * from mysql.user1; +--error ER_WRONG_OBJECT +flush privileges; +drop view mysql.user; +create temporary table mysql.user select * from mysql.user1 limit 0; +--error ER_NO_SUCH_TABLE +flush privileges; +drop temporary table mysql.user; +rename table mysql.user1 to mysql.user; +source include/switch_to_mysql_global_priv.inc; + +# +# Bug#28986737: RENAMING AND REPLACING MYSQL.USER TABLE CAN LEAD TO A SERVER CRASH +# +source include/switch_to_mysql_user.inc; +call mtr.add_suppression('mysql.user table is damaged'); +rename table mysql.user to mysql.user1; +create table mysql.user (Host char(100), User char(100)); +--error ER_UNKNOWN_ERROR +flush privileges; +drop table mysql.user; +rename table mysql.user1 to mysql.user; +source include/switch_to_mysql_global_priv.inc; + +--echo End of 5.5 tests + +--echo # +--echo # Additional coverage for refactoring which is made as part +--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +--echo # to allow temp table operations". +--echo # +--echo # Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE +--echo # privileges for all tables involved are checked before processing +--echo # any tables. Doing otherwise, i.e. checking privileges for table +--echo # right before processing it might result in lost results for tables +--echo # which were processed by the time when table for which privileges +--echo # are insufficient are discovered. +--echo # +call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); +call mtr.add_suppression("MariaDB thread id .*, query id .* localhost.*mysqltest_u1 Checking table"); +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +let $MYSQLD_DATADIR = `SELECT @@datadir`; +create database mysqltest_db1; +--echo # Create tables which we are going to CHECK/REPAIR. +create table mysqltest_db1.t1 (a int, key(a)) engine=myisam; +create table mysqltest_db1.t2 (b int); +insert into mysqltest_db1.t1 values (1), (2); +insert into mysqltest_db1.t2 values (1); +--echo # Create user which will try to do this. +create user mysqltest_u1@localhost; +grant insert, select on mysqltest_db1.t1 to mysqltest_u1@localhost; +connect (con1,localhost,mysqltest_u1,,); +connection default; + +--echo # Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created +--echo # by doing: 'create table t1 (a int key(a))' +--echo # head -c1024 t1.MYI > corrupt_t1.MYI +flush table mysqltest_db1.t1; +--remove_file $MYSQLD_DATADIR/mysqltest_db1/t1.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/mysqltest_db1/t1.MYI + +connection con1; +check table mysqltest_db1.t1; +--echo # The below statement should fail before repairing t1. +--echo # Otherwise info about such repair will be missing from its result-set. +--error ER_TABLEACCESS_DENIED_ERROR +repair table mysqltest_db1.t1, mysqltest_db1.t2; +--echo # The same is true for CHECK TABLE statement. +--error ER_TABLEACCESS_DENIED_ERROR +check table mysqltest_db1.t1, mysqltest_db1.t2; +check table mysqltest_db1.t1; +repair table mysqltest_db1.t1; + +--echo # Clean-up. +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; +drop database mysqltest_db1; +drop user mysqltest_u1@localhost; + +create user foo1 identified by password '11111111111111111111111111111111111111111'; +create user foo2 identified by password '2222222222222222'; +create user foo3 identified via mysql_native_password using '11111111111111111111111111111111111111111'; +create user foo4 identified via mysql_old_password using '2222222222222222'; + +grant select on test.* to foo5 identified by password '11111111111111111111111111111111111111111'; +grant select on test.* to foo6 identified by password '2222222222222222'; +grant select on test.* to foo7 identified via mysql_native_password using '11111111111111111111111111111111111111111'; +grant select on test.* to foo8 identified via mysql_old_password using '2222222222222222'; + +--sorted_result +select user,password,plugin,authentication_string from mysql.user where user like 'foo%'; + +drop user foo1; +drop user foo2; +drop user foo3; +drop user foo4; +drop user foo5; +drop user foo6; +drop user foo7; +drop user foo8; + +--error ER_PASSWD_LENGTH +create user foo1 identified via mysql_native_password using '00'; +--error ER_PASSWD_LENGTH +create user foo2 identified via mysql_native_password using '2222222222222222'; +--error ER_PASSWD_LENGTH +create user foo3 identified via mysql_old_password using '00'; +--error ER_PASSWD_LENGTH +create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; + +set GLOBAL sql_mode=default; + +--echo End of 10.1 tests + +--enable_service_connection |