数据库连接反查，定位具体库的连接信息，内网必备。

文章来源于t00ls，方便各位师傅快速梳理内网资产。

通过弱口令或其他方法连接到数据库后，发现一个数据库中搭建了多个库，如何确认这些库由哪些地址正在使用or连接？

如下查询将返回每个连接到数据库的连接信息，包括数据库名称、连接数、登录名、应用程序名称、主机名和 IP 地址。

SQLserver

SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections,
    loginame AS LoginName, program_name AS ApplicationName,
    hostname AS HostName, net_library AS NetworkLibrary,
    client_net_address AS IPAddress, nt_username AS NTUserName,
    login_time AS LoginTime
FROM sys.sysprocesses
JOIN sys.dm_exec_connections ON sys.sysprocesses.spid = sys.dm_exec_connections.session_id
WHERE dbid > 0
GROUP BY dbid, loginame, program_name, hostname, net_library, client_net_address, nt_username, login_time
ORDER BY DBName, NumberOfConnections DESC;

Mysql

SELECT 
    DBName, COUNT(processlist.ID) AS NumberOfConnections,
    user AS LoginName, processlist.INFO AS ApplicationName,
    host AS HostName, 
    processlist.TIME AS Time, 
    processlist.STATE AS State
FROM 
    (SELECT 
        ID, USER, DB AS DBName, INFO, HOST, TIME, STATE 
    FROM 
        information_schema.PROCESSLIST 
    WHERE 
        DB IS NOT NULL) AS processlist
GROUP BY 
    DBName, LoginName, ApplicationName, HostName, Time, State
ORDER BY 
    DBName, NumberOfConnections DESC;

Oracle
在Oracle中，运行如下命令列出连接信息。

SELECT sid, serial#, username, osuser, machine, program, status, logon_time 
FROM v$session 
WHERE audsid != userenv('SESSIONID') 
AND type='USER' 
AND service_name='<database_name>';

Mongodb
在Mongodb中，先切换到具体库下，再运行如下命令列出连接信息

db.currentOp(true).inprog.forEach(function(op) {
   if(op.client) {
      print(op.client, op.clientMetadata.application);
   }
});

PostgreSQL
在PostgreSQL中，先切换到具体库下，再运行如下命令列出连接信息

SELECT pid, usename, client_addr, application_name FROM pg_stat_activity WHERE datname = '<database_name>';

Redis
在Redis中，运行如下命令列出连接信息，<database_index>是数据库的索引号，从0开始。

CLIENT LIST | grep <database_index>

原文始发于微信公众号（乐至享）：数据库连接反查，定位具体库的连接信息，内网必备。