list all persmission of all users of a MySQL database

Read comments near the "WHERE" clause:

SELECT
 md.host `Host`,
 md.user `User`,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM
 mysql.db md
-- If you omit the WHERE clause below, you will see the permissions for all databases
-- If you only need a specific db, uncomment the where clause below and provide the name of the desired db
-- WHERE md.db='YourDatabaseNameHere'
ORDER BY
 md.Host,
 md.User,
 md.Db

Select allOpen in new window

FYR:
http://blog.devart.com/how-to-get-a-list-of-permissions-of-mysql-users.html

Regards,
Hielo

Dear all,

the result of running:

use <dbname>;
select * from mysql.user;

is consistent ! but not consistent when I run this:

SELECT
 md.host `Host`,
 md.user `User`,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM
 mysql.db md
-- If you omit the WHERE clause below, you will see the permissions for all databases
-- If you only need a specific db, uncomment the where clause below and provide the name of the desired db
-- WHERE md.db='YourDatabaseNameHere'
ORDER BY
 md.Host,
 md.User,
 md.Db

Select allOpen in new window

how come.

but the task I want to do is
1) export table and data.
2) make sure that if I need to restore that by import, all permission is the same.

Can export keep all permission ?

please help to answer this :

https://www.experts-exchange.com/questions/28443951/MySQL-5-5-database-backup-using-toad-and-OR-workbench-6-1x.html

too

how about this :

select * from information_schema.user_privileges;

any different from the 1st and 2nd one ?

this one select * from information_schema.user_privileges; seems only user level but not per database level ?

you know, from the Workbench, it seems that the result from export can't be import on the same workbench, can't see why !

there are no errors at all but after the process is done (I import everything to a different database name in order to verify both dB table is the same after import), the new DB name I restore to do not contain any table and data!

See if the old GUI Tools bundle work with your version of mysql.  I've used that in the past to do server-to-server migration.
http://downloads.mysql.com/archives/gui/

hielo,

that one is not what I need bascailly ! but do you know if the export and import keep all user permission?

server to server is not what I need, I just need backup

hielo,

if I use the MySQL enterprise backup, once instlaled I can run the mysqlbackup to backup something, what is the full command to backup and restore DB ?

"With the GUI Tools, your backup file ends up being an actual sql file - a file with all the sql commands/statements necessary to recreate the entire db  (schema, tables, data).   So the only thing you would need to do with that sql file, is to find a way to grab each statement at a time and execute one command at a time.  This is where Query Browser (also in included in the GUI Tools bundle) helps you out.  From Query Browser you click on File > Open Script > "locateYourBackupFile.sql" and it will parse the file, executing one statement at a time for you once you click on the "Execute" button on the upper left."

I can surly tell you that it doesn't work, as if the database is very big and the sql files exported can be 700MB, the Query Browser can't open it at all.

"The MySQL Administrator has a "Backup" option that can create an sql backup file (Schema+Table Definitions, and DATA INSERTION statements) for you.  Once it has created that backup file for you, if you want to restore the db, then launch Query Browser and do as I described in the previous paragraph."

As I said, it doesn't work !

In the MysQL administrator, just select restore !

"The above steps need to be performed the the "root" user OR some other account with full privileges."

I am full on that MySQL server and it seems only doesnt' work on workbench !! I can do backup on MySQL administrator without root !

the online bakcup from Workbench enterprise seems need ROOT, right?

please help to answer this:

https://www.experts-exchange.com/questions/28570501/MySQL-trigger-warning-and-error-handling-control.html?anchorAnswerId=40499264#a40499264

sorry focusing on other post and I already keep updating post everyday.

in a new company focusing on SQL replication environment and need all of your help.