mirror of
https://github.com/MariaDB/server.git
synced 2025-10-26 16:38:11 +01:00
250 lines
13 KiB
Text
250 lines
13 KiB
Text
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 `mysqltest_db1`.`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 `mysqltest_db1`.`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 `mysqltest_db1`.`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 `mysqltest_db1`.`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 COLLATE=latin1_swedish_ci
|
|
|
|
** 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 `mysqltest_db1`.`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 IGNORED
|
|
** 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 `mysqltest_db1`.`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 Ignored
|
|
** 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 `mysqltest_db1`.`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 `mysqltest_db1`.`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 `mysqltest_db1`.`v5`
|
|
SHOW CREATE VIEW v6;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_db1`.`v6`
|
|
SHOW CREATE VIEW v2;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table `mysqltest_db1`.`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;
|
|
# switching from mysql.global_priv to mysql.user
|
|
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;
|
|
flush privileges;
|
|
ERROR HY000: 'mysql.user' is not of type 'TABLE'
|
|
drop view mysql.user;
|
|
create temporary table mysql.user select * from mysql.user1 limit 0;
|
|
flush privileges;
|
|
ERROR 42S02: Table 'mysql.user' doesn't exist
|
|
drop temporary table mysql.user;
|
|
rename table mysql.user1 to mysql.user;
|
|
# switching back from mysql.user to mysql.global_priv
|
|
# switching from mysql.global_priv to mysql.user
|
|
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));
|
|
flush privileges;
|
|
ERROR HY000: Fatal error: mysql.user table is damaged or in unsupported 3.20 format
|
|
drop table mysql.user;
|
|
rename table mysql.user1 to mysql.user;
|
|
# switching back from mysql.user to mysql.global_priv
|
|
End of 5.5 tests
|
|
#
|
|
# 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("MariaDB 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 `mysqltest_db1`.`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 `mysqltest_db1`.`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 mysql_native_password 11111111111111111111111111111111111111111
|
|
foo2 2222222222222222 mysql_old_password 2222222222222222
|
|
foo3 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111
|
|
foo4 2222222222222222 mysql_old_password 2222222222222222
|
|
foo5 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111
|
|
foo6 2222222222222222 mysql_old_password 2222222222222222
|
|
foo7 11111111111111111111111111111111111111111 mysql_native_password 11111111111111111111111111111111111111111
|
|
foo8 2222222222222222 mysql_old_password 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;
|
|
End of 10.1 tests
|