Open Source

Setting up privileges

MySQL databases can be a nightmare to administer. Fortunately, Linux has xmysqladmin to simplify this task. Larry Mintz is here to help you to understand this dream of a tool.


In this Daily Feature, I’ll show you how to use xmysqladmin, a graphical front end to MySQL. You can create, edit, and set up tables and databases with xmysqladmin. In effect, you can do the basic work of database administration without the hassle of using a command-line interface. Go to rpmfind to download the latest version of xmysqladmin.

Setup
In this Daily Feature, I’ll assume you have just downloaded MySQL and MySQL-client and have installed them. So how do we get the ball rolling? First, log in as root. Next, open a terminal window and type at the prompt:
mysqld &

Now you need to create the password for root. To do so, type at the prompt:
mysqladmin -u root password newpassword

At this point, we’ll create the privileges so users can access the various databases. Let’s say we’re a running a cigar store for Linux hackers Tony, Tiny, and Pong. We’ll set this up as a user-only database for a special clientele, so you will allow only certain people access to it (including yourself). The privileges we’ll create will allow general access to the MySQL database server.

Type at the prompt:
mysql -u root mysql

Now we are into MySQL. You should see the following prompt:
mysql>

Next, we’ll create the privileges for root and our users. We’ll use the GRANT statement to set up permissions, as shown here:
mysql —user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost.com
  IDENTIFIED BY admin WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%"
  IDENTIFIED BY admin WITH GRANT OPTION;
mysql> GRANT RELOAD,UPDATE,MODIFY,PROCESS,SELECT ON cigar TO tom@localhost.com
mysql> GRANT RELOAD,SELECT,UPDATE,MODIFY,PROCESS ON cigar TO tiny@localhost.com
mysql>GRANT RELOAD,UPDATE,MODIFY,PROCESS,SELECT ON cigar TO pongo@localhost.com

That’s all there is to it. MySQL is now ready to go.

Now, open MySQL (using the command mysql) and type:
mysql - uroot password
mysql> create cigars mysql>exit


Using xmysqladmin
To use xmysqladmin, you need XForms 0.88 or later. So, at any X terminal type
xmysqladmin

at the prompt. Once the xmysqladmin window opens, type the following in the xmysqladmin Connect window:
Host: localhost
User: root
Password: your password


Then, click the Try Connection button. If everything goes well, you should see the following message:
  Connection  Result
———————————————-
| Connection Successful ! |
———————————————-


Setting permissions
Now, we’ll set the permissions for the Cigars database from xmysqladmin. Begin by left-clicking the Grants button. Once the new window appears, highlight the Cubans database in the Hosts window. You know it will be highlighted when a clear yellow bar appears over it.

Next, click the Modify button. In the Modify window, specify that the Cubans database be fully functional and click the Save button. In the Users For The Current Host window, highlight root and click the Modify button.

In the resulting window, you’ll see:
Host name:——————-
User name:——————
Password:
Retype password:


If you are the administrator for this database, make root fully functional. If you aren’t, click the Add button and fill in the following:
Host name:————-
User name: admin
Password: xxxxxx
Retype:


Now, make admin fully functional.

Next, create each of the users—Tony, Tiny, and Pong—in a similar fashion but give the following permissions:
  • SELECT
  • INSERT
  • UPDATE
  • PROCESS
  • RELOAD

Creating the tables
At this point, click the Create Table button. Then, in the Select A Database list box adjacent to the label Database, select the Cigars database. In the Table Name field, enter Cubans and press [Enter].

We will enter the following fields:
  • BRAND—This field displays the factory (for example, Partagas or Cohiba).
  • MODEL—This field contains the types of cigars each factory produces.
  • SIZE—Some examples of entries in the SIZE field are petit corona, corona, corona grande, corona extra, robusto, royal robusto, and churchill.
  • PRICE—This field contains the price.
  • STRENGTH—This field displays the strength of the cigar; entries include full, medium mild, and so forth.
  • FLAVOR—This field displays the flavor; for example, full, medium, or mild.
  • DATE1—This field displays how long the cigar has been in stores.
  • DATE2—This field tells you when the product was shipped from the factory.

So the fields for the Cigars database (and their specifications) will look like:
BRAND: CHAR[20]
MODEL: CHAR[20]
SIZE: CHAR[40]
PRICE: FLOAT
STRENGTH: CHAR[5]
FLAVOR: CHAR[5]
DATE1: DATE
DATE2: DATE


Here are the steps for completing the fields using xmysqladmin:
  • Click in the Type field list box. You’ll see a large list box with different fields.
  • Select the field CHAR(1<=num<=255) and right-click.
  • Enter the name BRAND in the edit field below the Type field and click the Add button.
  • Repeat these steps with fields MODEL, SIZE, STRENGTH, and FLAVOR.
  • Return to the list box and this time click the FLOAT field.
  • Type the name PRICE in the NAME field.
  • Go to the Length and Decimals edit bars. We want a number like this: $xxxx.xx. That means our total length will be six digits, including decimal places. So, enter 4 in the number field and 2 in the Decimal field.
  • Go back to the Type list box and select the DATE field. Select the 4-byte version.
  • Type the name DATE1 in the NAME field.
  • Repeat those steps for DATE2.

Now, we’ll create the indexes for the Cigars database. Go to the Key section of the Create Table menu. We will create both a primary key and a multi index. We’ll use BRAND as the primary key. Leave the key size at 18. In the Key Name field, enter BRAND and click the Modify button. You should see the following:
BRAND|char(20)|NULL|primary|  |key name:BRAND

This indicates that we have created a primary key successfully. Finally, we’ll create a compound key. Left-click the Compound Key Mode button. Then, in the Key Name edit field, enter FLAVOR and click the Add button. The name of our compound key is now in the database. The key type is Multi.

Next, left-click the Select A Column button, select the FLAVOR column, and click the Add button adjacent to that column. That column name will now appear in the Parts Of The Compound Key List window. Select MODEL and PRICE from the Column Name list box as well.

At this point, add the compound key name STRENGTH. Choose the Key Type Multi. Select STRENGTH, MODEL, and FLAVOR from the Column Name list box. So, we have two compound keys and one primary key. This should be enough.

How to browse
Browsing is easy. Begin by clicking the Browse button. Then, left-click in the Select A Database list box. You’ll see the following:
—————————
|Select a database|
—————————-
|  mysql  |
|  cigars  |
|————————-|


From the Select Database drop-down list box, choose Cigars. Then, from the Select A Table list box, choose Cubans.

In the Select Columns To Display window, you should see the following columns:
  • BRAND
  • MODEL
  • SIZE
  • PRICE
  • STRENGTH
  • FLAVOR
  • DATE1
  • DATE2

By highlighting any column, you can search, delete, and add entries to the database. When you click the Add button, a dialog box will appear displaying the BRAND, MODEL, and other entry fields.

Wrap it up
I hope you enjoy using xmysqladmin as much as I have. My aim was to show you how to get MySQL up quickly and demonstrate how to set up and administer a database using xmysqladmin.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks