Create a user with Admin Privileges in AWS RDS MySQL/MariaDB.

AWS RDS MySQL/MariaDB Admin user Creation Error
This will be a short article on creating a user and assigning him full privileges in Mysql/MariaDB Database Engine. Before moving ahead I would also like to point out for the case of AWS RDS MySQL/MariaDB instance we have to explicitly define all the permission in order to make that particular user an admin user. Ideally, In order to create an admin database user, we can simply use ALL PRIVILEGES but unfortunately, it does not work for the case of AWS RDS Instances.
Let’s have a look at it.

Login to the RDS MySQL Database and Run below command to create a User

mysql> CREATE USER 'devops_user'@'%' IDENTIFIED BY 'devops_password';

We can see the User is created with the defined password. Now Let’s try adding the Admin privileges to the user we have created. Normally the command should work as shown below but It fails to execute in  RDS Mysql Database with an error “ERROR 1045 (28000): Access denied for user ‘admin’@’%’ (using password: YES)”.

To grant all privileges to the user, 

mysql> GRANT ALL PRIVILEGES ON . TO 'devops_user'@'%';

It Fails as shown in the screenshot below,

So Just to fix this issue, We have to Explicitly define all the permission which is shown in the command below.

mysql> 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 devops_user@'%' with grant option;

Just Reload the Grant Tables,

 mysql> FLUSH PRIVILEGES; 

Now, Let’s try to connect to the database using the new admin user which we have created.

root@devopsage:~# mysql -h test-db.c4smsi3zs1gt61hm.ap-southeast-1.rds.amazonaws.com -u devops_user -p

So we can see that we are able to login to the the database using the new user which we have created. 

Additional
Let’s also cover how we can create a admin user in Oracle database.

create user admin identified by admin_password default tablespace users temporary tablespace temp;

grant connect , resource, DBA to admin;

---

If you Like Our Content here at Devopsage, then please support us by sharing this post.

Please Like and follow us at, LinkedIn, Facebook, Twitter, and GitHub

Also, Please comment on the post with your views and let us know if any changes need to be done.

Thanks!!