Last edit: 2009-05-13 Graham Wideman

MySQL

MySQL privileges explored

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

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.

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:

• What action is permitted: Read either the "What action is permitted" column, or the "Privilege Column Name" column
• At what level of granularity can it be set: For the privilege you're interested in, note the "X"'s, and read up the table to see which privilege tables provide that privilege, and then inspect their Key columns to understand the level of granularity.

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?