Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 заблокировать пользователя до определенной даты  [new]
MMM_Corp
Member

Откуда: Тернопіль, Україна
Сообщений: 2102
Добрый день,

имеется: mssql 2012

имеется задача: блокировать пользователя до определенной даты
тоесть админ вводит дату до которой нужно забанить юзера, наступила дата - система должна его розбанить, админ может еще до того времени разбанить раньше пользователя или изменить дату розбана

ищу качественные путы решения задачи

как мне кажется здесь нужно сделать примерно так:
если есть дата блокировки, то:
1. блокировать пользователя
login ... disable

2. создать джоб для разблокирвоки, при этом как то нужно идентифицировать этот джоб чтобы можно было удалить джоб если админ раньше разблочит пользователя или изменит время авторозбана
3. когда сработает джоб он должен розбанить юзера и затереть время авторозбана

вот примерно так я себе это представляю, но хотел бы сначала узнать мнение комюнити может есть способ проще или корректнее?

Спасибо!
7 апр 13, 17:09    [14147557]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Куда уж проще.
7 апр 13, 17:47    [14147665]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
З.Ы. Джоб один на всех и удалять не надо.
7 апр 13, 17:48    [14147666]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
MMM_Corp
Member

Откуда: Тернопіль, Україна
Сообщений: 2102
Гавриленко Сергей Алексеевич
З.Ы. Джоб один на всех и удалять не надо.


проблема в том когда запускать этот 1 джоб?) раз в минуту? раз в секунду? нет, так не подходит, ведь кроме даты еще учитывать и время надо!
7 апр 13, 18:25    [14147772]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
MMM_Corp,

Джоб запускайте раз в минуту. Для хранения даты и времени блокировки следует использовать, как это ни странно, дополнительную таблицу.
7 апр 13, 19:29    [14148023]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
MMM_Corp,

Джоб можно запускать раз в сутки.
7 апр 13, 19:44    [14148067]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Ennor Tiegael
MMM_Corp,

Джоб можно запускать раз в сутки.
"Раз в сутки" не обеспечит приемлемой интерактивности процесса разбаниванивания.
7 апр 13, 21:16    [14148317]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
Гавриленко Сергей Алексеевич
Ennor Tiegael
MMM_Corp,

Джоб можно запускать раз в сутки.
"Раз в сутки" не обеспечит приемлемой интерактивности процесса разбаниванивания.

Ну да, "модераторы делятся опытом"

PS Да раз в минуту - нормально. Но желательно тогда вести журналирование запусков дабы избежать повторного запуска "разблокировщика/блокировщика" при уже запущенном (и работающем) экземпляре. Вдруг кто-то решит "запаузить" полторы тыщи логинов, а потом разом "выпустить джина из бутылки"...
7 апр 13, 23:56    [14148886]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
SIMPLicity_
Да раз в минуту - нормально. Но желательно тогда вести журналирование запусков дабы избежать повторного запуска "разблокировщика/блокировщика" при уже запущенном (и работающем) экземпляре. Вдруг кто-то решит "запаузить" полторы тыщи логинов, а потом разом "выпустить джина из бутылки"...
Нельзя запустить два экземляра одного и того же джоба физически. В первом приближении, после этого в простых случаях не надо никакой синхронизации.
8 апр 13, 00:53    [14149091]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
Гавриленко Сергей Алексеевич
SIMPLicity_
Да раз в минуту - нормально. Но желательно тогда вести журналирование запусков дабы избежать повторного запуска "разблокировщика/блокировщика" при уже запущенном (и работающем) экземпляре. Вдруг кто-то решит "запаузить" полторы тыщи логинов, а потом разом "выпустить джина из бутылки"...
Нельзя запустить два экземляра одного и того же джоба физически. В первом приближении, после этого в простых случаях не надо никакой синхронизации.


Да, но процедуру-то, которая ВЫПОЛНЯЕТ действия (если конечно назначенное задание запускает процедуру разблокировки), ни чего не мешает запустить "ручками" (например, из какой-нибудь "аппликашки")...
8 апр 13, 01:06    [14149116]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
sphinx_mv
Member [заблокирован]

Откуда:
Сообщений: 1672
SIMPLicity_
Гавриленко Сергей Алексеевич
пропущено...
Нельзя запустить два экземляра одного и того же джоба физически. В первом приближении, после этого в простых случаях не надо никакой синхронизации.


Да, но процедуру-то, которая ВЫПОЛНЯЕТ действия (если конечно назначенное задание запускает процедуру разблокировки), ни чего не мешает запустить "ручками" (например, из какой-нибудь "аппликашки")...
А что говорит теория про уровни изоляции транзакций?
8 апр 13, 01:09    [14149125]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
sphinx_mv
SIMPLicity_
пропущено...


Да, но процедуру-то, которая ВЫПОЛНЯЕТ действия (если конечно назначенное задание запускает процедуру разблокировки), ни чего не мешает запустить "ручками" (например, из какой-нибудь "аппликашки")...
А что говорит теория про уровни изоляции транзакций?


Насчёт теории не знаю,- я в "скуле" слабоват.
Но вот практика говорит, что "перегибать палку" не стоит.
8 апр 13, 02:38    [14149267]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
SIMPLicity_
Гавриленко Сергей Алексеевич
пропущено...
Нельзя запустить два экземляра одного и того же джоба физически. В первом приближении, после этого в простых случаях не надо никакой синхронизации.


Да, но процедуру-то, которая ВЫПОЛНЯЕТ действия (если конечно назначенное задание запускает процедуру разблокировки), ни чего не мешает запустить "ручками" (например, из какой-нибудь "аппликашки")...
Да никто не спорит. Именно поэтому я написал "в первом приближении". Я просто надеюсь, что автору надо решать свою задачу, а не "рельсу под бензопилу" подкладывать.
8 апр 13, 04:27    [14149380]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Гавриленко Сергей Алексеевич
Ennor Tiegael
MMM_Corp,

Джоб можно запускать раз в сутки.
"Раз в сутки" не обеспечит приемлемой интерактивности процесса разбаниванивания.
А это ТС решать уже :) Если у его юзеров каждая секунда простоя - это потерянные деньги, то я бы не рекомендовал делать это через джоб вообще. В данном случае лучше сделать через Service Broker, посредством BEGIN CONVERSATION TIMER.
Ну а если это выход из отпуска, допустим, то запуск джоба каждую полночь, имхо, будет вполне достаточен.
8 апр 13, 04:49    [14149384]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Ennor Tiegael
Гавриленко Сергей Алексеевич
пропущено...
"Раз в сутки" не обеспечит приемлемой интерактивности процесса разбаниванивания.
А это ТС решать уже :) Если у его юзеров каждая секунда простоя - это потерянные деньги, то я бы не рекомендовал делать это через джоб вообще. В данном случае лучше сделать через Service Broker, посредством BEGIN CONVERSATION TIMER.
Ну а если это выход из отпуска, допустим, то запуск джоба каждую полночь, имхо, будет вполне достаточен.
"Бан" пользователей последующая и забота об их "секундах простоя"? Ну, мне от этого сочетания даже как-то смешно становится.

З.Ы. Джоб можно сделать бесконечным, причем с интерактивностью не хуже секунды.
8 апр 13, 05:18    [14149387]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Гавриленко Сергей Алексеевич,

В оригинале было
MMM_Corp
блокировать пользователя до определенной даты
Во-первых, предметная область озвучена так и не была, что позволяет предлагать любые решения. Во-вторых, для "даты" запуск джоба раз в сутки как раз нормально.
8 апр 13, 06:17    [14149400]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гавриленко Сергей Алексеевич
Куда уж проще.

Logon trigger вроде проще?
8 апр 13, 09:20    [14149578]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33997
Блог
Гость333
Logon trigger вроде проще?


и пользователя не блокировать, а "перемещать" в роль, где он ничего не сможет сделать, кроме как залогинится
8 апр 13, 11:09    [14150066]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33997
Блог
Гость333,

аа, понял, вы скорее всего имели ввиду сразу там делать проверку таблички со временем блокирования )
8 апр 13, 11:10    [14150075]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Критик
Гость333,

аа, понял, вы скорее всего имели ввиду сразу там делать проверку таблички со временем блокирования )

Да, это и имел в виду :-)
8 апр 13, 11:23    [14150150]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
MMM_Corp
Member

Откуда: Тернопіль, Україна
Сообщений: 2102
хм.... спасибо за идеи, к сожалению ниодна по ряду причин не подходит

делюсь как сделал создание/редактирование юзера/админа с возможностью авторозбана:
CREATE PROCEDURE dbo.LoginCreateEdit
  @op_type  INT, -- 0 - создание, 1 - редактирование  
  @type     INT, -- 0 - админ, 1 - пользователь
  @id       INT, -- id юзера
  @username VARCHAR(50)=null,
  @password VARCHAR(50),
  @change_password BIT,
  @banned_todate DATETIME,
  @disable_logon BIT
  --,@sname    VARCHAR(50)
