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.
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 http://dev.mysql.com/doc/refman/5.0/en/grant.html.
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:
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 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 firstname.lastname@example.org IDENTIFIED BY "45sdg11";
If you know that janescomputer.company.com has an IP address of 188.8.131.52, you can substitute that address in the hostname portion of the command, as follows:
GRANT ALL ON myCompany.employees TO jane@'184.108.40.206' 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 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:
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.