diff options
Diffstat (limited to 'mysql-test/main/grant4.result')
| -rw-r--r-- | mysql-test/main/grant4.result | 226 |
1 files changed, 226 insertions, 0 deletions
diff --git a/mysql-test/main/grant4.result b/mysql-test/main/grant4.result new file mode 100644 index 00000000000..c3db2e03e3e --- /dev/null +++ b/mysql-test/main/grant4.result @@ -0,0 +1,226 @@ +drop database if exists mysqltest_db1; +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; +** Connect as restricted user mysqltest_u1. + +connect con1,localhost,mysqltest_u1,,; +connection con1; +** Test column level privileges only. No SELECT privileges on the table. +** INSERT INTO ... VALUES ... +** Attempting to insert values to a table with only column privileges +** should work. +insert into mysqltest_db1.t_column_priv_only (a) VALUES (1); + +** SHOW COLUMNS +** 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'; +Field Type Null Key Default Extra +a int(11) YES NULL +show columns from mysqltest_db1.t_column_priv_only; +Field Type Null Key Default Extra +a int(11) YES NULL +** SHOW COLUMNS +** Should fail because there are no privileges on any column combination. +show columns from mysqltest_db1.t_no_priv; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' +** 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'; +Field Type Null Key Default Extra + +** CREATE TABLE ... LIKE ... require SELECT privleges and will fail. +create table test.t_no_priv like mysqltest_db1.column_priv_only; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'column_priv_only' + +** Just to be sure... SELECT also fails. +select * from mysqltest_db1.t_column_priv_only; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' + +** SHOW CREATE TABLE ... require any privileges on all columns (the entire table). +** First we try and fail on a table with only one column privilege. +show create table mysqltest_db1.t_column_priv_only; +ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_column_priv_only' + +** Now we do the same on a table with SELECT privileges. + +** SHOW COLUMNS +** Success because we got some privileges on the table (SELECT_ACL) +show columns from mysqltest_db1.t_select_priv; +Field Type Null Key Default Extra +a int(11) YES NULL +b int(11) YES NULL + +** CREATE TABLE ... LIKE ... require SELECT privleges and will SUCCEED. +drop table if exists test.t_duplicated; +create table test.t_duplicated like mysqltest_db1.t_select_priv; +drop table test.t_duplicated; + +** 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; +Table Create Table +t_select_priv CREATE TABLE `t_select_priv` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +** SHOW CREATE TABLE will fail if there is no grants at all: +show create table mysqltest_db1.t_no_priv; +ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't_no_priv' + +connection default; +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; +** Connect as restricted user mysqltest_u1. +** SELECT FROM INFORMATION_SCHEMA.STATISTICS will succeed because any privileges will do (authentication is enough). +** but will return no rows +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='t5'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +** SHOW INDEX FROM t5 will fail because we don't have any privileges on any column combination. +SHOW INDEX FROM t5; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' +** SHOW INDEX FROM t6 will succeed because there exist a privilege on a column combination on t6. +SHOW INDEX FROM t6; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t6 1 i 1 s1 A NULL NULL NULL YES BTREE +** CHECK TABLE requires any privilege on any column combination and should succeed for t6: +CHECK TABLE t6; +Table Op Msg_type Msg_text +mysqltest_db1.t6 check status OK +** With no privileges access is naturally denied: +CHECK TABLE t5; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't5' +** CHECKSUM TABLE requires SELECT privileges on the table. The following should fail: +CHECKSUM TABLE t6; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't6' +** And this should work: +CHECKSUM TABLE t_select_priv; +Table Checksum +mysqltest_db1.t_select_priv 0 +SHOW CREATE VIEW v5; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v5' +SHOW CREATE VIEW v6; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v6' +SHOW CREATE VIEW v2; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v2' +SHOW CREATE VIEW v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t_select_priv`.`a` AS `a`,`t_select_priv`.`b` AS `b` from `t_select_priv` latin1 latin1_swedish_ci +connection default; +disconnect con1; +drop database mysqltest_db1; +drop user mysqltest_u1@localhost; +# +# Additional coverage for refactoring which is made as part +# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +# to allow temp table operations". +# +# Check that for statements like CHECK/REPAIR and OPTIMIZE TABLE +# privileges for all tables involved are checked before processing +# any tables. Doing otherwise, i.e. checking privileges for table +# right before processing it might result in lost results for tables +# which were processed by the time when table for which privileges +# are insufficient are discovered. +# +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"); +drop database if exists mysqltest_db1; +create database mysqltest_db1; +# 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); +# 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; +# Corrupt t1 by replacing t1.MYI with a corrupt + unclosed one created +# by doing: 'create table t1 (a int key(a))' +# head -c1024 t1.MYI > corrupt_t1.MYI +flush table mysqltest_db1.t1; +connection con1; +check table mysqltest_db1.t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly +mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 +mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 +mysqltest_db1.t1 check error Corrupt +# The below statement should fail before repairing t1. +# Otherwise info about such repair will be missing from its result-set. +repair table mysqltest_db1.t1, mysqltest_db1.t2; +ERROR 42000: SELECT, INSERT command denied to user 'mysqltest_u1'@'localhost' for table 't2' +# The same is true for CHECK TABLE statement. +check table mysqltest_db1.t1, mysqltest_db1.t2; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't2' +check table mysqltest_db1.t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 check warning Table is marked as crashed +mysqltest_db1.t1 check warning 1 client is using or hasn't closed the table properly +mysqltest_db1.t1 check error Size of indexfile is: 1024 Should be: 2048 +mysqltest_db1.t1 check warning Size of datafile is: 14 Should be: 7 +mysqltest_db1.t1 check error Corrupt +repair table mysqltest_db1.t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 repair warning Number of rows changed from 1 to 2 +mysqltest_db1.t1 repair status OK +# Clean-up. +disconnect con1; +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'; +select user,password,plugin,authentication_string from mysql.user where user like 'foo%'; +user password plugin authentication_string +foo1 11111111111111111111111111111111111111111 +foo2 2222222222222222 +foo3 11111111111111111111111111111111111111111 +foo4 2222222222222222 +foo5 11111111111111111111111111111111111111111 +foo6 2222222222222222 +foo7 11111111111111111111111111111111111111111 +foo8 2222222222222222 +drop user foo1; +drop user foo2; +drop user foo3; +drop user foo4; +drop user foo5; +drop user foo6; +drop user foo7; +drop user foo8; +create user foo1 identified via mysql_native_password using '00'; +ERROR HY000: Password hash should be a 41-digit hexadecimal number +create user foo2 identified via mysql_native_password using '2222222222222222'; +ERROR HY000: Password hash should be a 41-digit hexadecimal number +create user foo3 identified via mysql_old_password using '00'; +ERROR HY000: Password hash should be a 16-digit hexadecimal number +create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; +ERROR HY000: Password hash should be a 16-digit hexadecimal number +set GLOBAL sql_mode=default; |