Answers to Exercises - MySQL

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:

1. 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.

2. 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

3. Connect to the server from the local host:

   shell> mysql

   No username is needed here because the server is not using the grant tables.

4. 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;

5. 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.

6. Start the server using your normal startup procedure.

7. 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