2759 Rafal Somla 2009-02-05
A fix for backup_db_grants_extra failure in PB in 6.0-backup tree.
This patch extends ORDER BY clauses in selects to ensure that the order
of the result set is fixed.
modified:
mysql-test/suite/backup/r/backup_db_grants_extra.result
mysql-test/suite/backup/t/backup_db_grants_extra.test
2758 Hema Sridharan 2009-02-04
WL4230(Test backup and restore of grants). This patch is small fix for failing test in PB
modified:
mysql-test/suite/backup/r/backup_db_grants_extra.result
mysql-test/suite/backup/t/backup_db_grants_extra.test
=== modified file 'mysql-test/suite/backup/r/backup_db_grants_extra.result'
--- a/mysql-test/suite/backup/r/backup_db_grants_extra.result 2009-02-04 21:05:09 +0000
+++ b/mysql-test/suite/backup/r/backup_db_grants_extra.result 2009-02-05 11:42:00 +0000
@@ -49,18 +49,19 @@ GRANT EXECUTE ON FUNCTION db2.f21 TO car
FLUSH PRIVILEGES;
Verify the grant privileges for users
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
-% db1 jim f11 FUNCTION root@localhost Execute #
% db1 carry p11 PROCEDURE root@localhost Execute #
-% db2 jim p21 PROCEDURE root@localhost Execute #
+% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
+% db2 jim p21 PROCEDURE root@localhost Execute #
Perform backup
BACKUP DATABASE db1, db2 TO 'db12.bak';
backup_id
@@ -90,18 +91,19 @@ SHOW FUNCTION STATUS;
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
db1 f11 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
db2 f21 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
-% db1 jim f11 FUNCTION root@localhost Execute #
% db1 carry p11 PROCEDURE root@localhost Execute #
-% db2 jim p21 PROCEDURE root@localhost Execute #
+% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
+% db2 jim p21 PROCEDURE root@localhost Execute #
We notice that all the grants are restored intact.
Excercise objects
CALL db1.p11(2);
@@ -177,15 +179,17 @@ SHOW FUNCTION STATUS;
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
db1 f11 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
db2 f21 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
-From the above table, We can notice that grants for stored routines will--echo not be restored. Bug#41979
+From the above table, We can notice that grants for stored routines will
+not be restored. Bug#41979
Drop users, then restore and verify the grants.
Restore will throw a warning message
DROP DATABASE db1;
@@ -234,9 +238,10 @@ SHOW FUNCTION STATUS;
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
db1 f11 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
db2 f21 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
@@ -246,27 +251,29 @@ GRANT EXECUTE ON PROCEDURE db1.p11 TO ca
GRANT EXECUTE ON FUNCTION db1.f11 TO jim@'%';
GRANT EXECUTE ON FUNCTION db2.f21 TO carry@'%';
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
-% db1 jim f11 FUNCTION root@localhost Execute #
% db1 carry p11 PROCEDURE root@localhost Execute #
+% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
% db2 jim p21 PROCEDURE root@localhost Execute #
Drop procedure db1.p11 and check the grants for users
DROP PROCEDURE db1.p11;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
@@ -280,13 +287,14 @@ DROP DATABASE db1;
DROP DATABASE db2;
verify grants
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
@@ -297,13 +305,14 @@ backup_id
#
Verify objects and grant privileges
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
% db1 jim f11 FUNCTION root@localhost Execute #
% db2 carry f21 FUNCTION root@localhost Execute #
@@ -325,13 +334,14 @@ db1 f11 FUNCTION root@localhost # # DEFI
db2 f21 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
Drop function and check grants for users
DROP FUNCTION db1.f11;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
% db2 carry f21 FUNCTION root@localhost Execute #
% db2 jim p21 PROCEDURE root@localhost Execute #
@@ -348,13 +358,14 @@ backup_id
#
Verify objects and grant privileges
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
GRANTEE PRIVILEGE_TYPE TABLE_SCHEMA
'jim'@'%' SELECT db1
'jim'@'%' SELECT db2
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
% db2 carry f21 FUNCTION root@localhost Execute #
% db2 jim p21 PROCEDURE root@localhost Execute #
@@ -393,15 +404,17 @@ GRANT INSERT, SELECT ON db2.t1 TO jim@'%
GRANT INSERT(a1) ON db1.t2 TO carry@'%';
GRANT UPDATE, SELECT ON db2.t2 TO carry@'%';
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -415,15 +428,17 @@ Drop a column to which grant is assigned
ALTER TABLE db1.t1 DROP COLUMN b;
check the grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -448,15 +463,17 @@ backup_id
#
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -464,15 +481,17 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE
Now drop a table to which privilege is assigned
DROP TABLE db2.t2;
check the grants for users
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -497,15 +516,17 @@ backup_id
#
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -548,15 +569,17 @@ Rename table to which grant is assigned.
RENAME TABLE db2.t1 TO db2.trename;
check the grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -580,15 +603,17 @@ backup_id
#
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -600,15 +625,17 @@ Field Type Null Key Default Extra
a1rename smallint(6) YES NULL
b1 char(20) YES NULL
check the grants for users
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -632,15 +659,17 @@ backup_id
#
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'jim'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'jim'@'%' NULL db1 t1 b UPDATE NO
@@ -684,15 +713,17 @@ RENAME USER jim@'%' TO tom@'%';
check the grants for users
SHOW GRANTS FOR jim@'%';
ERROR 42000: There is no such grant defined for user 'jim' on host '%'
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db2 t1 INSERT NO
'tom'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db1 t1 a SELECT NO
'tom'@'%' NULL db1 t1 b UPDATE NO
@@ -709,15 +740,17 @@ backup_id
#
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db2 t1 INSERT NO
'tom'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db1 t1 a SELECT NO
'tom'@'%' NULL db1 t1 b UPDATE NO
@@ -741,15 +774,17 @@ Warning # The grant 'INSERT ON db2.t1' f
Warning # The grant 'SELECT ON db2.t1' for the user 'jim'@'%' was skipped because the user does not exist.
verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db2 t1 INSERT NO
'tom'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'tom'@'%' NULL db1 t1 a SELECT NO
'tom'@'%' NULL db1 t1 b UPDATE NO
@@ -764,8 +799,9 @@ backup_id
#
Verify grants for user jim and tom
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db2 t1 INSERT NO
'tom'@'%' NULL db2 t1 INSERT NO
@@ -773,8 +809,9 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE
'tom'@'%' NULL db2 t1 SELECT NO
'carry'@'%' NULL db2 t2 SELECT NO
'carry'@'%' NULL db2 t2 UPDATE NO
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'jim'@'%' NULL db1 t1 a SELECT NO
'tom'@'%' NULL db1 t1 a SELECT NO
=== modified file 'mysql-test/suite/backup/t/backup_db_grants_extra.test'
--- a/mysql-test/suite/backup/t/backup_db_grants_extra.test 2009-02-04 21:05:09 +0000
+++ b/mysql-test/suite/backup/t/backup_db_grants_extra.test 2009-02-05 11:42:00 +0000
@@ -75,11 +75,12 @@ FLUSH PRIVILEGES;
--echo
--echo Verify the grant privileges for users
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo Perform backup
--replace_column 1 #
@@ -105,11 +106,12 @@ SHOW PROCEDURE STATUS;
--replace_column 5 # 6 #
SHOW FUNCTION STATUS;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo We notice that all the grants are restored intact.
--echo Excercise objects
@@ -158,13 +160,15 @@ SHOW PROCEDURE STATUS;
--replace_column 5 # 6 #
SHOW FUNCTION STATUS;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
---echo From the above table, We can notice that grants for stored routines will--echo not be restored. Bug#41979
+--echo From the above table, We can notice that grants for stored routines will
+--echo not be restored. Bug#41979
--echo Drop users, then restore and verify the grants.
--echo Restore will throw a warning message
@@ -209,9 +213,10 @@ SHOW PROCEDURE STATUS;
--replace_column 5 # 6 #
SHOW FUNCTION STATUS;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--echo Grant Routine level privileges to users
@@ -221,20 +226,22 @@ GRANT EXECUTE ON FUNCTION db1.f11 TO jim
GRANT EXECUTE ON FUNCTION db2.f21 TO carry@'%';
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo Drop procedure db1.p11 and check the grants for users
DROP PROCEDURE db1.p11;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo Perform backup
--replace_column 1 #
@@ -246,11 +253,12 @@ DROP DATABASE db2;
--echo verify grants
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo Perform restore
--replace_column 1 #
@@ -259,12 +267,13 @@ RESTORE FROM 'db12a.bak';
--echo Verify objects and grant privileges
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
SHOW TABLES FROM db1;
SHOW TABLES FROM db2;
@@ -276,12 +285,13 @@ SHOW FUNCTION STATUS;
--echo Drop function and check grants for users
DROP FUNCTION db1.f11;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
--echo Perform backup
--replace_column 1 #
@@ -297,12 +307,13 @@ RESTORE FROM 'db12b.bak';
--echo Verify objects and grant privileges
FLUSH PRIVILEGES;
-SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA FROM
-INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_SCHEMA, GRANTEE;
+SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA
+FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, PRIVILEGE_TYPE, GRANTEE;
--replace_column 8 #
-SELECT * FROM mysql.procs_priv ORDER BY Db;
+SELECT * FROM mysql.procs_priv ORDER BY Db, User, Routine_name;
SHOW TABLES FROM db1;
SHOW TABLES FROM db2;
@@ -336,10 +347,12 @@ GRANT INSERT(a1) ON db1.t2 TO carry@'%';
GRANT UPDATE, SELECT ON db2.t2 TO carry@'%';
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo perform backup
--replace_column 1 #
@@ -351,10 +364,12 @@ ALTER TABLE db1.t1 DROP COLUMN b;
--echo check the grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Perform backup again
--replace_column 1 #
@@ -378,19 +393,23 @@ RESTORE FROM 'db12a.bak' OVERWRITE;
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Now drop a table to which privilege is assigned
DROP TABLE db2.t2;
--echo check the grants for users
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Perform backup operation
--replace_column 1 #
@@ -414,10 +433,12 @@ RESTORE FROM 'db12a.bak' OVERWRITE;
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
SHOW TABLES FROM db1;
SHOW TABLES FROM db2;
@@ -440,10 +461,12 @@ RENAME TABLE db2.t1 TO db2.trename;
--echo check the grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Perform backup again
--replace_column 1 #
@@ -466,20 +489,24 @@ RESTORE FROM 'db12a.bak' OVERWRITE;
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Rename a column to which grant is assigned.
ALTER TABLE db1.t2 CHANGE a1 a1rename SMALLINT;
DESCRIBE db1.t2;
--echo check the grants for users
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Perform backup again
--replace_column 1 #
@@ -502,10 +529,12 @@ RESTORE FROM 'db12a.bak' OVERWRITE;
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
SHOW TABLES FROM db1;
SHOW TABLES FROM db2;
@@ -529,10 +558,12 @@ RENAME USER jim@'%' TO tom@'%';
--echo check the grants for users
--error ER_NONEXISTING_GRANT
SHOW GRANTS FOR jim@'%';
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Perform backup again
--replace_column 1 #
@@ -547,10 +578,12 @@ RESTORE FROM 'db12b.bak';
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo drop database and perform restore from db12a.bak
DROP DATABASE db1;
@@ -563,10 +596,12 @@ SHOW WARNINGS;
--echo verify grants for users
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo Create new user with same name jim@'%'
CREATE USER jim@'%';
@@ -577,10 +612,12 @@ RESTORE FROM 'db12a.bak' OVERWRITE;
--echo Verify grants for user jim and tom
FLUSH PRIVILEGES;
-SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
-SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA='db1' OR
-TABLE_SCHEMA='db2' ORDER BY TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, GRANTEE;
+SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
+ WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2'
+ ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE, GRANTEE;
--echo We can notice that there are some additional grants shown for jim
--echo Bug#41597
| Thread |
|---|
| • bzr push into mysql-6.0-backup branch (Rafal.Somla:2758 to 2759) | Rafal Somla | 5 Feb |
