Database Reference
In-Depth Information
tenance. Besides, this task might be given to someone who we don't want to have com-
plete control over our database system.
To create a user account with the ability to create other user accounts and grant those other
user accounts privileges, the
GRANT
statement has to includethe
GRANT OPTION
clause. This clause allows the user to grant the same privileges it has to other users — but
only the precise privileges granted in this
GRANT
statement. If we limit the privileges in
the
GRANT
statement to our two databases, the user account cannot grant privileges to
other databases. For instance, execute the following on your server to create this user ac-
count and give it the
GRANT OPTION
for our two databases:
GRANT ALL
PRIVILEGES
ON
rookery
.*
TO
'admin_granter'
@
'localhost'
IDENTIFIED
BY
'avocet_123'
WITH GRANT OPTION
;
GRANT ALL
PRIVILEGES
ON
birdwatchers
.*
TO
'admin_granter'
@
'localhost'
IDENTIFIED
BY
'avocet_123'
WITH GRANT OPTION
;
This creates the
admin_granter@localhost
user account, which has the privilege of grant-
ing privileges on the
rookery
and
birdwatchers
databases to other user accounts.
This user account's privileges are still fairly limited if we want it to be used to manage
other user accounts. Suppose we want this user account to create and drop user accounts
for our databases. To do that, we need to grantthe
CREATE USER
privilege globally to
admin_granter@localhost
. So that this user account can executethe
SHOW GRANTS
statement, it will also needthe
SELECT
privilege on the
mysql
database. This is another
security risk, so be careful who gets this privilege. Enter these two SQL statements to give
this user account these two additional privileges:
GRANT CREATE
USER
ON
*.*
TO
'admin_granter'
@
'localhost'
;
GRANT SELECT ON
mysql
.*
TO
'admin_granter'
@
'localhost'
;
Now the
admin_granter@localhost
user account has the privileges to perform its tasks of
managing user accounts on our databases. Let's test it by entering the first line in the fol-
lowing example from the command line to log into MySQL, then the following SQL
statements from within the
mysql
client:
