MySQL DBA Tips Corner

     Return to the MySQL DBA Tips Corner.


What are the MySQL Privilege Tables?

by Jeff Hunter, Sr. Database Administrator

The privilege tables are used by the MySQL Privilege System to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update, and delete.

Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

The privilege tables are core to MySQL in order to perform the functions of it's privilege system. The privilege tables are created by running the script mysql_install_db and created in a special database called mysql.

The following is a breif overview of the privilege tables in the mysql database.

Show all databases created for this instance:
  % mysqlshow
  +-------------+
  |  Databases  |
  +-------------+
  | bankaccount |
  | customer    |
  | expenses    |
  | mysql       |
  | test        |
  +-------------+

Show all tables in the mysql database:

  % mysqlshow -u root mysql
  Database: mysql
  +--------------+
  |    Tables    |
  +--------------+
  | columns_priv |
  | db           |
  | func         |
  | host         |
  | tables_priv  |
  | user         |
  +--------------+

Describe all Privilege Tables:

  % desc user;
  +-----------------+-----------------+------+-----+---------+-------+
  | Field           | Type            | Null | Key | Default | Extra |
  +-----------------+-----------------+------+-----+---------+-------+
  | Host            | char(60) binary |      | PRI |         |       |
  | User            | char(16) binary |      | PRI |         |       |
  | Password        | char(16) binary |      |     |         |       |
  | Select_priv     | enum('N','Y')   |      |     | N       |       |
  | Insert_priv     | enum('N','Y')   |      |     | N       |       |
  | Update_priv     | enum('N','Y')   |      |     | N       |       |
  | Delete_priv     | enum('N','Y')   |      |     | N       |       |
  | Create_priv     | enum('N','Y')   |      |     | N       |       |
  | Drop_priv       | enum('N','Y')   |      |     | N       |       |
  | Reload_priv     | enum('N','Y')   |      |     | N       |       |
  | Shutdown_priv   | enum('N','Y')   |      |     | N       |       |
  | Process_priv    | enum('N','Y')   |      |     | N       |       |
  | File_priv       | enum('N','Y')   |      |     | N       |       |
  | Grant_priv      | enum('N','Y')   |      |     | N       |       |
  | References_priv | enum('N','Y')   |      |     | N       |       |
  | Index_priv      | enum('N','Y')   |      |     | N       |       |
  | Alter_priv      | enum('N','Y')   |      |     | N       |       |
  +-----------------+-----------------+------+-----+---------+-------+
  % desc db;
  +-----------------+-----------------+------+-----+---------+-------+
  | Field           | Type            | Null | Key | Default | Extra |
  +-----------------+-----------------+------+-----+---------+-------+
  | Host            | char(60) binary |      | PRI |         |       |
  | Db              | char(64) binary |      | PRI |         |       |
  | User            | char(16) binary |      | PRI |         |       |
  | Select_priv     | enum('N','Y')   |      |     | N       |       |
  | Insert_priv     | enum('N','Y')   |      |     | N       |       |
  | Update_priv     | enum('N','Y')   |      |     | N       |       |
  | Delete_priv     | enum('N','Y')   |      |     | N       |       |
  | Create_priv     | enum('N','Y')   |      |     | N       |       |
  | Drop_priv       | enum('N','Y')   |      |     | N       |       |
  | Grant_priv      | enum('N','Y')   |      |     | N       |       |
  | References_priv | enum('N','Y')   |      |     | N       |       |
  | Index_priv      | enum('N','Y')   |      |     | N       |       |
  | Alter_priv      | enum('N','Y')   |      |     | N       |       |
  +-----------------+-----------------+------+-----+---------+-------+
  % desc host;
  +-----------------+-----------------+------+-----+---------+-------+
  | Field           | Type            | Null | Key | Default | Extra |
  +-----------------+-----------------+------+-----+---------+-------+
  | Host            | char(60) binary |      | PRI |         |       |
  | Db              | char(64) binary |      | PRI |         |       |
  | Select_priv     | enum('N','Y')   |      |     | N       |       |
  | Insert_priv     | enum('N','Y')   |      |     | N       |       |
  | Update_priv     | enum('N','Y')   |      |     | N       |       |
  | Delete_priv     | enum('N','Y')   |      |     | N       |       |
  | Create_priv     | enum('N','Y')   |      |     | N       |       |
  | Drop_priv       | enum('N','Y')   |      |     | N       |       |
  | Grant_priv      | enum('N','Y')   |      |     | N       |       |
  | References_priv | enum('N','Y')   |      |     | N       |       |
  | Index_priv      | enum('N','Y')   |      |     | N       |       |
  | Alter_priv      | enum('N','Y')   |      |     | N       |       |
  +-----------------+-----------------+------+-----+---------+-------+
  % desc func;
  +-------+------------------------------+------+-----+----------+-------+
  | Field | Type                         | Null | Key | Default  | Extra |
  +-------+------------------------------+------+-----+----------+-------+
  | name  | char(64) binary              |      | PRI |          |       |
  | ret   | tinyint(1)                   |      |     | 0        |       |
  | dl    | char(128)                    |      |     |          |       |
  | type  | enum('function','aggregate') |      |     | function |       |
  +-------+------------------------------+------+-----+----------+-------+
  % desc tables_priv;
  +-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
  | Field       | Type                                                                                          | Null | Key | Default | Extra |
  +-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
  | Host        | char(60) binary                                                                               |      | PRI |         |       |
  | Db          | char(64) binary                                                                               |      | PRI |         |       |
  | User        | char(16) binary                                                                               |      | PRI |         |       |
  | Table_name  | char(60) binary                                                                               |      | PRI |         |       |
  | Grantor     | char(77)                                                                                      |      | MUL |         |       |
  | Timestamp   | timestamp(14)                                                                                 | YES  |     | NULL    |       |
  | Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') |      |     |         |       |
  | Column_priv | set('Select','Insert','Update','References')                                                  |      |     |         |       |
  +-------------+-----------------------------------------------------------------------------------------------+------+-----+---------+-------+
  % desc columns_priv;
  +-------------+----------------------------------------------+------+-----+---------+-------+
  | Field       | Type                                         | Null | Key | Default | Extra |
  +-------------+----------------------------------------------+------+-----+---------+-------+
  | Host        | char(60) binary                              |      | PRI |         |       |
  | Db          | char(64) binary                              |      | PRI |         |       |
  | User        | char(16) binary                              |      | PRI |         |       |
  | Table_name  | char(64) binary                              |      | PRI |         |       |
  | Column_name | char(64) binary                              |      | PRI |         |       |
  | Timestamp   | timestamp(14)                                | YES  |     | NULL    |       |
  | Column_priv | set('Select','Insert','Update','References') |      |     |         |       |
  +-------------+----------------------------------------------+------+-----+---------+-------+


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 25-Jul-2005 20:25:31 EDT
Page Count: 5878