How To Create a MySql User and Assign Permissions in AWS RDS on Ubuntu Linux

How can I create a new MySQL user and assign permissions in the AWS RDS cloud service from the Linux command line?

MySQL is a free open-source database. Many sites on the Internet use MySQL along with Python, Perl, PHP, and other server-side programming languages. AWS offers a managed MySQL service with high availability options, including backups, restores, and patches. This short guide explains how to create MySQL user accounts and assign privileges on AWS RDS using Linux command lines.

Prerequisite

You need an AWS account along with MySQL/MariaDB RDS and a running instance of EC2/Lightsail. I also assume you have a MySQL client installed on your EC2/Lightsail Linux server. For example, we can install the MySQL client as follows.

Log in first with the ssh command

ssh -i ~/.ssh/EC2-keypem.pem ec2-user@ec2-54-161-60-164.compute-1.amazonaws.com

Next, install the MySQL client according to the Linux distro

sudo apt install mysql-client

Test AWS RDS connectivity with CLI, now we can connect to AWS RDS MySQL server using MySQL command

mysql -u {USER_NAME} -h {AWS_RDS_HOST_NAME} -P {MYSQL_PORT} -p

For example

mysql -u masteruser -h mysql–instance1.134342.us-west-1.rds.amazonaws.com -P 3306 -p

How To Create Mysql User Accounts and Assign Privileges

I will first log in as a dbmasteruser user:

mysql -u dbmasteruser -h ls-gdgdg6585684767gdgjdg.eetg96lp.us-east-1.rds.amazonaws.com -P 3306 -p

Step 1: Create a new database

In most cases, you need to create a new database. However, skip this step if you have existing MySQL databases on RDS. Let’s create a new MySQL database called a blog

CREATE DATABASE blog;

Step 2: Create a new MySQL account on AWS RDS

I will create a new user named goran for a database called goran_blog

CREATE USER 'goran'@'%' IDENTIFIED BY 'my_Super_Secret_Password';

Furthermore, SSL connections for specific user accounts can be forced as follows when creating a new user for security reasons

CREATE USER 'goran'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

Any client/server user can connect to AWS RDS when part of the client host name is set to ‘%’. Therefore, we can replace ‘%’ with the actual IP address of the ECC/Lightsail server or VPC subnet for greater security. For example

CREATE USER 'goran'@'172.26.9.11' IDENTIFIED BY 'password';
CREATE USER 'goran'@'172.26.0.0/255.255.240.0' IDENTIFIED BY 'passwd';

We can force an SSL connection for an existing MySQL account as follows

ALTER USER 'user_name'@'client_ip' REQUIRE SSL;
ALTER USER 'goran'@'%' REQUIRE SSL;

By default, the following privileges are granted in the AWS RDS account. Let’s run the SHOW GRANTS SQL command for a user named goran:

SHOW GRANTS for userName;
SHOW GRANTS for goran;

Step 3: Grant privileges to the MySQL account

However, a MySQL account is set up with minimal or no database privileges. Here is a list of standard privileges:

USAGE data privileges include: SELECT, INSERT, UPDATE, DELETE, and FILE

Structure privileges include: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER

Administrator privileges include: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER

SSL privileges include: REQUIRE NONE, REQUIRE SSL, REQUIRE X509

ALL PRIVILEGES: Shortcut for assigning all MySQL privileges to the user account.

The syntax is as follows to assign different user permissions

GRANT permission ON DB_NAME.TABLE_NAME TO 'userNameHere'@'client_ip';
GRANT permission1,permission2 ON DB.TABLE TO 'userNameHere'@'client_vpc_sub_net';

For example

GRANT SELECT, INSERT, UPDATE, DELETE ON blog.* TO 'goran'@'%';

Here is another example for vpc sub/net

GRANT SELECT, INSERT, UPDATE, DELETE ON blog.* TO 'goran'@'172.26.0.0/255.255.240.0';

In this example, assign various structure privileges

GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `blog`.* TO 'goran'@'%';

Of course, we can also grant ALL PRIVILEGES as follows for blog database for user goran:

GRANT ALL PRIVILEGES ON `blog`.* TO 'goran'@'%';

Let’s say you need to create a new database called salesstats with a user named ivan and grant all PRIVILEGES:

CREATE DATABASE salesstats;
CREATE USER 'ivan'@'%' IDENTIFIED BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON `salesstats`.* TO 'ivan'@'%';
SHOW GRANTS for ivan;
SHOW GRANTS for 'ivan'@'%';

Note that you must use the SQL FLUSH PRIVILEGES statement; only when you directly modify assignment tables using statements such as INSERT, UPDATE, or DELETE:

FLUSH PRIVILEGES;

Step 4: Test

Simply run the following command from the second instance of EC2:

mysql -u sai -h mysql–instance1.134342.us-west-1.rds.amazonaws.com -P 3306 -p salesstats

Encrypted connection to RDS instance

First, download the certificate package that contains both the middle and root certificates using the wget command:

wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

Now connect as follows:

mysql -h mysql–instance1.134342.us-west-1.rds.amazonaws.com --ssl-ca=rds-combined-ca-bundle.pem --ssl-mode=VERIFY_IDENTITY -u ivan -P 3306 -p salesstats

Now, the rest of the guide explains how to reverse all the steps. In other words, you will learn how to remove a database, user, and grant/permission using MySQL CLI.

Step 5: Revoke privileges from the AWS RDS MySQL account

The REVOKE SQL statement allows sysadmins to revoke privileges and roles. The syntax is

REVOKE ALL PRIVILEGE1,PRIVILEGE2 ON database.* FROM 'user'@'client_ip';
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'vpc_sub_net';
REVOKE INSERT, DELETE ON `salesstats`.* FROM 'ivan'@'%';
REVOKE ALL PRIVILEGES ON `salesstats`.* FROM 'ivan'@'%';

Step 6: Delete the AWS RDS MySQL account

To remove an AWS RDS MySQL account, use the DROP SQL statement as follows:

DROP USER 'user'@'client_ip';
DROP USER 'sai'@'%';

Step 7: Remove the existing AWS RDS MySQL database

The SQL statement DROP DATABASE deletes all tables in the database and removes the database. Therefore, be very careful with this statement:

DROP DATABASE db_name_here;
DROP DATABASE salesstats;

Confirm:

SHOW DATABASES;

Note that when a database is ejected from RDS, privileges granted specifically for the database will not be automatically dropped. They must be dropped manually as described above.

Conclusion

In this guide, you learned how to create new MySQL users on AWS RDS using the Linux or Unix MySQL command line.