无服务器管理员权限时，如何监控Database X的登录与查询历史？

以下是针对主流数据库系统的可行方案，所有操作均仅需你拥有的Database X管理员权限，无需服务器级权限：

SQL Server 环境 #

• 扩展事件（Extended Events）：这是SQL Server轻量且强大的监控工具，你可以在Database X内部创建仅针对该库的事件会话，精准捕获登录、查询执行等行为。
  • 创建监控会话示例：

    CREATE EVENT SESSION [DBX_Access_Monitor] ON DATABASE 
    ADD EVENT sqlserver.login(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.username)
        WHERE database_id = DB_ID('Database X')),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.username, sqlserver.sql_text)
        WHERE database_id = DB_ID('Database X'))
    ADD TARGET package0.event_file(SET filename=N'DBX_Monitor.xel', max_file_size=(10), max_rollover_files=(5))
    WITH (STARTUP_STATE=OFF);
    

  • 启动会话后，可通过以下语句查询监控数据：

    SELECT 
        event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
        event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
        event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(100)') AS Username,
        event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(100)') AS ClientHost,
        event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText
    FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file('DBX_Monitor*.xel', NULL, NULL, NULL)
    ) AS x;
    

• 系统视图实时追踪：通过查询数据库级系统视图，可实时或定期抓取当前及最近的查询活动：

  SELECT 
      s.session_id,
      s.login_name,
      s.host_name,
      s.program_name,
      r.start_time,
      t.text AS QueryText
  FROM sys.dm_exec_sessions s
  LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
  WHERE s.database_id = DB_ID('Database X');
  

MySQL 环境 #

• 自定义日志表+触发器：MySQL默认通用查询日志是服务器级，但你可以在Database X内创建自定义日志表，通过触发器捕获DML操作的查询历史：
  • 先创建日志存储表：

    CREATE TABLE DBX_Access_Log (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        event_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        username VARCHAR(100),
        client_host VARCHAR(100),
        query_text TEXT
    );
    

  • 创建触发器（仅捕获DML语句，DDL需单独处理）：

    DELIMITER //
    CREATE TRIGGER TRG_DBX_Insert_Log AFTER INSERT ON Database_X.your_table_name
    FOR EACH ROW
    BEGIN
        INSERT INTO DBX_Access_Log (username, client_host, query_text)
        VALUES (USER(), CURRENT_USER(), (SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = CONNECTION_ID()));
    END //
    DELIMITER ;
    

  • 定期查询当前连接与活动：

    SELECT ID, USER, HOST, DB, COMMAND, TIME, INFO
    FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE DB = 'Database X';
    

PostgreSQL 环境 #

• pg_stat_statements 扩展：如果服务器已安装该扩展（多数默认环境支持），你可在Database X内启用并查询该库的查询统计与历史：
  • 启用扩展：

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    

  • 查询数据库级查询历史：

    SELECT 
        userid::regrole AS username,
        dbid::regnamespace AS database_name,
        queryid,
        query,
        calls,
        total_time,
        min_time,
        max_time,
        mean_time
    FROM pg_stat_statements
    WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'Database X');
    

• pg_stat_activity 实时监控：直接查询系统视图获取当前连接与执行的查询：

  SELECT usename, client_addr, query, state, query_start
  FROM pg_stat_activity
  WHERE datname = 'Database X';
  

关键注意事项 #

• 性能开销：所有监控操作会产生一定性能消耗，建议先在测试环境验证，可通过缩小监控范围（如仅捕获慢查询、特定用户操作）降低影响。
• 日志清理：定期清理监控日志表或事件文件，避免占用过多存储资源。
• 权限确认：确保你的Database X管理员权限包含CREATE EVENT SESSION（SQL Server）、CREATE TRIGGER（MySQL/PostgreSQL）、CREATE EXTENSION（PostgreSQL）等必要权限，通常数据库管理员默认拥有这些权限。

内容的提问来源于stack exchange，提问作者Du Nguyen