How can you create multiple database users with AWS MariaDB RDS? Not using “grant all”, unfortunately.

AWS RDS lets you run a database with minimal operational overhead. Backups, maintenance and operating system upgrades are all taken care of by the service. However, the ease of RDS comes with some limits.

When you set up RDS (whether MySQL or MariaDB), a database root user is created for you. This is your root user, which has permissions for all databases you add on that RDS instance. That obviously isn’t the user your applications should connect as. You may have more than one application connect to the RDS instance, and different applications shouldn’t be able to access each other’s data. Even if you have only one application now, you may have more in the future, and the least privilege principle means that it is easier to start out with limited access and grant more as needed, rather than the other way around..
Unfortunately, if you are using MariaDB, you can’t create a new user and grant all permissions for a given database using typical syntax. (More about attempts to do this here.) If you wanted to set up a user called appdb that will have access to the appdb database, this won’t work (it will however, work with a MySQL RDS database):

grant all on appdb.*

You’ll get an error message:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'appdb' This is a confusing error message because as the root user, you can login to the database appdb and create tables, execute queries, etc. Because of the limitations of MariaDB RDS, you need to explicitly specify each and every permission for the new user. grant select,update,delete,insert,create,drop,index,alter,lock tables, execute, create temporary tables, execute, trigger, create view, show view, event on appdb.* to appdb@'%' ;

Of course, this creates what is essentially a root user for the appdb database. (These were permissions that we needed to allow for a drupal migration.) You may or may not want your application to connect with that level of permissions. You can always limit the permissions further, depending on your application needs. It’s always a good idea to follow the principle of least privilege. For instance:

• if an application is not going to be adding records to a database, the user it connects as doesn’t need “insert” privileges.
• if the application shouldn’t be able to write to an audit table, you could limit the tables it can insert to instead of allowing it to manage all the tables.
• if the application is connecting from a known address, you could limit the hosts from which a database user can connect, instead of wildcarding it. There are other ways to protect RDS instances from network access, including security groups, VPC settings and NACLs.

RDS is a powerful technology for limiting your operational concerns, but like any tool, comes with its own issues. Hopefully the grant statement above will help you set up your MariaDB RDS databases correctly.