Client Access Control with MySQL - Answers to Exercises
(Page 5 of 5 )
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
|
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 6 
