How do MySQL privileges work?

MySQL has an advanced but non-standard security/privilege system.

What can the privilege system do.

The basic function of the MySQL privilege system is to give a username on a host select,insert,update and delete privileges on a database.

Extra functionality includes the ability to have a anonymous user and give permission to use MySQL specific funtions like LOAD DATA INFILE.

How does the privilege system work?

In MySQL the combination of host and user is the unique identity. Don't think of users, think of host+user and everything should be much clearer. You can for example have a user named 'Robb' at two different hosts (with different privileges) in MySQL without any conflicts.

The MySQL privilege system makes sure that each user may do exactly the things that they are supposed to be allowed to do. The system decides to grant different privileges depending on which xuser connects from which host to which database.

You can always test your privileges with the script mysqlaccess, which Yves Carlier has provided for the MySQL distribution. See section Why do I get this Access denied? error.

The following switches to mysqld is relevant to security:

--skip-grant-tables

Do not use the privilege system att all. This gives everyone full access to all databases!

--skip-name-resolve

Don't resolve hostnames. All hostnames must be IP-numbers or 'localhost'.

--skip-networking

Don't allow connections over the network (no TCP/IP).

--secure

Check that the ip that was returned from get_hostbyname resolves back to the original hostname. This is done to make it harder for someone on the outside to get access by simulating another host. This is turned off by default since it sometimes takes a long time to check this.

All privileges are stored in three tables. user, host and db.

Everything granted in the user table is valid for every database that cannot be found in the db table. For this reason, it might be wise to grant users (apart from superusers) privileges on a per-database basis only.

The host table is mainly there to maintain a list of "secure" servers. At TcX host contains a list of all machines on the local network. These are granted all privileges.

The connecting user's privileges are calculated by the following algorithm:

1. First sort the tables by:

   Table	Sorted by
   host	host without wild/hosts with wild/empty hosts
   db	host without wild/hosts with wild/empty hosts
   user	host/user

   Host by putting hosts without wildcards first, followed by hosts with wildcards and entries with host = "". Within each host, sort by user using the same rules. Finally, in the db table, sort by db using the same rules. In the steps below, we will look through the sorted tables and always use the first match found.
2. Get the privileges for the connecting user from the user table using the first match found. Call this set of privileges Priv.
3. Get the privileges for the connecting user from the db table using the first match found.
4. If host = "" for the entry found in the db table, AND Priv with the privileges for the host in the host table, i.e. remove all privileges that are not "Y" in both. (If host <> "", Priv is not affected. In suchcases, host must have matched the connecting host's name at least partially. Therefore it can be assumed that the privileges found in this row match the connecting host's profile.)
5. OR (add) Priv with the privileges for the user from the user table, i.e. add all privileges that are "Y" in user.

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

• localhost/root
• localhost/any
• any/jeffrey
• any/root

shell> mysql mysql.
mysql> insert into user (host,user,password)
       values('localhost','custom',password('stupid'));
mysql> insert into user (host,user,password)
       values('server.domain','custom',password('stupid'));
mysql> insert into user (host,user,password)
       values('whitehouse.gov','custom',password('stupid'));

mysql> insert into db
       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       values
       ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> insert into db
       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       values
       ('%','customers','custom','Y','Y','Y','Y','Y','Y');

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.

Adding new user privileges to MySQL

To add privileges to the MySQL database:

This assumes the current user has insert privileges for the mysql db table and reload privileges. The server (mysqld) has to be running. If it is not, start it with safe_mysqld --log &.

