1############################################################################### 2# This script executes GRANT AS scenarios # 3############################################################################### 4 5--echo ------------------------------------------------------------------------- 6--echo # 1. GRANT AS sanity checks 7--echo ------------------------------------------------------------------------- 8 9CREATE USER foo, bar, baz; 10CREATE DATABASE db1; 11CREATE TABLE db1.t1(c1 int); 12 13--error ER_UNSUPPORTED_USE_OF_GRANT_AS 14GRANT SELECT ON db1.* TO foo AS bar; 15 16--error ER_UNSUPPORTED_USE_OF_GRANT_AS 17GRANT SELECT ON db1.t1 TO foo AS bar; 18 19--error ER_UNSUPPORTED_USE_OF_GRANT_AS 20GRANT SELECT(c1) ON db1.t1 TO foo AS bar; 21 22--error ER_PARSE_ERROR 23GRANT PROXY ON foo TO bar AS foo; 24 25--error ER_PARSE_ERROR 26GRANT baz TO bar AS foo; 27 28--error ER_PARSE_ERROR 29GRANT SELECT ON *.* TO bar WITH ROLE foo; 30 31--error ER_PARSE_ERROR 32GRANT SELECT ON *.* TO bar WITH ROLE NONE; 33 34--error ER_PARSE_ERROR 35GRANT SELECT ON *.* TO bar WITH ROLE ALL; 36 37--error ER_PARSE_ERROR 38GRANT SELECT ON *.* TO bar WITH ROLE DEFAULT; 39 40--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 41GRANT SELECT ON *.* TO foo AS hohoho; 42 43--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 44GRANT SELECT ON *.* TO foo AS bar WITH ROLE hohoho; 45 46--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 47GRANT SELECT ON *.* TO foo AS bar WITH ROLE baz, hohoho; 48 49DROP TABLE db1.t1; 50DROP DATABASE db1; 51DROP USER foo, bar, baz; 52 53--echo ------------------------------------------------------------------------- 54--echo # 2. Permission checks 55--echo ------------------------------------------------------------------------- 56 57CREATE USER foo, bar; 58CREATE ROLE baz, qux; 59CREATE DATABASE db1; 60 61--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 62GRANT SELECT ON *.* TO foo AS bar WITH ROLE baz; 63 64GRANT SELECT ON *.* TO foo AS `root`@`localhost`; 65 66GRANT SELECT ON *.* TO foo WITH GRANT OPTION; 67REVOKE SELECT ON db1.* FROM foo; 68 69--connect(conn_foo, localhost, foo,,,,,) 70 71# Impersonating a user more privileged than you is not allowed 72--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 73GRANT SELECT ON *.* TO bar AS `root`@`localhost`; 74 75--connection default 76GRANT SYSTEM_USER ON *.* TO foo; 77 78--connection conn_foo 79# Impersonating a user more privileged than you is not allowed 80--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 81GRANT SELECT ON *.* TO bar AS `root`@`localhost`; 82 83--connection default 84--disconnect conn_foo 85REVOKE ALL ON *.* FROM foo; 86GRANT SYSTEM_USER ON *.* TO foo; 87GRANT INSERT ON *.* TO foo WITH GRANT OPTION; 88GRANT SELECT ON *.* TO baz WITH GRANT OPTION; 89GRANT SELECT ON db1.* to qux; 90GRANT baz, qux TO foo; 91 92--connect(conn_foo, localhost, foo,,,,,) 93SET ROLE baz; 94GRANT SELECT ON *.* TO bar AS `root`@`localhost`; 95 96SET ROLE qux; 97--error ER_ACCESS_DENIED_ERROR 98GRANT SELECT ON *.* TO bar; 99 100--connection default 101--disconnect conn_foo 102 103DROP DATABASE db1; 104DROP ROLE baz, qux; 105DROP USER foo, bar; 106 107CREATE USER foo, bar, baz; 108CREATE ROLE r1, r2, r3; 109GRANT SELECT ON *.* TO foo WITH GRANT OPTION; 110GRANT SYSTEM_USER ON *.* TO r2; 111GRANT r2 TO r3; 112GRANT r1, r3 TO bar; 113 114--connect(conn_foo, localhost, foo,,,,,) 115# Should succeed 116GRANT SELECT ON *.* TO baz AS bar; 117GRANT SELECT ON *.* TO baz AS bar WITH ROLE NONE; 118GRANT SELECT ON *.* TO baz AS bar WITH ROLE r1; 119 120# Impersonating a user more privileged than you is not allowed 121--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 122GRANT SELECT ON *.* TO baz AS bar WITH ROLE r2; 123--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 124GRANT SELECT ON *.* TO baz AS bar WITH ROLE r1, r2; 125 126GRANT SELECT ON *.* TO baz AS bar WITH ROLE ALL; 127 128--connection default 129--disconnect conn_foo 130SHOW GRANTS FOR baz; 131 132DROP ROLE r1, r2, r3; 133DROP USER foo, bar, baz; 134 135--echo ------------------------------------------------------------------------- 136--echo # 3. User test 137--echo ------------------------------------------------------------------------- 138 139CREATE USER foo, bar; 140CREATE DATABASE db1; 141 142GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER(); 143SHOW GRANTS FOR bar; 144 145REVOKE ALL ON *.* FROM bar; 146GRANT SELECT ON *.* TO bar AS foo; 147SHOW GRANTS FOR bar; 148 149REVOKE ALL ON *.* FROM bar; 150GRANT SELECT ON *.* TO foo; 151REVOKE SELECT ON db1.* FROM foo; 152 153GRANT SELECT ON *.* TO bar AS foo; 154SHOW GRANTS FOR bar; 155 156REVOKE ALL ON *.* FROM bar; 157GRANT SELECT, INSERT ON *.* TO bar AS foo; 158SHOW GRANTS FOR bar; 159 160DROP DATABASE db1; 161DROP USER foo, bar; 162 163--echo ------------------------------------------------------------------------- 164--echo # 4. Roles test 165--echo ------------------------------------------------------------------------- 166 167CREATE USER foo, bar; 168CREATE ROLE r1, r2, r3, r4; 169CREATE DATABASE db1; 170 171GRANT SELECT ON *.* TO r1; 172REVOKE SELECT ON db1.* FROM r1; 173GRANT r1 TO r2; 174 175GRANT INSERT ON *.* TO r3; 176REVOKE INSERT ON db1.* FROM r3; 177GRANT r3 TO r4; 178 179GRANT r2, r4 TO foo; 180 181GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL; 182SHOW GRANTS FOR bar; 183 184REVOKE ALL ON *.* FROM bar; 185ALTER USER foo DEFAULT ROLE ALL; 186 187GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL; 188SHOW GRANTS FOR bar; 189 190REVOKE ALL ON *.* FROM bar; 191 192GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r4; 193SHOW GRANTS FOR bar; 194 195REVOKE ALL ON *.* FROM bar; 196ALTER USER foo DEFAULT ROLE ALL; 197 198GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2, r4; 199SHOW GRANTS FOR bar; 200 201DROP DATABASE db1; 202DROP ROLE r1, r2, r3, r4; 203DROP USER foo, bar; 204 205--echo ------------------------------------------------------------------------- 206--echo # 6. Without --partial_revokes 207--echo ------------------------------------------------------------------------- 208 209SET @save_partial_revokes = @@global.partial_revokes; 210SET @@global.partial_revokes = OFF; 211 212CREATE USER foo, bar; 213CREATE ROLE r1, r2; 214 215GRANT r1, r2 TO foo; 216ALTER USER foo DEFAULT ROLE ALL; 217 218GRANT SELECT, INSERT ON *.* TO bar AS foo; 219GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE NONE; 220GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL; 221GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r1; 222GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE DEFAULT; 223 224DROP USER foo, bar; 225DROP ROLE r1, r2; 226 227SET @@global.partial_revokes = @save_partial_revokes; 228 229--echo ------------------------------------------------------------------------- 230--echo # 7. Binlog tests 231--echo ------------------------------------------------------------------------- 232 233--let $mask_anonymous_gtid_events= 1 234--let $mask_user_password_events= 1 235 236CREATE USER foo, bar; 237CREATE ROLE r1, r2, r3, r4; 238CREATE DATABASE db1; 239 240GRANT SELECT ON *.* TO r1 WITH GRANT OPTION; 241REVOKE SELECT ON db1.* FROM r1; 242GRANT r1 TO r2; 243 244GRANT INSERT ON *.* TO r3 WITH GRANT OPTION; 245REVOKE INSERT ON db1.* FROM r3; 246GRANT r3 TO r4; 247 248GRANT r2, r4 TO foo; 249 250ALTER USER foo DEFAULT ROLE ALL; 251 252--source include/save_binlog_position.inc 253--let $binlog_start = $binlog_position 254 255GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL; 256GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE NONE; 257GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r2; 258GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE DEFAULT; 259GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2, r4; 260 261GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER(); 262GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE ALL; 263GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE NONE; 264GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE DEFAULT; 265 266GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE ALL; 267GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE NONE; 268GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE ALL EXCEPT r2; 269GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE DEFAULT; 270GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE r2, r4; 271 272--connect(conn_foo, localhost, foo,,,,,) 273 274GRANT SELECT, INSERT ON *.* TO bar; 275 276--connection default 277--disconnect conn_foo 278--source include/show_binlog_events.inc 279 280GRANT SELECT, INSERT ON *.* TO foo; 281REVOKE SELECT, INSERT ON db1.* FROM foo; 282 283--source include/save_binlog_position.inc 284--let $binlog_start = $binlog_position 285 286GRANT SELECT, INSERT ON *.* TO bar AS foo; 287 288--source include/show_binlog_events.inc 289 290DROP DATABASE db1; 291DROP ROLE r1, r2, r3, r4; 292DROP USER foo, bar; 293 294--echo ------------------------------------------------------------------------- 295--echo # 8. Current user in AS caluse 296--echo ------------------------------------------------------------------------- 297 298CREATE USER foo, bar, baz; 299CREATE ROLE r1, r2; 300GRANT SELECT, INSERT ON *.* TO r1 WITH GRANT OPTION; 301REVOKE SELECT ON mysql.* FROM r1; 302GRANT SELECT, INSERT ON *.* TO r2 WITH GRANT OPTION; 303REVOKE INSERT ON mysql.* FROM r2; 304GRANT r1, r2 TO foo; 305ALTER USER foo DEFAULT ROLE ALL; 306 307--connect(conn_foo, localhost, foo,,,,,) 308 309GRANT SELECT ON *.* TO bar AS current_user() WITH ROLE r1; 310 311GRANT INSERT ON *.* TO baz AS foo WITH ROLE r2; 312 313--connection default 314--disconnect conn_foo 315SHOW GRANTS FOR bar; 316SHOW GRANTS FOR baz; 317 318DROP ROLE r1, r2; 319DROP USER foo, bar, baz; 320 321CREATE USER foo, bar; 322CREATE ROLE r1, r2; 323GRANT r1, r2 TO foo; 324GRANT SELECT ON *.* TO r1 WITH GRANT OPTION; 325REVOKE SELECT ON mysql.* FROM r1; 326GRANT INSERT ON *.* TO r2 WITH GRANT OPTION; 327REVOKE INSERT ON mysql.* FROM r2; 328ALTER USER foo DEFAULT ROLE ALL; 329 330--connect(conn_foo, localhost, foo,,,,,) 331 332--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 333GRANT SELECT, INSERT ON *.* TO bar AS current_user(); 334--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 335GRANT SELECT, INSERT ON *.* TO bar AS current_user() WITH ROLE r1; 336--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 337GRANT SELECT, INSERT ON *.* TO bar AS current_user() WITH ROLE r2; 338 339--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 340GRANT SELECT, INSERT ON *.* TO bar AS foo; 341--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 342GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r1; 343--error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS 344GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2; 345 346--connection default 347--disconnect conn_foo 348DROP ROLE r1, r2; 349DROP USER foo, bar; 350 351--echo ------------------------------------------------------------------------- 352--echo # 9. General log 353--echo ------------------------------------------------------------------------- 354 355SET @save_partial_revokes = @@global.partial_revokes; 356 357CREATE USER u1, u2; 358CREATE ROLE r1, r2; 359GRANT r1, r2 TO u1; 360GRANT SELECT ON *.* TO r1 WITH GRANT OPTION; 361GRANT INSERT ON *.* TO r2 WITH GRANT OPTION; 362ALTER USER u1 DEFAULT ROLE ALL; 363--connect(conn_u1, localhost, u1,,,,,) 364 365--connection default 366 367# Make sure we start with a clean slate. log_tables.test says this is OK. 368TRUNCATE TABLE mysql.general_log; 369SET @old_log_output= @@global.log_output; 370SET @old_general_log= @@global.general_log; 371SET @old_general_log_file= @@global.general_log_file; 372 373let $general_file_off = $MYSQLTEST_VARDIR/log/grant_as_ddl_general.log; 374--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 375eval SET GLOBAL general_log_file = '$general_file_off'; 376SET GLOBAL log_output = 'FILE,TABLE'; 377SET GLOBAL general_log= 'ON'; 378 379SET @@global.partial_revokes = ON; 380 381REVOKE SELECT ON mysql.* FROM r1; 382REVOKE INSERT ON mysql.* FROM r2; 383 384GRANT SELECT, INSERT ON *.* TO u2; 385GRANT SELECT, INSERT ON *.* TO u2 AS u1; 386GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE; 387GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT; 388GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL; 389GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1; 390 391--connection conn_u1 392SET ROLE NONE; 393 394SET ROLE r1; 395GRANT SELECT ON *.* TO u2; 396 397SET ROLE r1, r2; 398GRANT SELECT, INSERT ON *.* TO u2; 399 400SET ROLE ALL; 401GRANT SELECT, INSERT ON *.* TO u2; 402 403SET ROLE DEFAULT; 404GRANT SELECT, INSERT ON *.* TO u2; 405GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE r1; 406GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL; 407GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE DEFAULT; 408GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r2; 409 410SET ROLE NONE; 411 412--connection default 413GRANT SELECT ON *.* TO r1; 414GRANT INSERT ON *.* TO r2; 415 416SET @@global.partial_revokes = OFF; 417 418GRANT SELECT, INSERT ON *.* TO u2; 419GRANT SELECT, INSERT ON *.* TO u2 AS u1; 420GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE; 421GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT; 422GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL; 423GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1; 424 425--connection conn_u1 426SET ROLE NONE; 427 428SET ROLE r1; 429GRANT SELECT ON *.* TO u2; 430 431SET ROLE r1, r2; 432GRANT SELECT, INSERT ON *.* TO u2; 433 434SET ROLE ALL; 435GRANT SELECT, INSERT ON *.* TO u2; 436 437SET ROLE DEFAULT; 438GRANT SELECT, INSERT ON *.* TO u2; 439GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE r1; 440GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL; 441GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE DEFAULT; 442GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r2; 443 444SET ROLE NONE; 445 446--connection default 447--disconnect conn_u1 448DROP ROLE r1, r2; 449DROP USER u1, u2; 450 451SET @@global.partial_revokes = @save_partial_revokes; 452 453--echo Show what is logged: 454 455--echo ------ rewrite ------ 456SELECT COUNT(*) = 8 AS NO_AS_CLAUSE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2' AND command_type NOT LIKE 'Prepare'; 457SELECT COUNT(*) = 2 AS WITH_AS_WITHOUT_ROLE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1' AND command_type NOT LIKE 'Prepare'; 458SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_NONE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE' AND command_type NOT LIKE 'Prepare'; 459SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_DEFAULT_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT' AND command_type NOT LIKE 'Prepare'; 460SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_ALL_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL' AND command_type NOT LIKE 'Prepare'; 461SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_ALL_EXCEPT_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1' AND command_type NOT LIKE 'Prepare'; 462--echo ------ done ------ 463 464--remove_file $general_file_off 465 466SET GLOBAL general_log_file= @old_general_log_file; 467SET GLOBAL general_log= @old_general_log; 468SET GLOBAL log_output= @old_log_output; 469 470--echo ------------------------------------------------------------------------- 471 472