Sign Up

SqlBak Blog

Guide to Setting Up Permissions for Database Backup and Restore

by
Last updated on July 16, 2024

In order to create database backups through SqlBak or SQLBackupAndFTP, the user specified when adding the connection must have sufficient privileges to perform the backup operation. Although you can use a superuser to create a backup without any problems, it is considered best practice to create a separate user specifically for this purpose.

Below are examples of SQL scripts for creating a backup user, as well as the necessary privileges for database recovery.

Please note that it is not necessary to grant recovery permissions immediately. This can be done when you need to restore the database.

SQL Server

This script creates a database login and user and grants them the right to perform the BACKUP DATABASE operation.

To use the script, replace the password with your own password and “MyDatabase” with the name of your database.

USE master;
GO

-- Create a login
CREATE LOGIN BackupUser WITH PASSWORD = 'StrongPassword';
GO

-- Create a user in the context of your database
USE MyDatabase;
GO
CREATE USER BackupUser FOR LOGIN BackupUser;
GO

-- Grant the BACKUP DATABASE permission to the user
GRANT BACKUP DATABASE TO BackupUser;
GO

To restore the database, you need to additionally grant the privilege to create a database:

-- Add the user to the dbcreator server role
ALTER SERVER ROLE dbcreator ADD MEMBER BackupUser;
GO

MySQL

This script creates a user and grants them privileges to backup all databases.

The script also grants privileges to view the list of databases.

-- Create a user account
CREATE USER 'backupUser'@'localhost' IDENTIFIED BY 'StrongPassword'; -- Replace 'StrongPassword' with your own password

-- Grant permission to view the list of databases
GRANT SHOW DATABASES ON *.* TO 'backupUser'@'localhost';
FLUSH PRIVILEGES;

-- Grant LOCK TABLES and SELECT permissions
GRANT LOCK TABLES, SELECT ON *.* TO 'backupUser'@'localhost';
FLUSH PRIVILEGES;

To restore an existing database, you need to grant full privileges for editing. However, if you restore a database under a new name, you don’t need to grant privileges, as the backupUser will become the owner of the database during its restoration and will have all the privileges to restore the data.

-- Grant permissions to create and modify databases and tables
GRANT CREATE, ALTER, DROP, INDEX, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON *.* TO 'backupUser'@'localhost';
FLUSH PRIVILEGES;

Backup a single database

-- Create a user account
CREATE USER 'backupUser'@'localhost' IDENTIFIED BY 'StrongPassword'; -- Replace 'StrongPassword' with your own password

-- Grant LOCK TABLES and SELECT permissions for a specific database (e.g., MyDatabase)
GRANT LOCK TABLES, SELECT ON MyDatabase.* TO 'backupUser'@'localhost'; -- Replace 'MyDatabase' with your actual database name
FLUSH PRIVILEGES;

Restore

To restore an existing database, you need to grant full privileges for editing. However, if you restore a database under a new name, you don’t need to grant privileges, as the backupUser will become the owner of the database during its restoration and will have all the privileges to restore the data.

-- Grant permissions to create and modify databases and tables
GRANT CREATE, ALTER, DROP, INDEX, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON MyDatabase.* TO 'backupUser'@'localhost'; -- Replace 'MyDatabase' with your actual database name
FLUSH PRIVILEGES;

Privileges for incremental backups

If you are performing incremental backups, you need to grant additional privileges:

-- Grant RELOAD and FLUSH_TABLES privileges to the user
GRANT EVENT, SUPER, REPLICATION, RELOAD, FLUSH_TABLES ON *.* TO 'backupUser'@'localhost';
FLUSH PRIVILEGES;

PostgreSQL

Create a user for PostgreSQL:

-- Replace 'StrongPassword' with your own password
CREATE USER backupUser WITH PASSWORD 'StrongPassword';

Next, switch to the desired database, replace ‘MyDatabase’ with your actual database name:

\c MyDatabase

And grant privileges to the user:

GRANT USAGE ON SCHEMA public TO backupUser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backupUser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backupUser;

GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backupUser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backupUser;

Restore

When restoring a database under a new name, you only need the CREATEDBprivilege:

\c postgres
ALTER USER backupUser CREATEDB;

However, if you plan to restore the database under its current name, you will need superuser privileges. This is because before restoring the database, you will need to delete the existing database. For this, you can use the following SQL script:

Create a separate user for restore operations:

-- Replace 'StrongPassword' with your own password
CREATE USER restoreUser WITH PASSWORD 'StrongPassword';

Then, grant superuserprivileges:

\c postgres
ALTER USER restoreUser WITH SUPERUSER;

 

Leave a Comment