Managing Users and Privileges - Learning MySQL - page 335

Now that you've made it through the checklist, you need to consider how the issues
are related. For each user, consider which client hosts the user must be able to access
the server from. For example, you might decide that root needs access from only local
host , while the user working with the music database needs access from localhost and
ruttle.invyhome.com . For each user and host combination, determine the minimal set
of privileges that you need.
Let's try a simple example. Suppose you're setting up a database server that will manage
the music database and be accessed by PHP scripts that run on a web server on the
localhost . Let's run through the checklist:
Clients
Only the localhost needs access.
Users
We need the root user, and one other user that we'll name musicuser to use in the
PHP script.
Privileges
After examining the list at the beginning of the chapter in “Privileges,” we identify
that the PHP scripts need the following privileges: DELETE , INSERT , SELECT , UPDATE ,
and LOCK TABLES . We identify that they are needed for all tables in the database.
Now we're ready to create musicuser .
The first step in setting up our user is to remove all other non- root users and ensure
root is allowed access from only the localhost ; the steps for this were described earlier
in “Securing the Default Users.” Then, create musicuser with the following statement
(we're setting the password as 'MiSeCr8' ):
mysql> GRANT DELETE, INSERT, SELECT, UPDATE, LOCK TABLES ON music.*
-> TO 'musicuser'@'localhost' IDENTIFIED BY 'MiSeCr8';
Query OK, 0 rows affected (0.28 sec)
Your PHP scripts now have sufficient privileges to access the database.
More Security Tips
The previous section explained a simple philosophy for creating users and privileges.
This section lists some basic tips to consider when creating users and privileges. Think
very carefully before granting these privileges:
ALTER
The ALTER privilege allows the user to change the structure of databases, permitting
operations such as renaming tables, adding and removing columns, and creating
and deleting indexes. This can allow the user to change or destroy data; for exam-
ple, reducing the size of an INT(5) column to an INT(1) destroys four digits of integer
precision. Importantly, if you grant ALTER as a global privilege, the user can subvert
the privilege-checking process by renaming the mysql database or its tables.