pt-show-grants --ignore does not ignore all accounts
General
Escalation
General
Escalation
Description
Environment
None
Activity
Show:
Aaditya Dubey August 21, 2024 at 9:27 AM
Hi @Matthew Boehm
Thank you for the report.
Verified as described.
$:~/percona-toolkit-3.6.0/bin$ ./pt-show-grants --version
pt-show-grants 3.6.0
Created user on MySQL 8.0.36 like following:
mysql [localhost:8036] {root} ((none)) > CREATE USER 'adi'@'%' IDENTIFIED WITH mysql_native_password BY 'msandbox';
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:8036] {root} ((none)) > grant all on *.* to 'adi'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8036] {root} ((none)) >
mysql [localhost:8036] {root} ((none)) > CREATE USER 'adi'@'127.%' IDENTIFIED WITH mysql_native_password BY 'msandbox';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8036] {root} ((none)) > grant all on *.* to 'adi'@'127.%';
Query OK, 0 rows affected (0.01 sec)
Run following cmd without ignore user:
./pt-show-grants --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox8036.sock
-- Grants for 'adi'@'%'
CREATE USER IF NOT EXISTS `adi`@`%`;
ALTER USER `adi`@`%` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `adi`@`%`;
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 `adi`@`%`;
-- Grants for 'adi'@'127.%'
CREATE USER IF NOT EXISTS `adi`@`127.%`;
ALTER USER `adi`@`127.%` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `adi`@`127.%`;
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 `adi`@`127.%`;
-- Grants for 'adi'@'localhost'
Run following cmd with ignore user:
./pt-show-grants --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox8036.sock --ignore=adi
-- Grants for 'adi'@'127.%'
CREATE USER IF NOT EXISTS `adi`@`127.%`;
ALTER USER `adi`@`127.%` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `adi`@`127.%`;
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 `adi`@`127.%`;
-- Grants for 'adi'@'localhost'
CREATE USER IF NOT EXISTS `adi`@`localhost`;
ALTER USER `adi`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALL PRIVILEGES ON `test`.* TO `adi`@`localhost`;Only `adi`@`%` is ignored.
The expectation is that if I say
--ignore=bobthat every combination of the bob user will be ignored. This includes bob@localhost, bob@::1, bob@foobar, etc. But this is not the case. Only bob@% is ignored; all other instances of bob user are output.$ pt-show-grants ppw1dv08msqcn1a.cdc.com | grep mboehm -- Grants for 'mboehm'@'%' CREATE USER IF NOT EXISTS 'mboehm'@'%' IDENTIFIED WITH mysql_native_password BY '*F808D5D9B30E254386' ; GRANT ALL PRIVILEGES ON *.* TO 'mboehm'@'%' WITH GRANT OPTION; -- Grants for 'mboehm'@'10.4.%' CREATE USER IF NOT EXISTS 'mboehm'@'10.4.%' IDENTIFIED WITH mysql_native_password BY '*9B50032AE5CF4847604C6E5'; GRANT USAGE ON *.* TO 'mboehm'@'10.4.%' ; $ pt-show-grants --ignore=mboehm ppw1dv08msqcn1a.cdc.com | grep mboehm -- Grants for 'mboehm'@'10.4.%' CREATE USER IF NOT EXISTS 'mboehm'@'10.4.%' IDENTIFIED WITH mysql_native_password BY '*9B50B52AE5CF4847604C6E5'; GRANT USAGE ON *.* TO 'mboehm'@'10.4.%' ;