Home arrow MySQL arrow Page 3 - MySQL Administration

A Real-Life example - MySQL

MySQL Administration correctly administering the MySQL server, including the manipulation the privilege system, and making intelligent decisions about the capabilities (permissions) given to users.

  1. MySQL Administration
  2. The Privilege System
  3. A Real-Life example
  4. More Basics
By: W.J. Gilmore
Rating: starstarstarstarstar / 21
March 30, 1999

print this article


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.

FAQ: Why do I have to enter "password('mamamia')" instead of just "mamamia" into the password variable of the 'user' table?
This is because MySQL, like any security-minded server, stores the password encrypted. Thus, to allow the user to continue to use the password 'mamamia', it must be entered as above.

FAQ: Previously you stated that there were 10 privileges within the user table, but in the example you only listed 6. Why?
This is because the default of every privilege is 'N'. Thus, if it is not listed within the insert command, it is considered to be 'NO' ('N').

FAQ: What happens if I leave host or db empty?
Both 'host' and 'db' can handle wildcards. Thus, if one or the other are left empty (''), it will be entered as '%'. This is obviously dangerous, unless you really know what you are doing. Therefore, be especially careful when entering data into these tables.

FAQ:What if I want to delete the inserted user and db info?
Easy. Just follow the above instructions, except using the delete syntax instead of the insert. Finally, use mysqladmin to delete the database. Don't forget to execute 'mysqladmin reload' after you're done.

FAQ: Isn't there an easier way to do this?
Believe it or not, yes. There are a number of programs included along with the MySQL distribution (within the contrib directory), including xmysqladmin, mysql_webadmin, mysqladmin and even xmysql to modify values within the privilege tables..
For more tips you may also read our article on MySQL Grant Tables

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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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