[MANUAL] Защита от брута средствами MsSQL

Тема в разделе "Установка и настройка (Setup and configuration)", создана пользователем root, 29 сен 2014.

  1. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    254
    Симпатии:
    58
    Баллы:
    11
    При частых попытках захода на аккаунт с неверным паролем (более 5 раз за 15 минут с интервалом менее 2с) блокируется аккаунт на 15 минут, меняя пароль на рандомный.
    Код:
    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON 
    go 
    
    
    ALTER PROCEDURE [dbo].[ap_GPwd] @account varchar(14), @pwd binary(16) output 
    AS 
    --exec ap_GPwd_log @account 
    
    
    -- Created By darkangel 
    -- 2003.06.19 
    -- updated 2006.05.14 by KEMBL 
    -- set Account's auth logging 
    -- updated 2006.08.31 Logrus, Drone 
    -- added login counts per time check 
    -- update 2006.10.25 by Stpavel 
    -- added random generate password 
    
    
    DECLARE @RandPass varchar(32) 
    DECLARE @counter smallint 
    DECLARE @RandNum float 
    DECLARE @RandNumInt tinyint 
    DECLARE @CurrChar varchar(1) 
    DECLARE @ValidChar varchar(255) 
    SET @ValidChar = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$' 
    DECLARE @ValidCharLength int 
    SET @ValidCharLength = len(@ValidChar) 
    SET @CurrChar = '' 
    SET @RandNum = 0 
    SET @RandNumInt = 0 
    SET @RandPass = '' 
    
    
    
    
    SET NOCOUNT ON 
    
    
    if (SELECT COUNT(*) FROM lin2db..user_auth_log WHERE [email protected] AND DATEDIFF(mi, dt, GETDATE()) < 16) > 4 
    OR 
    (SELECT COUNT(*) FROM lin2db..user_auth_log WHERE [email protected] AND DATEDIFF(ss, dt, GETDATE()) < 3) > 0 
    BEGIN 
    INSERT INTO user_auth_log (account,success) VALUES (@account, 0) 
    
    
    SET @counter = 1; 
    WHILE @counter < (17) 
    BEGIN 
    SET @RandNum = Rand(); 
    SET @RandNumInt = Convert(tinyint, ((@ValidCharLength - 1) * @RandNum + 1)) 
    
    
    SELECT @CurrChar = SUBSTRING(@ValidChar, @RandNumInt, 1); 
    SET @counter = @counter + 1; 
    SET @RandPass = @RandPass + @CurrChar; 
    END 
    
    
    SET NOCOUNT OFF 
    
    
    SELECT @pwd=convert(binary(16),@RandPass); 
    
    
    END 
    ELSE 
    BEGIN 
    INSERT INTO user_auth_log (account,success) VALUES (@account, 1) 
    SET NOCOUNT OFF 
    SELECT @pwd=password FROM user_auth with (nolock) WHERE [email protected] 
    END