Home arrow MySQL arrow Page 5 - Access Granted

The Perfect Host - MySQL

mySQL comes with a pretty powerful security system, the grant tables, which allows database administrators to precisely control access to databases, tables and even specific rows and columns. In this article, find out how the five grant tables combine to offer power users a tremedous amounts of flexibility and control over database access and operations.

TABLE OF CONTENTS:
  1. Access Granted
  2. Meet Joe User
  3. Beeping Turkeys
  4. Born Privileged
  5. The Perfect Host
  6. Cream Of The Crop
  7. The Mechanics
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 9
April 24, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
A blank entry under the "Host" column in the "db" table implies that the list of allowed hosts should be obtained from the third table, the "hosts" table - which looks like this:

+-----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+-------+ | Host | char(60) | | PRI | | | | Db | char(32) | | PRI | | | | Select_priv | enum('N','Y') | | | N | | | Insert_priv | enum('N','Y') | | | N | | | Update_priv | enum('N','Y') | | | N | | | Delete_priv | enum('N','Y') | | | N | | | Create_priv | enum('N','Y') | | | N | | | Drop_priv | enum('N','Y') | | | N | | | Grant_priv | enum('N','Y') | | | N | | | References_priv | enum('N','Y') | | | N | | | Index_priv | enum('N','Y') | | | N | | | Alter_priv | enum('N','Y') | | | N | | +-----------------+---------------+------+-----+---------+-------+

This separation is more useful than you might think. If you would like to connect and use a database from several different hosts, you would usually have to create a separate entry naming each host in the "db" table. However, with the introduction of the "host" table, you can place the host names in the "host" table while retaining only a single entry (with a blank "Host" field) in the "user" table.

The "host" table also has privilege fields - these allow you to control the level of access for each database, with the connecting host name as the criteria for operation.

Here's an example of how the relationship between the "host" and "db" table can be exploited for maximum benefit:

mysql> SELECT Host, User, Password FROM user; +-----------+------+----------+ | Host | User | Password | +-----------+------+----------+ | | jim | h35472k | +-----------+------+----------+ mysql> SELECT Host, User, Db FROM db +------+------+-------+ | Host | User | Db | +------+------+-------+ | | jim | title | +------+------+-------+ mysql> SELECT Host, Db, Select_priv, Insert_priv FROM host +-------------------+-------+----------------+----------------+ | Host | Db | Select_priv | Insert_priv | +-------------------+-------+----------------+----------------+ | turkey.ix6.com | title | Y | Y | +-------------------+-------+----------------+----------------+ | blackbox.glue.net | title | Y | N | +-------------------+-------+----------------+----------------+ | fireball.home.net | title | Y | Y | +-------------------+-------+----------------+----------------+

In this case, "jim" will be able to connect to the mySQL server from any of the hosts listed in the "hosts" table, and the privileges assigned can differ on the basis of the host.

An important point to be noted is that, in the hierarchy of mySQL grant tables, the "user" table comes first, with the "db" and "host" tables below it, and the "tables_priv" and "columns_priv" tables at the bottom. A table at a lower level is referred to only if a higher-level table fails to provide the necessary scope or privileges.

When deciding whether or not to allow a particular database operation, mySQL takes the privilege fields in all three tables into account. It starts with the "user" table and checks to see if the user has appropriate privileges for the operation being attempted; if not, the "db" and "host" tables are checked to see if privileges are available. It is only after logically parsing the privileges in the different tables that mySQL allows or disallows a specific database request.

 
 
>>> More MySQL Articles          >>> More By icarus, (c) Melonfire
 

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: