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.

If you are the administrator of your own server or have the ability 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

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

The privileges you can grant are

• ALL Gives the user all the following 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 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

• REFERENCES Not currently used by MySQL, but a column for REFERENCES privileges exists in the user table

• 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

• USAGE User can connect to MySQL but has no privileges

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

 GRANT SELECT, INSERT ON myDB.* TO john@"%" IDENTIFIED BY "99hjc!5"; 

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 list indeed.

Here's another example of adding a user using 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 [e-mail] IDENTIFIED BY "45sdg11"; 

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

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

One note about adding users: Always use a password and make sure that the password is a good one! MySQL allows you to create users without a password, but that leaves the door wide open should someone with bad intentions guess the name of one of your users with full privileges granted to them!

If you use the GRANT command to add users, the changes will immediately take 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 a GRANT command, you use REVOKE. The REVOKE command syntax is

 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 just 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:

 REVOKE INSERT ON myDB.* FROM john@"%"; 

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.