MySQL Administration - A Real-Life example

(Page 3 of 4 )

Suppose we want to allow user 'dario' to access the server via hosts 'localhost' and 'www.devshed.com', which lies on the 'dv1' host. He wants to access the database 'pasta' strictly from 'localhost', but he wants to access the database 'chicken' from both hosts. Finally, he wants to use the password 'mamamia'.

Step 1: Set up the host table (assuming it has not yet been set up)
The host table is of considerable importance when administering larger networks, yet it needs to be configured for every server. Assuming you have just one server, you will have to insert just two hostnames, the localhost, and your server name. Otherwise, you will have to list each server that you would like to give access to the MySQL server.

$ mysql mysql
mysql> insert into
    -> host(host,db,Select_priv,Insert_priv,Update_priv,
    -> Delete_priv,Create_priv,Drop_priv)
    ->        values('localhost','%','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('dv1','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y');

Step 2: Update the 'user' table, for reason of granting access to a new host+user combination.

mysql> insert into user (host,user,password)
    ->        values('localhost','dario',password('mamamia'));
mysql> insert into user (host,user,password)
    ->       values('www.devshed.com','dario',password('mamamia'));

Step 3: Update the database (db) table.

mysql> insert into db
    ->       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->        Create_priv,Drop_priv)
    ->       values ('localhost','pasta','dario','Y','Y','Y','Y','Y','Y');
mysql> insert into db
    ->       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->        Create_priv,Drop_priv)
    ->       values ('%','chicken','dario','Y','Y','Y','Y','Y','Y');

Step 4: Create the necessary tables, using mysqladmin.
Exit from the MySQL server (q). Now, you must use a tool called mysqladmin to create the actual database.

$ mysqladmin -u root -p create pasta
enter password: *******
Database "pasta" created.

Don't forget to do the same thing for the 'chicken' database!

Step 5: Use mysqladmin again.
After all necessary modifications have been made, the command mysqladmin reload must be executed. If you do not execute this command, the changes will not take effect.

mysql> mysqladmin -u root -p reload;
enter password: *******

That's all there is to it. Dario, not working on the 'localhost' and entering the server via telnet, should be able to access the pasta and chicken databases. However, if Dario enters via host 'www.devshed.com', he will only be able to enter the chicken database. If he attempts to enter the pasta database, and error will occur.













Using MySQLAdmin
As noted in the above example, one uses the MySQLAdmin to carry out very important administrative tasks, such as finalizing modifications on the server, and creating databases. At the UNIX command line, try typing:

$ mysqladmin

A list of commands will scroll down the screen. These commands are carried out as the 'reload' or 'create databasename'. BE CAREFUL of these commands, as they are capable of erasing or shutting down the database server. However, be sure to study these commands carefully, as they are indispensable to running MySQL administration.

You now should be able to add (and thus modify and delete information from the privilege tables. Yet how do you enter these databases, and what are some security measures you can take to keep the 'bad guys' out? This is the subject of the next section.

Next: More Basics >>
 

More MySQL Articles
More By W.J. Gilmore

Comments On: MySQL Administration

>>> Be the FIRST to comment on this article!