Last edit: 2009-05-13 Graham Wideman
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.
...in MySQL's online 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,|
|18.104.22.168||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.
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.
|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)|
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
Manual: "...takes its values from the mysql.user table."
Manual: "... takes its values from the mysql.db table."
Manual: "information comes from the mysql.tables_priv grant table"
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.)
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 .)
This table anticipates that you want to know two things about privileges:
Tables in database mysql
|Allowed values ||What action is permitted|
|Privs ||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|
|Create_tmp_table||X||X||X||create temp table|
|Lock_tables||X||X||X||lock tables ONLY if user has select priv|
|Show_view||X||X||X||X||SHOW CREATE VIEW statement|
|Create_routine||X||X||X||create stored routine|
|Execute||X||X||X||X||run stored procs|
|Event||X||X||create events for scheduler|
|File||X||SELECT...into outfile; load data infile|
|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. |
|Column_priv||X||X||Set of privs in lieu of distinct cols. |
|Proc_priv||X||Set of privs in lieu of distinct cols. |
 Version applicability: The information in this table is applicable to MySQL 5.0.45.
 Privs columns are generally named with "_priv" suffix, as in Select_priv. I removed this for readability.
. 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.
 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?".
 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.
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.
(From Manual 22.214.171.124)
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?".
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.
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: 126.96.36.199 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax. Possibly this corresponds to the proc.definer field?