12.4 Exercises - MySQL

Question 1:

Which components of MySQL must you protect on the filesystem level?

Question 2:

Assume that you have three users who have login accounts on a host where a MySQL server is running. Users pablo and charlton need to communicate with the MySQL server, but user steve doesn't. How would you set the file permissions for the /usr/local/mysql/data directory so that pablo and charlton can access their databases located there but steve cannot?

Question 3:

As the root login user on a Linux host, how can you start the MySQL server so that it doesn't run as root, without having to log in as another user? How can you make sure that the server will always run as a user different from root?

Question 4:

What's the initial password of the MySQL root accounts in the grant tables that are set up during the installation procedure?

Question 5:

Having installed MySQL, you want to make sure that there's no MySQL account left that could connect to the server without specifying a password. How can you do this?

Question 6:

You want to set up a MySQL administrator account with all privileges. This administrator should be called superuser and the password should be s3creT. superuser should be able to connect only from the local host, and should be allowed to create new MySQL users. How would you create this account?

Question 7:

Which SQL functions could you use to store encrypted information? What functions could you use to retrieve the stored information unencrypted? Are there special prerequisites or requirements for using these functions?

Question 8:

What GRANT statement would you issue to set up an account for user steve, who should be able to manipulate data of tables only in the accounting database? steve should be able to connect to the server from anywhere. The account password should be some_password1.

Question 9:

What GRANT statement would you issue to set up an account for user pablo, who should be able to do all kinds of operations on the tables in the marketing database and should also be able to grant permissions to do those operations to other MySQL users? pablo should be able to connect to the server from the local network where IP numbers of all machines start with 192.168. The account password should be some_password2.

Question 10:

What GRANT statement would you issue to set up an account for user admin, who should be able to administer the database server, including performing all operations on all its databases and tables? admin should not, however, be able to grant privileges to other accounts. admin should be able to connect to the server only from the local host. The account password should be some_password3.

Question 11:

Consider the following privilege settings for the accounts associated with a given MySQL username, where the Select_priv column indicates the setting for the global SELECT privilege:

mysql> SELECT
-> Host, User, Select_priv
-> FROM mysql.user
-> WHERE User = 'icke'

-> ;
+--------------+------+-------------+
| Host         | User | Select_priv |
+--------------+------+-------------+
| 62.220.12.66 | icke | N           |
| 62.220.12.%  | icke | Y           |
| 62.220.%     | icke | N           |
+--------------+------+-------------+

The Select_priv column indicates that the SELECT privilege for the second entry has been granted on a global scope (*.*). Will user icke be able to select data from any table on the MySQL server when connecting from the following hosts:

• 62.220.12.66

• 62.220.12.43

• 62.220.42.43

• localhost

Assume that the icke accounts are not granted privileges in any of the other grant tables.

Question 12:

Assume that you set up an administrator for the MySQL server named superuser, and that this is the only account with the full set of privileges. In particular, this is the only account that can grant privileges to other accounts or shut down the server using mysqladmin shutdown. Unfortunately, you've forgotten the password for superuser. Assume that you can log on to the host where the MySQL server runs, and that you can do so as some administrative account (such as Administrator for Windows or root for Unix). What can you do to set up the MySQL superuser account with a new password, and what safety precautions would you take?