Working with User Privileges in MySQL

When you installed the MySQL database in Chapter 1, you were working as the anonymous or root user. Before you begin working regularly with databases, you should create a real user with a real password. To do this, you need to understand a bit about the MySQL privilege system.

Note

If you are accessing MySQL through an Internet service provider, you probably have only one user and one database available to you. By default, that one user has access to all tables in the database and is allowed to perform all commands. If this is the case, you can skip the information in this section and proceed to the script-creation sections. In all instances where a username and password are used, use the one given to you by your ISP.

Creating a New User

If you have proper permissions for adding a user, the simplest method for performing this task is the GRANT command. The basic syntax of the GRANT command follows, where [privilege list] is a placeholder for the privileges you want to give to the new user.

GRANT [privilege list] ON databasename.tablename TO
username@host IDENTIFIED BY "password";

You can grant many types of privileges, and for more information, please visit the MySQL Manual topic at http://www.mysql.com/doc/G/R/GRANT.html. For now, you will just grant all privileges to your new user, on all tables in the database.

Note

The following commands are exactly the same for MySQL on Windows and Linux/Unix platforms.

Start the MySQL monitor from the command line, using the path to the mysql executable file that is relevant to your file system.
Select the database called mysql by typing the following at the mysql> prompt: use mysql;
Type the following SQL statement, substituting your own username and password if you want. The wildcard (*) grants permissions on all databases and tables:
```
GRANT ALL ON *.* TO spike@localhost IDENTIFIED BY "9sj7En4";
```
Exit the MySQL monitor by typing the following at the mysql> prompt:
```
exit
```
Issue the command to reload the grant tables using the mysqladmin program:
```
mysqladmin reload
```

The new user (spike) will now have access to all databases and tables when using the password 9sj7En4. This user will be the sample user in all database connectivity scripts from this point forward. Please substitute your own username and password where appropriate.