AWS RDS - Access denied to admin user when using GRANT ALL PRIVILEGES ON the_db.* TO 'the_user'@'%'

Question

When we try to GRANT ALL permissions to a user for a specific database, the admin (superuser) user of database receives the following error.

Access denied for user 'admin'@'%' to database 'the_Db'

After looking other questions in stackoverflow I could not find the solution. I already tried to change * -> % without success, that is the approach suggested in the following source:

http://www.fidian.com/problems-only-tyler-has/using-grant-all-with-amazons-mysql-rds

I think there is an underlying configuration on RDS so I can't grant all permissions for the users, but I don't know how to detect what is happening.

Update

After doing some workarounds I noticed that the "Delete versioning rows" permissions is the one that causes the problem. I can add all permissions but that one.

https://mariadb.com/kb/en/grant/

So the only "way" I could grant other permissions was to specific each one of those with a script like this.

GRANT Alter ON *.* TO 'user_some_app'@'%';
GRANT Create ON *.* TO 'user_some_app'@'%';  
GRANT Create view ON *.* TO 'user_some_app'@'%';
GRANT Delete ON *.* TO 'user_some_app'@'%';
GRANT Drop ON *.* TO 'user_some_app'@'%';
GRANT Grant option ON *.* TO 'user_some_app'@'%';
GRANT Index ON *.* TO 'user_some_app'@'%';
GRANT Insert ON *.* TO 'user_some_app'@'%';
GRANT References ON *.* TO 'user_some_app'@'%';
GRANT Select ON *.* TO 'user_some_app'@'%';
GRANT Show view ON *.* TO 'user_some_app'@'%';
GRANT Trigger ON *.* TO 'user_some_app'@'%';
GRANT Update ON *.* TO 'user_some_app'@'%';
GRANT Alter routine ON *.* TO 'user_some_app'@'%';
GRANT Create routine ON *.* TO 'user_some_app'@'%';
GRANT Create temporary tables ON *.* TO 'user_some_app'@'%';
GRANT Execute ON *.* TO 'user_some_app'@'%';
GRANT Lock tables ON *.* TO 'user_some_app'@'%';

Answer 1

Try this

mysql -h your-rds-host-name -P 3306 -u rds-master-user -p

CREATE DATABASE sitedb;

CREATE USER 'siteuser'@'%' IDENTIFIED BY 'Password';

// For MySQL 5.7 or Less

GRANT ALL ON sitedb.* TO 'siteuser'@'%' IDENTIFIED BY 'Password' WITH GRANT OPTION;

// MariaDB 10 Up

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `sitedb`.* TO 'siteuser'@'%';
FLUSH PRIVILEGES;
EXIT