How to create MySQL user and grant permissions in AWS RDS

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

MySQL is a free and open-source database. Many websites on the Internet use MySQL along with Python, Perl, PHP, and other server-side programming languages. AWS offers managed MySQL service with high availability options, including backups, restores, and patching. This quick tutorial explains how to create MySQL user accounts and grant privileges on AWS RDS using Linux command-line options.
Tutorial requirements
Operating system/appAWS RDS (MySQL or MariaDB instance)
Root privileges requiredYes
DifficultyEasy (rss)
Estimated completion time5m
Table of contents

ADVERTISEMENTS

Prerequisite

The following commands work with both MariaDB and MySQL RDS instance.

Obviously, you need an AWS account along with MySQL/MariaDB RDS and EC2/Lightsail instance running. I am also assuming that you have mysql client installed on your EC2/Lightsail Linux server. For example, we can install the mysql client as follows. First, log in using 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 as per Linux distro.

Debian/Ubuntu Linux

$ sudo apt install mysql-client
Installing mysql client on EC2 or Lightsail Linux cloud server

CentOS/RHEL

$ sudo yum install mysql

Fedora Linux

$ sudo dnf install mysql

Test AWS RDS connectivity from the CLI

Now we can connect to the AWS RDS mysql server using the mysql command:
$ mysql -u {USER_NAME} -h {AWS_RDS_HOST_NAME} -P {MYSQL_PORT} -p
For instance:
$ mysql -u masteruser -h mysql–instance1.134342.us-west-1.rds.amazonaws.com -P 3306 -p

How to create MySQL users accounts and grant privileges

First I am going to log in as ‘dbmasteruser’ user:
$ mysql -u dbmasteruser -h ls-gdgdg6585684767gdgjdg.eetg96lp.us-east-1.rds.amazonaws.com -P 3306 -p
Connect and Create a New User and Grant Permissions in MySQL AWS RDS

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 us create a new MySQL database called blog:
mysql> CREATE DATABASE blog;
Query OK, 1 row affected (0.00 sec)

Step 2: Create a new MySQL user account on AWS RDS

I am going to create a new user named ‘vivek‘ for our database called nixcraft_blog as follows:
mysql> CREATE USER 'vivek'@'%' IDENTIFIED BY 'my_Super_Secret_Password';
Query OK, 0 rows affected (0.01 sec)

Further we can force SSL connections for specific users accounts as follows while creating a new user for security reasons:
mysql> CREATE USER 'tom'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

Any client/server users can connect to AWS RDS when the client hostname part set to ‘%‘. Hence, we can replace ‘%‘ with the actual EC2/Lightsail server’s IP address or VPC subnet for better security. For example:
## EC2/Lightsail server IP address/client ##
mysql> CREATE USER 'vivek'@'172.26.9.11' IDENTIFIED BY 'password';
## VPC sub/net client example for 172.26.0.0/20 ##
mysql> CREATE USER 'vivek'@'172.26.0.0/255.255.240.0' IDENTIFIED BY 'passwd';

We can force SSL connection for an existing MySQL user account as follows:
mysql> ALTER USER 'user_name'@'client_ip' REQUIRE SSL;
mysql> ALTER USER 'vivek'@'%' REQUIRE SSL;

By default following privileges are granted on AWS RDS user account. Let us run the SHOW GRANTS SQL command for user named ‘vivek’:
mysql> SHOW GRANTS for userName;
mysql> SHOW GRANTS for vivek;

+-----------------------------------+
| Grants for vivek@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `vivek`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

Step 3: Grant privileges to a MySQL user account

So far, so good. However, the mysql account set up with minimum or zero (no) database privileges. Here is a list of standard privileges:

  1. Data USAGE privileges includes: SELECT, INSERT, UPDATE, DELETE, and FILE
  2. Structure privileges includes: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER
  3. Administration privileges includes: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER
  4. SSL privileges includes: REQUIRE NONE, REQUIRE SSL, REQUIRE X509
  5. ALL PRIVILEGES: Shortcut to grants all privileges to a mysql user account.

The syntax is as follows to grant 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:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON blog.* TO 'vivek'@'%';
Here is another example for vpc sub/net:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON blog.* TO 'vivek'@'172.26.0.0/255.255.240.0';
In this example, grant various structure privileges:
mysql> GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `blog`.* TO 'vivek'@'%';
Of course we can grant ALL PRIVILEGES too as follows for the `blog` database for ‘vivek’ user:
mysql> GRANT ALL PRIVILEGES ON `blog`.* TO 'vivek'@'%';
Let us say you need to create a new database named ‘salesstats‘ with user named ‘sai‘ and grant all PRIVILEGES:
mysql> CREATE DATABASE salesstats;
mysql> CREATE USER 'sai'@'%' IDENTIFIED BY 'PASSWORD_HERE';
mysql> GRANT ALL PRIVILEGES ON `salesstats`.* TO 'sai'@'%';
mysql> SHOW GRANTS for sai;
mysql> SHOW GRANTS for 'sai'@'%';

Please note that you need to use the FLUSH PRIVILEGES SQL statement; only when you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE:
mysql> FLUSH PRIVILEGES;

Step 4: Test it

Simply run the following command from another EC2 instance:
$ mysql -u sai -h mysql–instance1.134342.us-west-1.rds.amazonaws.com -P 3306 -p salesstats
How to Create MySQL Users Accounts and Grant Privileges on AWS RDS Cloud service

Encrypted connection to a RDS instance

First download a certificate bundle that contains both the intermediate and root certificates using the wget command:
$ wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
Now connect it 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 sai -P 3306 -p salesstats

Now, the rest of the tutorial explains how to reverse all steps. In other words, you will learn how to remove the database, user, and grants/permission using the mysql CLI.

Step 5: Revoke privileges from a AWS RDS MySQL user account

The REVOKE SQL statement allows sysadmins to revoke privileges and roles. The syntax is:
mysql> REVOKE ALL PRIVILEGE1,PRIVILEGE2 ON database.* FROM 'user'@'client_ip';
mysql> REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'vpc_sub_net';
mysql> REVOKE INSERT, DELETE ON `salesstats`.* FROM 'sai'@'%';
mysql> REVOKE ALL PRIVILEGES ON `salesstats`.* FROM 'sai'@'%';

Step 6: Delete AWS RDS MySQL user account

To remove AWS RDS MySQL user account use the DROP sql statement as follows:
mysql> DROP USER 'user'@'client_ip';
mysql> DROP USER 'sai'@'%';

See how to delete or remove a MySQL/MariaDB user account for further information.

Step 7: Remove an existing AWS RDS MySQL database

The DROP DATABASE SQL statement deletes all tables in the database and removes the database. So, be very careful with this statement:
mysql> DROP DATABASE db_name_here;
mysql> DROP DATABASE salesstats;

Verify it:
mysql> SHOW DATABASES;

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

Conclusion

In this tutorial, you learned how to create a new MySQL users on AWS RDS using Linux or Unix mysql command line. See RDS docs here for more info.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
0 comments… add one

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.