Database Privileges

This page outlines the necessary database privileges for encryption and migration, the minimum required privileges for users, and a troubleshooting section.

• Database privileges for encryption and migration
• Minimum required database privileges
• Troubleshooting unsupported database features or operations

Database privileges for encryption and migration

In order to carry out encryption and migration, Baffle Shield requires certain user permissions on the database. It is recommended that you create a new user on your database for Baffle Shield to use, rather than assign your database administrator. 

Use your SQL client to issue the following grants.

To create a user, issue the following commands:

1. create user '<baffle user>'@'%';
2. set password for '<baffle user>' = password('<password>');

To grant the requisite permissions, issue the following commands:

1. GRANT USAGE ON *.* TO '<baffle user>'@'%';
2. GRANT ALL PRIVILEGES ON shadow_information_schema.* TO '<baffle user>'@'%';
3. GRANT ALL PRIVILEGES ON <target database>.* TO '<baffle user>'@'%' WITH GRANT OPTION;
   
   Repeat step 3 for each database you wish to encrypt. When completed, Baffle Shield has the necessary permissions in order to carry out encryption and migration. Use the credentials of the user specified here.
   

Minimum required database privileges

These are the minimum required grants for users on your database who need the least access privileges. Use your SQL client to issue the following commands with your admin user. These grants permit the restricted-access user to obtain only the data you specify.

For MySQL and Aurora databases, issue the following commands:

1. GRANT USAGE ON *.* TO '<username>'@'%';
2. GRANT ALL PRIVILEGES ON shadow_information_schema.* TO '<username>'@'%';
3. GRANT SELECT ON <target database>.<target table> TO '<username>'@'%';
4. Repeat step 3 for each table you wish to make accessible to the user. When completed, you may connect to the Baffle Shield proxy with this user.
5. To confirm user privileges, use: show grants;

OPTIONAL: For databases that require additional information from the user, take the hash of the user’s password with the following:

1. SELECT PASSWORD ('<user password>');
   Insert the hash back into the expressions:
2. GRANT USAGE ON *.* TO '<username>'@'%' IDENTIFIED BY PASSWORD '<password hash>';
3. GRANT ALL PRIVILEGES ON shadow_information_schema.* TO '<username>'@'%' IDENTIFIED BY PASSWORD '<password hash>';
4. GRANT SELECT ON <target database>.<target table> TO '<username>'@'%' IDENTIFIED BY PASSWORD '<password hash>';
5. Repeat step 4 for each table you have selected.

Troubleshooting unsupported database features or operations

Different Baffle encryption modes have different levels of support for operations on encrypted data. In many cases, errors might be due to use of unsupported operations for the current mode of encryption: