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

12.3.1 Connection Request Checking - 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

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'



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