> mysql mysql
  insert into user values ('%','monty',password('something'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;
  insert into user (host,user,password) values('localhost','dummy',") ;
  insert into user values ('%','admin',",'N','N','N','N','N','N','Y','N','Y','Y') ;
  quit
> mysqladmin reload

This makes three new users:

Monty

Full superuser, but must use password when using MySQL.

admin

Doesn't need a password but is only allowed to use mysqladmin reload, mysqladmin refresh and mysqladmin processlist. May be granted individual database privileges through table db.

dummy

Must be granted individual database privileges through table db.

Default privileges.

The default privileges (set in `scripts/mysql_install_db') let root do anything. Any user can do anything with any database whose name is 'test' or starts with 'test_'. A normal user can't use mysqladmin shutdown or mysqladmin processlist. See the script (`scripts/mysql_install_db') for an example on how to add other users.

The privilege tables are read into mysqld with mysqladmin reload.

A example of permission setup.

A common mistake is to forget that passwords are stored encrypted, which leads to something like:

INSERT INTO user VALUES ('%','jeffrey','bLa81m0','Y','Y','Y','N','N','N','N','N', 'N','N');

Then (of course) a mysqladmin reload to make the authentication change take effect, then trying to connect to the server:

$ ./mysql -h sqlserver -u jeffrey -p bLa81m0 test
Access denied

Try this instead:

INSERT INTO user VALUES
('%','jeffrey',password('bLa81m0'),'Y','Y','Y','N','N','N','N','N','N','N');

As before, mysqladmin reload to make the authentication change take effect.

Now things should work.

Why do I get this Access denied? error.

• Have you installed the MySQL grant tables with the script `mysql_install_db'? Test by executing mysql -u root test. This should not give an error. You can also check if you have a file 'user.ISD' in the mysql database directory (ordinary install_dir/var/mysql/user.ISD).
• One a fresh installation you should use 'mysql -u root mysql' to access the grant tables.
• Remember that you have to do mysqladmin reload each time you change the grant tables. Otherwise the old tables are still used!
• For testing, you should start the mysqld daemon with the --without-grant-tables option. Now you can change the MySQL grant tables and use the script mysqlaccess to check if your grant worked. mysqladmin reload tells the mysqld daemon to start using the new grant tables.
• Even if you have access problems with perl, python, or ODBC, always test your privilege problems with mysql -u user database or mysql -u user -p password database.
• If you get the error 'Access denied' when trying to connect to the database with mysql -u user database then you have a problem with the 'user' table. Check this by doing mysql -u root mysql and select * from user. You should get an entry with 'hostname' and 'user' matching your computers hostname and your username. If the client and the server are running on the same host and you haven't used the --host option to mysql and you are not using MIT threads, 'localhost' is a synonym for your hostname.
• The Access denied error message will tell you who you are trying to log in as, from which host you are trying to log in, and if you were using a password or not. You should normally have one entry in the user table that exactly matches your host and user, exactly as given in the error message.
• If mysql -u root test works but mysql -h your_hostname -u root test gives 'Access denied' then you don't have the right name for your host in the user table. For example if you have an entry with host 'tcx' in the 'user' table, but your DNS tells MySQL that your hostname is 'tcx.subnet.se' then the entry will not work. Test by adding a record with the IP of your host in the 'user' table. You can, natuarally, also add a host with a wildcard (for example 'tcx%') in the 'user table' (but using hostnames ending with % is pretty insecure).
• When using MIT threads, localhost is never used. All connections to the mysqld daemon is done by TCP IP and you must have your real hostname in 'user' even if you are running the client on the same host as the server.
• If you get the error 'Access to database denied' then you have a problem with the db table. If the used entry in the db table has an empty hostname, check also the corresponding entry in the 'host' table.
• If mysql -u user database works on the server machine, but mysql -u host -u user database doesn't work on another client machine, then you don't have the client machine in the 'user' or the 'db' table.
• If you can't get password to work, remember that passwords must be inserted with the PASSWORD function. See section A example of permission setup.
• If mysql -u user test works but mysql -u user other_database doesn't work, you don't have the other_database in the 'db' table.
• If you get 'Access to database denied' when using the SELECT ... INTO OUTFILE or LOAD DATA SQL commands then you probably don't have the File_priv privilege set for you in the user table.
• If everything else fails, start the mysqld daemon with: --debug=d,general,query. This will print info about the host and user that tries to connect and also information about each command issued. See section Debugging MySQL.
• If you have any other problems with the MySQL grant tables and feel you must post the problem to the list, always add a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. As always, post your problem with the mysqlbug script.

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