gw_logo_08.gif (1982 bytes)  
Last edit: 2009-05-13 Graham Wideman

MySQL

MySQL privileges explored

Document Status
Date (reverse) Who Description
     
2009-05-13 GW Minor edits
2008-07-05 GW Original

Introduction

This article seeks to distill and consolidate information regarding MySQL's privileges/permissions, that is to say the relationships that MySQL recognizes between users, database objects, and actions permitted or denied. The article draws on MySQL's manual, combined with information read directly from a MySQL installation.

My goals were to portray all privilege settings in one comprehensive table so as to see the entire landscape at a glance, particularly the different levels of granularity at which privileges can be set.  In addition, this effort identifies some puzzling corners and prompts some additional questions, pursued below.  There are more nuances beyond this, and I have not tried to cover all the ifs ands and buts to be found in the MySQL manual.

Descriptive articles

...in MySQL's online manual

v 5.0
manual
Topic Description (as of 2008-07-05)
5.4 The MySQL Access Privilege System Table of contents for the privilege-related pages
5.4.2 How the Privilege System Works Several useful tables describing privileges vs the objects they apply to, but with confusingly column heads and organization
5.4.3 Privileges Provided by MySQL More sections and tables describing privileges vs the objects they apply to,
12.5.1.3 GRANT statement syntax Alternative discussion of the structure of permissions, featuring:
-- "Levels": Global, database, table, column, routine
-- A table showing privilege vs meaning in terms of what SQL command the privilege enables.
     

System tables

There are two system databases that implement or report on privileges: the database called "mysql", and another called INFORMATION_SCHEMA. Although it's possible to interact with the mysql database in order to set privileges, normally one sets privileges using the GRANT command. However, the point of looking at the underlying tables is to get a complete overview of the privileges that may be set, and at what level of granularity may they be set. To that end, for each privileges-related table, I have noted the primary key, as the key shows the level of granularity that the table pertains to.

v 5.0
manual
Table Primary Key cols Description (as of 2008-07-05)
5.4.2 "mysql" database system tables   Hst DB Usr Tbl Col Rou The "mysql" "system" database is where MySQL stores permissions that have been granted.  However, I could find no documentation on this database, despite its being referred to on several of the pages above. (It is of course browsable in a live database if you're an admin.)
Hst: Host; DB: Database; Usr: User; Tbl: Table; Col: Column; Rou: Routine
  mysql.user   Y   Y       User list (with passwords) and their global privileges
  mysql.db   Y Y Y       Users' database-level privileges.
  mysql.host   Y Y         Used to modify privs provided by the db table, where the user's "host" value is left empty. The host table has to be edited directly, not through GRANT statements.  In many installations this table is not used.
  mysql.tables_priv   Y Y Y Y     Table-level privileges (...not otherwise granted at a coarser level)
  mysql.columns_priv   Y Y Y Y Y   Column privileges (...not otherwise granted at a coarser level)
  mysql.procs_priv   Y Y Y     Y Procedure privileges
                   
23 INFORMATION_SCHEMA Tables Tbl
Cat
hst Tbl
Sch
usr Tbl Col   Table of contents for tables in the INFORMATION_SCHEMA pseudo-database. These are views that are read-only and provided to report on various metadata or structural aspects of the MySQL installation including its databases and permissions.  Some of these INFORMATION_SCHEMA tables report on privileges. Clearly this functionality overlaps and in some cases elaborates on the tables in the "mysql" system database.
TblCat: Table Catalog; hst: Host; Tbl Sch: Table Schema; usr" user; Tbl: Table; Col: Column
23.5 USER_PRIVILEGES Y Y   Y       Privilege_type, Is_grantable.
Manual: "...takes its values from the mysql.user table."
23.6 SCHEMA_PRIVILEGES Y Y Y Y       Privilege_type, Is_grantable
Manual: "... takes its values from the mysql.db table."
23.7 TABLE_PRIVILEGES Y Y Y Y Y     Privilege_type, Is_grantable
Manual: "information comes from the mysql.tables_priv grant table"
23.8 COLUMN_PRIVILEGES Y Y Y Y Y Y   Privilege_type, Is_grantable
Manual: "information comes from the mysql.columns_priv grant table"
                   

I ultimately concluded that it's the mysql system database tables that provide the most reliable reference as to actual privilege control capability, rather that the INFORMATION_SCHEMA tables, which seem to be more of an effort to supply metadata in an industry-standard-like structure, regardless of whether it applies to MySQL. (Nonetheless, there are other metadata areas, such as details of indexes, where INFORMATION_SCHEMA provides the only information.)

All mysql system table privilege columns/values considered together

The following table gives the grand overview of privileges.  For table mysql.host, db, and user, the table below shows the privilege columns that are provided.  Tables mysql.tables_priv, columns_priv, and procs_priv don't have one-column-per-privilege; instead they provide one column into which may be placed a list of values which set privileges. Thus the table below shows what privileges may be set this way. (tables_priv has a second privilege-related column called privs_column, explained in note [3].)

This table anticipates that you want to know two things about privileges:

Column
Category
Privilege
Column
 Name

Tables in database mysql

 
host
[4]
db user tables
_priv
columns
_priv
procs
_priv
Key Host X X X X X X    <=== Granularity
  Db X X   X X X
  User   X X X X X
  Table_name       X X  
  Column_name         X  
  Routine_name           X
  Routine_type           X
    Privilege
Columns [2]
Allowed values [2] What action is permitted
    Table
_priv [3]
Column
_priv [3]
Column
_priv
Proc
_priv
Privs [1] Select X X X X X X   SELECT (ie: read) data
  Insert X X X X X X   insert rows, (or values into cols of inserted rows?)
  Update X X X X X X   change values in cols
  Delete X X X X       rows from table
  Create X X X X       create table (or database)
  Drop X X X X       drop table (or database)
  Grant X X X X     X permits granting any privs user has on selected item
  References X X X X X X   N/A
  Index X X X X       create/drop index
  Alter X X X X       alter table
  Create_tmp_table X X X         create temp table
  Lock_tables X X X         lock tables ONLY if user has select priv
  Create_view X X X X       create view
  Show_view X X X X       SHOW CREATE VIEW statement
  Create_routine X X X         create stored routine
  Alter_routine X X X       X alter/drop routine
  Execute X X X       X run stored procs
  Trigger X X X X       create/drop triggers
  Event   X X         create events for scheduler
  Reload     X         FLUSH
  Shutdown     X         mysqladmin shutdown
  Process     X         show processlist
  File     X         SELECT...into outfile; load data infile
  Show_db     X         show databases
  Super     X         CHANGE MASTER, KILL, PURGE MASTER LOGS, SET GLOBAL etc
  Repl_slave     X         Used by repl slave to see master bin log events
  Repl_client     X         Ask about slave/master servers
  Create_user     X         create/drop/rename/revoke all privs on user
PrivsList Table_priv       X       Set of privs in lieu of distinct cols. [2]
  Column_priv         X X   Set of privs in lieu of distinct cols. [2]
  Proc_priv             X Set of privs in lieu of distinct cols. [2]
          _____   ________ ________  

Notes:

[0] Version applicability: The information in this table is applicable to MySQL 5.0.45.

[1] Privs columns are generally named with "_priv" suffix, as in Select_priv. I removed this for readability.

[2]. Permissions in the host, db and user tables are stored in individual boolean columns, while permissions in tables_priv, columns_priv and procs_priv are stored in a single column (Table_priv, for example) of type "set", permitting a list of values corresponding to each possible permission. In the above table I've aligned the set members permitted in tables_priv, columns_priv and procs_priv with their counterpart boolean columns from host, db and user.

[3] The tables_priv has two privileges-related fields: Table_priv and Column_priv. These are explained below in "What's the difference between tables_priv fields Table_priv and Column_priv?".

[4] I have greyed the host column as privs in the host table as they are not set with GRANT, and are for the relatively special-purpose of restricting the privs provided to a user by the db table, depending on which host they are working from.

Wrinkles

1. GRANT privilege is special: 
The "grant" privilege, (provided by the "GRANT OPTION" option) is special: It provides a user the ability to grant to other users any or all the privileges in this GRANT OPTION option.

2. Grants are cumulative: 
Grants issued to a user on the same object are cumulative: Eg: GRANT INSERT on MyDB followed by GRANT SELECT on MyDB leaves the user with both INSERT and SELECT privs.  The GRANT OPTION priv is also cumulative, so if any grant provides the user with grant privs on an object, that grant priv allows the user to pass along to other users all privs this user has on that object. Ie: It's not possible to grant a user SELECT and INSERT, and then only allow the user to pass along SELECT privs to some other user. (Of course the user can choose to pass along only SELECT; the point is they can't be prevented from passing along INSERT.)

