Thursday, September 21, 2017

Migrating users to RDS or Aurora

I've mentioned this before what a pain it can be migrating databases that have DEFINERS in the triggers/stored procs/events to AWS. I'm doing migrations to Aurora now and the user permissions are also very annoying.

There are some privileges which are not allowed at all on Aurora and RDS:
Super,  Shutdown, File

The above three privileges will give this error:
Access denied for user '<user name>'@'%' (using password: YES)

If you are scripting out user migrations from MySQL to RDS and have users with "ALL PRIVILEGES ON *.*" then you need to remove that from the grant statements and replace it with allowed permissions.

These are allowed when granting global privileges on *.* with RDS:

Select, Insert, Update, Delete, Create, Drop, Reload, Process, References,  Index, Alter, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, Execute, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, Event, Trigger, WITH GRANT OPTION;

For example:

GRANT Select, Insert, Update, Delete, Create, Drop, Reload, Process, References,  Index, Alter, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, Execute, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, Event, Trigger  ON *.* TO 'test_user'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;

Note: Using "WITH GRANT OPTION" should only be give to administrators.

These are allowed when granting to <database>.* on RDS:

Select, Insert, Update, Delete, Create, Drop, References,  Index, Alter, CREATE TEMPORARY TABLES, LOCK TABLES, Execute,  CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, Event, Trigger, WITH GRANT OPTION

For example:

GRANT Select, Insert, Update, Delete, Create, Drop, References,  Index, Alter, CREATE TEMPORARY TABLES, LOCK TABLES, Execute,  CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, Event, Trigger  ON test.* TO 'test_user'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;

Note: Using "WITH GRANT OPTION" should only be give to administrators.

All of the following  GLOBAL PRIVILEGES will fail regardless of using RDS or normal MySQL if you try to grant them to a specific database (they can only be granted to *.*):

Process, SHOW DATABASES, CREATE USER, REPLICATION SLAVE, REPLICATION CLIENT, Reload

For example:

GRANT Process, SHOW DATABASES, CREATE USER, REPLICATION SLAVE, REPLICATION CLIENT, Reload ON test.* TO 'test_user'@'%' IDENTIFIED BY '12345678';

The error you will get is:
Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

There are also more permissions which you can grant to a user for AWS that I have not referenced here such as:

GRANT SELECT INTO S3 ON *.* TO 'test_user'@'%';

I am not covering those AWS specific permissions because those won't appear in your non RDS MySQL Servers because they are specific to RDS. But you would get an error if you try running that on a normal MySQL server.

No comments:

Post a Comment