Snippet Name: Track_Logins_in_RDS
Tags:
Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:32:04
Dependencies:
DBA Notes:
Sample Output:
Title: Track Logins in RDS
Author: user
In RDS MySQL 8 and above, cani create an event that tracks successful logins?
Author: ChatGPT
In **Amazon RDS for MySQL 8 and above**, you **cannot create an event to track successful logins directly**???at least not in the traditional MySQL sense.
Here???s why:
---
### ??? **Limitations in RDS MySQL:**
1. **NO access to general logs or audit logs by default:**
- You can't access the `mysql.general_log` or `mysql.audit_log` tables (or log files directly) in RDS like you can in a full MySQL installation.
2. **NO SUPER privilege or FULL FILE access:**
- RDS restricts many administrative operations, including setting certain log destinations or creating system-level triggers.
3. **EVENT Scheduler in RDS is limited:**
- While you can create scheduled events in RDS MySQL, **they cannot hook into login events**, because MySQL doesn???t expose login events as triggerable actions (unlike SQL Server's Logon triggers).
---
### ??? **What you CAN do in RDS MySQL 8+:**
#### 1. **Enable general log (optional, not ideal for production)**
You can enable the general log to record all connections:
```sql
CALL mysql.rds_set_configuration('general_log', 'ON');
```
Then, to read it:
```sql
SELECT * FROM mysql.general_log
WHERE command_type = 'Connect';
```
> ?????? Note: This log can grow very large and impact performance. Use with caution and only temporarily.
---
#### 2. **Use Performance Schema to monitor current connections**
You can check `performance_schema.threads` or `events_statements_summary_by_user_by_event_name` for currently active sessions, but it doesn't track historical logins.
---
#### 3. **Use CloudTrail (for IAM database authentication)**
If you're using **IAM database authentication**, CloudTrail can log access attempts???but this only works if you're using AWS IAM to authenticate.
---
#### 4. **Use ProxySQL or an external audit plugin**
For advanced auditing, you could route traffic through **ProxySQL** or use **MySQL Enterprise Audit plugin** (not available in RDS, but available in Aurora MySQL). RDS does not support custom plugins.
---
### Summary
| Option | Works in RDS? | Notes |
|----------------------------------|---------------|-------|
| `AFTER LOGON` trigger | ??? | Not supported in MySQL |
| `mysql.general_log` | ??? (limited) | Can be enabled temporarily |
| MySQL Enterprise Audit Plugin | ??? | Not supported in RDS |
| AWS CloudTrail (IAM auth only) | ??? | If using IAM authentication |
| Performance Schema | ??? | Only shows current sessions |
---
Would you like help building a query to extract login records from the general log after enabling it?
Author: user
instead of the general log, what if we used this query, and saved the data into a table in the dbaresources schema, and created a table that captured the first and last login per user when grouped by hostname, applciation name, username? select distinct user,CURRENT_TIMESTAMP from performance_schema.events_statements_summary_by_user_by_event_name
WHERE user not in ('event_scheduler','rdsadmin') and user is not null
order by user
Author: ChatGPT
Yes, that???s a solid idea! Using `performance_schema.events_statements_summary_by_user_by_event_name` as a **lightweight login activity proxy** is a practical workaround in RDS MySQL 8+ where traditional logging/auditing is limited.
While it doesn???t track logins directly, it reflects **user activity**, and the presence of a user in this table means they've executed at least one statement since the last MySQL restart or `FLUSH` of the performance schema summary tables.
---
## ??? Approach Overview
### 1. **Query the users from `performance_schema.events_statements_summary_by_user_by_event_name`**
You already have this:
```sql
SELECT DISTINCT user, CURRENT_TIMESTAMP AS capture_time
FROM performance_schema.events_statements_summary_by_user_by_event_name
WHERE user NOT IN ('event_scheduler', 'rdsadmin') AND user IS NOT NULL
ORDER BY user;
```
### 2. **Create a table to store first/last login info**
You can create something like this in your `dbaresources` schema:
```sql
CREATE TABLE dbaresources.user_login_activity (
username VARCHAR(128),
hostname VARCHAR(128),
app_name VARCHAR(128),
first_seen DATETIME,
last_seen DATETIME,
PRIMARY KEY (username, hostname, app_name)
);
```
> ?????? You will need to decide how to determine hostname and app name ??? those are not in the `events_statements_summary_by_user_by_event_name` table, so we might need to combine with other performance schema tables like `threads`.
---
### 3. **Periodically merge new data into that table**
You can write a merge (upsert) query in a stored procedure or event (if allowed), like:
```sql
INSERT INTO dbaresources.user_login_activity (username, hostname, app_name, first_seen, last_seen)
SELECT
ssu.user,
th.HOST,
th.NAME AS app_name,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
FROM performance_schema.events_statements_summary_by_user_by_event_name ssu
JOIN performance_schema.threads th ON ssu.user = th.USER
WHERE ssu.user NOT IN ('event_scheduler','rdsadmin') AND ssu.user IS NOT NULL
GROUP BY ssu.user, th.HOST, th.NAME
ON DUPLICATE KEY UPDATE
last_seen = CURRENT_TIMESTAMP;
```
> ?????? This assumes the `username + hostname + app_name` combination is unique and that you're able to get HOST and NAME from `performance_schema.threads`.
---
### ???? Automate with a Scheduled Event
If RDS permits events in your environment, you can schedule it every few minutes:
```sql
CREATE EVENT IF NOT EXISTS dbaresources.capture_user_activity
ON SCHEDULE EVERY 5 MINUTE
DO
CALL dbaresources.update_login_tracking(); -- your procedure to merge data
```
> Enable the event scheduler (should already be enabled in RDS):
```sql
SET GLOBAL event_scheduler = ON;
```
---
## Caveats
- This only tracks **activity**, not actual successful logins.
- Data is **reset** when MySQL restarts or when `performance_schema` is flushed.
- `HOST` and `NAME` may be empty depending on client connections and application behavior.
---
Would you like help generating the full setup ??? table, stored procedure, and event?