Access Control - MySQL

The process in which the MySQL server controls user privileges, although seemingly daunting at first look, is actually a fairly simple, although secure, procedure. Let's take a look at some not-so-obvious properties of this process before working through an example.

Property#1:
The tables can be looked at as a sort of filter which works from most general to most specific. This filter, (working from general to specific), is as follows:

• User table.
• Db Table
• Host Table
• Tables_priv Table
• Columns_priv Table

Property#2:
Once a server-connection is made, there are two kinds of requests that a user can make:

• Administrative Request (shutdown, reload, process, etc...)
• Database-related Request (insert, delete, alter, update, etc...)

When a user makes an administrative request, the server only has to look in one specific location: the user table. This is because the user table is the only table containing privileges related to administrative processes. However, when the user makes a database request, the process is a slight bit more complicated.

You may have noticed that the grant tables are somewhat redundant (i.e. A 'select' privilege within the user table, and the same privilege repeated within the host and user tables); This is not without reason. One could consider the database-related privileges within the user table as global. That is, the privileges granted to the user within this table are good for every database on the server. These privileges could be considered superuser privileges. On the other hand, the database-related privileges contained within the host and db tables are specifically related to the host or database in question. Thus it would be a wise decision to leave all privileges within this table as 'N'.

Regardless of you decide to set the user table, please take note of the following example. Let's assume our user and db tables are as follows:

Scenario #1: Failed Connection Attempt

1. User 'alessia' connection attempt failed. - Host, User and/or password does not match up with those contained within the user table. User is denied access.

Scenario #2: 'N' db-privilege in user table, 'Y' db-privilege in db table.

1. User 'wj' connection attempt successful.
2. User 'wj' attempts to perform a 'Select' command on the 'oats' database.
3. Server looks towards the user table. There is a 'N' (denied) entry for the 'Select' command.
4. Server then looks toward the db table. There is a 'Y' (allowed) entry for the 'Select' command.
5. Request is successful, because there is a 'Y' within the SELECT column of the user's db table insertion.

Scenario #3: 'Y' db-privilege in user table, 'N' db-privilege in db table.

1. User 'wj' connection attempt successful.
2. User 'wj' attempts to perform a 'Select' command on the 'oats' database.
3. Server looks towards the user table. There is a 'Y' (allowed) entry for the 'Select' command. Since the privileges granted within the user table are global, the request is successfully carried out.

Scenario #4: 'N' db-privilege in user table, 'N' db-privilege in db table.

1. User 'wj' connection attempt successful.
2. User 'wj' attempts to perform a 'Select' command on the 'oats' database.
3. Server looks towards the user table. There is a 'N' (denied) entry for the 'Select' command.
4. Server now looks towards the db table. There is a 'N' (denied= entry for the 'Select' command.
5. Server now looks towards the tables_priv and columns_priv tables. If the privileges are in accordance with the user's request, access is granted. Otherwise, access is denied.

The tables_privand columns_priv tables are discussed in further detail later on in this article.

Scenario #5: Let's assume the following is true:

• The 'host' column for user'wj' is '%' within the user table.
• the 'host' column for user 'wj' was blank within the db table.

What happens?

1. User 'wj' connection via a given host is attempted.
2. Assuming the password is correct, the attempt is successful, because the user table states that any ('%') host can connect if the connection is via username 'wj' and the given password.
3. The MySQL server looks to the db table. However, there is no host given.
4. The MySQL server now looks to the host table. IF the db in which the user is connecting to is listed within the host table along with the particular host name that the user is connecting from, then the user is free to carry out commands in accordance with the privileges listed within the host table. If the db/host are not in accordance with those in which the user is connecting from, the user cannot carry out commands and is in essence denied of connection.

The above scenarios should give the reader at least a bit of insight into the privilege system. We will now move on to the latest additions to the privilege system, the tables_priv and columns_priv tables.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus