12.4 Exercises
| 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? |
Answers to Exercises | | | Answer 1: | On the filesystem level, you must protect the following:
Databases and their tables, so that unauthorized users cannot access them directly Log files and status files, so that unauthorized users cannot access them directly Configuration files, so that unauthorized users cannot replace or modify them Programs and scripts that manage and access databases, so that users cannot replace or modify them
| | | | Answer 2: | Neither pablo nor charlton need file system-level access to their database directories. If they want to access their databases, they should do this through the MySQL server; for example, by using the mysql client program. Therefore, the /usr/local/mysql/data directory should be accessible only to user mysql (assuming that this is the system user the server runs as). | | | | Answer 3: | To start the server so that it runs as user mysql, you can start it with a --user option like this:
shell> mysqld --user=mysql
The mysqld_safe script also accepts a --user option. To make sure that the server will always start as that user, put the option in an option file (for example, /etc/my.cnf):
[mysqld]
user=mysql
| | | | Answer 4: | None of the MySQL accounts, not even the root accounts, are assigned a password by the installation procedure. You can connect to the server like this, without specifying any password option:
shell> mysql -u root
| | | | Answer 5: | To determine which accounts, if any, can be used without specifying a password, use the following statement:
mysql> SELECT Host, User FROM mysql.user WHERE Password = '';
If any such accounts exist, you can delete them as follows:
mysql> DELETE FROM mysql.user WHERE Password = '';
mysql> FLUSH PRIVILEGES;
The FLUSH PRIVILEGES statement is necessary because DELETE doesn't cause the server to reread the grant tables into memory. | | | | Answer 6: | To create the superuser account, use a GRANT statement:
mysql> GRANT
-> ALL PRIVILEGES ON *.*
-> TO 'superuser'@'localhost'
-> IDENTIFIED BY 's3creT'
-> WITH GRANT OPTION
-> ;
See section A.1.17, "GRANT." | | | | Answer 7: | For encryption and decryption, you could use the following functions:
ENCODE() and DECODE(); these have no special requirements. DES_ENCRYPT() and DES_DECRYPT(); these require SSL support to be enabled. AES_ENCRYPT() and AES_DECRYPT(); these have no special requirements. PASSWORD() can encrypt data, but has no corresponding decryption function. It should only be used for MySQL user account management.
See section A.2, "SQL Functions." | | | | Answer 8: | This statement sets up an account for steve:
mysql> GRANT
-> SELECT, INSERT, UPDATE, DELETE
-> ON accounting.*
-> TO 'steve'@'%'
-> IDENTIFIED BY 'some_password1'
-> ;
See section A.1.17, "GRANT." | | | | Answer 9: | This statement sets up an account for pablo:
mysql> GRANT
-> ALL PRIVILEGES
-> ON marketing.*
-> TO 'pablo'@'192.168.%'
-> IDENTIFIED BY 'some_password2'
-> WITH GRANT OPTION
-> ;
See section A.1.17, "GRANT." | | | | Answer 10: | This statement sets up an account for admin:
mysql> GRANT
-> ALL PRIVILEGES
-> ON *.*
-> TO 'admin'@'localhost'
-> IDENTIFIED BY 'some_password3'
-> ;
See section A.1.17, "GRANT." | | | | Answer 11: |
62.220.12.66 is the most specific entry that matches from which the host user icke is trying to connect. Because the SELECT privilege for that entry is N, user icke cannot select from any table on the server. The most specific entry that matches 62.220.12.43 is 62.220.12.%. Because the SELECT privilege for that entry is Y, user icke can select from any table on the server. The most specific entry that matches 62.220.42.43 is 62.220.%. Because the SELECT privilege for that entry is N, user icke cannot select from any table on the server. There's no entry that would match icke@localhost. Therefore, user icke cannot even connect to the server.
| | | | Answer 12: | To set up a new password for superuser, you could use the following procedure:
Bring down the MySQL server by means of the operating system. If you run the server as a Windows service, you can stop the service. On Unix, you might have to forcibly terminate the server by using the kill command. Restart the server in a way that it will not read the grant tables. As a safety precaution, make sure that no clients can connect to it other than from the local host:
shell> mysqld --skip-grant-tables --skip-networking
Connect to the server from the local host:
shell> mysql
No username is needed here because the server is not using the grant tables. Update the Password column in the mysql.user table entry for the superuser account, and then end the mysql session:
mysql> UPDATE mysql.user
-> SET Password = PASSWORD('NeverForget')
-> WHERE User = 'superuser'
-> ;
mysql> EXIT;
Shut down the server normally:
shell> mysqladmin shutdown
The UPDATE statement in the previous step does not cause the server to refresh the in-memory grant tables, so no password is needed here. Start the server using your normal startup procedure. If you had to forcibly terminate the server, it would be a good idea to check all tables:
shell> mysqlcheck -u root -p --all-databases
|
|
