MySQL has an advanced but non-standard security/privilege system.
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
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
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:
get_hostbynameresolves 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.
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.
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:
|host||host without wild/hosts with wild/empty hosts|
|db||host without wild/hosts with wild/empty hosts|
host = "". Within each host, sort by user using the same rules. Finally, in the
dbtable, 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.
usertable using the first match found. Call this set of privileges Priv.
dbtable using the first match found.
host = ""for the entry found in the
dbtable, AND Priv with the privileges for the host in the
hosttable, i.e. remove all privileges that are not "Y" in both. (If
host <> "", Priv is not affected. In suchcases,
hostmust 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.)
usertable, i.e. add all privileges that are "Y" in
mysqladmin reloadto 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/anyline, not by the
any/jeffreyline. 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.
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.
usertable has the following columns:
dbtable Contains which databases a host+user is allowed to use, and what he can do with the tables in each database. The
dbtable has the following columns:
hosttable 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
hosttable should contain a entry for every host in your network. The
hosttable has the following columns:
_. Leaving any of these columns empty is equivalent to setting it to '%'.
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.
usertable will be processed as a no-name user.
dbtable. This means that a superuser only needs to be in the user table with all privilege-flags set to
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.comit will never be allowed with name matching. Use the IP number in this case.
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:
mysqladmin processlist. May be granted individual database privileges through table
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
The privilege tables are read into mysqld with
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');
mysqladmin reload to make the authentication change
Now things should work.
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).
mysqladmin reloadeach time you change the grant tables. Otherwise the old tables are still used!
--without-grant-tablesoption. Now you can change the MySQL grant tables and use the script
mysqlaccessto check if your grant worked.
mysqladmin reloadtells the mysqld daemon to start using the new grant tables.
mysql -u user databaseor
mysql -u user -p password database.
mysql -u user databasethen you have a problem with the 'user' table. Check this by doing
mysql -u root mysqland
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
--hostoption to mysql and you are not using MIT threads, 'localhost' is a synonym for your hostname.
Access deniederror 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
usertable that exactly matches your host and user, exactly as given in the error message.
mysql -u root testworks but
mysql -h your_hostname -u root testgives '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).
mysql -u user databaseworks on the server machine, but
mysql -u host -u user databasedoesn't work on another client machine, then you don't have the client machine in the 'user' or the 'db' table.
mysql -u user testworks but
mysql -u user other_databasedoesn't work, you don't have the other_database in the 'db' table.
SELECT ... INTO OUTFILEor
LOAD DATASQL commands then you probably don't have the
File_privprivilege set for you in the user table.
--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.
mysqldump mysqlcommand. As always, post your problem with the
Go to the first, previous, next, last section, table of contents.