1 files changed, 237 insertions, 0 deletions
diff --git a/mysql-test/main/grant4.test b/mysql-test/main/grant4.test
new file mode 100644
index 00000000000..2715b7c7145
--- /dev/null
+++ b/mysql-test/main/grant4.test
@@ -0,0 +1,237 @@
+--source include/not_embedded.inc
+
+# 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;
+
+
+--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("MySQL 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;