Setting User Permissions

Most database servers distinguish between database users and operating system users (although some, such as PostgreSQL, use both). Thus, while you may log in as the user L337u53r under Unix or Windows, the database administrator might require you to use the drab name webapp_db to connect to the database. We will now show you how to create and remove users on MySQL and how to grant or revoke privileges to those accounts.

The system used for user authentication and privilege management under MySQL is both flexible and powerful. You have the ability not only to manage which users can do what, but you are also able to control from which hosts they may connect to your server for a bit of added security.

Preparing to Create Users

To create a user in MySQL, you need to be connected to the server as a database user with permissions to create users (typically done as the root user). We establish our connection again by using the mysql client program. To create a user, you need to have the following bits of information established or ready:

• The name of the user you wish to create; this can be up to 16 characters long.

• The databases and tables to which you want to give them access. This can be "*.*," which would be all tables on all databases, "dbname.*," which would be all tables in the database dbname, or "dbname.tablename," which would restrict the permissions to access of the tablename table in the dbname database.

• What operations you would like to permit them to perform on those databases.

• From which hosts you would like to permit them to connect.

• Whether or not you would like to permit them to grant or revoke permissions to other users on tables to which they have access.

What to Grant or Revoke

There is a wide range of specific permissions that can be granted to or revoked from a user of the database. At the broadest level, ALL PRIVILEGES gives you the opportunity to give to or revoke from a user all basic privileges (except the ability to grant permissions to others) on the specified databases, while at the most narrow level, USAGE permits the specified user to connect to the server and do nothing else.

Since we rarely want to be so "all or nothing" in our approach to permissions, we have the option to grant the ability to do specific tasks, the most common of which are the following. (The rest can be found by consulting the MySQL documentation.)

• SELECT Whether the user has permission to execute a query with the SELECT keyword.

• INSERT Whether the user can insert rows into a table.

• DELETE Whether the user can delete rows from a table.

• UPDATE Whether the user can update (change the data) for rows in a table.

• CREATE Whether the user can create new tables in the database.

• DROP Whether the user can drop (delete) tables from the database.

• ALTER Whether the set of columns and their types in a table can be modified.

To try to maintain adequate security levels, we typically have two users for our databases: one who can create and drop tables in addition to manipulating data within them, and another who can only select, insert, delete, and update tables.

From Which Hosts to Permit

MySQL lets us choose from which hosts we let users connect, and it supports wildcards to let us specify any host in a domain or specific IP addresses. If our web server and database server are on the same computer, we can grant users permissions to only connect from localhost, which would be quite secure. We could grant them permissions to connect from all the computers in our internal domain, such as %.mydomain.com, or we could let a user connect from all (nonlocal) machines by specifying %.

If we want to permit a user to connect from both the localhost and from other computers, we need to execute two queries to grant him permissions. This is due to how MySQL works its way through the permissions table. If you only give a user permissions from remote hosts and it tries to connect from localhost, MySQL connects that user with lower permission levels. The MySQL documentation has a full description of how MySQL's access control system works.

Many people would argue that your database server and your web server with PHP should never be on the same physical computeryour web server interacts directly with the open Internet, and as a result lives in a more dangerous world. This train of thought argues that by keeping your database server(s) on a different machine, you reduce the risk to your data in the event that the web server is compromised.

Creating a User

Now that we have gathered all of the data, we are ready to grant the user permissions. The syntax is as follows:

 GRANT permissions   ON databases.tables   TO 'username'@'permittedhost'   IDENTIFIED BY 'password'   WITH GRANT OPTION; 

The last part of the previous query, WITH GRANT OPTION, is entirely optional, depending on whether you want to permit the user to grant permissions to others. We rarely use it for our sample projects in this book.

If we had a database called MessageBoard and wanted to create two users for itone for managing the database and one for simply accessing and manipulating the data, we might create the following statements. (We will assume that our web server and database server are running on the same machine.)

 GRANT CREATE,DROP,SELECT,UPDATE,INSERT,DELETE   ON MessageBoard.*   TO 'message_admin'@'localhost'   IDENTIFIED BY 'adminpassword'; GRANT SELECT,UPDATE,INSERT,DELETE   ON MessageBoard.*   TO 'message_user'@'localhost'   IDENTIFIED BY 'userpassword'; 

MySQL gives us the following after each query, provided that there were no mistakes:

 Query OK, 0 rows affected (0.01 sec) 

MySQL is modifying a database table called user in the mysql administrative database it maintains for us. You are fully able to go and modify this table using regular SQL queries. To see a list of all users currently managed in the permissions system, you can execute the following query:

 SELECT User,Host FROM mysql.user; 

Deleting Users or Removing Privileges

To delete a user in MySQL, use the DROP USER query along with the full name (including hosts) specified in the GRANT query executed when the account was created. To delete the users we created earlier for our MessageBoard database, we run the following:

 DROP USER 'message_admin'@'localhost'; DROP USER 'message_user'@'localhost'; 

The DROP USER query was only added in MySQL version 4.1.1. In prior versions, you would run the following:

 DELETE FROM mysql.user WHERE User='message_admin'    AND Host='localhost'; 

To remove certain permissions from users, you can execute a REVOKE query, which is similar to the GRANT query, except that permissions are removed. If we no longer wanted our message_admin user to have the ability to create or drop tables, we could execute

 REVOKE CREATE,DROP   ON MessageBoard.*   FROM 'message_admin'@'localhost'; 

Notice that we revoke permissions from a user instead of granting them to one.