none
trigger a práva s KILL

    Dotaz

  • Zdravím všechny,

    můžete mi poradit jak provedu následující? Je to trigger na celý server. Všechno funguje tak jak má, jen bych chtěl zapsat zabité spojení do tabulky, a to se mi nepovede. Tedy povede, ale jen v okamžiku, kdy zaremuji ten řádek EXEC (KILL).

    Pak se mi to sice zapíše, ale zase mi to nezabije to spojení. Je mi jasné, že někde v tom bude vada, ale už nevím co bych zkusil. Zkusil jsem pod jiným userem ten INSERT i ten KILL, ale nějak mi to pořád nefunguje. 

    Díky za každou radu

    Petr

    FETCH NEXT FROM LoginCursor INTO @session_id
    WHILE (@@fetch_status <> -1) 
    BEGIN 
    	IF (@@fetch_status <> -2) 
    	BEGIN
    	 BEGIN TRANSACTION
    	     EXECUTE AS LOGIN = 'zapis'
    	       INSERT INTO master.dbo.Odmitnuti (HOST, PROGRAM,IP,NAME)
    	       SELECT DISTINCT master.sys.dm_exec_sessions.host_name, master.sys.dm_exec_sessions.program_name, 
                      master.sys.dm_exec_connections.client_net_address, master.sys.dm_exec_sessions.original_login_name
               FROM   master.sys.dm_exec_connections INNER JOIN
                      master.sys.dm_exec_sessions ON master.sys.dm_exec_connections.session_id = master.sys.dm_exec_sessions.session_id
               WHERE  master.sys.dm_exec_connections.session_id = @session_id 
               REVERT
         COMMIT TRANSACTION
             
        BEGIN TRANSACTION    
         SET @Prik = 'KILL ' + @session_id	             
    	 EXEC (@Prik) 
        COMMIT TRANSACTION
        END 
    	FETCH NEXT FROM LoginCursor INTO @session_id
    

    10. listopadu 2013 18:59

Odpovědi

  • Pokud tedy nema byt povolen pristup v momente pristupu nekoho, kdo nesplnuje podminky, pak bych trigger napsal nasledovne.

    USE [master]
    GO
    ALTER TRIGGER limit_logins
    ON ALL SERVER WITH EXECUTE AS 'zapis'
    FOR LOGON
    AS
    BEGIN
    	DECLARE
    		@session_ID int,
    		@host nvarchar(128),
    		@program_name nvarchar(128),
    		@client_net_address varchar(40),
    		@login_name nvarchar(128);
    
    	SELECT
    		@session_ID = s.session_id,
    		@host = s.host_name,
    		@program_name = s.program_name,
    		@client_net_address = c.client_net_address,
    		@login_name = s.original_login_name
    	FROM master.sys.dm_exec_sessions s
    	INNER JOIN master.sys.dm_exec_connections c on s.session_id = c.session_id
    	WHERE
    		(
    			(
    				NOT (c.client_net_address LIKE N'<local%') 
    				AND 
    				NOT (c.client_net_address LIKE N'192.168.2.%') 
    				AND 
    				NOT (c.client_net_address LIKE N'172.27.232.%')
    			)
    			AND 
    			(
    				NOT (s.original_login_name LIKE N'%dministrator')
    			) 
    			AND 
    			(
    				NOT (s.program_name LIKE N'INSYS%')
    			)
    		)
    		AND
    		s.original_login_name = ORIGINAL_LOGIN()
    		AND
    		s.session_id = @@SPID
    
    	IF (@session_ID IS NOT NULL)
    	BEGIN
    		ROLLBACK;
    		INSERT INTO master.dbo.Odmintnuti(HOST, PROGRAM, IP, NAME) VALUES(@host, @program_name, @client_net_address, @login_name)
    	END
    END

    Prvni cast triggeru bezi v originalni implicitni transakci. Ta zjisti udaje o aktualne pristupujicim uzivateli, programu a pod.

    Pokud nesplnuje podminky, tzn select vrati nejaky zanam, pak se provede ROLLBACK puvodni implicitni transakce. Pokud je v ramci originalni implicitni transakce proveden ROLLBACK, pak se lOGIN nezdari. Logovani pak probiha v nasledujici transakci a provede se uspesne.

    Jinak, pokud jde o omezeni pripojieni k SQL Serveru z urcitych IP adres, pak toto bych resil na urovni Firewallu.


    27. listopadu 2013 11:44

