is_column_privileges.result 19.2 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
SHOW TABLES FROM information_schema LIKE 'COLUMN_PRIVILEGES';
Tables_in_information_schema (COLUMN_PRIVILEGES)
COLUMN_PRIVILEGES
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.COLUMN_PRIVILEGES;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLUMN_PRIVILEGES;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.COLUMN_PRIVILEGES;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.COLUMN_PRIVILEGES;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.5.1: INFORMATION_SCHEMA.COLUMN_PRIVILEGES layout
#########################################################################
DESCRIBE          information_schema.COLUMN_PRIVILEGES;
Field	Type	Null	Key	Default	Extra
GRANTEE	varchar(81)	NO			
TABLE_CATALOG	varchar(512)	YES		NULL	
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
COLUMN_NAME	varchar(64)	NO			
PRIVILEGE_TYPE	varchar(64)	NO			
IS_GRANTABLE	varchar(3)	NO			
SHOW CREATE TABLE information_schema.COLUMN_PRIVILEGES;
Table	Create Table
COLUMN_PRIVILEGES	CREATE TEMPORARY TABLE `COLUMN_PRIVILEGES` (
41 42 43 44 45 46 47
  `GRANTEE` varchar(81) NOT NULL DEFAULT '',
  `TABLE_CATALOG` varchar(512) DEFAULT NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
) ENGINE=MEMORY DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.COLUMN_PRIVILEGES;
Field	Type	Null	Key	Default	Extra
GRANTEE	varchar(81)	NO			
TABLE_CATALOG	varchar(512)	YES		NULL	
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
COLUMN_NAME	varchar(64)	NO			
PRIVILEGE_TYPE	varchar(64)	NO			
IS_GRANTABLE	varchar(3)	NO			
SELECT table_catalog, table_schema, table_name, column_name, privilege_type
FROM information_schema.column_privileges WHERE table_catalog IS NOT NULL;
table_catalog	table_schema	table_name	column_name	privilege_type
######################################################################
# Testcase 3.2.5.2+3.2.5.3+3.2.5.4:
#          INFORMATION_SCHEMA.COLUMN_PRIVILEGES accessible information
######################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 INT, f2 DECIMAL, f3 TEXT)
ENGINE = <other_engine_type>;
USE db_datadict;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP   USER 'testuser3'@'localhost';
CREATE USER 'testuser3'@'localhost';
GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT INSERT(f1)     ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT UPDATE(f2)     ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT SELECT(f2)     ON db_datadict.t1 TO 'testuser2'@'localhost';
GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
GRANT SELECT(f3)     ON db_datadict.t1 TO 'testuser3'@'localhost';
GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'
WITH GRANT OPTION;
GRANT ALL            ON db_datadict.*  TO 'testuser3'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	INSERT	NO
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	t1	f2	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	t1	f3	SELECT	NO
'testuser2'@'localhost'	NULL	db_datadict	t1	f2	SELECT	NO
'testuser3'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
GRANT UPDATE(f3)     ON db_datadict.t1 TO 'testuser1'@'localhost'
WITH GRANT OPTION;
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	INSERT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	SELECT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f2	UPDATE	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f3	UPDATE	YES
'testuser2'@'localhost'	NULL	db_datadict	t1	f2	SELECT	NO
'testuser3'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	INSERT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f1	SELECT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f2	UPDATE	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
'testuser1'@'localhost'	NULL	db_datadict	t1	f3	UPDATE	YES
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser2'@'localhost'	NULL	db_datadict	t1	f2	SELECT	NO
# Establish connection testuser3 (user=testuser3)
# FIXME: Is it correct that granted TABLES do not occur in COLUMN_PRIVILEGES?
SELECT * FROM information_schema.table_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee,table_schema,table_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser3'@'localhost'	NULL	db_datadict	t1	INSERT	YES
'testuser3'@'localhost'	NULL	db_datadict	t1	SELECT	YES
SELECT * FROM information_schema.schema_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee,table_schema,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser3'@'localhost'	NULL	db_datadict	ALTER	NO
'testuser3'@'localhost'	NULL	db_datadict	ALTER ROUTINE	NO
'testuser3'@'localhost'	NULL	db_datadict	CREATE	NO
'testuser3'@'localhost'	NULL	db_datadict	CREATE ROUTINE	NO
'testuser3'@'localhost'	NULL	db_datadict	CREATE TEMPORARY TABLES	NO
'testuser3'@'localhost'	NULL	db_datadict	CREATE VIEW	NO
'testuser3'@'localhost'	NULL	db_datadict	DELETE	NO
'testuser3'@'localhost'	NULL	db_datadict	DROP	NO
144
'testuser3'@'localhost'	NULL	db_datadict	EVENT	NO
145 146 147 148 149 150 151
'testuser3'@'localhost'	NULL	db_datadict	EXECUTE	NO
'testuser3'@'localhost'	NULL	db_datadict	INDEX	NO
'testuser3'@'localhost'	NULL	db_datadict	INSERT	NO
'testuser3'@'localhost'	NULL	db_datadict	LOCK TABLES	NO
'testuser3'@'localhost'	NULL	db_datadict	REFERENCES	NO
'testuser3'@'localhost'	NULL	db_datadict	SELECT	NO
'testuser3'@'localhost'	NULL	db_datadict	SHOW VIEW	NO
152
'testuser3'@'localhost'	NULL	db_datadict	TRIGGER	NO
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372
'testuser3'@'localhost'	NULL	db_datadict	UPDATE	NO
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser3'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser2'@'localhost';
# FIXME: Is it intended that *my* grants to others are *NOT* shown here?
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser3'@'localhost'	NULL	db_datadict	t1	f3	SELECT	YES
# Switch to connection testuser2 (user=testuser2)
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser2'@'localhost'	NULL	db_datadict	t1	f1	SELECT	NO
'testuser2'@'localhost'	NULL	db_datadict	t1	f2	SELECT	NO
'testuser2'@'localhost'	NULL	db_datadict	t1	f3	SELECT	NO
# Switch to connection default and close connections testuser1,testuser2,testuser3
DROP DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
################################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMN_PRIVILEGES modifications
################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.my_table (f1 BIGINT, f2 CHAR(10), f3 DATE)
ENGINE = <engine_type>;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT ALL ON test.* TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
# Switch to connection default
GRANT SELECT (f1,f3) ON db_datadict.my_table TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection default
ALTER TABLE db_datadict.my_table DROP COLUMN f3;
GRANT UPDATE (f1) ON db_datadict.my_table TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
SELECT f1, f3 FROM db_datadict.my_table;
ERROR 42S22: Unknown column 'f3' in 'field list'
# Switch to connection default
ALTER TABLE db_datadict.my_table CHANGE COLUMN f1 my_col BIGINT;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection default
DROP TABLE db_datadict.my_table;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	SELECT	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f1	UPDATE	NO
'testuser1'@'localhost'	NULL	db_datadict	my_table	f3	SELECT	NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
# Switch to connection default
REVOKE ALL ON db_datadict.my_table FROM 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
# Switch to connection default and close connection testuser1
DROP USER 'testuser1'@'localhost';
DROP DATABASE db_datadict;
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
ENGINE = <engine_type>;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
INSERT INTO information_schema.column_privileges
SELECT * FROM information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.column_privileges SET table_schema = 'test'
WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.column_privileges WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_tables
ON information_schema.column_privileges(table_schema);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges
RENAME db_datadict.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges
RENAME information_schema.xcolumn_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';