trigger a práva s KILL

Question

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

Answer 1

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?

Answer 2

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

Answer 3

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.

Answer 4

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