15.8 Managing Users and Privileges

MySQL has complex, flexible account and database access management. It supports multiple accounts (known as users), and each has an optional password and a set of privileges that define what the user can do. For example, you can allow a database administrator to startup, shutdown, and manage MySQL. You can allow an application administrator to create, drop, and alter tables and databases. In a web database application, you might limit a user to only altering data in tables, or give them read-only access. You can also control which databases, tables, and attributes a user can access, and from where they can access the server.

This section explains how user and privilege management is supported in MySQL, and recommends how to manage it for a web database application.

15.8.1 Creating Users and Privileges

When you installed MySQL by following our instructions in Appendix A through Appendix C, you set up two users (the root user and a web database application user) and created passwords for each. The root user has more privileges than should be used with an application: it can create other users and privileges, view and manipulate all databases, and control and manage MySQL. We recommend that you use the additional user you created for your application and that you create an additional user for each application that you build. We also recommend you keep it to one simple user per application: extra users or complex privileges slow down MySQL since there's more information to check before an operation can proceed.

Suppose you want to create a new user, lucy, who has control over the application database and can access MySQL from the machine that hosts the MySQL server. You can create this user by logging in as the root user and typing the following statement into the command interpreter:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON application.* TO

  lucy@127.0.0.1 IDENTIFIED BY 'password';

This statement grants the same privileges as the statement you executed in Appendix A through Appendix C to create a winestore database user.

The new user can then run the command interpreter from the Unix shell with the command:

% /usr/local/mysql/bin/mysql -ulucy -ppassword

Or, for Microsoft Windows from the Run dialog in the Start menu, type:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -ulucy -ppassword

The user in our online winestore application in Chapter 16 through Chapter 20 has these privileges.

15.8.1.1 Privileges and scope

Table 15-6 shows the privileges you can grant to a user; we've omitted a few privileges that involve advanced applications and future MySQL features. We've granted the basic privileges you need to work with a database to the user lucy in the previous section. To that list, depending on your requirements, you might add CREATE, CREATE TEMPORARY TABLES, DROP, INDEX, and FILE.

Unless you want to give the user administrator-style privileges, there's no need to use PROCESS, RELOAD, SHOW DATABASES, SHUTDOWN, SUPER, or GRANT OPTION. Be careful with GRANT OPTION : it allows a user to pass on their privileges to another user, and users can get together to grant each other privileges (perhaps leading to a security hole). Also, we don't recommend using ALL : for better security, think about what privileges are needed and explicitly list them.

Table 15-6. Privileges

Privilege

Function

ALL

Every privilege except GRANT OPTION

ALTER

Allows ALTER TABLE

CREATE

Allows CREATE TABLE

CREATE TEMPORARY TABLES

Allows CREATE TEMPORARY TABLE

DELETE

Allows DELETE

DROP

Allows DROP TABLE

FILE

Allows SELECT...INTO OUTFILE and LOAD DATA INFILE

INDEX

Allows CREATE INDEX and DROP INDEX

INSERT

Allows INSERT

LOCK TABLES

Allows LOCK TABLES and UNLOCK TABLES on those tables that have the SELECT privilege

PROCESS

Allows SHOW FULL PROCESSLIST

RELOAD

Allows FLUSH

SELECT

Allows SELECT

SHOW DATABASES

Allows SHOW DATABASES to show all databases (including those the user can't access)

SHUTDOWN

Allows mysqladmin shutdown

SUPER

Overrides connection limitations, and allows the user to kill database threads, and set MySQL options

UPDATE

Allows UPDATE

USAGE

The same as no privileges

GRANT OPTION

Allows the user to pass on their privileges using GRANT


In our examples so far, we've granted privileges to application.*. This means that the privileges apply to the application database, and all tables within the database. For a database, you can grant ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, FILE, INDEX, INSERT, LOCK TABLES, FLUSH, SELECT, and UPDATE. Some privileges can be applied to complete databases, some to individual tables, and some even to attributes (columns) within tables.

If you want to grant a global privilege for all databases and tables, use *.*. For example, to allow lucy to SELECT from all databases and tables:

GRANT SELECT ON *.* TO lucy@127.0.0.1 IDENTIFIED BY 'password';

For the privileges PROCESS, RELOAD, SHOW DATABASES, SHUTDOWN, and SUPER, it only makes sense to grant these to *.* since they aren't specific to a database.

If you want to grant the INSERT privilege for only the customer table in the winestore database, you can do that with:

GRANT INSERT ON winestore.customer TO lucy@127.0.0.1 

  IDENTIFIED BY 'password';

For tables, you can grant the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER privileges.

You can even go a step further and allow a user only to INSERT into the attributes cust_id and surname in the customer table:

GRANT INSERT (cust_id, surname) ON winestore.customer TO lucy@127.0.0.1 

  IDENTIFIED BY 'password';

For attributes, you can grant the SELECT, INSERT, and UPDATE privileges.

Privileges take precedence by their level in the hierarchy. If the user lucy has SELECT access to *.*, she can access all databases and tables; it doesn't matter whether she has SELECT access for the winestore database. The same applies to a database: if you can SELECT from a database, you can SELECT from all its tables and attributes. Similarly, if you have table privileges, you have all attribute privileges for that table.

15.8.1.2 Network access

There is usually no need to allow network access for a web database application if the middle-tier components (the web server and PHP scripting engine) are installed on the same machine as the MySQL server. However, if you want to allow access, you can do so in a broad or selective manner. Thus, to give user lucy access over a network from the server hugh.hughinvy.com, you can replace 127.0.0.1 with the IP address of the server or its domain name:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 

  TO lucy@hugh.hughinvy.com IDENTIFIED BY 'password';

If you want to allow access from all hosts in the hughinvy.com domain, you can use the wildcard %:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 

  TO lucy@"%.hughinvy.com" IDENTIFIED BY 'password';

You can use the % wildcard anywhere in a domain or IP address. Note that when you use a wildcard, you need to enclose the domain string in quotes. If you want to allow access from anywhere, grant privileges to lucy without the @ suffix:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* 

  TO lucy IDENTIFIED BY 'password';

As discussed previously, we don't recommend using wildcards. Instead, we recommend that you explicitly list the servers so that you minimize the chance of creating a security hole.

15.8.2 Revoking Privileges

The REVOKE statement removes privileges. In contrast, executing a second or subsequent GRANT statement for a user doesn't revoke their previous privileges. For example, if you type:

GRANT SELECT ON winestore.* TO lucy@127.0.0.1 IDENTIFIED by 'password';

GRANT INSERT ON winestore.* TO lucy@127.0.0.1;

then the user lucy can now SELECT and INSERT into the winestore database.

To revoke one or more privileges, use REVOKE. For example, to remove the INSERT privilege we've just granted, use:

REVOKE INSERT ON winestore.* FROM lucy@127.0.0.1;

The REVOKE statement has much the same syntax as GRANT. You can revoke global, databases, table, and attribute privileges, and you use the same method of specifying databases and tables. For example, to remove all global privileges from lucy, use:

REVOKE ALL ON *.* FROM lucy@127.0.0.1;

Beware: this doesn't remove the user's privileges on all databases, tables, or attributes! You need to explicitly remove privileges that you've granted from each level of the hierarchy.

Here's one final example. To remove an attribute privilege from the customer table, use:

REVOKE INSERT (cust_id) ON winestore.customer FROM lucy@127.0.0.1;

15.8.2.1 How MySQL manages privileges

The user and privilege information is stored in the mysql database, and you can explore and maintain that database in the same way as any other. The user table contains the global privilege settings. It contains one row for each user and host combination, and each attribute value in the row is set to Y or N, depending on whether the user has the privilege described by the attribute name. The encrypted password of the user is also stored in the row (it's encrypted with the password( ) function described in "Functions").

Similarly to the user table, the db table in the mysql database contains database-level settings for all databases, the tables_priv contains table-level settings for all tables, and the columns_priv table contains attribute-level settings for all attributes. If a table doesn't contain a row for a user, that user has no privileges for that level of setting. If you revoke all privileges in a row, the row is deleted.

Exploring these tables is an excellent way to check what settings you've created, and to remove or change settings quickly using UPDATE, INSERT, or DELETE. However, if you do adjust privileges manually, issue a FLUSH PRIVILEGES statement afterwards so that MySQL rereads the tables and updates itself.