Client Access Control with 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).

12.3 Client Access Control

This section describes how the server uses account information in the grant tables to control which clients may connect and what they may do after connecting.

There are two stages of client access control:

  • In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which a client connects, the username, and the password.

  • In the second stage (which occurs only if a client has already connected successfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the username the client provides. However, it’s possible for more than one record to match:

  • Host values in grant tables may be specified as patterns containing wildcard values. If a grant table contains entries for myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

  • Patterns are not allowed for User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list. Sorting takes place as follows:

  • In the Host column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

  • In the User column, nonblank usernames sort ahead of blank usernames. That is, nonanonymous users sort ahead of anonymous users.

The server performs this sorting at startup. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

Suppose that the user table contains the following values in the Host and User columns:

+--------------------+--------+
| Host               | User   |
+--------------------+--------+
| localhost          |        |
| %                  | james  |
| %.example.com      | jen    |
| %.com              | jobril |
| localhost          | jon    |
| myhost.example.com | james  |
+--------------------+--------+

When the server reads the grant tables into memory, it sorts the user table records as follows:

  • localhost and myhost.example.com are literal values, so they sort ahead of the other Host values that contain pattern characters. The Host values that contain pattern characters sort from most specific to least specific.

  • The two entries that have localhost in the Host column are ordered based on the User values. The entry with the nonblank username sorts ahead of the one with the blank username.

The sorting rules result in entries that are ordered like this:

+--------------------+--------+
| Host               | User   |
+--------------------+--------+
| localhost          | jon    |
| localhost          |        |
| myhost.example.com | james  |
| %.example.com      | jen    |
| %.com              | jobril |
| %                  | james  |
+--------------------+--------+

{mospagebreak title=12.3.1 Connection Request Checking}

When a client attempts to connect, the server matches the sorted records to the client using the Host values first and the User values second:

  • If jon connects from the local host, the entry with localhost and jon in the Host and User columns matches first.

  • If james connects from localhost, the two entries with localhost in the Host column match the host, and the entry with the blank User value matches any username. Therefore, that’s the first entry that matches both the client hostname and username. (The entry with % in the Host column matches localhost as well, but the server doesn’t consider it in this case because it has already found a matching record.)

  • On the other hand, if james connects from pluto.example.com instead, the first entry that matches the hostname has a Host value of %.example.com. That entry’s username doesn’t match, so the server continues looking. The same thing happens with the entry that has a Host value of %.com: The hostname matches but the username does not. Finally, the entry with a Host value of % matches and the username matches as well.

When you attempt to determine which grant table record the server will find as the best match for a client, remember to take the sort order into account. In particular, the fact that Host matching is done before User matching leads to a property that might be surprising unless you’re aware of it. Consider again the case where james connects from the local host. There are two entries with james in the User column, but neither is the first match. Host matching takes place first, so on that basis the entry that matches first is the anonymous-user entry: localhost matches the host from which james connects, and the blank User value matches any username. This means that when james connects from the local host, he will be treated as an anonymous user, not as james.

When you connect successfully to the server, the USER() function returns the username you specified and the client host from which you connected. The CURRENT_USER() function returns the username and hostname values from the User and Host columns of the user table record the server used to authenticate you. The two values may be different. If james connects from the local host, USER() and CURRENT_USER() have these values:

mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER()          | CURRENT_USER() |
+-----------------+----------------+
| james@localhost | @localhost     |
+-----------------+----------------+

The username part of CURRENT_USER() is empty. This occurs because the server authenticates james as an anonymous user.

If james connects from pluto.example.com instead, USER() and CURRENT_USER() have these values:

mysql> SELECT USER(), CURRENT_USER();
+-------------------------+----------------+
| USER()                  | CURRENT_USER() |
+-------------------------+----------------+
| james@pluto.example.com | james@%        |
+-------------------------+----------------+

Here the host part of CURRENT_USER() is % because the server authenticates james using the user table entry that has % as the Host value.

For connection attempts that the server denies, an error message results:

  • If the client attempts to connect from a host for which there is no record in the user table with a matching Host value, the error is

    Host 'host_name' is not allowed to connect to
    this MySQL server
  • If connections from the client host are allowed by one or more user table records, but no match can be found for the User and Password values, the error is

    "Access denied for user:
    'user_name'@'host_name'

{mospagebreak title=12.3.2 Statement Privilege Checking}

Each time the server receives a statement from a client, it checks the client’s privileges to see whether it’s allowed to execute the statement. For example, if you issue an UPDATE statement, you must possess the UPDATE privilege for each of the columns to be updated.

The server checks privileges in an additive fashion from the global level to the column- specific level. To check a statement, the server determines which privileges the statement requires, and then assesses whether the client possesses them by proceeding successively through the grant tables.

First, the server checks the client’s global privileges in the user table. If these are sufficient, the server executes the statement. If the global privileges are not sufficient, the server adds any database-specific privileges indicated for the client in the db table and checks again. If the combined privileges are sufficient, the server executes the statement. Otherwise, it continues as necessary, checking the table-specific and column-specific privileges in the tables_priv and columns_priv tables. If, after checking all the grant tables, the client does not have sufficient privileges, the server refuses to execute the statement.

12.3.3 Resource Limit Checking

For an account that has resource limits, the server applies them to access control as follows:

  • If the client has a limit on the number of times per hour it can connect to the server, that limit applies in the first stage of access control, when the server determines whether to accept the client connection.

  • If the client has a limit on the number of queries or updates per hour it can issue, those limits apply in the second stage of access control. The server checks the limits for each query received before checking whether the client has the proper privileges to execute it.

12.3.4 Disabling Client Access Control

The –skip-grant-tables option tells the server not to use the grant tables to control client access. This option has the following effects:

  • You can connect from anywhere with no password, and you have full privileges to do anything. That’s convenient if you’ve forgotten the root password and need to reset it because you can connect without knowing the password. On the other hand, because anyone else can connect, running the server in this mode is dangerous. To prevent remote clients from connecting over TCP/IP, you might want to use the –skip-networking option as well. Clients then can connect only from the local host using a Windows-named pipe or a Unix socket file.

  • --skip-grant-tables disables the GRANT, REVOKE, and SET PASSWORD statements. These statements require the in-memory copies of the grant tables, which aren’t set up when you skip use of the tables. To make a change to the grant tables while those statements are inoperative, you must update them directly. Alternatively, when you connect to the server, you can issue a FLUSH PRIVILEGES statement to cause the server to read the tables. That will enable GRANT, REVOKE, and SET PASSWORD. (Note that if you also started the server with the –skip-networking option, you’ll still need to restart it without that option to cause it to listen for TCP/IP connections again.)

{mospagebreak title=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?

{mospagebreak title=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:

  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
[gp-comments width="770" linklove="off" ]

chat sex hikayeleri