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:
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:
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:
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.