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 41 42 43 44 45 46 47 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 144 145 146 147 148 149 150 151 152 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
|
SHOW TABLES FROM information_schema LIKE 'SCHEMA_PRIVILEGES';
Tables_in_information_schema (SCHEMA_PRIVILEGES)
SCHEMA_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.SCHEMA_PRIVILEGES;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMA_PRIVILEGES;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.SCHEMA_PRIVILEGES;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.SCHEMA_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.15.1: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES layout
#########################################################################
DESCRIBE information_schema.SCHEMA_PRIVILEGES;
Field Type Null Key Default Extra
GRANTEE varchar(385) NO NULL
TABLE_CATALOG varchar(512) NO NULL
TABLE_SCHEMA varchar(64) NO NULL
PRIVILEGE_TYPE varchar(64) NO NULL
IS_GRANTABLE varchar(3) NO NULL
SHOW CREATE TABLE information_schema.SCHEMA_PRIVILEGES;
Table Create Table
SCHEMA_PRIVILEGES CREATE TEMPORARY TABLE `SCHEMA_PRIVILEGES` (
`GRANTEE` varchar(385) NOT NULL,
`TABLE_CATALOG` varchar(512) NOT NULL,
`TABLE_SCHEMA` varchar(64) NOT NULL,
`PRIVILEGE_TYPE` varchar(64) NOT NULL,
`IS_GRANTABLE` varchar(3) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SHOW COLUMNS FROM information_schema.SCHEMA_PRIVILEGES;
Field Type Null Key Default Extra
GRANTEE varchar(385) NO NULL
TABLE_CATALOG varchar(512) NO NULL
TABLE_SCHEMA varchar(64) NO NULL
PRIVILEGE_TYPE varchar(64) NO NULL
IS_GRANTABLE varchar(3) NO NULL
SELECT GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE
FROM information_schema.schema_privileges WHERE table_catalog IS NOT NULL;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE
###############################################################################
# Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
###############################################################################
DROP DATABASE IF EXISTS db_datadict_1;
DROP DATABASE IF EXISTS db_datadict_2;
DROP DATABASE IF EXISTS db_datadict_3;
CREATE DATABASE db_datadict_1;
CREATE DATABASE db_datadict_2;
CREATE DATABASE db_datadict_3;
CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
ENGINE = MEMORY;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
GRANT INSERT ON db_datadict_1.* TO 'testuser1'@'localhost';
GRANT INSERT ON db_datadict_2.t1 TO 'testuser1'@'localhost';
GRANT SELECT ON db_datadict_4.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
GRANT SELECT ON db_datadict_3.* TO 'testuser2'@'localhost';
GRANT SELECT ON db_datadict_1.* TO 'testuser2'@'localhost';
connect testuser1, localhost, testuser1, ,"*NO-ONE*";
GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost';
# Root granted INSERT db_datadict_1 to me -> visible
# Root granted SELECT db_datadict_1 to testuser2 -> invisible
# Root granted INSERT db_datadict_2.t1 (no schema-level priv!)
# but not db_datadict_2 to me -> invisible
# Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible
# Root granted SELECT db_datadict_4. to me -> visible
# I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement)
# FIXME
SELECT * FROM information_schema.schema_privileges
WHERE table_schema LIKE 'db_datadict%'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict_1 INSERT NO
'testuser1'@'localhost' def db_datadict_4 SELECT YES
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO `testuser1`@`localhost`
GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
SHOW GRANTS FOR 'testuser2'@'localhost';
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'mysql'
connect testuser2, localhost, testuser2, ,"*NO-ONE*";
# Root granted SELECT db_datadict_1 to me -> visible
# Root granted INSERT db_datadict_1 to testuser1 -> invisible
# Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible
# Root granted SELECT db_datadict_3. to me -> visible
# testuser1 granted SELECT db_datadict_4. to me -> visible
SELECT * FROM information_schema.schema_privileges
WHERE table_schema LIKE 'db_datadict%'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser2'@'localhost' def db_datadict_1 SELECT NO
'testuser2'@'localhost' def db_datadict_3 SELECT NO
'testuser2'@'localhost' def db_datadict_4 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
ERROR 42000: Access denied for user 'testuser2'@'localhost' to database 'mysql'
SHOW GRANTS FOR 'testuser2'@'localhost';
Grants for testuser2@localhost
GRANT USAGE ON *.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
connection default;
disconnect testuser1;
disconnect testuser2;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema LIKE 'db_datadict%'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict_1 INSERT NO
'testuser1'@'localhost' def db_datadict_4 SELECT YES
'testuser2'@'localhost' def db_datadict_1 SELECT NO
'testuser2'@'localhost' def db_datadict_3 SELECT NO
'testuser2'@'localhost' def db_datadict_4 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO `testuser1`@`localhost`
GRANT INSERT ON `db_datadict_1`.* TO `testuser1`@`localhost`
GRANT SELECT ON `db_datadict_4`.* TO `testuser1`@`localhost` WITH GRANT OPTION
GRANT INSERT ON `db_datadict_2`.`t1` TO `testuser1`@`localhost`
SHOW GRANTS FOR 'testuser2'@'localhost';
Grants for testuser2@localhost
GRANT USAGE ON *.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_3`.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_1`.* TO `testuser2`@`localhost`
GRANT SELECT ON `db_datadict_4`.* TO `testuser2`@`localhost`
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP DATABASE db_datadict_1;
DROP DATABASE db_datadict_2;
DROP DATABASE db_datadict_3;
################################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES modifications
################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
DROP USER 'the_user'@'localhost';
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT SELECT ON test.* TO 'testuser1'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
connect testuser1, localhost, testuser1, , test;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
connection default;
GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict UPDATE NO
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict UPDATE NO
connection default;
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT NO
'testuser1'@'localhost' def db_datadict UPDATE NO
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT NO
'testuser1'@'localhost' def db_datadict UPDATE NO
# Switch to connection default
connection default;
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
'testuser1'@'localhost' def db_datadict UPDATE YES
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
'testuser1'@'localhost' def db_datadict UPDATE YES
connection default;
DROP SCHEMA db_datadict;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
'testuser1'@'localhost' def db_datadict UPDATE YES
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
'testuser1'@'localhost' def db_datadict UPDATE YES
connection default;
REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict SELECT YES
connection default;
RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'the_user'@'localhost' def db_datadict SELECT YES
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'the_user'@'localhost' def db_datadict SELECT YES
disconnect testuser1;
connect the_user, localhost, the_user, , test;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'the_user'@'localhost' def db_datadict SELECT YES
disconnect the_user;
connection default;
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'the_user'@'localhost' def db_datadict SELECT YES
DROP USER 'the_user'@'localhost';
SELECT * FROM information_schema.schema_privileges
WHERE table_schema = 'db_datadict'
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
########################################################################
# 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 ON db_datadict.* TO 'testuser1'@'localhost';
INSERT INTO information_schema.schema_privileges
SELECT * FROM information_schema.schema_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.schema_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.schema_privileges
WHERE table_schema = 'db_datadict';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.schema_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_tables
ON information_schema.schema_privileges(table_schema);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schema_privileges ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.schema_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schema_privileges
RENAME db_datadict.schema_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schema_privileges
RENAME information_schema.xschema_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';
|