In the previous blog, we have gone through about network-level security in RDS. In this blog, we will see about the Database level security in RDS.

After network-level restriction to host, we can’t allow a user to connect to the database from anywhere over the internet. We need to restrict user access at the Database level as well. Need to create a user with a restricted host along with a strong password to avoid cracking of password. In RDS we have a special feature named Identity and Access Management (IAM).

We need to monitor the user activity as well in the Database. Because the user may wrongly execute the query in the server which leads to data loss or production outage. The user activity has to be monitored as per the compliances. We can achieve this by enabling the audit log in the RDS.

By default, The MariaDB audit plugin is available for MySQL RDS.

Identity and Access Management (IAM) :

Instead of creating individual users for both MySQL and console, we can consider using an IAM user to access both console and MySQL.

Features of using IAM users:

1. Single user for both MySQL and console.
2. Password policy is based on our convenience.
3. The specific role and access to console and Database.
4. One Time Password (OTP) to login into the Database.

Login with IAM user to DB

Prerequisite :

• Enable IAM authentication in the server.

• IAM users should have rds-db:connect policy.

• The policy should attach to EC2 roles as well.

• CLI should be configured in the host box.

Step 1: We need to create the corresponding DB user in MySQL as well.

 
mysql> CREATE USER mydbops IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';
Query OK, 0 rows affected (0.05 sec)
	

Step 2: Provide the required access to the user.

 
mysql> Grant select on mydbops_rds.* to 'mydbops'@'%';
Query OK, 0 rows affected (0.02 sec)
	

Step 3: Create an authentication token

 
aws rds generate-db-auth-token --hostname webinar-09.c9qrczhblkjo.ap-south-1.rds.amazonaws.com --port 3306 --username mydbops
webinar-09.c9qrczhblkjo.*myddbops&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-
	

we can able to generate the token for IAM user mydbops, By using that token we can able to log in the DB server. But the token is valid only for 15 mins. we need to use the token within 15 mins. If there is an active connection it will be valid until the user logout or wait_timeout is reached out is reached.

By this method, we can provide temporary access to the user in MySQL.

Step 4: Login using the token

 
[root@centos11 vagrant]# mysql -h webinar-09.c9qrczhblkjo.ap-south-1.rds.amazonaws.com -u mydbops -p"webinar-09.c9qrczhblkjo.*****mydbops&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-****" --enable-cleartext-plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 170
Server version: 8.0.25 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydbops_rds        |
+--------------------+
1 row in set (0.06 sec)
	

Monitoring DB user activity :

Flow chart for enabling the audit plugin:

Note: No downtime is required. to enable the audit plugin.

But in RDS we have a limitation that we can able store the logs for only a day. To have higher retention then we can enable the cloudwatch log exporter for the individual logs.

We can enable the cloud watch log exporter for slow log, audit log, error log, and general log as well. We can have max retention of 10 years in cloudwatch.

We can enable it by modifying the RDS instance or at the time of creating the instance. The plugin SERVER_AUDIT is from MariaDB, whereas AWSAuthenticationPlugin is from AWS.

 
mysql> SELECT   PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,   PLUGIN_LIBRARY, PLUGIN_LICENSE FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_TYPE in
('AUDIT','AUTHENTICATION') and PLUGIN_LIBRARY !='NULL';
+-------------------------+---------------+----------------+-----------------+----------------+
| PLUGIN_NAME             | PLUGIN_STATUS | PLUGIN_TYPE    | PLUGIN_LIBRARY  | PLUGIN_LICENSE |
+-------------------------+---------------+----------------+-----------------+----------------+
| SERVER_AUDIT            | ACTIVE        | AUDIT          | server_audit.so | GPL            |
| AWSAuthenticationPlugin | ACTIVE        | AUTHENTICATION | aws_auth.so     | PROPRIETARY    |
+-------------------------+---------------+----------------+-----------------+----------------+
	

In this way, we can protect the RDS MySQL server from direct access to DB, and also track the user activity. They can help us by avoiding DB server attacks and analyzing the user activity.