MySQL Database User Permissions

MYSQL Server uses an internal database called mysql to store database user information. We can play around with several information tables liket the followings to get idea about user permissions and related information.

select * from mysql.user;

select * from mysql.db;

select * from mysql.tables_priv;

select * from mysql.columns_priv;

select * from mysql.procs_priv;

Followings are some formatted reports based upon these basic tables.

List of DATABASE Privileges:

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

ORDER BY md.Host, md.User, md.Db

LIST OF GLOBAL PRIVILEGES:

SELECT mu.host `Host`,

       mu.user `User`,

       REPLACE(

          RTRIM(

             CONCAT(

                IF(mu.Select_priv = 'Y', 'Select ', ''),

                IF(mu.Insert_priv = 'Y', 'Insert ', ''),

                IF(mu.Update_priv = 'Y', 'Update ', ''),

                IF(mu.Delete_priv = 'Y', 'Delete ', ''),

                IF(mu.Create_priv = 'Y', 'Create ', ''),

                IF(mu.Drop_priv = 'Y', 'Drop ', ''),

                IF(mu.Reload_priv = 'Y', 'Reload ', ''),

                IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),

                IF(mu.Process_priv = 'Y', 'Process ', ''),

                IF(mu.File_priv = 'Y', 'File ', ''),

                IF(mu.Grant_priv = 'Y', 'Grant ', ''),

                IF(mu.References_priv = 'Y', 'References ', ''),

                IF(mu.Index_priv = 'Y', 'Index ', ''),

                IF(mu.Alter_priv = 'Y', 'Alter ', ''),

                IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),

                IF(mu.Super_priv = 'Y', 'Super ', ''),

                IF(mu.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),

                IF(mu.Lock_tables_priv = 'Y', 'Lock_tables ', ''),

                IF(mu.Execute_priv = 'Y', 'Execute ', ''),

                IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),

                IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),

                IF(mu.Create_view_priv = 'Y', 'Create_view ', ''),

                IF(mu.Show_view_priv = 'Y', 'Show_view ', ''),

                IF(mu.Create_routine_priv = 'Y', 'Create_routine ', ''),

                IF(mu.Alter_routine_priv = 'Y', 'Alter_routine ', ''),

                IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),

                IF(mu.Event_priv = 'Y', 'Event ', ''),

                IF(mu.Trigger_priv = 'Y', 'Trigger ', ''))),

          ' ',

          ', ')

          AS `Privileges`

  FROM mysql.user mu

ORDER BY mu.Host, mu.User

List of privileges for TABLES:

SELECT mt.host `Host`,

       mt.user `User`,

       CONCAT(mt.Db, '.', mt.Table_name) `Tables`,

       REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`

  FROM mysql.tables_priv mt

 WHERE mt.Table_name IN

          (SELECT DISTINCT t.table_name `tables`

             FROM information_schema.tables AS t

            WHERE    t.table_type IN

                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mt.Host,

         mt.User,

         mt.Db,

         mt.Table_name;

List of privileges for VIEWS:

SELECT mv.host `Host`,

       mv.user `User`,

       CONCAT(mv.Db, '.', mv.Table_name) `Views`,

       REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`

  FROM mysql.tables_priv mv

 WHERE mv.Table_name IN (SELECT DISTINCT v.table_name `views`

                           FROM information_schema.views AS v)

ORDER BY mv.Host,

         mv.User,

         mv.Db,

         mv.Table_name;

List of privileges for TABLE COLUMNS:

SELECT mtc.host `Host`,

       mtc.user `User`,

       CONCAT(mtc.Db,

              '.',

              mtc.Table_name,

              '.',

              mtc.Column_name)

          `Tables Columns`,

       REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`

  FROM mysql.columns_priv mtc

 WHERE mtc.Table_name IN

          (SELECT DISTINCT t.table_name `tables`

             FROM information_schema.tables AS t

            WHERE    t.table_type IN

                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')

                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)

ORDER BY mtc.Host,

         mtc.User,

         mtc.Db,

         mtc.Table_name,

         mtc.Column_name;

List of privileges for VIEW COLUMNS:

SELECT mvc.host `Host`,

       mvc.user `User`,

       CONCAT(mvc.Db,

              '.',

              mvc.Table_name,

              '.',

              mvc.Column_name)

          `Views Columns`,

       REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`

  FROM mysql.columns_priv mvc

 WHERE mvc.Table_name IN (SELECT DISTINCT v.table_name `views`

                            FROM information_schema.views AS v)

ORDER BY mvc.Host,

         mvc.User,

         mvc.Db,

         mvc.Table_name,

         mvc.Column_name;

List of privileges for PROCEDURES:

SELECT mp.host `Host`,

       mp.user `User`,

       CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,

       REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`

  FROM mysql.procs_priv mp

 WHERE mp.Routine_type = 'PROCEDURE'

ORDER BY mp.Host,

         mp.User,

         mp.Db,

         mp.Routine_name;

List of privileges for FUNCTIONS:

SELECT mf.host `Host`,

       mf.user `User`,

       CONCAT(mf.Db, '.', mf.Routine_name) `Procedures`,

       REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`

  FROM mysql.procs_priv mf

 WHERE mf.Routine_type = 'FUNCTION'

ORDER BY mf.Host,

         mf.User,

         mf.Db,

         mf.Routine_name;