Home arrow MySQL arrow Page 2 - The MySQL Grant Tables

Access Control - MySQL

One of the most powerful aspects of the MySQL server is the amazing amount of control the administrator has over each user's intended behavior. This control can restrict user privileges over a general part of the server, such as limited access to an entire database, but can also be as specific as limiting privileges for a specific table or even column.

TABLE OF CONTENTS:
  1. The MySQL Grant Tables
  2. Access Control
  3. Tables_priv and columns_priv
  4. References
By: W.J. Gilmore
Rating: starstarstarstarstar / 21
March 01, 1999

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

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

User Table
Host %.pi.com
User wj
Password 34ghyT
Select_priv 'N'
Insert_priv 'Y'
Update_priv 'N'
Delete_priv 'N'
Index_priv 'N'
Alter_priv 'N'
Create_priv 'N'
Drop_priv 'N'
Grant_priv 'N'
Reload_priv 'N'
Shutdown_priv 'N'
Process_priv 'N'
File_priv 'N'
 
Db Table
Host %.pi.com
Db oats
User wj
Select_priv 'Y'
Insert_priv 'Y'
Update_priv 'Y'
Delete_priv 'N'
Index_priv 'N'
Alter_priv 'N'
Create_priv 'N'
Drop_priv 'N'
Grant_priv 'Y'

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