Learning Mysql

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 insetting 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 example, 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.

Download 4.24 Mb.

Share with your friends: