villaenterprise.blogg.se - Mysql get list of users

#MYSQL GET LIST OF USERS UPDATE#
#MYSQL GET LIST OF USERS FREE#

IF(usr.Host='%',"ALL",usr.Host) AS 'Remote-IP(s) Affected', " 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