Home arrow MySQL arrow Page 5 - Client Access Control with MySQL

Answers to Exercises - MySQL

In our third and final article covering MySQL security, you will learn about client access control. There are exercises included (with answers) so you can test yourself on what you learned. This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Client Access Control with MySQL
  2. 12.3.1 Connection Request Checking
  3. 12.3.2 Statement Privilege Checking
  4. 12.4 Exercises
  5. Answers to Exercises
By: Sams Publishing
Rating: starstarstarstarstar / 6
July 27, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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


 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: