Database Reference
In-Depth Information
Revoking Privileges
So far in this chapterwe have been giving privileges to user accounts. But there may also
be times when you want to revoke a privilege that you gave to a user account. Maybe you
gave a privilege by mistake, or you've changed your mind about which tables you want the
user account to have access, or changed your policy about which tables you want to pro-
tect.
The
REVOKE
statementrevokes all or certain privileges that were granted to a user ac-
count. There are two forms of syntax to do this: one to revoke all privileges and another for
specific privileges. Let's look at examples for both syntaxes.
Suppose we have a user, Michael Stone, who is taking a leave of absence for a few months,
and there is no chance he will access the database while he's gone. We could delete his user
account, but instead we decide to revoke his user account privileges. We'll add them back
when he returns. To do this, we would enter something like this:
REVOKE ALL
PRIVILEGES
ON
rookery
.*
FROM
'michael_stone'
@
'localhost'
;
REVOKE ALL
PRIVILEGES
ON
birdwatchers
.*
FROM
'michael_stone'
@
'localhost'
;
The syntax is similar to the
GRANT
statement thatgrants all privileges. The main difference
is that instead of an
ON
clause, there's a
FROM
to revoke privileges from a user account. Al-
though Michael may have had privileges for only certain tables in the two databases, this
removes them all. We don't have to remove the specific privileges with multiple SQL state-
ments for each table. To give privileges again to the user account, though, we may have to
use the
GRANT
statement many times as we would for a new user account.
The second syntax can be used to revoke only some privileges. The specific privileges have
to be given in a comma-separated list after the keyword
REVOKE
. The privileges for
REVOKE
statement, or revoke privileges on all tables of a database by putting an asterisk in
as the table name. To revoke privileges for specific columns, list them within parentheses
in a comma-separated list — the same as with the
GRANT
statement. Let's look at an ex-
ample of this second syntax.
To keep security tight, suppose we have a policy of removing any privileges not needed by
user accounts. When we granted privileges to the
admin_restore@localhost
user account,
