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.
See section How to make MySQL secure against crackers.
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:
| 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 = "". 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.
user table
using the first match found.
Call this set of privileges
Priv.
db table
using the first match found.
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 such cases, 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.)
user
table, i.e. add all privileges that are "Y" in user.
mysqladmin reload to make the changes take effect.
The connecting user gets the set of privileges Priv.
Let's show an example of the sorting and matching! Suppose that the user
table contains this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-Then the search order will be:
localhost/any line, not by the any/jeffrey line. The
first match found is used!
So if you have access problems, print out the user table, sort it by
hand, and see where the match is being made.
Here follows an example to add a user 'custom' that can connect from hosts
'localhost', 'server.domain' and 'whitehouse.gov'. He wants to have password
'stupid'. The database 'bankaccount' he only want to use from 'localhost' and
the 'customer' database he wants to be able to reach from all three hosts.
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');
You can of course also use xmysqladmin, mysql_webadmin, mysqladmin and even
xmysql to insert/change and update values in the privilege tables.
You can find these utilities in the Contrib directory.
Go to the first, previous, next, last section, table of contents.