with - stored procedure sql




Получить IP-адрес пользователя при вызове хранимой процедуры(SQL) (3)

Возможно ли, и если да, то каким образом получить удаленный IP-адрес пользователя, выполняющего запрос, аналогично тому, как мы можем получить имя пользователя с помощью: SUSER_SNAME() ?

Обновление до награды

Я ищу решение, которое позволяет получить IP-адрес обычного смертного пользователя, а не владельца базы данных. Идеи, предложенные TheGameiswar или njc, не позволяют фиксировать IP-адрес пользователя, которому предоставлено только разрешение на execute . Тем не менее, они являются отличными идеями, чтобы начать с проблемы. Здесь я перечислю суть идеи:

Пожалуйста, смотрите последовательность, которой я следую:

create procedure MyStoredProcedure as
select client_net_address 
from sys.dm_exec_connections
where session_id = @@SPID

Теперь добавьте пользователя и предоставьте разрешение:

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

Когда я запускаю процедуру с запросом:

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT

Я получаю сообщение об ошибке:

Выполняемый модуль не является доверенным. Либо владельцу базы данных модуля должно быть предоставлено разрешение на аутентификацию, либо модуль должен иметь цифровую подпись.

Я получу это сообщение, даже если я дам дополнительное разрешение:

grant VIEW SERVER STATE to [user_mortal_jack];

Если я изменю начало хранимой процедуры на:

create procedure MyStoredProcedure
with execute as OWNER as 

Я в конечном итоге с разного рода ошибками:

Не удалось получить информацию о группе / пользователе Windows NT 'blahblah \ admin_user', код ошибки 0x534.

Обновление после щедрости

Щедрость предоставляется Хади за эту единственную строку кода, скрытую в их ответе:

CONNECTIONPROPERTY('client_net_address')

что позволяет захватывать IP-адрес любого смертного пользователя, не предоставляя пользователю никаких дополнительных прав, не устанавливая параметр TRUSTWORTHY ON базы данных и даже не создавая процедуру с предложением WITH EXECUTE AS OWNER .


Общая информация

Есть два способа получить информацию о текущем соединении

  1. Получение информации из представлений динамического управления

    SELECT
    conn.session_ID as SPID,
    conn.client_net_address as IPAddress,
    sess.host_name as MachineName,
    sess.program_name as ApplicationName,
    login_name as LoginName
    FROM sys.dm_exec_connections conn
    inner join sys.dm_exec_sessions sess
    on conn.session_ID=sess.session_ID
  2. Использование функции CONNECTIONPROPERTY (SQL Server 2008 и более новая версия):

    select
    CONNECTIONPROPERTY('net_transport') AS net_transport,
    CONNECTIONPROPERTY('protocol_type') AS protocol_type,
    CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
    CONNECTIONPROPERTY('local_net_address') AS local_net_address,
    CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
    CONNECTIONPROPERTY('client_net_address') AS client_net_address

Предлагаемые решения

  1. Если вы хотите предоставить пользователю определенный IP-адрес

    CREATE PROCEDURE MyStoredProcedure AS
    BEGIN
        DECLARE @IP_Address varchar(255);
    
        SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))
    
        IF @IP_Address = 'XXX.XXX.XXX.XXX'
        SELECT TOP 1 FROM tb
    
    END
  2. Предполагая, что у вас есть таблица, содержащая предоставленный IP-адрес (т.е. TBL_IP )

    CREATE PROCEDURE MyStoredProcedure AS BEGIN ОБЪЯВЛЯЕТСЯ @IP_Address varchar (255);

    SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))
    
    IF EXISTS (SELECT 1 FROM TBL_IP WHERE [IP] = @IP_Address )
    SELECT TOP 1 FROM tb

    КОНЕЦ

  3. Если вы хотите предоставить пользователю (пользователю базы данных) выполнение хранимой процедуры, вам следует использовать эту команду

    ВЫПОЛНИТЬ ВЫПОЛНЕНИЕ НА MyStoredProcedure ДЛЯ Пользователя;

    Существует множество подробных статей и ответов, в которых рассказывается о проблеме, с которой вы сталкиваетесь, а также множество предлагаемых решений, таких как Настройка базы данных в режиме TRUSTWORTHY (перед ее использованием прочитайте первую ссылку ниже), Доверие к аутентификатору и другие методы. Вы можете найти их в ссылках ниже

    Примечание: вы можете проверить ответ @SteveFord для использования свойства TRUSTWORTHY

  4. Если вы хотите заблокировать соединения, кроме определенных IP-адресов, вы должны следовать этому ответу

    Также есть много сценариев, которые можно использовать для получения IP-адресов клиента или сервера, которые можно найти в вопросе ниже:

Рекомендации


Вы можете использовать соединения DMV для достижения этой цели ..

select ec.client_net_address,* from sys.dm_exec_connections ec
join
sys.dm_exec_requests rq
on rq.connection_id=ec.connection_id
cross apply
sys.dm_exec_sql_text(rq.sql_handle) txt
where txt.text like '%your stored proc%'

MSDN для client_net_address

Адрес хоста клиента, подключающегося к этому серверу. Обнуляемый


Чтобы получить IP-адрес и имя пользователя любого вызывающего абонента без предоставления им всех специальных разрешений, вы можете немного обмануть сервер и базу данных. Для этого нужно несколько вещей:

  1. ALTER DATABASE MyDataBase SET TRUSTWORTHY ON
  2. Создайте логин (не [user_mortal_jack] ) в моем примере [user_immortan_joe]
  3. Создайте пользователя для [user_immortan_joe] в MyDataBase
  4. В контексте master grant VIEW SERVER STATE to [user_immortan_joe];
  5. В MyDataBase создайте хранимую процедуру (не MyStoredProcedure , в моем примере get_ip ), которая получает int, представляющий определенный параметр session_id будет output (выводить, а не возвращать) IP-адрес или этот session_id . Создайте его with execute as 'user_immortan_joe' .
  6. Создайте MyStoredProcedure таким образом, чтобы с помощью get_ip и SUSER_SNAME() возвращались IP-адрес и имя пользователя вызывающей стороны.

Таким образом, вы получаете IP-адрес и имя пользователя любого абонента MyStoredProcedure соблюдая принцип наименьших привилегий и избегая проблем, с которыми вы столкнулись при поиске решения.

Пример скрипта:

use MyDataBase
go
alter database MyDataBase set  trustworthy on;
go

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';
go
create user [user_mortal_jack];
go

CREATE LOGIN [user_immortan_joe] WITH PASSWORD=N'ToTheGatesOfValhalla!!!';
go
create user [user_immortan_joe];
go

use master
go
grant VIEW SERVER STATE to [user_immortan_joe];

use MyDataBase
go


create  PROCEDURE get_ip
@spid int, @ip varchar(50) output
with execute as 'user_immortan_joe'
as
begin
    select @ip = client_net_address
    from sys.dm_exec_connections
    where session_id =  @spid
end;
go

create procedure MyStoredProcedure
as
begin
    declare @spid int = @@spid, @ip varchar(50);
    exec dbo.get_ip @spid,@ip output;
    select @ip as ipAddress ,SUSER_SNAME() as userName
end
go

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];
go

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT






ip