Users and Privileges

After you have MySQL successfully up and running, and after you've established a password for the root user, it's time to begin adding other users. To improve the security of your databases, you should always create new users for accessing your databases, rather than continuing to use the root user at all times.

The MySQL privileges system was designed to ensure proper authority for certain commands on specific databases. This technology is how a Web host, for example, can securely have several users accessing several databases, without concern. Each user within the MySQL system can have specific capabilities on specific databases from specific hosts (computers). The root userthe MySQL root user, not the system'shas the most power and is used for creating subusers, although subusers can be given root-like powers (inadvisably so).

When a user attempts to do something with the MySQL server, MySQL will first check to see if the user has the permission to connect to the server at all (based upon the user name and host, the user's password, and the information in the user table of the mysql database). Second, MySQL will check to see if the user has the permission to run the specific SQL statement on the specific databasesfor example, to select data, insert data, or create a new table. To determine this, MySQL uses the db, host, user, tables_priv, and columns_priv tables, again from the mysql database. Tables 2.2, 2.3, and 2.4 list the various privileges that can be set on a user-by-user basis (the groupings are mine for clarification purposes; these are not official MySQL labels).

Table 2.2. This is the list of basic privileges that can be assigned to MySQL users on a case-by-case basis. It is generally safe to assign these to users for a specific database (except for the mysql database, which must be kept off-limits).

Basic MySQL Privileges
Privilege	Allows
SELECT	Reading of rows from tables
INSERT	Adding new rows of data to tables
UPDATE	Altering existing data in tables
DELETE	Removing existing data in tables
SHOW DATABASES	Listing the available databases
INDEX	Creating and dropping indexes in tables
ALTER	Modifying the structure or properties of a table
CREATE	Creating new tables or databases
CREATE TEMPORARY TABLES	Creating temporary tables
DROP	Deleting existing tables or databases

Table 2.3. These privileges should be given only to administrative users.

Administrative MySQL Privileges
Privilege	Allows
RELOAD	Reloading the grant tables (and therefore enact user changes)
SHUTDOWN	Stopping the MySQL server
FILE	Importing data into tables from text files
GRANT OPTION	Creating new users with the same permissions as current user
CREATE USER	Creating new users
REVOKE	Removing the permissions of users
PROCESS	Showing currently running processes
SUPER	Terminating running processes

Table 2.4. These privileges have been added in newer versions of MySQL (mostly 5.0 and up).

Newer MySQL Privileges
Privilege	Allows
CREATE VIEW	Creating a view
SHOW VIEW	Using a view
ALTER ROUTINE	Modifying a stored procedure
CREATE ROUTINE	Creating a stored procedure
EXECUTE	Running a stored procedure
LOCK TABLES	Locking tables
REPLICATION CLIENT	Showing replication status
REPLICATION SLAVE	Performing replication

Creating new users

There are a handful of ways to establish users and privileges within MySQL, but the most failsafe is to use the mysql client and the GRANT command. The syntax goes like this:

GRANT privileges ON database.* TO 'username'@'hostname' IDENTIFIED BY 'password'

For the privileges aspect of this statement, you can list specific privileges from those in Tables 2.2, 2.3, and 2.4, or you can allow for all of them using ALL (which is not prudent). The database.* part of the statement specifies which database and tables the user can work on. You can name specific tables using database.tablename syntax or allow for every database with *.* (again, not prudent). Finally, you can specify the user name, the host, and a password.

The user name has a maximum length of 16 characters. When creating a user name, be sure to avoid spaces (use the underscore instead) and note that user names are case-sensitive. The host name is the computer from which the user is allowed to connect. The most secure option is to set the host as localhost, meaning that the user can connect only from the same computer on which MySQL is running. The least secure is to use the wildcard character (%), meaning that any host is valid. You can also set the host name to be a specific IP address (192.168.0.1), an IP address within a certain realm (192.168.0.%), or a specific host name (mysite.com). Whatever you decide to do, it is the combination of a username@host that is important. If you create two usersJessica@apple and Jessica@bananathese are entirely different entities.

The user's password has no length limit but is also case-sensitive. The passwords will be encrypted within the mysql database, meaning they cannot be recovered in a plain text format. Omitting the IDENTIFIED BY 'password' clause results in that user not being required to enter a password (which, once again, should be avoided).

As an example of this process, I will create two new users with specific privileges.

To create new users:

1.	Log in to the mysql client. You don't necessarily need to log in as the root user, but you do need to be a user that has the privilege of creating other users.
2.	Create two new databases (Figure 2.34). Figure 2.34. Before adding new users, I created a couple of extra databases using the CREATE DATABASE databasename SQL command. CREATE DATABASE alpacas; CREATE DATABASE movies; Although I have not formally discussed creating databases before now, the syntax is obvious and having two example databases already created will allow for better examples in these steps.
3.	Create a user that has management privileges on the alpacas database(Figure 2.35). Figure 2.35. The first user I have created will have every requisite privilege for manipulating the alpacas database. (It's fine that it shows 0 rows affected.) [View full width] GRANT SELECT, INSERT, UPDATE, DELETE, 'CREATE, DROP, ALTER, INDEX ON 'alpacas.* TO 'llama'@'localhost' 'IDENTIFIED BY 'camel'; This user, llama@localhost, will be able to create tables, alter tables, insert data, update data, and so forth, on the alpacas database. This essentially constitutes every management-type capability aside from creating new users. Be certain to use a password, perhaps one more clever than this.
4.	Modify the llama@localhost privileges(Figure 2.36). Figure 2.36. The first user has been modified so that they have FILE privileges on every database and MySQL 5.x privileges on the alpacas database. [View full width] GRANT FILE ON *.* TO ''llama'@'localhost'; GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, SHOW VIEW ON alpacas.* TO 'llama'@'localhost'; A couple of interesting things are going on here. First of all, the user has been granted the FILE privilege, which will let them run queries stored in text files. This privilege must be granted globally, not on a specific database. MySQL has a few such privileges that are not database-specific. Others include LOCK_TABLES, RELOAD, and SHOW DATABASES. Second, I've added a few more privileges, but only on the alpacas database. These privileges all relate to MySQL 5.0 and up, so they cannot be granted in earlier versions of the software. Notice that I don't need to specify the password in these updates, either.
5.	Create a user that has browsing-level access to both databases (Figure 2.37). Figure 2.37. The webuser created here, who can connect from any host, is a generic and safe user for selecting data from tables. [View full width] GRANT USAGE ON *.* TO 'webuser'@'%' IDENTIFIED BY 'BroWs1ng'; GRANT SELECT ON alpacas.* TO 'webuser'@'%'; GRANT SELECT ON movies.* TO 'webuser'@'%'; The first line establishes a userthat can connect from any hostand password without granting any privileges at all. The next two lines grant SELECT privileges to two databases. Now the generic webuser can browse through records (SELECT from tables) but cannot modify the data therein. A more direct way of creating this user for every database would be to use the command GRANT SELECT ON *.* TO 'webuser'@'%' IDENTIFIED BY 'BroWs1ng'; except that would also give this user permission to select from the mysql database, which is not a good idea. When establishing users and privileges, work your way from the bottom up, allowing the bare minimum of access at all times.
6.	Check the new user's privileges (Figure 2.38). Figure 2.38. The SHOW GRANTS command reveals what specific users are allowed to do. Notice that there are separate entries for the global FILE privilege and the other, alpacas-specific ones. SHOW GRANTS FOR 'llama'@'localhost'; The SHOW GRANTS command lists the privileges for a user.
7.	Exit out of mysql. exit
8.	Log back in to mysql as one of your new users (Figure 2.39). Figure 2.39. Once the new user has been given permission, I can now access the alpacas database through the mysql client with that account. ./bin/mysql -u llama -p alpacas This command should let you into the mysql client to begin creating the alpacas tables. If you're using Windows, you'll need to bring up a console window, then move into the MySQL directory, and then use bin/mysql -u llama -p alpacas You cannot use the Start Menu shortcut, as that assumes you are using the root user by default.

Tips

• Any database whose name begins with test can be modified by any user who has permission to connect to MySQL. Therefore, be careful not to create databases with such names unless they are truly experimental.

• There is an even more manual way to create new users: by running INSERT commands on the user and other mysql database tables. This is only for the more experienced users who fully comprehend the relationships among the user, db, and other mysql tables. After running such inserts, you must enact the changes by typing the command FLUSH PRIVILEGES from within the mysql client.

• If you were to grant access to a user with a statement like

  GRANT privileges to database.* TO username IDENTIFIED BY 'password'

  that has the effect of granting the privileges to username@'%', which is to say any host is valid for that username.

• New as of MySQL 5.0.2 is the CREATE USER command. It's an alternative way to create a user (without granting privileges).

Changing Passwords

Once a user has been created, changing that user's password could not be more simple. The syntax for the command is:

SET PASSWORD FOR 'username'@'host' = PASSWORD('newPassword')

Obviously you will need to be logged in as a user with the proper privileges in order to execute this. If you want to update yourthe currently logged-in user'spassword, just use:

SET PASSWORD = PASSWORD('newPassword')

The SET PASSWORD command is available as of MySQL 4.0.

Deleting and revoking users

For security purposes it's imperative that you create new users, but it's also often necessary to remove existing users. For starters, MySQL will create, during the installation process, between one and four users from this list (hostname is meant to represent the name of the computer on which MySQL is hosted):

• root@localhost

• root@%

• root@hostname

• @localhost

• @%

• @hostname

Which ones have been created for you depends upon your operating system, version of MySQL, and so on, but you should look into what users exist and get rid of any unnecessary ones. Deleting a user is as simple as running this command, which was added in MySQL 4.1:

DROP USER username

If what you'd rather do is just remove some privileges that a user has, you can use the REVOKE command. It works much like GRANT:

REVOKE privileges ON database.* FROM 'username'@'host'

With respect to securing your initial setup, you may have two anonymous users, meaning that they'll allow connections to MySQL without even using a username and host. There may also be a root user that can connect remotely, which is normally not necessary and can be a security risk. Finally, none of these users have passwords at first. Hopefully you've already created the root user password, but let's clean up these other users now.

To remove existing users:

1.	Log in to the mysql client as an administrative, preferably root, user.
2.	Get the list of current users (Figure 2.40). Figure 2.40. This SQL query will show you all of the current users. It also reveals for which ones passwords have been established (although the passwords are encrypted for security purposes). SELECT User, Host, Password FROM mysql.user; This query will mean more to you in time, but basically it's a simple way to see all of the listed users and which ones have no passwords.
3.	Delete any users that shouldn't exist (Figure 2.41). Figure 2.41. Deleting two superfluous users. DROP USER ''@'ubuntu'; DROP USER ''@'localhost'; On my Linux box, two anonymous users were created that I no longer want. These commands will remove them.
4.	Make sure that all users have passwords (Figure 2.42). Figure 2.42. Updating a user's password. SET PASSWORD FOR 'root'@'ubuntu' = PASSWORD('newPassword'); Again, on my Linux MySQL installation, no password was set for the root users. I had already updated the password for root@localhost, but root@ubuntu (where ubuntu is the computer's name) was still lacking this security measure. This command will solve that problem.
5.	Revoke privileges as warranted. Using the REVOKE command briefly discussed earlier in this section (and more thoroughly covered in the MySQL manual), you can remove specific privileges from a user's arsenal.
6.	If you want, repeat Step 2 to confirm the current standing of all users. Alternatively, you can use the SHOW GRANTS command to see the privileges allotted to individual users.
7.	Exit out of mysql. exit

Other Security Recommendations

A large part of the security of your MySQL databases is based upon user access. When it comes to your MySQL users, the fundamental rules are:

• Grant only the most minimal privileges necessary to each user.

• Avoid granting SUPER or PROCESS privileges unless absolutely necessary.

• Restrict FILE privilege to administrators.

• Always require a password for all users.

• Use good, secure passwords (non-dictionary words, incorporating numbers and symbols, mixed-capitalization, etc.).

Besides, those recommendations, you should:

• Validate all data used in queries (you'll see this in the programming chapters).

• Watch for quotation marks and other problematic characters in queries.

• Run the MySQL server as its own user (on Mac OS X and Unix).

As security is such an important topic, best practices and other recommendations will be made throughout the book, as relevant to the topic at hand.