Database Reference
In-Depth Information
Administrative User Accounts
Earlier, I mentioned that weneed to create three administrative accounts for Lena to use in
performing her duties as a database administrator from the localhost:
admin_backup
,
ad-
min_restore
, and
admin_import
. These are common administrative user accounts that you
may need to create and use. You'll use them in examples and exercises in
Chapter14
(which covers backing up and restoring), and
Chapter15
(importing data). In this section,
we'll create these administrative user accounts and look at the privileges needed for them,
as well as another one for granting privileges to other user accounts.
User Account for Making Backups
The
admin_backup
user account will beused with the
mysqldump
utility to make back-ups
few privileges are needed to accomplish these tasks:
▪ At a minimum, it will needthe
SELECT
privilege to read our two databases. You
should limit an administrative account to the databases it needs to backup. In par-
ticular, you should not let it have
SELECT
privileges for the
mysql
database, be-
cause that contains user passwords.
▪ To lock the tables when making a backup,the
LOCK TABLES
privilege is re-
quired.
▪ If a database contains views and triggers, which we didn't cover in this topic, the
user account will needthe
SHOW VIEW
and
TRIGGER
privileges, respectively.
Based on those considerations, let's create the
admin_backup@localhost
user account and
give it the
SELECT
and
LOCK TABLES
privileges, but only for the
rookery
and
bird-
watchers
databases. Do that by executing the following SQL statement:
CREATE
USER
'admin_backup'
@
'localhost'
IDENTIFIED
BY
'its_password_123'
;
GRANT SELECT
,
LOCK
TABLES
ON
rookery
.*
TO
'admin_backup'
@
'localhost'
;
GRANT SELECT
,
LOCK
TABLES
ON
birdwatchers
.*
TO
'admin_backup'
@
'localhost'
;
This allows Lena to use this
admin_restore
account to make backups of our databases.
We created another account for restoring data, so let's give that account the privileges it
needs.
