diff options
Diffstat (limited to 'mysql-test/r/grant2.result')
| -rw-r--r-- | mysql-test/r/grant2.result | 196 |
1 files changed, 193 insertions, 3 deletions
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 6b6bb697306..ca375ef5057 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -6,16 +6,42 @@ delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; +grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; +create user mysqltest_2@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +ERROR 42000: You must have privileges to update tables in the mysql database to be able to change passwords for others +grant update on mysql.* to mysqltest_1@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +grant select on `my\_1`.* to mysqltest_3@localhost; +grant insert on mysql.* to mysqltest_1@localhost; +grant select on `my\_1`.* to mysqltest_3@localhost; +grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; select current_user(); current_user() mysqltest_1@localhost grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%' +set @@sql_mode='NO_AUTO_CREATE_USER'; +select @@sql_mode; +@@sql_mode +NO_AUTO_CREATE_USER +grant select on `my\_1`.* to mysqltest_4@localhost with grant option; +ERROR 42000: Can't find any matching row in the user table +grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' +with grant option; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost' GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION show grants for mysqltest_2@localhost; Grants for mysqltest_2@localhost @@ -33,6 +59,7 @@ current_user() mysqltest_1@localhost show databases; Database +information_schema mysqltest_1 test grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; @@ -56,9 +83,9 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' insert into t1 values (1, 'I can''t change it!'); update t1 set data='I can change it!' where id = 1; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' select * from t1; id data 1 I can't change it! @@ -80,6 +107,169 @@ delete from mysql.columns_priv where user like 'mysqltest\_%'; flush privileges; drop database mysqltest; use test; +set sql_mode='maxdb'; +drop table if exists t1, t2; +create table t1(c1 int); +create table t2(c1 int, c2 int); +create user 'mysqltest_1'; +create user 'mysqltest_1'; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%' +create user 'mysqltest_2' identified by 'Mysqltest-2'; +create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; +grant select on *.* to 'mysqltest_2'; +grant insert on test.* to 'mysqltest_2'; +grant update on test.t1 to 'mysqltest_2'; +grant update (c2) on test.t2 to 'mysqltest_2'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_1 +% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_2 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_2 t1 +% test mysqltest_2 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_2 t2 c2 +show grants for 'mysqltest_1'; +Grants for mysqltest_1@% +GRANT USAGE ON *.* TO 'mysqltest_1'@'%' +show grants for 'mysqltest_2'; +Grants for mysqltest_2@% +GRANT SELECT ON *.* TO 'mysqltest_2'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO 'mysqltest_2'@'%' +GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_2'@'%' +GRANT UPDATE ON "test"."t1" TO 'mysqltest_2'@'%' +drop user 'mysqltest_1'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_2 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_2 t1 +% test mysqltest_2 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_2 t2 c2 +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +rename user 'mysqltest_2' to 'mysqltest_1'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_1 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_1 t1 +% test mysqltest_1 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_1 t2 c2 +show grants for 'mysqltest_1'; +Grants for mysqltest_1@% +GRANT SELECT ON *.* TO 'mysqltest_1'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO 'mysqltest_1'@'%' +GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%' +GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%' +drop user 'mysqltest_1', 'mysqltest_3'; +drop user 'mysqltest_1'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%' +drop table t1, t2; +insert into mysql.db set user='mysqltest_1', db='%', host='%'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +revoke all privileges, grant option from 'mysqltest_1'; +ERROR HY000: Can't revoke all privileges, grant for one or more of the requested users +drop user 'mysqltest_1'; +select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user; +host db user +insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +drop user 'mysqltest_1'; +select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' order by host,db,user,table_name; +host db user table_name +insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +drop user 'mysqltest_1'; +select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' order by host,db,user,table_name,column_name; +host db user table_name column_name +create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; +rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a'; +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%' +drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a'; +create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a'; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%' +rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b'; +ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%' +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%' +create user 'mysqltest_2' identified by 'Mysqltest-2'; +drop user 'mysqltest_2' identified by 'Mysqltest-2'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'Mysqltest-2'' at line 1 +create user '%@b'@'b'; +show grants for '%@b'@'b'; +Grants for %@b@b +GRANT USAGE ON *.* TO '%@b'@'b' +grant select on mysql.* to '%@b'@'b'; +show grants for '%@b'@'b'; +Grants for %@b@b +GRANT USAGE ON *.* TO '%@b'@'b' +GRANT SELECT ON "mysql".* TO '%@b'@'b' +rename user '%@b'@'b' to '%@a'@'a'; +show grants for '%@b'@'b'; +ERROR 42000: There is no such grant defined for user '%@b' on host 'b' +show grants for '%@a'@'a'; +Grants for %@a@a +GRANT USAGE ON *.* TO '%@a'@'a' +GRANT SELECT ON "mysql".* TO '%@a'@'a' +drop user '%@a'@'a'; +create user mysqltest_2@localhost; +grant create user on *.* to mysqltest_2@localhost; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' +create user mysqltest_A@'%'; +rename user mysqltest_A@'%' to mysqltest_B@'%'; +drop user mysqltest_B@'%'; +drop user mysqltest_2@localhost; +create user mysqltest_3@localhost; +grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; +show grants; +Grants for mysqltest_3@localhost +GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' +GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost' +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' +insert into mysql.user set host='%', user='mysqltest_B'; +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value +create user mysqltest_A@'%'; +rename user mysqltest_B@'%' to mysqltest_C@'%'; +drop user mysqltest_C@'%'; +drop user mysqltest_3@localhost; +set @@sql_mode=''; create database mysqltest_1; create table mysqltest_1.t1 (i int); insert into mysqltest_1.t1 values (1),(2),(3); |