que - Obtenga la dirección IP de un usuario al llamar a un procedimiento almacenado(SQL)

sql exec store procedure

Question

¿Es posible, y de ser así, cómo obtener la dirección IP remota de un usuario ejecutando la consulta, de manera análoga, como podemos obtener el nombre de usuario con: SUSER_SNAME() ?

Actualizar antes de recompensa

Estoy buscando una solución que permita capturar la dirección IP de un usuario mortal común, no el propietario de una base de datos. Las ideas propuestas por TheGameiswar o njc no permiten capturar la dirección IP de un usuario a quien se le ha otorgado solo un permiso de execute . Sin embargo, son excelentes ideas para comenzar con el problema. Aquí enumero la esencia de las ideas:

Por favor vea la secuencia que sigo:

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

Ahora agregue un usuario y otorgue permiso:

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

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

Cuando ejecuto el procedimiento con una consulta:

EXECUTE AS USER = ''user_mortal_jack''
exec MyStoredProcedure
REVERT

Recibo un mensaje de error:

El módulo que se está ejecutando no es de confianza. O bien el propietario de la base de datos del módulo debe recibir un permiso de autenticación o el módulo debe estar firmado digitalmente.

Recibiré este mensaje incluso si otorgo un permiso adicional:

grant VIEW SERVER STATE to [user_mortal_jack];

Si cambio el inicio del procedimiento almacenado a:

create procedure MyStoredProcedure
with execute as OWNER as 

Acabo con otro tipo de error:

No se pudo obtener información sobre el grupo de Windows NT / usuario ''blahblah / admin_user'', código de error 0x534.

Actualizar después de recompensa

Se otorga una recompensa a Hadi por esta única línea de código oculta en su respuesta:

CONNECTIONPROPERTY(''client_net_address'')

que permite capturar la dirección IP de cualquier usuario mortal sin otorgar ningún derecho adicional al usuario ni configurar la base de datos CONFIANZA EN LA CONFIANZA ni siquiera crear un procedimiento WITH EXECUTE AS OWNER cláusula WITH EXECUTE AS OWNER .

Answer 1

Información general

Hay dos formas de obtener la información de conexión actual

1. Obtención de información de vistas de gestión dinámica

   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. Usando la función CONNECTIONPROPERTY (SQL Server 2008 y una versión más reciente):

   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

Las soluciones sugeridas

1. Si está buscando otorgar al usuario una dirección IP específica

   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. Suponiendo que tiene una tabla que contiene la dirección IP otorgada (es decir, TBL_IP )

   CREAR PROCEDIMIENTO MyStoredProcedure COMO COMENZAR A DECLARAR @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

   FIN

3. Si desea otorgarle a un usuario (usuario de la base de datos) que ejecute un procedimiento almacenado, debe usar este comando

   GRANT EJECUTE EN MyStoredProcedure TO User;

   Hay muchos artículos y respuestas detalladas que hablan sobre el problema al que se enfrenta, y muchas soluciones sugeridas, como configurar la base de datos en el modo TRUSTWORTHY (antes de usarla, lea el primer enlace a continuación) y Confiar en el autenticador, y otros métodos. Puedes encontrarlos en los enlaces de abajo.

   • SQL Server EJECUTAR COMO un problema
   • Procedimiento de ejecución de SQL Server como usuario
   • Ampliación de la suplantación de la base de datos mediante EXECUTE AS (sección Ampliación del ámbito de la suplantación)
   • EJECUTAR COMO (Transact-SQL)
   • Ejecutar un procedimiento almacenado como otro permiso de usuario.

   Nota: Puede verificar la respuesta de @SteveFord para usar la propiedad TRUSTWORTHY

4. Si está buscando bloquear conexiones, excepto direcciones IP específicas, debe seguir esta respuesta

   • Restringir la conexión de SQL Server a una dirección IP específica

   También hay muchos scripts que se pueden usar para obtener direcciones IP de clientes o servidores que se pueden encontrar en la siguiente pregunta:

   • Cómo obtener una dirección IP de cliente de una base de datos de SQL Server

Referencias

• MS SQL 2008: dirección IP del cliente en alojamiento compartido
• Encontrar la dirección IP del cliente
• Cómo obtener una dirección IP de cliente de una base de datos de SQL Server
• Obtener la dirección IP del cliente en SQL Server

Answer 2

Para obtener la dirección IP y el nombre de usuario de cualquier persona que llama sin otorgarles permisos especiales, puede engañar un poco al servidor y a la base de datos. Se necesitan un par de cosas para lograr esto:

1. ALTER DATABASE MyDataBase SET TRUSTWORTHY ON
2. Crear un inicio de sesión (no [user_mortal_jack] ) en mi ejemplo [user_immortan_joe]
3. Cree un usuario para [user_immortan_joe] en MyDataBase
4. En el contexto de la grant VIEW SERVER STATE to [user_immortan_joe]; master grant VIEW SERVER STATE to [user_immortan_joe];
5. En MyDataBase cree un procedimiento almacenado (no MyStoredProcedure , en mi ejemplo get_ip ) que recibe un int que representa un parámetro de session_id específico y output (enviará, no devolverá) la dirección IP o ese session_id . with execute as ''user_immortan_joe'' .
6. Cree MyStoredProcedure de manera que con la ayuda de get_ip y de SUSER_SNAME() devuelva la dirección IP y el nombre de usuario de su interlocutor.

De esta manera, obtendrá la dirección IP y el nombre de usuario de cualquier persona que llame a MyStoredProcedure respetando el principio de privilegio mínimo y evitando los problemas que ha encontrado al buscar una solución.

Ejemplo de script:

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

Answer 3

Puedes usar conexiones DMV para lograr eso ...

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 para client_net_address

Dirección de host del cliente que se conecta a este servidor. Es anulable

Answer 4

Usando la sentencia EXECUTE AS OWNER en una CREATE PROCEDURE :

Desde MSDN

Cuando un usuario ejecuta un módulo que se ha especificado para ejecutarse en un contexto que no sea CALLER, se comprueba su permiso para ejecutar el módulo, pero se verifican los permisos adicionales en los objetos a los que accede el módulo en la cuenta de usuario especificada en el EJECUTAR como cláusula. El usuario que ejecuta el módulo está, en efecto, suplantando al usuario especificado.

El contexto especificado en la cláusula EXECUTE AS del módulo es válido solo por la duración de la ejecución del módulo. El contexto vuelve a la persona que llama cuando se completa la ejecución del módulo.

Lo siguiente debe ser creado por un usuario que tenga permisos para consultar a los DMV.

CREATE PROCEDURE MyStoredProcedure
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
   SELECT TOP 1
   FROM tb
   INNER JOIN sys.dm_exec_connections cn
         ON tb.client_net_address = cn.client_net_address
   WHERE cn.Session_Id = @@SPID
END

Luego deberá otorgar a los usuarios permisos para ejecutar el procedimiento almacenado:

Actualizar para crear los permisos correctos.

Necesitará configurar su base de datos como confiable (ver Establecer la base de datos como confiable :

ALTER DATABASE MyDataBase SET TRUSTWORTHY ON

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

CREATE USER [user_mortal_jack] FOR LOGIN [user_mortal_jack] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

He probado esto y esto ahora funciona como se esperaba