User Accounts and Privileges - Learning MySQL and MariaDB - page 315

User Account for Restoring Backups
Although you could create oneadministrative user account for both making backups and
restoring them, you might want to use separate user accounts for those tasks. The main
reason is that the task of making backups is usually one handled by scripts that run auto-
matically. But the task of restoring data is generally run manually and can overwrite or
destroy data on a live server. You might not want the user account with those privileges to
be the same one for which you use in a script containing its password. For our examples
in this chapter, let's give the admin_restore@localhost user account the privileges needed
for restoring data to our databases:
▪ At a minimum, a user account for restoring a dump fileneeds the INSERT priv-
ilege to insert data into tables.
▪ It should also havethe LOCK TABLES privilege to lock the tables while insert-
ing data.
▪ It will need the CREATE privilegeto create tables and INDEX to create indexes.
▪ Because a dump file can include SQL statements to alter tables to set the colla-
tion,the ALTER privilege may be needed.
▪ Depending on the method Lena uses torestore tables, she might also want to re-
store them to temporary tables. For that, she will need the CREATE TEMPORARY
TABLES privilege. Temporary tables are dropped when the client connection is
closed.
▪ If a database has views and triggers,the CREATE VIEW and TRIGGER priv-
ileges are required.
For our database usage, we won't need CREATE VIEW or TRIGGER , but we will need
the other privileges. Create the admin_restore@localhost user account and give it the ne-
cessary privileges by entering the following on your server:
CREATE USER 'admin_restore' @ 'localhost'
IDENTIFIED BY 'different_pwd_456' ;
GRANT INSERT , LOCK TABLES , CREATE ,
CREATE TEMPORARY TABLES , INDEX , ALTER
ON rookery .*
TO 'admin_restore' @ 'localhost' ;
GRANT INSERT , LOCK TABLES , CREATE ,
CREATE TEMPORARY TABLES , INDEX , ALTER
ON birdwatchers .*
TO 'admin_restore' @ 'localhost' ;