302 lines
12 KiB
Plaintext
302 lines
12 KiB
Plaintext
# TestWithGrantOption
|
|
drop user if exists 'testWithGrant'@'localhost';
|
|
CREATE USER 'testWithGrant'@'localhost' IDENTIFIED BY '123';
|
|
SELECT * FROM mysql.db WHERE User="testWithGrant" and host="localhost";
|
|
GRANT select ON executor__grant.* TO 'testWithGrant'@'localhost' WITH GRANT OPTION;
|
|
SELECT grant_priv FROM mysql.DB WHERE User="testWithGrant" and host="localhost" and db="executor__grant";
|
|
drop user if exists 'testWithGrant1';
|
|
CREATE USER 'testWithGrant1';
|
|
SELECT grant_priv FROM mysql.user WHERE User="testWithGrant1";
|
|
GRANT ALL ON *.* TO 'testWithGrant1';
|
|
SELECT grant_priv FROM mysql.user WHERE User="testWithGrant1";
|
|
GRANT ALL ON *.* TO 'testWithGrant1' WITH GRANT OPTION;
|
|
SELECT grant_priv FROM mysql.user WHERE User="testWithGrant1";
|
|
|
|
# TestIssue2456
|
|
drop user if exists 'dduser'@'%';
|
|
drop DATABASE if exists `dddb_%`;
|
|
CREATE USER 'dduser'@'%' IDENTIFIED by '123456';
|
|
CREATE DATABASE `dddb_%`;
|
|
CREATE table `dddb_%`.`te%` (id int);
|
|
GRANT ALL PRIVILEGES ON `dddb_%`.* TO 'dduser'@'%';
|
|
GRANT ALL PRIVILEGES ON `dddb_%`.`te%` to 'dduser'@'%';
|
|
|
|
# TestNoAutoCreateUser
|
|
DROP USER IF EXISTS 'test'@'%';
|
|
SET sql_mode='NO_AUTO_CREATE_USER';
|
|
-- error 1410
|
|
GRANT ALL PRIVILEGES ON *.* to 'test'@'%' IDENTIFIED BY 'xxx';
|
|
set sql_mode=default;
|
|
|
|
# TestCreateUserWhenGrant
|
|
DROP USER IF EXISTS 'test'@'%';
|
|
# This only applies to sql_mode:NO_AUTO_CREATE_USER off
|
|
SET SQL_MODE='';
|
|
GRANT ALL PRIVILEGES ON *.* to 'test'@'%' IDENTIFIED BY 'xxx';
|
|
# Make sure user is created automatically when grant to a non-exists one.
|
|
SELECT user FROM mysql.user WHERE user='test' and host='%';
|
|
DROP USER IF EXISTS 'test'@'%';
|
|
# Grant without a password.
|
|
GRANT ALL PRIVILEGES ON *.* to 'test'@'%';
|
|
# Make sure user is created automatically when grant to a non-exists one.
|
|
SELECT user, plugin FROM mysql.user WHERE user='test' and host='%';
|
|
DROP USER IF EXISTS 'test'@'%';
|
|
set sql_mode=default;
|
|
|
|
# TestCreateUserWithTooLongName
|
|
-- error 1470
|
|
CREATE USER '1234567890abcdefGHIKL1234567890abcdefGHIKL@localhost';
|
|
-- error 1470
|
|
CREATE USER 'some_user_name@host_1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890abcdefghij1234567890X';
|
|
|
|
# TestGrantPrivilegeAtomic
|
|
drop role if exists r1, r2, r3, r4;
|
|
create role r1, r2, r3;
|
|
create table executor__grant.testatomic(x int);
|
|
-- error 1410
|
|
grant update, select, insert, delete on *.* to r1, r2, r4;
|
|
select Update_priv, Select_priv, Insert_priv, Delete_priv from mysql.user where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
grant update, select, insert, delete on *.* to r1, r2, r3;
|
|
-- error 1105
|
|
revoke all on *.* from r1, r2, r4, r3;
|
|
select Update_priv, Select_priv, Insert_priv, Delete_priv from mysql.user where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
-- error 1410
|
|
grant update, select, insert, delete on executor__grant.* to r1, r2, r4;
|
|
select Update_priv, Select_priv, Insert_priv, Delete_priv from mysql.db where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
grant update, select, insert, delete on executor__grant.* to r1, r2, r3;
|
|
-- error 1105
|
|
revoke all on *.* from r1, r2, r4, r3;
|
|
select Update_priv, Select_priv, Insert_priv, Delete_priv from mysql.db where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
-- error 1410
|
|
grant update, select, insert, delete on executor__grant.testatomic to r1, r2, r4;
|
|
select Table_priv from mysql.tables_priv where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
grant update, select, insert, delete on executor__grant.testatomic to r1, r2, r3;
|
|
-- error 1105
|
|
revoke all on *.* from r1, r2, r4, r3;
|
|
select Table_priv from mysql.tables_priv where user in ('r1', 'r2', 'r3', 'r4') and host = "%";
|
|
drop role if exists r1, r2, r3, r4;
|
|
drop table executor__grant.testatomic;
|
|
|
|
# TestIssue2654
|
|
DROP USER IF EXISTS 'test'@'%';
|
|
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
|
|
GRANT SELECT ON executor__grant.* to 'test';
|
|
SELECT user,host FROM mysql.user WHERE user='test' and host='%';
|
|
|
|
# TestGrantUnderANSIQuotes
|
|
SET SQL_MODE='ANSI_QUOTES';
|
|
# Fix a bug that the GrantExec fails in ANSI_QUOTES sql mode
|
|
# The bug is caused by the improper usage of double quotes like:
|
|
# INSERT INTO mysql.user ... VALUES ("..", "..", "..")
|
|
GRANT ALL PRIVILEGES ON video_ulimit.* TO web@'%' IDENTIFIED BY 'eDrkrhZ>l2sV';
|
|
REVOKE ALL PRIVILEGES ON video_ulimit.* FROM web@'%';
|
|
DROP USER IF EXISTS 'web'@'%';
|
|
set sql_mode=default;
|
|
|
|
# TestMaintainRequire
|
|
DROP USER if exists 'ssl_auser'@'%';
|
|
DROP USER if exists 'ssl_buser'@'%';
|
|
DROP USER if exists 'ssl_cuser'@'%';
|
|
DROP USER if exists 'ssl_duser'@'%';
|
|
DROP USER if exists 'ssl_euser'@'%';
|
|
DROP USER if exists 'ssl_fuser'@'%';
|
|
DROP USER if exists 'ssl_guser'@'%';
|
|
drop user if exists 'u1'@'%';
|
|
drop user if exists 'u2'@'%';
|
|
drop user if exists 'u3'@'%';
|
|
CREATE USER 'ssl_auser'@'%' require issuer '/CN=TiDB admin/OU=TiDB/O=PingCAP/L=San Francisco/ST=California/C=US' subject '/CN=tester1/OU=TiDB/O=PingCAP.Inc/L=Haidian/ST=Beijing/C=ZH' cipher 'AES128-GCM-SHA256';
|
|
CREATE USER 'ssl_buser'@'%' require subject '/CN=tester1/OU=TiDB/O=PingCAP.Inc/L=Haidian/ST=Beijing/C=ZH' cipher 'AES128-GCM-SHA256';
|
|
CREATE USER 'ssl_cuser'@'%' require cipher 'AES128-GCM-SHA256';
|
|
CREATE USER 'ssl_duser'@'%';
|
|
CREATE USER 'ssl_euser'@'%' require none;
|
|
CREATE USER 'ssl_fuser'@'%' require ssl;
|
|
CREATE USER 'ssl_guser'@'%' require x509;
|
|
select * from mysql.global_priv where `user` like 'ssl_%';
|
|
CREATE USER 'u1'@'%';
|
|
GRANT ALL ON *.* TO 'u1'@'%' require issuer '/CN=TiDB admin/OU=TiDB/O=PingCAP/L=San Francisco/ST=California/C=US' and subject '/CN=tester1/OU=TiDB/O=PingCAP.Inc/L=Haidian/ST=Beijing/C=ZH';
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u1';
|
|
GRANT ALL ON *.* TO 'u1'@'%' require cipher 'AES128-GCM-SHA256';
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u1';
|
|
GRANT select ON *.* TO 'u1'@'%';
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u1';
|
|
GRANT ALL ON *.* TO 'u1'@'%' require none;
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u1';
|
|
CREATE USER 'u2'@'%';
|
|
alter user 'u2'@'%' require ssl;
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u2';
|
|
alter user 'u2'@'%' require x509;
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u2';
|
|
alter user 'u2'@'%' require issuer '/CN=TiDB admin/OU=TiDB/O=PingCAP/L=San Francisco/ST=California/C=US' subject '/CN=tester1/OU=TiDB/O=PingCAP.Inc/L=Haidian/ST=Beijing/C=ZH' cipher 'AES128-GCM-SHA256';
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u2';
|
|
alter user 'u2'@'%' require none;
|
|
select priv from mysql.global_priv where `Host` = '%' and `User` = 'u2';
|
|
CREATE USER 'u3'@'%' require issuer '/CN=TiDB admin/OU=TiDB/O=PingCAP/L=San Francisco/ST=California/C=US' subject '/CN=tester1/OU=TiDB/O=PingCAP.Inc/L=Haidian/ST=Beijing/C=ZH' cipher 'AES128-GCM-SHA256';
|
|
show create user 'u3';
|
|
-- error 1105
|
|
CREATE USER 'u4'@'%' require issuer 'CN=TiDB,OU=PingCAP';
|
|
-- error 1105
|
|
CREATE USER 'u5'@'%' require subject '/CN=TiDB\OU=PingCAP';
|
|
-- error 1105
|
|
CREATE USER 'u6'@'%' require subject '/CN=TiDB\NC=PingCAP';
|
|
-- error 1105
|
|
CREATE USER 'u7'@'%' require cipher 'AES128-GCM-SHA1';
|
|
-- error 1105
|
|
CREATE USER 'u8'@'%' require subject '/CN';
|
|
-- error 1105
|
|
CREATE USER 'u9'@'%' require cipher 'TLS_AES_256_GCM_SHA384' cipher 'RC4-SHA';
|
|
-- error 1105
|
|
CREATE USER 'u9'@'%' require issuer 'CN=TiDB,OU=PingCAP' issuer 'CN=TiDB,OU=PingCAP2';
|
|
-- error 1105
|
|
CREATE USER 'u9'@'%' require subject '/CN=TiDB\OU=PingCAP' subject '/CN=TiDB\OU=PingCAP2';
|
|
-- error 1064
|
|
CREATE USER 'u9'@'%' require ssl ssl;
|
|
-- error 1064
|
|
CREATE USER 'u9'@'%' require x509 x509;
|
|
|
|
# TestMaintainAuthString
|
|
drop user if exists 'maint_auth_str1'@'%';
|
|
CREATE USER 'maint_auth_str1'@'%' IDENTIFIED BY 'foo';
|
|
SELECT authentication_string FROM mysql.user WHERE `Host` = '%' and `User` = 'maint_auth_str1';
|
|
ALTER USER 'maint_auth_str1'@'%' REQUIRE SSL;
|
|
SELECT authentication_string FROM mysql.user WHERE `Host` = '%' and `User` = 'maint_auth_str1';
|
|
|
|
# TestIssue22721
|
|
drop table if exists xx;
|
|
drop user if exists 'sync_ci_data'@'%';
|
|
create table xx (id int);
|
|
CREATE USER 'sync_ci_data'@'%' IDENTIFIED BY 'sNGNQo12fEHe0n3vU';
|
|
GRANT USAGE ON *.* TO 'sync_ci_data'@'%';
|
|
GRANT USAGE ON sync_ci_data.* TO 'sync_ci_data'@'%';
|
|
GRANT USAGE ON executor__grant.* TO 'sync_ci_data'@'%';
|
|
GRANT USAGE ON executor__grant.xx TO 'sync_ci_data'@'%';
|
|
|
|
# TestPerformanceSchemaPrivGrant
|
|
drop user if exists issue27867;
|
|
create user issue27867;
|
|
-- error 1044
|
|
grant all on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant all on PERFormanCE_scHemA.* to issue27867;
|
|
grant select on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant insert on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant update on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant delete on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant drop on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant lock tables on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant create on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant references on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant alter on PERFormAnCE_scHemA.* to issue27867;
|
|
-- error 1044
|
|
grant execute on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant index on PERFormanCE_scHemA.* to issue27867;
|
|
-- error 1044
|
|
grant create view on performance_schema.* to issue27867;
|
|
-- error 1044
|
|
grant show view on performance_schema.* to issue27867;
|
|
drop user issue27867;
|
|
|
|
# TestGrantDynamicPrivs
|
|
drop user if exists dyn;
|
|
create user dyn;
|
|
-- error 3619
|
|
GRANT BACKUP_ADMIN ON executor__grant.* TO dyn;
|
|
-- error 3929
|
|
GRANT BOGUS_GRANT ON *.* TO dyn;
|
|
GRANT BACKUP_Admin ON *.* TO dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
GRANT SYSTEM_VARIABLES_ADMIN, BACKUP_ADMIN ON *.* TO dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
GRANT ROLE_ADMIN, BACKUP_ADMIN ON *.* TO dyn WITH GRANT OPTION;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
GRANT SYSTEM_VARIABLES_ADMIN, Select, ROLE_ADMIN ON *.* TO dyn;
|
|
SELECT Grant_Priv FROM mysql.user WHERE `Host` = '%' AND `User` = 'dyn';
|
|
SELECT WITH_GRANT_OPTION FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' AND Priv='SYSTEM_VARIABLES_ADMIN';
|
|
GRANT CONNECTION_ADMIN, Insert ON *.* TO dyn WITH GRANT OPTION;
|
|
SELECT Grant_Priv FROM mysql.user WHERE `Host` = '%' AND `User` = 'dyn';
|
|
SELECT WITH_GRANT_OPTION FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' AND Priv='CONNECTION_ADMIN';
|
|
|
|
# TestNonExistTableIllegalGrant
|
|
drop user if exists u29302;
|
|
create user u29302;
|
|
-- error 1144
|
|
grant create temporary tables on NotExistsD29302.NotExistsT29302 to u29302;
|
|
-- error 1144
|
|
grant lock tables on executor__grant.NotExistsT29302 to u29302;
|
|
-- error 1221
|
|
grant create temporary tables (NotExistsCol) on NotExistsD29302.NotExistsT29302 to u29302;
|
|
drop user u29302;
|
|
|
|
# TestIssue34610
|
|
drop table if exists t1;
|
|
drop user if exists user_1@localhost;
|
|
CREATE USER user_1@localhost;
|
|
CREATE TABLE T1(f1 INT);
|
|
-- error 1050
|
|
CREATE TABLE t1(f1 INT);
|
|
GRANT SELECT ON T1 to user_1@localhost;
|
|
GRANT SELECT ON t1 to user_1@localhost;
|
|
|
|
# TestIssue38293
|
|
DROP USER IF EXISTS test;
|
|
CREATE USER test;
|
|
GRANT SELECT ON `mysql`.`db` TO test;
|
|
SELECT `Grantor` FROM `mysql`.`tables_priv` WHERE User = 'test';
|
|
|
|
# TestGrantOnNonExistTable
|
|
drop user if exists genius;
|
|
drop user if exists u29268;
|
|
create user genius;
|
|
-- error 1146
|
|
select * from nonexist;
|
|
-- error 1146
|
|
grant Select,Insert on nonexist to 'genius';
|
|
create table if not exists xx (id int);
|
|
grant Select,Insert on XX to 'genius';
|
|
grant Select,Insert on xx to 'genius';
|
|
grant Select,Update on executor__grant.xx to 'genius';
|
|
CREATE DATABASE d29268;
|
|
USE d29268;
|
|
CREATE USER u29268;
|
|
-- error 1146
|
|
GRANT SELECT ON t29268 TO u29268;
|
|
-- error 1146
|
|
GRANT DROP, INSERT ON t29268 TO u29268;
|
|
-- error 1146
|
|
GRANT UPDATE, CREATE VIEW, SHOW VIEW ON t29268 TO u29268;
|
|
-- error 1146
|
|
GRANT DELETE, REFERENCES, ALTER ON t29268 TO u29268;
|
|
GRANT CREATE ON t29268 TO u29268;
|
|
GRANT CREATE, SELECT ON t29268 TO u29268;
|
|
GRANT CREATE, DROP, INSERT ON t29268 TO u29268;
|
|
|
|
connect (conn1, localhost, u29268,, d29268);
|
|
CREATE TABLE t29268 (c1 int);
|
|
INSERT INTO t29268 VALUES (1), (2);
|
|
SELECT c1 FROM t29268;
|
|
DROP TABLE t29268;
|
|
disconnect conn1;
|
|
|
|
GRANT ALL ON t29268 TO u29268;
|
|
DROP USER u29268;
|
|
DROP DATABASE IF EXISTS d29268;
|
|
use executor__grant;
|
|
|
|
drop table if exists t;
|
|
drop user if exists test_user;
|
|
create user test_user;
|
|
create temporary table t(id int);
|
|
-- error 1146
|
|
grant select on t to test_user;
|
|
drop user test_user;
|
|
drop temporary table t;
|
|
|