go-pass

README

go-pass

• caching_sha2_password
• variable print_identified_with_as_hex

The why?

In older versions of MySQL, you used to be able to use the password() funtion and use that hash for scripts, Ansible and what not. You can't anymore and I wanted to see what I could do or what has been done.

Why not just use pt-show-grants

• https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html

I just wanted to learn more about it and how to use it, plus I wanted to see how to do it with Golang.

Testing Environment

• Docker run docker run -d --name ps -d -p 3306:3306/tcp -e MYSQL_ROOT_PASSWORD=root percona/percona-server:8.0.32-24
• Percona-Server 8.0.32-24

reference

• https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html
• https://dev.mysql.com/doc/refman/8.0/en/system-variables.html
• https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html#caching-sha2-pluggable-authentication-password-hashing
• https://dev.mysql.com/doc/refman/8.0/en/system-variables.html#sysvar_print_identified_with_as_hex

Author of the Bug: Simon Mudd https://bugs.mysql.com/bug.php?id=98732

Usage

Usage: ./go-pass -s < source host> -f <dump file>"
  -f string
        Dump file
  -s string
        Source host

Example - 1:

Without using Sed & Grep:

go-pass -s 10.8.0.15 -f show_users.sql                                                                                                           
2023/06/22 15:00:16 [+] Connecting to database: root:root@tcp(10.8.0.15:3306)/mysql
[+] Dumping user accounts to file: show_users.sql
-- CREATE USER for chaoshour@%: 
 CREATE USER `chaoshour`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240D5537623E2E2E57766976017D54187F50145825525739787850794C307154765055494B5569345A78736D4B2F36463244714F4459744D7734434A4E717236 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- Grants for chaoshour@%: 
 GRANT USAGE ON *.* TO `chaoshour`@`%`;
-- CREATE USER for johnny5@%: 
 CREATE USER `johnny5`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240A251F0C612928636C7D1F523B6B034A651B15694579477A6F6867424A48335453496B514F2F49653644334B6A6A7772533056643759692E494E4A62505543 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- Grants for johnny5@%: 
 GRANT USAGE ON *.* TO `johnny5`@`%`;
-- CREATE USER for klarsen@%: 
 CREATE USER `klarsen`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352446274D6E7F57015B673B1E4E5C272728022C585F6B6F2E2E6135484A706D5841467345543749447250477A6F764B5269734C6A59494333474663334B307044 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- Grants for klarsen@%: 
 GRANT USAGE ON *.* TO `klarsen`@`%`;
-- CREATE USER for root@%: 
 CREATE USER `root`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x24412430303524542E705C456F693A4E034D541F791E5E3264236E6E61724A71316A6654594667564661444F4777506862534A7A6653342E307677446A6E526F55656F685A36 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- Grants for root@%: 
 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`@`%` WITH GRANT OPTION;
-- Grants for root@%: 
 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,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION;
-- CREATE USER for root@localhost: 
 CREATE USER `root`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240566230F3279056A495A7870484E424E62780318336A62674D71524F4F5A482E7255497738324874337953795268676878666345494556586B633471416530 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
-- Grants for root@localhost: 
 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;
-- Grants for root@localhost: 
 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,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION;
-- Grants for root@localhost: 
 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION;

Example - 2:

With Sed & Grep:

go-pass -s 10.8.0.15 -f show_users.sql | sed -e 's/CREATE USER/CREATE USER IF NOT EXISTS/g' -e '/^-- Grants/d' | grep -v 'Dumping' > migrate.sql 
2023/06/22 14:58:10 [+] Connecting to database: root:root@tcp(10.8.0.15:3306)/mysql


migrate.sql 

-- CREATE USER IF NOT EXISTS for chaoshour@%: 
 CREATE USER IF NOT EXISTS `chaoshour`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240D5537623E2E2E57766976017D54187F50145825525739787850794C307154765055494B5569345A78736D4B2F36463244714F4459744D7734434A4E717236 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
 GRANT USAGE ON *.* TO `chaoshour`@`%`;
-- CREATE USER IF NOT EXISTS for johnny5@%: 
 CREATE USER IF NOT EXISTS `johnny5`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240A251F0C612928636C7D1F523B6B034A651B15694579477A6F6867424A48335453496B514F2F49653644334B6A6A7772533056643759692E494E4A62505543 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
 GRANT USAGE ON *.* TO `johnny5`@`%`;
-- CREATE USER IF NOT EXISTS for klarsen@%: 
 CREATE USER IF NOT EXISTS `klarsen`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x2441243030352446274D6E7F57015B673B1E4E5C272728022C585F6B6F2E2E6135484A706D5841467345543749447250477A6F764B5269734C6A59494333474663334B307044 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
 GRANT USAGE ON *.* TO `klarsen`@`%`;
-- CREATE USER IF NOT EXISTS for root@%: 
 CREATE USER IF NOT EXISTS `root`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x24412430303524542E705C456F693A4E034D541F791E5E3264236E6E61724A71316A6654594667564661444F4777506862534A7A6653342E307677446A6E526F55656F685A36 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
 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 IF NOT EXISTS, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION;
 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,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION;
-- CREATE USER IF NOT EXISTS for root@localhost: 
 CREATE USER IF NOT EXISTS `root`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240566230F3279056A495A7870484E424E62780318336A62674D71524F4F5A482E7255497738324874337953795268676878666345494556586B633471416530 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
 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 IF NOT EXISTS, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION;
 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,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION;
 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION;