Working with User Privileges

In most cases when you are accessing MySQL through an Internet service provider, you will have only one user and one database available to you. By default, that one user will have access to all tables in that database and will be allowed to perform all commands. In this case, the responsibility is yours as the developer to create a secure application through your programming.

However, if you are the administrator of your own server or if your Internet service provider allows you to add as many databases and users as you want as well as modify the access privileges of your users, these next few sections will take you through the processes of doing so.

Adding Users

Administering your server through a third-party application might afford you a simple method for adding users, using a wizard-like process or a graphical interface. However, adding users through the MySQL monitor is not difficult, especially if you understand the security checkpoints used by MySQL, which you just learned.

The simplest method for adding new users is the GRANT command. By connecting to MySQL as the root user, you can issue one command to set up a new user. The other method is to issue INSERT statements into all the relevant tables in the mysql database, which requires you to know all the fields in the tables used to store permissions. This method works just as well but is more complicated than the simple GRANT command.

The simple syntax of the GRANT command is shown here:

GRANT privileges ON databasename.tablename TO username@host IDENTIFIED BY "password";

Following are some of the common privileges you can grant. For a complete list, see the GRANT entry in the MySQL Manual at

  • ALL Gives the user all common privileges

  • ALTER User can alter (modify) tables, columns, and indexes

  • CREATE User can create databases and tables

  • DELETE User can delete records from tables

  • DROP User can drop (delete) tables and databases

  • FILE User can read and write files; this privilege is used to import or dump data

  • INDEX User can add or delete indexes

  • INSERT User can add records to tables

  • PROCESS User can view and stop system processes; only trusted users should be able to do this

  • RELOAD User can issue FLUSH statements; only trusted users should be able to do this

  • SELECT User can select records from tables

  • SHUTDOWN User can shut down the MySQL server; only trusted users should be able to do this

  • UPDATE User can update (modify) records in tables

If, for instance, you want to create a user called john with a password of 99hjc!5, with SELECT and INSERT privileges on all tables in the database called myDB, and you want this user to be able to connect from any host, use this command:


Note the use of two wildcards: * and %. These wildcards are used to replace values. In this example, * replaces the entire list of tables, and % replaces a list of all hosts in the known worlda very long listin deed.

Here's another example of adding a user with the GRANT command, this time to add a user called jane with a password of 45sdg11, with ALL privileges on a table called employees in the database called myCompany. This new user can connect only from a specific host:

GRANT ALL ON myCompany.employees TO IDENTIFIED BY "45sdg11";

If you know that has an IP address of, you can substitute that address in the hostname portion of the command, as follows:

GRANT ALL ON myCompany.employees TO jane@'' IDENTIFIED BY "45sdg11";

One note about adding users: Always use a password and make sure that the password is a good one!

If you use the GRANT command to add users, the changes take immediate effect. To make absolutely sure of this, you can issue the FLUSH PRIVILEGES command in the MySQL monitor to reload the privilege tables.

Removing Privileges

Removing privileges is as simple as adding them; instead of the GRANT command, you use REVOKE. The REVOKE command syntax is as follows:

REVOKE privileges ON databasename.tablename FROM username@hostname;

In the same way that you can grant permissions using INSERT commands, you can also revoke permissions by issuing DELETE commands to remove records from tables in the mysql database. However, this requires that you be familiar with the fields and tables, and it's much easier and safer to use REVOKE.

To revoke the ability for user john to INSERT items in the myCompany database, you would issue this REVOKE statement:


Changes made to the data in the privilege tables happen immediately, but in order for the server to be aware of your changes, issue the FLUSH PRIVILEGES command in the MySQL monitor.

Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327 © 2008-2017.
If you may any questions please contact us: