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 -h                                                                                                                                                      
Usage: ./go-pass -s < source host> -f <dump file>
Options:
Usage: ./go-pass -s < source host> -f <dump file> -o <user>

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;

Only dump a single user account and its grants

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

only-klarsen.sql
- 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`@`%`;

So, what can you do with this only-klarsen.sql file?

go-pass on  new-features via 🐹 v1.20.5 
❯ mysql -e "select user from mysql.user where user = 'klarsen'"
+---------+
| user    |
+---------+
| klarsen |
+---------+

go-pass on  main via 🐹 v1.20.5 
❯ mysql -e "DROP USER klarsen"

go-pass on  main via 🐹 v1.20.5 
❯ mysql -e "select user from mysql.user where user = 'klarsen'"

go-pass on  main via 🐹 v1.20.5 
❯ cat only-klarsen.sql | mysql -vv
--------------
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
--------------

Query OK, 0 rows affected

--------------
GRANT USAGE ON *.* TO `klarsen`@`%`
--------------

Query OK, 0 rows affected

Bye

go-pass on  main via 🐹 v1.20.5 
❯ mysql -u klarsen -pwH4FLM97jCkbhPcFy6Ip8YL

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 221
Server version: 8.0.32-24 Percona Server (GPL), Release 24, Revision e5c6e9d2

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+--------------------+
| user()             |
+--------------------+
| klarsen@172.17.0.1 |
+--------------------+
1 row in set (0.00 sec)


mysql> show grants;
+-------------------------------------+
| Grants for klarsen@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `klarsen`@`%` |
+-------------------------------------+
1 row in set (0.01 sec)