CONNECTIONPROPERTY: примеры (SQL)
CONNECTIONPROPERTY(property_name): sql_variantОписание функции CONNECTIONPROPERTY
Функция CONNECTIONPROPERTY в Microsoft SQL Server возвращает сведения о свойствах текущего соединения с сервером. Она полезна для аудита, мониторинга и логирования сессий, а также для реализации логики, зависящей от характеристик подключения.
Используется в SELECT-запросах, триггерах или процедурах для получения информации о сетевом соединении, через которое клиент подключился к SQL Server.
Синтаксис: CONNECTIONPROPERTY ( 'property_name' )
Аргумент property_name — строковое выражение, определяющее возвращаемое свойство соединения. Допустимые значения:
- net_transport — возвращает протокол транспорта (Например: TCP, Shared memory, Named pipe).
- protocol_type — тип протокола payload (TSQL, SOAP, и т.д.). Обычно возвращает TSQL.
- auth_scheme — схема аутентификации (Например: SQL, NTLM, KERBEROS, CERTIFICATE).
- local_net_address — IP-адрес сервера, к которому подключился клиент. NULL для соединений Shared memory.
- local_tcp_port — TCP-порт сервера, используемый соединением. NULL для неподдерживаемых протоколов.
- client_net_address — IP-адрес клиента, инициировавшего соединение.
- physical_net_transport — доступно начиная с SQL Server 2012. Возвращает физический транспортный протокол, если включена возможность MARS.
- encrypt_option — указывает, включено ли шифрование для данного соединения (TRUE или FALSE).
- num_reads — количество операций чтения через это соединение с момента установки.
- num_writes — количество операций записи через это соединение.
- last_read — дата и время последней операции чтения.
- last_write — дата и времени последней операции записи.
Возвращаемый тип данных: sql_variant. Результат может быть приведен к соответствующему типу.
Короткие примеры использования
Получение базовых свойств текущего сеанса:
SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme;net_transport | auth_scheme --------------|------------- TCP | SQL
Получение сетевых адресов:
SELECT CONNECTIONPROPERTY('client_net_address') AS client_ip, CONNECTIONPROPERTY('local_net_address') AS server_ip, CONNECTIONPROPERTY('local_tcp_port') AS server_port;client_ip | server_ip | server_port -------------|--------------|------------- 192.168.1.10 | 192.168.1.100| 1433
Проверка шифрования и количества операций ввода-вывода:
SELECT CONNECTIONPROPERTY('encrypt_option') AS is_encrypted, CONNECTIONPROPERTY('num_reads') AS reads_count;is_encrypted | reads_count -------------|------------- FALSE | 5
Похожие функции в MS SQL Server
SESSION_ID (APP_NAME()) — возвращает идентификатор текущего сеанса SPID. Полезна для идентификации соединения, но не дает сетевых свойств.
HOST_NAME() и APP_NAME() — возвращают имя рабочей станции и приложения. Используются для идентификации источника запроса на уровне логики приложения.
sys.dm_exec_connections — динамическое административное представление, предоставляет полную информацию обо всех соединениях, включая данные о буферах, статистику ввода-вывода. Позволяет получать свойства не только текущего, но и других соединений (требует соответствующих прав). CONNECTIONPROPERTY удобнее для получения данных только о текущем сеансе.
Аналоги в других СУБД
MySQL: Функции CONNECTION_ID() (возвращает ID соединения) и переменные сессии, такие как @@hostname. Полных аналогов по набору свойств нет. Информация о соединении частично доступна через SHOW PROCESSLIST или представление INFORMATION_SCHEMA.PROCESSLIST.
SELECT CONNECTION_ID(), USER(), @@hostname;CONNECTION_ID() | USER() | @@hostname ---------------|--------------|------------ 12345 | root@localhost| server-host
PostgreSQL: Используются системные информационные функции, такие как inet_client_addr() и inet_server_port(). Также можно запросить представление pg_stat_activity.
SELECT inet_client_addr() AS client_ip, inet_server_port() AS server_port, pid AS process_id FROM pg_stat_activity WHERE pid = pg_backend_pid();client_ip | server_port | process_id -------------|-------------|----------- 192.168.1.10 | 5432 | 7890
Oracle: Информация доступна через контекстные представления, например SYS_CONTEXT('USERENV', 'IP_ADDRESS'). Более полные данные находятся в V$SESSION.
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') AS client_ip, SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') AS auth_type FROM dual;CLIENT_IP | AUTH_TYPE -------------|---------- 192.168.1.10 | DATABASE
SQLite: Не имеет встроенных функций для получения сетевых свойств соединения, так как обычно работает с файлами локально.
Типичные ошибки при использовании
1. Неверное имя свойства: При указании недопустимого значения аргумента функция возвращает NULL, что может привести к неочевидным логическим ошибкам.
SELECT CONNECTIONPROPERTY('wrong_property_name');NULL
2. Неправильная интерпретация типа данных: Возвращаемый тип sql_variant требует явного приведения для дальнейших операций сравнения или конкатенации.
SELECT 'Encryption is: ' + CONNECTIONPROPERTY('encrypt_option'); -- ОшибкаСообщение об ошибке: Оператор сложения не работает с типами varchar и sql_variant.
Правильный подход:
SELECT 'Encryption is: ' + CAST(CONNECTIONPROPERTY('encrypt_option') AS NVARCHAR(10));Encryption is: FALSE
3. Ожидание не NULL значений для всех свойств: Для некоторых протоколов (например, Shared Memory) свойства local_net_address и local_tcp_port возвращают NULL.
Изменения в последних версиях
В SQL Server 2012 (11.x) добавлено свойство physical_net_transport. Оно предоставляет информацию о физическом транспортном протоколе, особенно когда включена функция Multiple Active Result Sets (MARS).
В более ранних версиях SQL Server (например, 2008) данное свойство отсутствовало, и его вызов возвращал NULL.
Расширенные примеры использования
Логирование параметров входа пользователя с помощью триггера входа:
CREATE TABLE dbo.LoginAudit (LoginTime DATETIME, LoginName NVARCHAR(128), ClientIP NVARCHAR(48), AuthScheme NVARCHAR(128), NetTransport NVARCHAR(128));
CREATE TRIGGER tr_login_audit ON ALL SERVER FOR LOGON AS
BEGIN
INSERT INTO master.dbo.LoginAudit (LoginTime, LoginName, ClientIP, AuthScheme, NetTransport)
VALUES (GETDATE(), ORIGINAL_LOGIN(), CONNECTIONPROPERTY('client_net_address'), CONNECTIONPROPERTY('auth_scheme'), CONNECTIONPROPERTY('net_transport'));
END;Динамическое управление ресурсами на основе типа соединения:
IF CONNECTIONPROPERTY('net_transport') = 'Named pipe'
PRINT 'Высокоприоритетное локальное соединение.';
ELSE IF CONNECTIONPROPERTY('encrypt_option') = 'FALSE'
PRINT 'Внимание: соединение не шифруется.';Анализ активности текущего сеанса:
SELECT CAST(CONNECTIONPROPERTY('last_read') AS DATETIME) AS last_activity_read, CAST(CONNECTIONPROPERTY('last_write') AS DATETIME) AS last_activity_write, DATEDIFF(SECOND, CAST(CONNECTIONPROPERTY('last_read') AS DATETIME), GETDATE()) AS seconds_since_read;last_activity_read | last_activity_write | seconds_since_read -----------------------|------------------------|------------------- 2023-10-05 14:30:25.123| 2023-10-05 14:30:25.127| 120
Использование в составе расширенной информации о сессии:
SELECT s.session_id, s.login_name, s.host_name, CONNECTIONPROPERTY('client_net_address') AS client_ip, s.program_name FROM sys.dm_exec_sessions s WHERE s.session_id = @@SPID;Определение, используется ли MARS (Multiple Active Result Sets):
SELECT CONNECTIONPROPERTY('physical_net_transport') AS physical_transport;physical_transport ------------------- TCP