Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Now give Scott UPDATE privileges only on the First_Name and Last_Name columns of the

Customers table. To do this, issue the following command:

GRANT UPDATE (First_Name, Last_Name) ON Meet_A_Geek.Customers

TO Scott@localhost IDENTIFIED BY "dinosaur";

This command limits Scott to updating the First_Name and Last_Name columns in the Customers

table in the Meet_A_Geek database. As you can see, using this command is an easy way to maintain

your user’s accounts.

There is yet another way to grant permissions to users in MySQL. This command takes you out of the

MySQL Monitor and into the mysql directory. In the bin subdirectory (where all the MySQL utilities are

stored) you will find the mysql_setpermission utility. This is a script written in Perl that will walk you

through setting permissions for your database.

To begin using this utility, you must be in the mysql installation directory and the mysqld daemon must

be up and running. The first step is to start the utility. To do this, type the following from the command

line:

/usr/local/mysql> bin/mysql_setpermission

You should see the following output:

>Password for user to connect to MySQL:

After you have entered your root password successfully, you should see a welcome screen followed

by the following menu options:

Set password for user.

Add a database + user privilege for that database—User can do all except all admin functions.

Add user privilege for an existing database—User can do all except admin functions.

Add user privilege for an existing database—User can do all except admin functions + no create/drop.

Add user privilege for an existing database—User can do only selects (no update, delete, insert, and so

on).

Exit this program.

As you can see, this utility is fairly limited in what it can do for you. But if you need something that is

quick and easy, you can use this. Using Scott again, add him to an existing database by choosing

option 3.

When you choose option 3, you will be shown a list of databases from which to choose. Select the

Meet_A_Geek database. The program will then prompt you for a username. Type in Scott. The

program will then ask if you would like to create a password for Scott. Type Y. It is not recommended

that you have users without passwords. This could potentially compromise your database. After you

type Y, you are prompted for the password. Enter the word dinosaur or whatever you want. After you

have entered the password, you will prompted to re-enter it. After you have completed that step, you will

be asked for a hostname. You can supply a hostname, a wildcard, or any combination. Enter %. The

utility will then ask you if you want to enter another hostname under this username. For now, answer

NO. The program will then review what you have selected and prompt you before it makes any changes.

If everything looks correct, accept your entries by typing Y. You have now created a user named Scott,

with all the privileges except administrative privileges for the Meet_A_Geek database, who can connect

from anywhere. This utility can save you a lot of time and thinking when you need to add somebody

quickly.

Now that you know how to add a user, what happens if you want to get rid of him or her?

Removing Users and Revoking Privileges

You have read extensively about adding users and privileges. Now it's time to talk about removing users and
revoking privileges. MySQL offers you only one way to remove a user. You must edit the grant tables
manually. You issue a DELETE statement against every table in the MySQL database. To do this correctly
and safely, you must specify the user and hostname in the WHERE clause. For example, you would use the
following commands to remove Scott from the grant tables:

DELETE FROM user WHERE User = "Scott" AND Host = "%";

DELETE FROM db WHERE User = "Scott" AND Host = "%";