Introducing the MySQL Privilege System

The MySQL privilege system is always on. The first time you try to connect, and for each subsequent action, MySQL checks the following three things:

• Where you are accessing from (your host)

• Who you say you are (your username and password)

• What you're allowed to do (your command privileges)

All this information is stored in the database called mysql, which is automatically created when MySQL is installed. There are several tables in the mysql database:

• columns_priv Defines user privileges for specific fields within a table.

• db Defines the permissions for all databases on the server.

• func Defines user-created functions.

• host Defines the acceptable hosts that can connect to a specific database.

• tables_priv Defines user privileges for specific tables within a database.

• user Defines the command privileges for a specific user.

These tables will become more important to you later in this chapter as you add a few sample users to MySQL. For now, just remember that these tables exist and must have relevant data in them in order for users to complete actions.

The Two-Step Authentication Process

As you've learned, MySQL checks three things during the authentication process. The actions associated with these three things are performed in two steps:

If step 1 fails, you'll see an error about it and you won't be able to continue on to step 2. For example, suppose you are connecting to MySQL with a username of joe and a password of abc123 and you want to access a database called myDB. You will receive an error message if any of those connection variables is incorrect for any of the following reasons:

• Your password is incorrect.

• Username joe doesn't exist.

• User joe can't connect from localhost.

• User joe can connect from localhost but cannot use the myDB database.

You may see an error like the following:

# /usr/local/mysql/bin/mysql -h localhost -u joe -pabc123 test
Error 1045: Access denied for user: 'joe@localhost' (Using password: YES)

If user joe with a password of abc123 is allowed to connect from localhost to the myDB database, MySQL will check the actions that joe can perform in step 2 of the process. For our purposes, suppose that joe is allowed to select data but is not allowed to insert data. The sequence of events and errors would look like the following:

# /usr/local/mysql/bin/mysql -h localhost -u joe -pabc123 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61198 to server version: 4.0.21-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from test_table;
+----+------------+
| id | test_field |
+----+------------+
+----+------------+
| 1  | blah       |
| 2  | blah blah  |
+----+------------+
2 rows in set (0.0 sec)

mysql> insert into test_table values ('', 'my text');
Error 1044: Access denied for user: 'joe@localhost' (Using password: YES)

Action-based permissions are common in applications with several levels of administration. For example, if you have created an application containing personal financial data, you might grant only SELECT privileges to entry-level staff members, but INSERT and DELETE privileges to executive-level staff with security clearances.