MySQL Administration - The Privilege System
( Page 2 of 4 )

Administering the MySQL server involves the maintenance of the database server (hosts, users, and databases). These duties can be better termed as administering MySQL's privilege system.

The privilege system is in fact, a simple concept. Through designating certain 'privileges', a certain user on a certain host can perform certain commands within a certain database. These privileges give a user a certain set of rules with which to use the MySQL server. For example, a user could have privileges to insert information, but not to delete it. Another user may have privileges to create tables, but cannot destroy (drop) them. These hosts, users, databases, and certain privileges are denoted within the 'host', 'user' and 'db' tables respectively, otherwise known as the 'privilege' tables.

The privilege tables
As stated above, all privileges are stored in three tables: 'user', 'host' and 'db'. A good way to look at these three tables is as an order of hierarchy:

First level: host
Second level: user
Third level: db

These tables operate much like normal MySQL tables. They are easily modified using normal INSERT, UPDATE, and DELETE commands. In fact, they are so easy to manipulate that all previous fears about these tables will most likely be erased after you have read the following paragraphs.

Entering MySQL as the administrator for the first time:
Assuming you have just installed MySQL, run the following command:

$ mysql -u root mysql

This will allow you to enter the mysql table of the MySQL database. From here, the administrator can execute all necessary administration commands.

The host table:
The 'host' table determines which hosts are able to enter the MySQL server. For those running only one server, one would only have to enter two hosts, the 'localhost' and the actual host name of the server.

Columns contained within the 'host' table:
Host - Which host?
Db - Name of database?

Again, the following are determined with a 'Y' or 'N' (Default 'N'). These allow the host to execute certain instructions while using the specified database.
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv

For example, if your hostname is 'devshed', and the only intended database is 'mydb', you would do as the following to make the server accessible from the actual server, as well as the devshed hostname:

mysql> insert into
    -> host(host,db,Select_priv,Insert_priv,Update_priv,
    -> Delete_priv,Create_priv,Drop_priv)
    ->       values('localhost','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> insert into
    -> host(host,db,Select_priv,Insert_priv,Update_priv,
    -> Delete_priv,Create_priv,Drop_priv)
       values('devshed','mydb','Y', 'Y', 'Y', 'Y', 'Y', 'Y');

[see notes below for clarification by Monty]
The host table is used as an extension of the db table when you want a given db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table entry, then populate the host table with an entry for each of those hosts.

In other words; The host table is only useful if you have a network with many hosts and you want to have completely different permissions for different hosts and you don't want to add a lot of rows into the db table for every host+user combination.

The User table:
The 'user' table contains all host+user combinations which are allowed to enter the MySQL server. Basically, a host+user combination could be considered a unique identity, much like a fingerprint or an id number, which tells the server exactly who is, and who is not, allowed server access. Those listed within the user table are capable of entering, and everyone else is not. Simple enough.

The user table contains data relevant to the following information:

Columns contained within the 'user' table:
Host - From which host is the connection being made?
User - From which user?
Password - The password to make the connection?

Each of the following privileges are determined with a 'Y' or 'N' (Default 'N'):
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv - Allows user to watch scrolling list of commands being executed on the server.
File_priv - allows user to write files to the server

The last two commands should bring panic, fear, and alarm into the eyes of any security-minded administrator. A user granted the Process_priv would be able to watch commands as they are executed, simply be typing mysqladmin proc. For example, one with this privilege could watch as a user's (or even root's) password is being modified within the user table.

File_priv would grant the user permission to write files to the server itself. This is obviously not a good idea. But, this also allows a user to run sometimes necessary commands such as LOAD DATA INFILE. This is a command which quickly fills databases with a tab delimited textfile, such as one converted from an Excel database. Typing in the 20,000 records by hand would be the only alternative to using LOAD DATA INFILE. With that in mind, just be careful as to who is given permission to use these commands.

The db table:
The 'db' table contains which information pertaining to which table a host+user listed in the 'user' table is allowed to enter.

Columns contained within the 'db' table:
Host - Which host?
Db - Name of database?
User - Which user?

Again, the following are determined with a 'Y' or 'N' (Default 'N')
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv

We have up to this point covered the terminology and structure lying behind the privilege system. Let's put that knowledge into practice by running through a Real-Life Example.

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