MySQL privileges explored

Document Status
Date (reverse)	Who	Description

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

...that implement or report on privileges, and associated manual pages if available. Here I have noted the primary key for each privilege-related table, 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.)
	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.
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

Following are the privileges columns (for the mysql.host, db, and user tables) or allowed values (mysql.tables_priv, columns_priv, procs_priv tables) as surveyed on a MySQL 5.0.45 installation. (Omitted: several other columns unrelated to privileges).

Cat	Col Name	host [4]	db	user	tables _priv [2]	columns _priv [2]	procs _priv [2]	What's permitted
Key	Host	X	X	X	X	X	X
	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
Privs [1]	Select	X	X	X	TC [3]	X		SELECT (ie: read) data
	Insert	X	X	X	TC	X		insert rows, (or values into cols of inserted rows?)
	Update	X	X	X	TC	X		change values in cols
	Delete	X	X	X	T			rows from table
	Create	X	X	X	T			create table (or database)
	Drop	X	X	X	T			drop table (or database)
	Grant	X	X	X	T		X	permits granting any privs user has on selected item
	References	X	X	X	TC	X		N/A
	Index	X	X	X	T			create/drop index
	Alter	X	X	X	T			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	T			create view
	Show_view	X	X	X	T			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	T			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:

[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 fields (Table_priv and Column_priv) containing separate sets of privs -- the available permissions are indicated with T and C. See "What's the difference between tables_priv fields Table_priv and Column_priv?" below.

[4] I have greyed the host column as privs in the host table 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 that the user is being granted.

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 the 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 row-wise privs as expected, while the Column_priv field controls no privs 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 looked for.

So, GRANT SELECT (mycol, othercol) ON mydb.mytable TO fred ... causes MySQL to place two records in columns_priv (one for each col) , AND adds/modifies a single record 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?