# How to get a list of permissions of mysql users

# 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:**

<table id="bkmrk-select-%C2%A0mu.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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

</td></tr></tbody></table>

**\* the list of privileges for a database:**

<table id="bkmrk-select-%C2%A0md.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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

</td></tr></tbody></table>

**\* the list of privileges for tables:**

<table id="bkmrk-select-%C2%A0mt.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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 &lt;&gt; 'VIEW' AND

`   `t.create\_options **IS** NOT NULL

` `)

**ORDER** **BY**

` `mt.Host,

` `mt.User,

` `mt.Db,

` `mt.Table\_name;

</td></tr></tbody></table>

**\* the list of privileges for views:**

<table id="bkmrk-select-%C2%A0mv.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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;

</td></tr></tbody></table>

**\* the list of privileges for table columns:**

<table id="bkmrk-select-%C2%A0%C2%A0mtc.host-%60h"><tbody><tr><td style="border:none;padding:0cm;">**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 &lt;&gt; 'VIEW' AND

`    `t.create\_options **IS** NOT NULL

`  `)

**ORDER** **BY**

`  `mtc.Host,

`  `mtc.User,

`  `mtc.Db,

`  `mtc.Table\_name,

`  `mtc.Column\_name;

</td></tr></tbody></table>

**\* the list of privileges for view columns:**

<table id="bkmrk-select-%C2%A0mvc.host-%60ho"><tbody><tr><td style="border:none;padding:0cm;">**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;

</td></tr></tbody></table>

**\* the list of privileges for procedures:**

<table id="bkmrk-select-%C2%A0mp.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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;

</td></tr></tbody></table>

**\* the list of privileges for functions:**

<table id="bkmrk-select-%C2%A0mf.host-%60hos"><tbody><tr><td style="border:none;padding:0cm;">**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;

</td></tr></tbody></table>