109 lines
4.2 KiB
Plaintext
109 lines
4.2 KiB
Plaintext
# TestRevokeTableSingle
|
|
# ref issue #38421
|
|
drop user if exists test;
|
|
drop table if exists test1;
|
|
CREATE USER test;
|
|
CREATE TABLE executor__revoke.test1(c1 int);
|
|
GRANT SELECT ON executor__revoke.test1 TO test;
|
|
REVOKE SELECT ON executor__revoke.test1 from test;
|
|
SELECT Column_priv FROM mysql.tables_priv WHERE User="test" ;
|
|
|
|
# TestRevokeTableSingleColumn
|
|
# ref issue #38421(column fix)
|
|
drop user if exists test;
|
|
CREATE USER test;
|
|
GRANT SELECT(Host) ON mysql.db TO test;
|
|
GRANT SELECT(DB) ON mysql.db TO test;
|
|
REVOKE SELECT(Host) ON mysql.db FROM test;
|
|
SELECT count(Column_priv) FROM mysql.columns_priv WHERE User="test" and Column_name ='Host' ;
|
|
SELECT count(Column_priv) FROM mysql.columns_priv WHERE User="test" and Column_name ='DB' ;
|
|
|
|
# TestRevokeDynamicPrivs
|
|
DROP USER if exists dyn;
|
|
create user 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;
|
|
|
|
## try revoking only on test.* - should fail:
|
|
-- error 3619
|
|
REVOKE BACKUP_Admin,system_variables_admin ON executor__revoke.* FROM dyn;
|
|
|
|
## privs should still be intact:
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
## with correct usage, the privilege is revoked
|
|
REVOKE BACKUP_Admin ON *.* FROM dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
|
|
## Revoke bogus is a warning in MySQL
|
|
REVOKE bogus ON *.* FROM dyn;
|
|
SHOW WARNINGS;
|
|
|
|
## grant and revoke two dynamic privileges at once.
|
|
GRANT BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
REVOKE BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* FROM dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
|
|
## revoke a combination of dynamic + non-dynamic
|
|
GRANT BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN, SELECT, INSERT ON *.* TO dyn;
|
|
REVOKE BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN, SELECT, INSERT ON *.* FROM dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
|
|
## revoke grant option from privileges
|
|
GRANT BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN, SELECT ON *.* TO dyn WITH GRANT OPTION;
|
|
REVOKE BACKUP_ADMIN, SELECT, GRANT OPTION ON *.* FROM dyn;
|
|
SELECT * FROM mysql.global_grants WHERE `Host` = '%' AND `User` = 'dyn' ORDER BY user,host,priv,with_grant_option;
|
|
|
|
# TestRevokeOnNonExistTable
|
|
# issue #28533
|
|
drop DATABASE if exists d1;
|
|
drop user if exists issue28533;
|
|
CREATE DATABASE d1;
|
|
USE d1;
|
|
CREATE TABLE t1 (a int);
|
|
CREATE USER issue28533;
|
|
|
|
## GRANT ON existent table success
|
|
GRANT ALTER ON d1.t1 TO issue28533;
|
|
|
|
## GRANT ON non-existent table success
|
|
GRANT INSERT, CREATE ON d1.t2 TO issue28533;
|
|
|
|
## REVOKE ON non-existent table success
|
|
DROP TABLE t1;
|
|
REVOKE ALTER ON d1.t1 FROM issue28533;
|
|
|
|
DROP USER issue28533;
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP DATABASE IF EXISTS d1;
|
|
use executor__revoke;
|
|
|
|
# TestIssue41773
|
|
drop user if exists 't1234'@'%';
|
|
create table if not exists xx (id int);
|
|
CREATE USER 't1234'@'%' IDENTIFIED BY 'sNGNQo12fEHe0n3vU';
|
|
GRANT USAGE ON * TO 't1234'@'%';
|
|
GRANT USAGE ON executor__revoke.* TO 't1234'@'%';
|
|
GRANT USAGE ON executor__revoke.xx TO 't1234'@'%';
|
|
REVOKE USAGE ON * FROM 't1234'@'%';
|
|
REVOKE USAGE ON executor__revoke.* FROM 't1234'@'%';
|
|
REVOKE USAGE ON executor__revoke.xx FROM 't1234'@'%';
|
|
|
|
# TestCaseInsensitiveSchemaNames
|
|
# Check https://github.com/pingcap/tidb/issues/41048
|
|
drop table if exists TABLE_PRIV;
|
|
CREATE TABLE executor__revoke.TABLE_PRIV(id int, name varchar(20));
|
|
|
|
## Verify the case-insensitive updates for mysql.tables_priv table.
|
|
GRANT SELECT ON executor__revoke.table_priv TO 'root'@'%';
|
|
revoke SELECT ON executor__revoke.TABLE_PRIV from 'root'@'%';
|
|
|
|
## Verify the case-insensitive updates for mysql.db table.
|
|
GRANT SELECT ON executor__revoke.* TO 'root'@'%';
|
|
revoke SELECT ON executor__revoke.* from 'root'@'%';
|
|
|
|
## Verify the case-insensitive updates for mysql.columns_priv table.
|
|
GRANT SELECT (id), INSERT (ID, name) ON executor__revoke.TABLE_PRIV TO 'root'@'%';
|
|
REVOKE SELECT (ID) ON executor__revoke.taBle_priv from 'root'@'%';
|
|
|