Access rights

You can connect to a MySQL database if you provide a user name and the 

associated password. Furthermore, the machine where you connect from must 

be associated with this user to allow a connection. This is based on the 

assumption that a user with a specific user name from one host is different from 

a user with the same user name from a different host.

This access information is stored in the 

mysql.user 

table in the fields 

user, 

password,

 and

 host

. The MySQL wildcard 

%

 is often used in the host field to 

specify that this user can connect from any host. The wildcard 

`_'

 is also 

sometimes used for single characters.

In Example 6 9 the user 

Klaus

 can connect from any host, the user 

Michael

 can 

connect from just the host 

lochness.almaden.ibm.com

, and from 

localhost

 any 

user can connect.

Example 6 9   Sample user data for connection verification

mysql> select user, password, host from user;

+         +                  +                          +

| user    | password         | host                     |

+         +                  +                          +

|         |                  | localhost                |

| Michael | 2f4fba967c46f214 | lochness.almaden.ibm.com |

| Klaus   | c46f2142f4fba967 | %                        |

+         +                  +                          +

Please refer to the

 MySQL Reference Manual

(at

http://dev.mysql.com/doc/

)for a complete description of the MySQL 

connection verification. You can also find information on how the entries in the 

mysql.user

table are ordered when the provided connection data meets more 

than one connection criteria. In the above example, this would be when user 

Klaus

 connects from 

localhost

.

Privileges

Once a connection to the MySQL database is established, each time a command 

is run against the database, MySQL checks if the connected user has sufficient 

privileges to run this command.

Privileges exist for selecting, inserting, updating, and deleting data for creating, 

altering, and dropping tables and other operations performed at the database 

level.

All privileges are stored in either the 

mysql.user, mysql.db, mysql.host, 

mysql.tables_priv

or 

mysql.columns_priv 

tables.

 Chapter 6. Data porting 

139