How to get a list of permissions of mysql users

From www.ReeltoReel.nl Wiki
Revision as of 07:52, 27 October 2012 by Root (talk | contribs) (Created page with "= How to Get a List of Permissions of MySQL Users = MySQL has advanced database access system. A database user can have access to the whole database, separate tables, or to se...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

How to Get a List of Permissions of MySQL Users

MySQL has advanced database access system. A database user can have access to the whole database, separate tables, or to separate columns of tables. Also there is a restriction for actions a user may perform with records. MySQL server uses several tables in a special database to organize such a complicated database access structure. The access policy is based on the values available in these tables.

The database that MySQL server uses to store internal data about users is called mysql by default. There are tables for storing information about users’ accounts in this database:

  • user contains a list of all users of the MySQL server and their permissions to access the database;
  • db contains a list of databases with a matching list of database users and their privileges for executing operations;
  • tables_priv contains a list of database tables (views) that a user has access to;
  • columns_priv contains a list of columns from the database tables (views) a user has access to;
  • procs_priv contains a list of database procedures (functions) a user has access to.
    To get the list of users’ privileges concerning data access, the following queries may be executed:

* the 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

* the list of privileges for a database:

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

* the 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;

* the 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;

* the 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;

* the 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;

* the 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;

* the 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;

Retrieved from "https://www.reeltoreel.nl/wiki/index.php?title=How_to_get_a_list_of_permissions_of_mysql_users&oldid=1587"