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.
Administering your server through a third-party application may 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 of 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, 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";
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 world?a 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 firstname.lastname@example.org IDENTIFIED BY "45sdg11";
If you know that janescomputer.company.com has an IP address of 220.127.116.11, you can substitute that address in the hostname portion of the command, as follows:
GRANT ALL ON myCompany.employees TO jane@'18.104.22.168' 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 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.