These commands will delete Scott’s records from the user and db tables where the hostname is the
wildcard. Scott’s other records might still exist, so you will have to perform some manual cleanup. To
see if Scott has any other records, you could use the following statements:
SELECT FROM user WHERE User = "Scott" AND Host = "%";
SELECT FROM db WHERE User = "Scott" AND Host = "%";
You can revoke a user’s privileges in one of two ways. The first way is to edit the grant tables with
SQL statements. The other way is to use the REVOKE statement. The REVOKE statement is the
recommended way for removing someone’s privileges because there is less chance of error.
The REVOKE statement’s syntax is very similar to the GRANT statement’s syntax. The REVOKE
statement’s syntax is as follows:
REVOKE [privileges](columns)
ON databasename.tablename FROM username@hostname
The privileges that can be revoked are the same set of privileges that were discussed during the GRANT
statement. With the REVOKE statement, you can revoke some or all of the privileges a user has.
Everything else in the REVOKE statement must match the corresponding GRANT statement exactly. In
going over the parts of the REVOKE statement, everything should seem pretty familiar.
After the keyword REVOKE, you will list the privileges you are removing from the user. After the privilege
list are the optional column names that have privileges. After the column names is the keyword ON.
Following the keyword are the database and table from which you are removing the privileges. You can
also specify all databases and all tables with the *.* syntax. Following the database/table name is the
keyword FROM, followed by the user and his or her hostname. Look next at some examples.To remove Scott’s privileges entirely, you could use the following statement:
REVOKE ALL ON *.* FROM Scott@%;
This removes all the privileges Scott had in all databases that had the wildcard as the hostname. If Scott
were ever granted permission to log on to the local server, you would have to issue the following
REVOKE statement to remove those privileges:
REVOKE ALL ON *.* FROM Scott@localhost;
To remove specific privileges, you would use the REVOKE statement much like you would the GRANT
statement. For example, revoke Scott’s UPDATE privileges on the First_Name and Last_Name
columns in the Customers table. To do this, use the following command:
REVOKE UPDATE (First_Name, Last_Name)
ON Meet_A_Geek.Customers
FROM Scott@%.jsmc.org;
As you can see, the REVOKE statement is similar to the GRANT statement in syntax and use. The major
difference is that in one you are granting privileges and in the other you are taking them away.Taking Proper Security Measures
As the database administrator for the MySQL database, the security and integrity of the data is your
responsibility. This means that it is up to you to maintain an accurate database. The information contained in
the database is solely your responsibility. Everything rests in your hands. So when the data becomes
corrupted because some disgruntled employee broke in and deleted rows and rows of data, everyone is
going to be looking at you. Maybe the data you are in charge of is highly classified or confidential. It is your
responsibility that it remains that way. This is a lot of responsibility, but if you are using MySQL, rest assured
that you are taking the right steps to a very secure environment.
There are some additional steps you can take to provide a more secure environment. One of the first
steps is to never run the mysqld as the operating system root. The main reason is that anybody who
has FILE privileges in MySQL can access any file if the server is running as root. Instead of running
MySQL as root, run it as another user, such as mysqladmin or mysqlgroup. In this way, you are
assured that malicious users who are given root access will not overwrite system files.
The next step is to secure the data subdirectory in the mysql directory. All the files in this directory
should be owned by the user who runs the mysqld daemon. They should only be readable and
writeable by this user as well. This guarantees that no other user can access these files.
Another step you can take is to limit the % when defining hostnames. Specify an IP address whenever
possible. This helps tighten up security.