MySQL Reference Manual for version .

Go to the first, previous, next, last section, table of contents.

The privilege tables

The grant tables privileges on rows are select, insert, update and delete.

The table and database privileges are create and drop. Create and drop are for both tables and databases. Since a user with a drop grant can delete any table, this is the same thing as a drop grant for the database.

Other privileges give the right to use files (for LOAD DATA INFILE and SELECT INTO OUTFILE) and to use the administrative commands shutdown, reload, refresh and process.

The privilege system is based on 3 tables.

user table

Contains all host+user combinations that are allowed to connect to the mysql server, together with their optional passwords. The user table has the following columns:

Field	Type	Key	Default
Host	char(60)	PRI	""
User	char(16)	PRI	""
Password	char(16)	-	""
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

db table Contains which databases a host+user is allowed to use, and what he can do with the tables in each database. The db table has the following columns:

Field	Type	Key	Default
Host	char(60)	PRI	""
Db	char(64)	PRI	""
User	char(16)	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

host table Is only used in big networks as a lookup for empty host entries in the db table. This means that if you want a user to be able to use the database from all hosts in your network, you should put " as the host name in the db table. In this case the host table should contain a entry for every host in your network. The host table has the following columns:

Field	Type	Key	Default
Host	char(60)	PRI	""
Db	char(64)	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

The host and db columns may contain a string with the SQL wildcards % and _. Leaving any of these columns empty is equivalent to setting it to '%'.
A host may be localhost, a hostname, an IP number or a string with wildcards. An empty host in the db table means any host in the host table. An empty host in the host or user table means any host that can create a TCP connection to your server.
A db is the name of a database or an SQL regexp.
An empty user column means any username will do. One can't have wildcards in the user name.
A user not matching anything in the user table will be processed as a no-name user.
The privileges from the user table are OR'ed to the db table. This means that a superuser only needs to be in the user table with all privilege-flags set to Y.

You can use an entry like 123.444.444.% in the host table to give every user on an IP C-net access. To avoid the possibility that somebody tries to fool this setup by naming a host 123.444.444.somewhere.com, MySQL disallows all hostnames that start with digits and a dot. So if your host is named something like 1.2.foo.com it will never be allowed with name matching. Use the IP number in this case.

Go to the first, previous, next, last section, table of contents.