#Mysql get list of users update
#Mysql get list of users free

' ON `',gdb.Db,'`.* TO AS 'GRANT Statement (Reconstructed)',ĬONCAT_WS(', ','SERVER','DATABASE') AS 'Scope', IF(gdb.Alter_routine_priv='Y','ALTER ROUTINE',NULL), IF(gdb.Create_routine_priv='Y','CREATE ROUTINE',NULL), IF(gdb.Show_view_priv='Y','SHOW VIEW',NULL), IF(gdb.Create_view_priv='Y','CREATE VIEW',NULL), IF(gdb.Lock_tables_priv='Y','LOCK TABLES',NULL), IF(gdb.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL), IF(gdb.References_priv='Y','REFERENCES',NULL), 'TO AS 'GRANT Statement (Reconstructed)',ĬONCAT_WS(', ','SERVER','DATABASE','TABLE','COLUMN') AS 'Scope',ĪND OR BY CONCAT(gcl.Host,gcl.Db,gcl.User,gcl.Table_name,gcl.Column_priv) GROUP_CONCAT(gcl.Column_name ORDER BY UPPER(gcl.Column_name)),') ', IF(gcl.Host='%','ALL',gcl.Host) AS 'Remote-IP(s) Affected',ĬONCAT('`',gcl.Db,'`') AS 'Database(s) Affected',ĬONCAT('`',gcl.Table_name,'`') AS 'Table(s) Affected',ĬONCAT(UPPER(gcl.Column_priv),' (',GROUP_CONCAT(gcl.Column_name ORDER BY UPPER(gcl.Column_name) SEPARATOR ', '),') ') AS 'Column(s) Affected',ĬONCAT('GRANT ',UPPER(gcl.Column_priv),' (', MySQL Grant Rebuilder (Reverse Engineer Grants) SET := '' /* If blank, show all user grants, otherwise show only target user's grants */

Adding a link to the script on GitHub: MySQL - Reverse Engineer MySQL Grants.sql.

Updated the MySQL Grant Rebuilder script (below) to the latest version I ended up with before taking a position at a different company (which didn't use MySQL at any capacity).

#Mysql get list of users update

There are a few issues to be handeld, such as user-password-linking, but because we frequently update passwords, that was not in the scope of this project.I designed this query in an attempt to re-build GRANT statements for all existing permissions (for frequent upkeep during database migration).

#Mysql get list of users free

Then feel free to use the below MySQL Grant Rebuilder script to expedite getting user permissions from a MySQL server. * Column-Specific Grants */ SELECT * FROM lumns_priv * Table-Specific Grants */ SELECT * FROM mysql.tables_priv * Database-Specific Grants */ SELECT * FROM mysql.db If you can run the following SELECT statements without error: /* User-Specific Grants */ SELECT * FROM er