AS
BEGIN
BEGIN TRANSACTION B1;
--  IF @banned_todate<getdate
DECLARE @SQL NVARCHAR(4000)='';
DECLARE @n VARCHAR(1) = char(13);

-- блокируем пользователя если дата блокировки стоит из будущего
IF (@banned_todate IS NOT null) AND (@banned_todate>getdate())
   SET @disable_logon=1
  ELSE
   SET @banned_todate=NULL;


-- запоминаем старый логин
DECLARE @new_username VARCHAR(50)=@username; 
-- получаем логин из сида если идет редактирование записи
IF @op_type=1
  SET @username = (SELECT TOP 1 s.[name] 
                   FROM sys.sql_logins s, options 
                   WHERE options.id=@id AND options.bsid=s.sid);

-- PRINT 'username: '+@username;

IF @username IS null 
  BEGIN
  PRINT N'Пользователь не найден!';
  RAISERROR (N'Пользователь не найден!', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
  RETURN;
  END;



-- CREATE
IF @op_type=0
  BEGIN
  -- PRINT 'CREATE';

  SET @SQL = @SQL +
  N'CREATE LOGIN ['+ @username +'] WITH PASSWORD = '''+ @password +''',
	  DEFAULT_DATABASE=[' + db_name() + '], DEFAULT_LANGUAGE=[Russian], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;' +@n;

  SET @SQL = @SQL +
    CASE @type
      WHEN 0 THEN    
        N'ALTER SERVER ROLE sysadmin ADD MEMBER ['+ @username +'];'
      WHEN 1 THEN     
        N'CREATE USER ['+ @username +'] FOR LOGIN ['+ @username +']; 
        ALTER ROLE client ADD MEMBER ['+ @username +'];'
    END;

  END;




-- select @SQL;

-- EDIT
IF @op_type=1
  BEGIN
  -- PRINT 'EDIT';
  -- DECLARE @s VARBINARY(MAX) = suser_sid(@username);

  -- меняем логин если нужно
  SELECT @new_username, @username;

  IF @new_username!=@username
  BEGIN
  SET @SQL = @SQL +
    N'ALTER LOGIN ['+@username+'] WITH NAME = ['+@new_username+'];'+@n;

  SET @SQL = @SQL +
    N'ALTER USER ['+@username+'] WITH NAME = ['+@new_username+'];'+@n;

  -- работаем уже с новым логином
  SET @username=@new_username;
  END;

  -- меняем пароль
  IF @change_password=1
  SET @SQL = @SQL +
    N'ALTER LOGIN ['+@username+'] WITH PASSWORD = '''+@password+'''' +@n;      

  -- меняем роль      
-- ROLE EDIT
  -- админ -> юзер 
  IF (is_srvrolemember('sysadmin', @username)=1) AND (@type=1)
       SET @SQL = @SQL +
        N'alter server role sysadmin drop member ['+@username+'];
        CREATE USER ['+ @username +'] FOR LOGIN ['+ @username +'];
        ALTER ROLE client ADD MEMBER ['+ @username +']; '+@n;

  
  
  -- юзер -> админ 
  IF (is_srvrolemember('sysadmin', @username)=0) AND (@type=0)
       SET @SQL = @SQL +
        N'ALTER ROLE client DROP MEMBER ['+ @username +'];
        drop user ['+ @username +'];    
        alter server role sysadmin ADD member ['+@username+'];'+@n;


END;




-- блокируем пользователя
DECLARE @sesufix VARCHAR(20);
IF @disable_logon=0 
    SET @sesufix='ENABLE'
  ELSE 
    SET @sesufix='DISABLE'

SET @SQL = @SQL +
   N'ALTER LOGIN ['+@username+'] '+@sesufix+';' +@n;



--   END;


-- PRINT '@sql';
PRINT @sql;
EXEC sp_executesql @sql;

-- вставляем запись в настройки, еслт это новый юзер
IF @op_type=0
  INSERT INTO options(bsid) VALUES(suser_sid(@username));

-- сохранем дату розбана
UPDATE options
  SET date_banned=@banned_todate
WHERE bsid=suser_sid(@username);


-- пересоздаем джоб
SET @SQL ='';
SET @id=NULL;
SET @id=(SELECT TOP 1 id FROM options WHERE bsid = suser_sid(@username));
DECLARE @svarid VARCHAR(100) = convert(VARCHAR(100), @id);

IF @id IS null 
  BEGIN
  PRINT N'Пользователь для планировщика не найден!';
  RAISERROR (N'Пользователь не найден!', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
  RETURN;
  END;


  -- удаляем старый джоб если он был создан 
   IF (@id IS NOT NULL) and (SELECT count(*) FROM msdb.dbo.sysjobs where name='LoginAutoBanEnable_'+@svarid)=1
      SET @SQL = @SQL +N'EXEC msdb.dbo.sp_delete_job 
          @job_name = ''LoginAutoBanEnable_'+@svarid+''';';


  -- создание джоба если дата розбана с будущего
  IF @banned_todate>getdate()
    begin  
    DECLARE @SheduleName VARCHAR(200) = 'LoginAutoBanEnable_'+@svarid+'_'+convert(VARCHAR(50), convert(INT, @banned_todate));

    SET @SQL = @SQL + N'EXEC msdb.dbo.sp_add_job
      @job_name = ''LoginAutoBanEnable_'+@svarid+''',
      @delete_level = 3;
      
      EXEC msdb.dbo.sp_add_jobstep
        @job_name = N''LoginAutoBanEnable_'+@svarid+''',
        @step_name = N''Auto enable disabled login'',
        @subsystem = N''TSQL'',
        @command = N''ALTER LOGIN ['+@username+'] ENABLE;'',
        @retry_attempts = 1,
        @retry_interval = 0 ;
      
      EXEC msdb.dbo.sp_add_jobserver 
        @job_name = N''LoginAutoBanEnable_'+@svarid+''',
        @server_name = N''(local)'';

      EXEC msdb.dbo.sp_add_schedule
        @schedule_name = N'''+@SheduleName+''',
        @freq_type = 1,
        @enabled = 1,
        @originating_server = NULL,
        @active_start_date = '+convert(varchar(10), @banned_todate, 112)+',
        @active_start_time = '+replace(convert(varchar,@banned_todate,108), ':', '')+';
      
      EXEC msdb.dbo.sp_attach_schedule
        @job_name = N''LoginAutoBanEnable_'+@svarid+''',
        @schedule_name = N'''+@SheduleName+''';'; 
    END;
   

PRINT @sql;
EXEC sp_executesql @sql;

COMMIT TRANSACTION B1;
END;

GO


работает исправно, прошу попинать

П.С. Для удаления пользователей используется другая хранимка, в ней идет проверка наличия джоба и его удаление
11 апр 13, 07:23    [14165101]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
На каждого юзера по джобу... Ничотак.

Ябытакнеделал... (это комментарий типа)
11 апр 13, 18:56    [14169165]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
MMM_Corp
Member

Откуда: Тернопіль, Україна
Сообщений: 2102
а как бы вы делали, и что такоо страшного в джобе на даждого баненого когда важно розбанить вовремя? объясните плиз, я с джобами работал очень мало, может чего не понимаю????
11 апр 13, 19:09    [14169244]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
MMM_Corp,

Эээ... Создайте табличку, куда пишите заблокированных пользователей и время, когда надо разблокировать. Раз в минуту ОДИН джоб ходит, разблокирует пользователей и удаляет записи разблокированных из таблички.

Сообщение было отредактировано: 11 апр 13, 19:51
11 апр 13, 19:50    [14169379]     Ответить | Цитировать Сообщить модератору
 Re: заблокировать пользователя до определенной даты  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
MMM_Corp,

Ну вы и нагородили...

Служебная табличка в вашей БД:
use MyDB;
go

create table dbo.DisabledLogins
(
 dl_LoginName sysname not null constraint PK_DisabledLogins primary key,
 dl_DisabledTill datetime not null
);
go

grant select on dbo.DisabledLogins to public;
go
Триггер входа:
use master;
go

if exists(select 1 from sys.server_triggers where name = 'trSRV_CheckLogin')
 drop trigger trSRV_CheckLogin on all server;
go

create trigger trSRV_CheckLogin
on all server
for logon
as
begin
 set nocount on;
 
 if has_dbaccess('MyDB') <> 1
  return;
 
 declare @l sysname = original_login();
 
 if exists(select 1 from MyDB.dbo.DisabledLogins where dl_LoginName = @l and dl_DisabledTill >= getdate())
  begin
   raiserror('trSRV_CheckLogin: Login "%s" disabled', 16, 1, @l);
   rollback;
  end;
end;
go
11 апр 13, 20:32    [14169533]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить