如何基于最后登录时间删除GridDB数据库中的不活跃用户？

Hey there! Let's break down how to handle inactive user cleanup in GridDB, since it doesn’t have a direct equivalent to SQL Server’s sys.dm_exec_sessions view for tracking last login times. We’ll cover two practical approaches tailored to your Ubuntu/GridDB Python client setup:

Option 1: Use GridDB’s Built-in Audit Logging #

GridDB includes audit logging that can capture user connection events. Here’s how to leverage it:

1. Enable Audit Logging

   • If you’re running GridDB in a container, exec into the container to modify the griddb.conf file (usually located at /var/lib/griddb/conf/griddb.conf).
   • Add or update these configuration values:

     audit_logging=true
     audit_log_path=/var/lib/griddb/log/audit/
     

   • Restart the GridDB container to apply changes.

2. Parse Audit Logs for Last Login Times
   Audit logs will include entries like this for successful user connections:

   2024-05-20T09:30:00+00:00 [INFO] USER: jane_smith OPERATION: CONNECT

   Use Python to read and parse these logs, then filter users inactive for over 6 months:

   import re
   from datetime import datetime, timedelta
   from collections import defaultdict
   
   def parse_audit_logs(log_path):
       login_pattern = re.compile(r'(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\+\d{2}:\d{2}) \[INFO\] USER: (\w+) OPERATION: CONNECT')
       user_last_login = defaultdict(lambda: datetime.min.replace(tzinfo=datetime.timezone.utc))
   
       with open(log_path, 'r') as f:
           for line in f:
               match = login_pattern.search(line)
               if match:
                   login_time = datetime.fromisoformat(match.group(1))
                   username = match.group(2)
                   if login_time > user_last_login[username]:
                       user_last_login[username] = login_time
   
       # Filter users inactive for 6+ months
       six_months_ago = datetime.now(datetime.timezone.utc) - timedelta(days=180)
       inactive_users = [user for user, last_login in user_last_login.items() if last_login < six_months_ago]
       return inactive_users
   

Option 2: Custom User Activity Tracking Table #

This approach uses a dedicated table to log login events, giving you more control and easier querying.

Step 1: Create the Login History Table #

Use the GridDB Python client to set up a collection table:

import griddb_python as griddb

def create_login_history_table(store):
    schema = griddb.ContainerSchema(
        "user_login_history",
        [
            ("user_name", griddb.Type.STRING),
            ("last_login_timestamp", griddb.Type.TIMESTAMP)
        ],
        griddb.ContainerType.COLLECTION,
        True  # Set user_name as primary key
    )
    if not store.get_container("user_login_history"):
        store.put_container(schema)

Step 2: Update Login Times on Authentication #

Modify your application’s login logic to update this table whenever a user successfully connects:

from datetime import datetime

def update_last_login(store, username):
    container = store.get_container("user_login_history")
    current_time = datetime.now(datetime.timezone.utc).isoformat()

    # Try updating existing record first
    update_query = f"UPDATE user_login_history SET last_login_timestamp = TIMESTAMP('{current_time}') WHERE user_name = '{username}'"
    result = container.query(update_query)
    result.close()

    # Insert new record if user hasn't logged in before
    if result.get_affected_rows() == 0:
        insert_query = f"INSERT INTO user_login_history VALUES ('{username}', TIMESTAMP('{current_time}'))"
        insert_result = container.query(insert_query)
        insert_result.close()

Step 3: Fetch and Delete Inactive Users #

Query the table to find inactive users, then delete them with admin privileges:

def get_inactive_users(store):
    six_months_ago = (datetime.now(datetime.timezone.utc) - timedelta(days=180)).isoformat()
    query = f"SELECT user_name FROM user_login_history WHERE last_login_timestamp < TIMESTAMP('{six_months_ago}')"
    
    container = store.get_container("user_login_history")
    result = container.query(query)
    inactive_users = []
    row = result.fetch_next()
    while row:
        inactive_users.append(row[0])
        row = result.fetch_next()
    result.close()
    return inactive_users

def delete_inactive_users(store, inactive_users):
    for user in inactive_users:
        try:
            store.execute(f"DROP USER {user}")
            print(f"Successfully deleted inactive user: {user}")
        except griddb.GSException as e:
            print(f"Failed to delete user {user}:")
            for i in range(e.get_error_stack_size()):
                print(f"Error {i}: {e.get_message(i)}")

Full Workflow Example #

if __name__ == "__main__":
    factory = griddb.StoreFactory.get_instance()
    try:
        # Connect to GridDB with admin credentials
        store = factory.get_store(
            host="localhost",
            port=31810,
            cluster_name="defaultCluster",
            username="admin",
            password="admin"
        )

        # Initialize the login history table (run once)
        create_login_history_table(store)

        # Get and delete inactive users
        inactive_users = get_inactive_users(store)
        print(f"Found {len(inactive_users)} inactive users: {inactive_users}")
        delete_inactive_users(store, inactive_users)

    except griddb.GSException as e:
        for i in range(e.get_error_stack_size()):
            print(f"Error {i}: {e.get_message(i)}")

Key Notes #

• Permissions: Ensure your Python client uses an admin-level account to execute DROP USER commands.
• Audit Log Rotation: If using the audit log approach, set up log rotation to prevent disk space issues.
• Date Precision: The 6-month threshold uses 180 days as a simplification—adjust the timedelta if you need calendar-based precision.

内容的提问来源于stack exchange，提问作者Emmanuel Oluwatosin