| 1 |
USE information_schema; |
| 2 |
#################################################################################### |
| 3 |
1 Prepare test. |
| 4 |
connection default (user=root) |
| 5 |
#################################################################################### |
| 6 |
#################################################################################### |
| 7 |
1.1 Create two user |
| 8 |
#################################################################################### |
| 9 |
DROP USER ddicttestuser1@'localhost'; |
| 10 |
DROP USER ddicttestuser2@'localhost'; |
| 11 |
CREATE USER ddicttestuser1@'localhost'; |
| 12 |
CREATE USER ddicttestuser2@'localhost'; |
| 13 |
SET PASSWORD FOR ddicttestuser1@'localhost' = 'ddictpass'; |
| 14 |
SET PASSWORD FOR ddicttestuser2@'localhost' = 'ddictpass'; |
| 15 |
#################################################################################### |
| 16 |
1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege): |
| 17 |
#################################################################################### |
| 18 |
#################################################################################### |
| 19 |
2 connection default(user=root with default privileges): |
| 20 |
SHOW/SELECT shows all processes/threads. |
| 21 |
#################################################################################### |
| 22 |
Timeout in wait_condition.inc for SELECT COUNT(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST |
| 23 |
WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1' |
| 24 |
SHOW CREATE TABLE processlist; |
| 25 |
Table Create Table |
| 26 |
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( |
| 27 |
`ID` bigint unsigned NOT NULL DEFAULT '0', |
| 28 |
`USER` varchar(32) NOT NULL DEFAULT '', |
| 29 |
`HOST` varchar(261) NOT NULL DEFAULT '', |
| 30 |
`DB` varchar(64) DEFAULT NULL, |
| 31 |
`COMMAND` varchar(16) NOT NULL DEFAULT '', |
| 32 |
`TIME` int NOT NULL DEFAULT '0', |
| 33 |
`STATE` varchar(64) DEFAULT NULL, |
| 34 |
`INFO` longtext |
| 35 |
) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3 |
| 36 |
SHOW processlist; |
| 37 |
Id User Host db Command Time State Info |
| 38 |
ID 1287 HOST_NAME |
| 39 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 40 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 41 |
ID root HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 42 |
Warnings: |
| 43 |
SELECT * FROM processlist ORDER BY id; |
| 44 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 45 |
ID 1287 HOST_NAME |
| 46 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 47 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 48 |
ID root HOST_NAME information_schema Query TIME executing SELECT * FROM processlist ORDER BY id |
| 49 |
Warnings: |
| 50 |
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; |
| 51 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 52 |
ID 1287 HOST_NAME |
| 53 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 54 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 55 |
ID root HOST_NAME information_schema Query TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id |
| 56 |
Warnings: |
| 57 |
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; |
| 58 |
Warnings: |
| 59 |
Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead |
| 60 |
UPDATE test.t_processlist SET user='horst' WHERE id=1 ; |
| 61 |
INSERT INTO processlist SELECT * FROM test.t_processlist; |
| 62 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 63 |
DROP TABLE test.t_processlist; |
| 64 |
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION; |
| 65 |
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist' |
| 66 |
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist; |
| 67 |
UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1; |
| 68 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 69 |
DROP VIEW test.v_processlist; |
| 70 |
UPDATE processlist SET user='any_user' WHERE id=1 ; |
| 71 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 72 |
DELETE FROM processlist WHERE id=1 ; |
| 73 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 74 |
REVOKE ALL ON processlist FROM current_user; |
| 75 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 76 |
GRANT INSERT,UPDATE ON processlist TO current_user; |
| 77 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 78 |
SHOW GRANTS; |
| 79 |
Grants for root@localhost |
| 80 |
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| 81 |
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| 82 |
GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION |
| 83 |
CREATE INDEX i_processlist ON processlist (user); |
| 84 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 85 |
DROP TABLE processlist; |
| 86 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 87 |
ALTER TABLE processlist DROP COLUMN user; |
| 88 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 89 |
ALTER TABLE processlist ADD COLUMN (my_column INT); |
| 90 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 91 |
RENAME TABLE processlist TO new_processlist; |
| 92 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 93 |
RENAME TABLE processlist TO files; |
| 94 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 95 |
CREATE TABLE new_processlist AS SELECT * FROM processlist; |
| 96 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 97 |
DROP DATABASE information_schema; |
| 98 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 99 |
RENAME DATABASE information_schema TO info_schema; |
| 100 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1 |
| 101 |
#################################################################################### |
| 102 |
3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege): |
| 103 |
SHOW/SELECT shows only the processes (1) of the user. |
| 104 |
#################################################################################### |
| 105 |
SHOW CREATE TABLE processlist; |
| 106 |
Table Create Table |
| 107 |
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` ( |
| 108 |
`ID` bigint unsigned NOT NULL DEFAULT '0', |
| 109 |
`USER` varchar(32) NOT NULL DEFAULT '', |
| 110 |
`HOST` varchar(261) NOT NULL DEFAULT '', |
| 111 |
`DB` varchar(64) DEFAULT NULL, |
| 112 |
`COMMAND` varchar(16) NOT NULL DEFAULT '', |
| 113 |
`TIME` int NOT NULL DEFAULT '0', |
| 114 |
`STATE` varchar(64) DEFAULT NULL, |
| 115 |
`INFO` longtext |
| 116 |
) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3 |
| 117 |
SHOW processlist; |
| 118 |
Id User Host db Command Time State Info |
| 119 |
ID 1287 HOST_NAME |
| 120 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 121 |
Warnings: |
| 122 |
SELECT * FROM processlist ORDER BY id; |
| 123 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 124 |
ID 1287 HOST_NAME |
| 125 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM processlist ORDER BY id |
| 126 |
Warnings: |
| 127 |
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id; |
| 128 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 129 |
ID 1287 HOST_NAME |
| 130 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id |
| 131 |
Warnings: |
| 132 |
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist; |
| 133 |
Warnings: |
| 134 |
Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead |
| 135 |
UPDATE test.t_processlist SET user='horst' WHERE id=1 ; |
| 136 |
INSERT INTO processlist SELECT * FROM test.t_processlist; |
| 137 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 138 |
DROP TABLE test.t_processlist; |
| 139 |
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION; |
| 140 |
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist' |
| 141 |
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist; |
| 142 |
UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1; |
| 143 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 144 |
DROP VIEW test.v_processlist; |
| 145 |
UPDATE processlist SET user='any_user' WHERE id=1 ; |
| 146 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 147 |
DELETE FROM processlist WHERE id=1 ; |
| 148 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 149 |
REVOKE ALL ON processlist FROM current_user; |
| 150 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 151 |
GRANT INSERT,UPDATE ON processlist TO current_user; |
| 152 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 153 |
SHOW GRANTS; |
| 154 |
Grants for ddicttestuser1@localhost |
| 155 |
GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` |
| 156 |
CREATE INDEX i_processlist ON processlist (user); |
| 157 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 158 |
DROP TABLE processlist; |
| 159 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 160 |
ALTER TABLE processlist DROP COLUMN user; |
| 161 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 162 |
ALTER TABLE processlist ADD COLUMN (my_column INT); |
| 163 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 164 |
RENAME TABLE processlist TO new_processlist; |
| 165 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 166 |
RENAME TABLE processlist TO files; |
| 167 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 168 |
CREATE TABLE new_processlist AS SELECT * FROM processlist; |
| 169 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 170 |
DROP DATABASE information_schema; |
| 171 |
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema' |
| 172 |
RENAME DATABASE information_schema TO info_schema; |
| 173 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1 |
| 174 |
#################################################################################### |
| 175 |
4 Grant PROCESS privilege to ddicttestuser1 |
| 176 |
connection default (user=root) |
| 177 |
#################################################################################### |
| 178 |
GRANT PROCESS ON *.* TO ddicttestuser1@'localhost'; |
| 179 |
#################################################################################### |
| 180 |
4.1 Existing connection con100 (ddicttestuser1) |
| 181 |
The user ddicttestuser1 has the PROCESS privilege, but the connection was |
| 182 |
established before PROCESS was granted. |
| 183 |
SHOW/SELECT shows only the processes (1) of the user. |
| 184 |
#################################################################################### |
| 185 |
SHOW GRANTS; |
| 186 |
Grants for ddicttestuser1@localhost |
| 187 |
GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost` |
| 188 |
SHOW processlist; |
| 189 |
Id User Host db Command Time State Info |
| 190 |
ID 1287 HOST_NAME |
| 191 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 192 |
Warnings: |
| 193 |
SELECT * FROM information_schema.processlist; |
| 194 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 195 |
ID 1287 HOST_NAME |
| 196 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 197 |
Warnings: |
| 198 |
#################################################################################### |
| 199 |
4.2 New connection con101 (ddicttestuser1 with PROCESS privilege) |
| 200 |
SHOW/SELECT shows all processes/threads. |
| 201 |
#################################################################################### |
| 202 |
SHOW GRANTS; |
| 203 |
Grants for ddicttestuser1@localhost |
| 204 |
GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost` |
| 205 |
SHOW processlist; |
| 206 |
Id User Host db Command Time State Info |
| 207 |
ID 1287 HOST_NAME |
| 208 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 209 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 210 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 211 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 212 |
Warnings: |
| 213 |
SELECT * FROM information_schema.processlist; |
| 214 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 215 |
ID 1287 HOST_NAME |
| 216 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 217 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 218 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 219 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 220 |
Warnings: |
| 221 |
#################################################################################### |
| 222 |
5 Grant PROCESS privilege to anonymous user. |
| 223 |
connection default (user=root) |
| 224 |
#################################################################################### |
| 225 |
CREATE USER ''@'localhost'; |
| 226 |
GRANT PROCESS ON *.* TO ''@'localhost'; |
| 227 |
#################################################################################### |
| 228 |
5.1 Establish connection (anonymous1,localhost,'',,information_schema) |
| 229 |
anonymous user with PROCESS privilege |
| 230 |
SHOW/SELECT shows all processes/threads. |
| 231 |
#################################################################################### |
| 232 |
SHOW GRANTS; |
| 233 |
Grants for @localhost |
| 234 |
GRANT PROCESS ON *.* TO ``@`localhost` |
| 235 |
SHOW processlist; |
| 236 |
Id User Host db Command Time State Info |
| 237 |
ID HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 238 |
ID 1287 HOST_NAME |
| 239 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 240 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 241 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 242 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 243 |
Warnings: |
| 244 |
SELECT * FROM information_schema.processlist; |
| 245 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 246 |
ID HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 247 |
ID 1287 HOST_NAME |
| 248 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 249 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 250 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 251 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 252 |
Warnings: |
| 253 |
#################################################################################### |
| 254 |
6 Revoke PROCESS privilege from ddicttestuser1 |
| 255 |
connection default (user=root) |
| 256 |
#################################################################################### |
| 257 |
REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost'; |
| 258 |
#################################################################################### |
| 259 |
6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege) |
| 260 |
Again (compared to state before GRANT PROCESS) only the processes of |
| 261 |
ddicttestuser1 are visible. |
| 262 |
#################################################################################### |
| 263 |
SHOW GRANTS; |
| 264 |
Grants for ddicttestuser1@localhost |
| 265 |
GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` |
| 266 |
SHOW processlist; |
| 267 |
Id User Host db Command Time State Info |
| 268 |
ID 1287 HOST_NAME |
| 269 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 270 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 271 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 272 |
Warnings: |
| 273 |
SELECT * FROM information_schema.processlist; |
| 274 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 275 |
ID 1287 HOST_NAME |
| 276 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 277 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 278 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 279 |
Warnings: |
| 280 |
#################################################################################### |
| 281 |
7 Revoke PROCESS privilege from anonymous user |
| 282 |
connection default (user=root) |
| 283 |
#################################################################################### |
| 284 |
REVOKE PROCESS ON *.* FROM ''@'localhost'; |
| 285 |
#################################################################################### |
| 286 |
7.1 New connection (anonymous2,localhost,'',,information_schema) |
| 287 |
The anonymous user has no more the PROCESS privilege |
| 288 |
Again only the processes of the anonymous user are visible. |
| 289 |
#################################################################################### |
| 290 |
SHOW GRANTS FOR ''@'localhost'; |
| 291 |
Grants for @localhost |
| 292 |
GRANT USAGE ON *.* TO ``@`localhost` |
| 293 |
SELECT * FROM information_schema.processlist; |
| 294 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 295 |
ID HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 296 |
ID HOST_NAME information_schema Sleep TIME NULL |
| 297 |
ID 1287 HOST_NAME |
| 298 |
Warnings: |
| 299 |
#################################################################################### |
| 300 |
8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1 |
| 301 |
connection default (user=root) |
| 302 |
#################################################################################### |
| 303 |
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost'; |
| 304 |
Warnings: |
| 305 |
Warning 1287 The SUPER privilege identifier is deprecated |
| 306 |
#################################################################################### |
| 307 |
8.1 New connection con103 (ddicttestuser1 with SUPER privilege) |
| 308 |
Only the processes of ddicttestuser1 user are visible. |
| 309 |
#################################################################################### |
| 310 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 311 |
Grants for ddicttestuser1@localhost |
| 312 |
GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost` |
| 313 |
SHOW processlist; |
| 314 |
Id User Host db Command Time State Info |
| 315 |
ID 1287 HOST_NAME |
| 316 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 317 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 318 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 319 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 320 |
Warnings: |
| 321 |
SELECT * FROM information_schema.processlist; |
| 322 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 323 |
ID 1287 HOST_NAME |
| 324 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 325 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 326 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 327 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 328 |
Warnings: |
| 329 |
#################################################################################### |
| 330 |
9 Revoke SUPER privilege from user ddicttestuser1 |
| 331 |
connection default (user=root) |
| 332 |
#################################################################################### |
| 333 |
REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost'; |
| 334 |
Warnings: |
| 335 |
Warning 1287 The SUPER privilege identifier is deprecated |
| 336 |
#################################################################################### |
| 337 |
9.1 New connection con104 (ddicttestuser1 without SUPER privilege) |
| 338 |
ddicttestuser1 has no more the SUPER privilege. |
| 339 |
Only the processes of ddicttestuser1 are visible. |
| 340 |
#################################################################################### |
| 341 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 342 |
Grants for ddicttestuser1@localhost |
| 343 |
GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` |
| 344 |
SHOW processlist; |
| 345 |
Id User Host db Command Time State Info |
| 346 |
ID 1287 HOST_NAME |
| 347 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 348 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 349 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 350 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 351 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 352 |
Warnings: |
| 353 |
SELECT * FROM information_schema.processlist; |
| 354 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 355 |
ID 1287 HOST_NAME |
| 356 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 357 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 358 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 359 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 360 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 361 |
Warnings: |
| 362 |
#################################################################################### |
| 363 |
10 Grant SUPER privilege with grant option to user ddicttestuser1. |
| 364 |
connection default (user=root) |
| 365 |
#################################################################################### |
| 366 |
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; |
| 367 |
Warnings: |
| 368 |
Warning 1287 The SUPER privilege identifier is deprecated |
| 369 |
#################################################################################### |
| 370 |
10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION) |
| 371 |
Try to grant PROCESS privilege to user ddicttestuser2 without having it. |
| 372 |
#################################################################################### |
| 373 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 374 |
Grants for ddicttestuser1@localhost |
| 375 |
GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION |
| 376 |
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; |
| 377 |
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES) |
| 378 |
#################################################################################### |
| 379 |
10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1 |
| 380 |
connection default (user=root) |
| 381 |
#################################################################################### |
| 382 |
GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION; |
| 383 |
Warnings: |
| 384 |
Warning 1287 The SUPER privilege identifier is deprecated |
| 385 |
#################################################################################### |
| 386 |
10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION) |
| 387 |
Grant PROCESS privilege to user ddicttestuser2 |
| 388 |
#################################################################################### |
| 389 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 390 |
Grants for ddicttestuser1@localhost |
| 391 |
GRANT PROCESS, SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION |
| 392 |
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; |
| 393 |
#################################################################################### |
| 394 |
10.4 New connection con200 (ddicttestuser2 with PROCESS privilege) |
| 395 |
ddicttestuser2 has now the PROCESS privilege and sees all connections |
| 396 |
#################################################################################### |
| 397 |
SHOW GRANTS FOR 'ddicttestuser2'@'localhost'; |
| 398 |
Grants for ddicttestuser2@localhost |
| 399 |
GRANT PROCESS ON *.* TO `ddicttestuser2`@`localhost` |
| 400 |
SHOW processlist; |
| 401 |
Id User Host db Command Time State Info |
| 402 |
ID HOST_NAME information_schema Sleep TIME NULL |
| 403 |
ID HOST_NAME information_schema Sleep TIME NULL |
| 404 |
ID 1287 HOST_NAME |
| 405 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 406 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 407 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 408 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 409 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 410 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 411 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 412 |
ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 413 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 414 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 415 |
Warnings: |
| 416 |
SELECT * FROM information_schema.processlist; |
| 417 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 418 |
ID HOST_NAME information_schema Sleep TIME NULL |
| 419 |
ID HOST_NAME information_schema Sleep TIME NULL |
| 420 |
ID 1287 HOST_NAME |
| 421 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 422 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 423 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 424 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 425 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 426 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 427 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 428 |
ID ddicttestuser2 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 429 |
ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL |
| 430 |
ID root HOST_NAME information_schema Sleep TIME NULL |
| 431 |
Warnings: |
| 432 |
#################################################################################### |
| 433 |
11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2 |
| 434 |
connection ddicttestuser1; |
| 435 |
#################################################################################### |
| 436 |
REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost'; |
| 437 |
#################################################################################### |
| 438 |
11.1 New connection con201 (ddicttestuser2) |
| 439 |
ddicttestuser2 has no more the PROCESS privilege and can only see own connects |
| 440 |
#################################################################################### |
| 441 |
SHOW GRANTS; |
| 442 |
Grants for ddicttestuser2@localhost |
| 443 |
GRANT USAGE ON *.* TO `ddicttestuser2`@`localhost` |
| 444 |
SHOW processlist; |
| 445 |
Id User Host db Command Time State Info |
| 446 |
ID 1287 HOST_NAME |
| 447 |
ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 448 |
ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL |
| 449 |
Warnings: |
| 450 |
SELECT * FROM information_schema.processlist; |
| 451 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 452 |
ID 1287 HOST_NAME |
| 453 |
ID ddicttestuser2 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 454 |
ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL |
| 455 |
Warnings: |
| 456 |
#################################################################################### |
| 457 |
11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1 |
| 458 |
connection default (user=root) |
| 459 |
#################################################################################### |
| 460 |
REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost'; |
| 461 |
Warnings: |
| 462 |
Warning 1287 The SUPER privilege identifier is deprecated |
| 463 |
#################################################################################### |
| 464 |
11.3 New connection con107 (ddicttestuser1) |
| 465 |
ddicttestuser1 has no more the PROCESS privilege and can only see own connects |
| 466 |
He is also unable to GRANT the PROCESS privilege to ddicttestuser2 |
| 467 |
#################################################################################### |
| 468 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 469 |
Grants for ddicttestuser1@localhost |
| 470 |
GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` |
| 471 |
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost'; |
| 472 |
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES) |
| 473 |
SHOW processlist; |
| 474 |
Id User Host db Command Time State Info |
| 475 |
ID 1287 HOST_NAME |
| 476 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 477 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 478 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 479 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 480 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 481 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 482 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 483 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 484 |
Warnings: |
| 485 |
SELECT * FROM information_schema.processlist; |
| 486 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 487 |
ID 1287 HOST_NAME |
| 488 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 489 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 490 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 491 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 492 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 493 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 494 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 495 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 496 |
Warnings: |
| 497 |
#################################################################################### |
| 498 |
12 Revoke the SELECT privilege from user ddicttestuser1 |
| 499 |
connection default (user=root) |
| 500 |
#################################################################################### |
| 501 |
REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost'; |
| 502 |
#################################################################################### |
| 503 |
12.1 New connection con108 (ddicttestuser1) |
| 504 |
ddicttestuser1 has neither PROCESS nor SELECT privilege |
| 505 |
Manual says: Each MySQL user has the right to access these tables, but can see |
| 506 |
only the rows ... |
| 507 |
Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST. |
| 508 |
#################################################################################### |
| 509 |
SHOW GRANTS FOR 'ddicttestuser1'@'localhost'; |
| 510 |
Grants for ddicttestuser1@localhost |
| 511 |
GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost` |
| 512 |
SHOW processlist; |
| 513 |
Id User Host db Command Time State Info |
| 514 |
ID 1287 HOST_NAME |
| 515 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist |
| 516 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 517 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 518 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 519 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 520 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 521 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 522 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 523 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 524 |
Warnings: |
| 525 |
SELECT * FROM information_schema.processlist; |
| 526 |
ID USER HOST DB COMMAND TIME STATE INFO |
| 527 |
ID 1287 HOST_NAME |
| 528 |
ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist |
| 529 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 530 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 531 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 532 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 533 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 534 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 535 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 536 |
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL |
| 537 |
Warnings: |
| 538 |
#################################################################################### |
| 539 |
12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1. |
| 540 |
connection default (user=root) |
| 541 |
#################################################################################### |
| 542 |
REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost'; |
| 543 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' |
| 544 |
#################################################################################### |
| 545 |
connection default (user=root) |
| 546 |
Cleanup: close connections, DROP USER etc. |
| 547 |
#################################################################################### |
| 548 |
DROP USER ddicttestuser1@'localhost'; |
| 549 |
DROP USER ddicttestuser2@'localhost'; |
| 550 |
REVOKE USAGE ON *.* FROM ''@'localhost'; |
| 551 |
DROP USER ''@'localhost'; |