조인 - mssql 프로시저 select 결과 변수




저장 프로 시저를 호출 할 때 사용자의 IP 주소 가져 오기(SQL) (3)

가능하면 SUSER_SNAME() 하여 사용자 이름을 얻을 수있는 것과 마찬가지로 쿼리를 실행하는 사용자의 원격 IP 주소를 얻는 방법이 있습니까?

현상금 전에 업데이트

나는 데이터베이스 소유자가 아닌 일반 필사자 사용자의 IP 주소를 가져올 수있는 솔루션을 찾고 있습니다. TheGameiswar 또는 njc가 제안한 아이디어는 단지 execute 권한이 부여 된 사용자의 IP 주소를 캡처하는 것을 허용하지 않습니다. 그러나, 그들은 문제로 시작하는 훌륭한 아이디어입니다. 여기에 나는 아이디어의 본질을 열거한다.

제가 따라하는 순서를보십시오 :

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')

사용자에 대한 추가 권한을 부여하거나 데이터베이스를 TRUSTWORTHY ON 옵션으로 설정하거나 WITH EXECUTE AS OWNER 절이있는 프로 시저를 만들지 않고도 모든 필사 사용자의 IP 주소를 캡처 할 수 있습니다.


일반 정보

현재 연결 정보를 얻는 방법에는 두 가지가 있습니다.

  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 DECLARE @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에 대한 EXANT를 사용자에게 부여합니다.

    당면한 문제에 대한 자세한 기사와 답변, 그리고 데이터베이스를 TRUSTWORTHY 모드로 설정 (사용하기 전에 아래의 첫 번째 링크 읽기) 및 인증 자 신뢰 및 기타 방법과 같은 많은 권장 솔루션이 있습니다. 아래 링크에서 찾을 수 있습니다.

    참고 : TRUSTWORTHY 속성을 사용하여 @SteveFord 응답을 확인할 수 있습니다.

  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%'

client_net_address MSDN

이 서버에 연결하는 클라이언트의 호스트 주소. Null을 허용합니다.


CREATE PROCEDURE 문에서 EXECUTE AS OWNER 문 사용 :

MSDN

사용자가 CALLER 이외의 컨텍스트에서 실행하도록 지정된 모듈을 실행하면 모듈을 실행할 수있는 사용자 권한이 검사되지만 모듈에서 액세스하는 개체에 대한 추가 사용 권한 확인은 해당 모듈에서 지정한 사용자 계정에 대해 수행됩니다. EXECUTE AS 절. 모듈을 실행하는 사용자는 사실상 지정된 사용자로 가장합니다.

모듈의 EXECUTE AS 절에 지정된 컨텍스트는 모듈 실행 기간 동안 만 유효합니다. 컨텍스트는 모듈 실행이 완료되면 호출자에게 되돌아갑니다.

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

그런 다음 사용자에게 저장 프로 시저를 실행할 수있는 권한을 부여해야합니다.

올바른 사용 권한을 만들기위한 업데이트

데이터베이스를 신뢰할 수있는 상태로 설정해야합니다 (데이터베이스를 신뢰할 수 있도록 설정 :

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];

나는 이것을 테스트했으며 이것은 예상대로 작동한다.





ip