3. No GRANT OPTION on columns:
columns_priv.Column_priv  and tables_priv.Column_privs don't include "Grant" as a legit value, which agrees with the manual's description of syntax for priv_type for column level.  Contrastingly, INFORMATION_SCHEMA.COLUMN_PRIVILEGES does show an IS_GRANTABLE field.  Presumably this is unused.

How privileges are combined

(From Manual 12.5.1.3)

The privileges for a table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels that apply:

Total privs =
user (global) privs
+ (db privs AND host privs)
+ table privs
+ column privs
+ routine privs

Note that this reliably describes privs as applied by GRANT.  At the level of actual mysql system tables, the privileges of columns are stored at table and column level in a way which does not just OR, as described in "What's the difference between tables_priv fields Table_priv and Column_priv?".

Additional Questions

What's the difference between tables_priv fields Table_priv and Column_priv?

Normally, if you're just using GRANT to grant privileges, this is not something you need to know. But in case you're looking to the priv tables for definitive info on the full range of privileges, then you might be as puzzled as I was.

What does it mean for tables_priv to contain both a Table_priv field AND a Column_priv field? For example, how would Select/Insert/Update in each of these columns differ in effect?

Turns out that the Table_priv field controls privileges for the table as expected, while the Column_priv field controls no privileges by itself. Instead, settings in the Column_priv field tell MySQL whether there are corresponding column-wise settings in the columns_priv table that should be taken into account.

So, GRANT SELECT (mycol, othercol) ON mydb.mytable TO fred ... causes MySQL to place two records in columns_priv (one for each col for fred) , AND adds/modifies a single record (pertaining to fred) in tables_priv to add "Select" to tables_priv.Column_priv.

Indeed, manually *removing* "Select" from tables_priv.Column_priv causes MySQL to no longer see any column-level select privs (at least, as reported by SHOW GRANTS).

Correspondingly, if you do decide to bypass the GRANT statement and instead set up privileges by directly accessing the priv tables, then for column level privs you have to remember to set corresponding privs in the tables_priv.Column_priv.

Note: If trying this out at home, be sure to perform the reload privs procedure each time after manually modifying the priv tables, in order to assess the effects properly. (phpmyadmin has a link to do this on various pages, or use FLUSH PRIVILEGES command in SQL.

Does MySQL have the concept of database "owner", and/or table "owner"?

As far as I can tell, there is no concept in MySQL of "Owner" of database or its objects, as there is in MS Access and MS SQL Server. I surmise this from the lack of "owner" field anywhere in mysql system tables.

Also, this posting : MySQL Equivalent of MSSQLTable Owner in MySQL

However, "owner" is mentioned in conjunction with stored procedures: 12.5.4.5 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax. Possibly this corresponds to the proc.definer field?