using the machine with the specified IP address or a computer in the staff.jsmc.org domain. If you do
not want to limit the computers from which Scott can connect, you can allow him to connect from anywhere
using the wildcard character (%). You could also leave the hostname column blank, which is in effect the
same thing as the wild card. Another option you have here is to allow Scott to connect from anywhere within
the jsmc.org domain. To do this you could use the wild card character in the following way:
INSERT INTO user (Host, User, password)
VALUES("%.jsmc.org", "Scott",
PASSWORD("dinosaur"));
This allows Scott to connect from anywhere within the jsmc.org domain. One important point to
remember is that even though the % means from any host, it really means from any remote host. For a
user to log in to the local machine, meaning the machine on which the MySQL server is running, the
user must have a localhost entry. If the user does not, he or she cannot log on to the machine
locally.Adding Privileges
Now that you have added a user, it's time to give him some privileges. To allow Scott access to any of the
databases that are available within the MySQL RDBMS, you must give him an entry in the db table.
Remember that this table holds all the permissions that are relevant to each individual database. If you want
Scott to have SELECT access to the Meet_A_Geek database, you would make the following entry:
INSERT INTO db (Host, User, Db, Select_priv)
VALUES
("localhost","Scott","Meet_A_Geek","Y");
This statement would give Scott SELECT privileges to the Meet_A_Geek database only if he connected
to it from the local machine. He couldn’t access this database if he were to connect from another
computer or domain. This is an example of how MySQL can add security that no other database on the
market can give. The same rules about the wildcard character apply here as well.If Scott needed to access more than one database in your RDBMS, he would have to have more than
one entry in this table, giving him explicit permissions to each database.Removing Privileges
If you want to take away privileges from a user, you can use the UPDATE statement to carry out your plans.
For example, if you want to take away Scott’s SELECT privileges and give him UPDATE privileges instead,
you would issue the following command:
UPDATE db
SET Select_priv = "N", Update_priv="Y"
WHERE User = "Scott"
AND Host="localhost"
AND Db ="Meet_A_GeeK";
This takes Scott’s SELECT privilege away and gives him UPDATE privileges. Now that you have given
Scott’s permissions to the database, you’re done—right? Not exactly. When MySQL starts, it loads the
grant tables into memory to help speed things up. The only problem is that when you make changes to
the grant tables, you are not updating the tables that are stored in memory. So a user that you just
entered will not be able to connect to the database until the tables in memory are updated. To update
those tables you need to flush them. This is done with the FLUSH privileges statement. Of course, you
must have FLUSH or RELOAD privileges to issue this command. The other way to flush the grant tables
is to use the RELOAD option with the mysqladmin command. This takes about a second to do, and
then all the changes you have made are updated.Using the GRANT Command
If you are not comfortable with directly updating the grant tables, there is another way to administer user
privileges—with the GRANT command. The GRANT command has the following syntax:
GRANT [privileges] (column names)
ON databasename.tablename
TO username@hostname