Všechny reakce

  • Několik dotazů k problem

    1. Vypíše to nějakou chybovou zprávu nebo to postě jen tiše nezapíše?
    2. Šlo by vidět SELECT statement a deklaraci LoginCursor?
    3. Na jaké události toto tělo trigger pověženo?
    27. listopadu 2013 6:12
  • USE [master]
    GO
    
    /****** Object:  DdlTrigger [tr_LOGON_SERVER]    Script Date: 27. 11. 2013 7:51:24 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    
    CREATE trigger [tr_LOGON_SERVER]
                   ON ALL SERVER
                   FOR LOGON
    AS BEGIN
    DECLARE @Prik varchar(MAX)
    DECLARE LoginCursor CURSOR 
    READ_ONLY 
    FOR SELECT  master.sys.dm_exec_connections.session_id
    FROM        master.sys.dm_exec_connections INNER JOIN
                master.sys.dm_exec_sessions ON master.sys.dm_exec_connections.session_id = master.sys.dm_exec_sessions.session_id
    WHERE (NOT (sys.dm_exec_connections.client_net_address LIKE N'<local%') AND NOT (sys.dm_exec_connections.client_net_address LIKE N'192.168.2.%') AND 
                          NOT (sys.dm_exec_connections.client_net_address LIKE N'172.27.232.%')) AND (NOT (sys.dm_exec_sessions.original_login_name LIKE N'%dministrator')) AND 
                          (NOT (sys.dm_exec_sessions.program_name LIKE N'INSYS%'))
    ORDER BY original_login_name
    
    DECLARE @session_id varchar(10) 
    OPEN LoginCursor
    
    FETCH NEXT FROM LoginCursor INTO @session_id
    WHILE (@@fetch_status <> -1) 
    BEGIN 
    	IF (@@fetch_status <> -2) 
    	BEGIN
    	 BEGIN TRANSACTION
    	     EXECUTE AS LOGIN = 'zapis'
    	       INSERT INTO master.dbo.Odmitnuti (HOST, PROGRAM,IP,NAME)
    	       SELECT DISTINCT master.sys.dm_exec_sessions.host_name, master.sys.dm_exec_sessions.program_name, 
                      master.sys.dm_exec_connections.client_net_address, master.sys.dm_exec_sessions.original_login_name
               FROM   master.sys.dm_exec_connections INNER JOIN
                      master.sys.dm_exec_sessions ON master.sys.dm_exec_connections.session_id = master.sys.dm_exec_sessions.session_id
               WHERE  master.sys.dm_exec_connections.session_id = @session_id 
               --REVERT
         COMMIT TRANSACTION
             
        BEGIN TRANSACTION    
         SET @Prik = 'KILL ' + @session_id	             
    	 EXEC (@Prik) 
        COMMIT TRANSACTION
         
        END 
    	FETCH NEXT FROM LoginCursor INTO @session_id
    END 
    
    CLOSE LoginCursor 
    DEALLOCATE LoginCursor 
    
    END
    
    
    
    GO
    
    SET ANSI_NULLS OFF
    GO
    
    SET QUOTED_IDENTIFIER OFF
    GO
    
    ENABLE TRIGGER [tr_LOGON_SERVER] ON ALL SERVER
    GO
    
    

    Trigger se spustí při přihlášení k serveru. Pokud nevyhoví podmínce, zabije spojení. A chtěl jsem toto spojení zapsat. Funguje buď jedno, nebo druhé. Jinak to funguje k mé spokojenosti, jen ten záznam....

    Petr

    Petr

    27. listopadu 2013 6:54
  • Pokud tedy nema byt povolen pristup v momente pristupu nekoho, kdo nesplnuje podminky, pak bych trigger napsal nasledovne.

    USE [master]
    GO
    ALTER TRIGGER limit_logins
    ON ALL SERVER WITH EXECUTE AS 'zapis'
    FOR LOGON
    AS
    BEGIN
    	DECLARE
    		@session_ID int,
    		@host nvarchar(128),
    		@program_name nvarchar(128),
    		@client_net_address varchar(40),
    		@login_name nvarchar(128);
    
    	SELECT
    		@session_ID = s.session_id,
    		@host = s.host_name,
    		@program_name = s.program_name,
    		@client_net_address = c.client_net_address,
    		@login_name = s.original_login_name
    	FROM master.sys.dm_exec_sessions s
    	INNER JOIN master.sys.dm_exec_connections c on s.session_id = c.session_id
    	WHERE
    		(
    			(
    				NOT (c.client_net_address LIKE N'<local%') 
    				AND 
    				NOT (c.client_net_address LIKE N'192.168.2.%') 
    				AND 
    				NOT (c.client_net_address LIKE N'172.27.232.%')
    			)
    			AND 
    			(
    				NOT (s.original_login_name LIKE N'%dministrator')
    			) 
    			AND 
    			(
    				NOT (s.program_name LIKE N'INSYS%')
    			)
    		)
    		AND
    		s.original_login_name = ORIGINAL_LOGIN()
    		AND
    		s.session_id = @@SPID
    
    	IF (@session_ID IS NOT NULL)
    	BEGIN
    		ROLLBACK;
    		INSERT INTO master.dbo.Odmintnuti(HOST, PROGRAM, IP, NAME) VALUES(@host, @program_name, @client_net_address, @login_name)
    	END
    END

    Prvni cast triggeru bezi v originalni implicitni transakci. Ta zjisti udaje o aktualne pristupujicim uzivateli, programu a pod.

    Pokud nesplnuje podminky, tzn select vrati nejaky zanam, pak se provede ROLLBACK puvodni implicitni transakce. Pokud je v ramci originalni implicitni transakce proveden ROLLBACK, pak se lOGIN nezdari. Logovani pak probiha v nasledujici transakci a provede se uspesne.

    Jinak, pokud jde o omezeni pripojieni k SQL Serveru z urcitych IP adres, pak toto bych resil na urovni Firewallu.


    27. listopadu 2013 11:44
  • Díky,

    tohle dělá to co má, ty IP adresy tam nejsou kvůli přístupu, ale kvůli eliminaci běžících procesů. Jinak mi je to zabíjelo, i když měli běžet.

    Ještě jednou díky

    Petr

    6. prosince 2013 9:02