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

Откуда:
Сообщений: 76
Доброе время суток.

Существует задача: Есть MSSQL Server 2005. На нем 10 баз. В каждой базе есть два логина: user1 и user2. В любой момент, к базе (к базе!!! а не к серверу) может быть подключен только один user1 или user2.

Пытался сделать триггер FOR LOGON , но он действует на весь сервер. Но проверка по логину не прокатывает, так как логины могут быть подключены к разным базам: user1 который имеет доступ к base1 подключен к ней, следовательно я блокирую user2 в триггере, но этот user2 может принадлежать вовсе не к table1 , а к table3 и я ему должен разрешить подключение к его базе. Пытался сделать проверку не только логина но и таблы, но бывает момент, что логины обращаются к общим системным таблам сервера.

В Delphi есть Семафоры для таких случаев, а как сделать в MSSQL , чтобы к конкретной базе !! был подключен только 1 логин.

спасибо.
3 июл 09, 20:59    [7375790]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Crimean
Member

Откуда:
Сообщений: 13148
для начала предлагаю убрать терминологическую путаницу. логины - они серверные. логины "допущены" на базы в виде пользователей баз данных. так работает сервер
теперь вопрос - а как вы определяете к какой базе кто подключается?
в зависимости от ответа будут предложены решения :)
подсказка - может ваше по построено так, что для каждого запроса делается "use". а может у вас чего еще на прикладном уровне происходит. в любом случае абстрактно можно говорить про 2 типа решений - системный и прикладной. системный будет заключаться в правдами / неправдами пустить / не пустить логин на базу общесерверными средствами. прикладной - сделать тоже самое но на уровне вашего приложения
3 июл 09, 21:04    [7375805]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Crimean
Member

Откуда:
Сообщений: 13148
p.s.
а базы перевести в single_user - не предлагать? :)
3 июл 09, 21:07    [7375813]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
и вообще у sys.sysprocesses.dbid может стоять одно,
а сама команда выглядеть типа такого: exec db3.dbo.someProc ...
3 июл 09, 21:31    [7375843]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Crimean
Member

Откуда:
Сообщений: 13148
BusyMan
и вообще у sys.sysprocesses.dbid может стоять одно,
а сама команда выглядеть типа такого: exec db3.dbo.someProc ...


я подозреваю, что и в этом случае однопользовательность поможет (проверил - да). только вот практической пользы от этого помогания извлечь не получится :) особенно если хранимки оч быстро отрабатывают
3 июл 09, 21:34    [7375850]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
Прикладной уровень не поможет. Нет исходников к клиентскому ПО. use и указание базы нигде не делается

Однопользовательский режм не предлагать. Наш админ БД объяснил чем это черевато.

не совсем понял вопроса про "а как вы определяете к какой базе кто подключается?
" , но возможно вы имели ввиду следующее. Настраиваю в Администрирование - ODBC соединение (типа название соединений base1 , base2) или видел в старых исходниках , что в самом коде прописана строка подключения и в ней определяется к какой базе подключаться .
3 июл 09, 22:00    [7375891]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> Прикладной уровень не поможет. Нет исходников к клиентскому ПО. use и указание базы нигде не делается

как вариант - заводить кучу логинов. да, неудобно, но задача будет решена

> Однопользовательский режм не предлагать. Наш админ БД объяснил чем это черевато.

да, работает хреново, если не делается "use" я пробовал :) в смысле то оба работают то оба отваливаются. хотя, может ваш админ объяснял чего другое?

> не совсем понял вопроса про "а как вы определяете к какой базе кто подключается?"

тогда нам не о чем говорить, вы не "носитель проблемы". как можно решать вопрос ограниечния доступа, не представляя, как доступ предоставляется? мы собираемся что-то где-то ограничить. если неизвестно что и где - как ограничивать?
4 июл 09, 12:40    [7376556]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

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

1) кучу логинов не катит. все скрипты завязаны на два логина.

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

3) "носитель или нет проблемы" не в этом суть. Задачас стоит передо мной. Человек ушел и приходиться разбираться мне. ТО, к какой базе коннектиться уточнил ,задается именно в ODBC. Программам в ini файле фала задается строка строка подключения, где идет ссылка на конкретный ODBC.

Я вычитал на сайте MSDN , что можно узнать базу, которая указана в строке подключения к серверу. Может это поможет.

А нет ли возможности повесить триггер на CONNECT к базе ?
4 июл 09, 14:46    [7376729]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
Вот такие строки подключения в ini файлах к приложениям. Отличаются по user , initial Catalog

"Base1_ConnectionString"="Provider=SQLNCLI.1;Password=test;Persist Security Info=True;User ID=user1;Initial Catalog=filial-1;Data Source=APPServer"
4 июл 09, 16:33    [7376850]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
Предполагаю, что в Logon триггере можно проверить original_db_name, только вот как узнать какое значение этой самой original_db_name у других подконнектившихся к серваку. в какой системной табле искать.
4 июл 09, 16:37    [7376854]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31194
alolya
Предполагаю, что в Logon триггере можно проверить original_db_name, только вот как узнать какое значение этой самой original_db_name у других подконнектившихся к серваку. в какой системной табле искать.
select * from sys.sysprocesses where spid
 = @@spid, поле dbid
4 июл 09, 18:00    [7376896]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

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

в MSSQL 2005 оказывается нет ORIGINAL_DB_NAME.

делаю так:

IF ORIGINAL_LOGIN()= 'user1'
and
(SELECT COUNT(*) FROM sys.sysprocesses sp
join sys.syslogins L on L.sid=sp.sid
WHERE sp.loginame='user2' and L.dbname=
(select SL.dbname from sys.syslogins SL
where sid=SUSER_SID(ORIGINAL_LOGIN())) )>0
ROLLBACK;

IF ORIGINAL_LOGIN()= 'user2'
and
(SELECT COUNT(*) FROM sys.sysprocesses sp
join sys.syslogins L on L.sid=sp.sid
WHERE sp.loginame='user1' and L.dbname=
(select SL.dbname from sys.syslogins SL
where sid=SUSER_SID(ORIGINAL_LOGIN())) )>0
ROLLBACK;

НЕ РАБОТАЕТ. Оба могут подключаться.
4 июл 09, 18:45    [7376946]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

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

указанный вами dbid - это идентификатор базы используемый в данный момент!!! Это не то, что мне нужно.
4 июл 09, 19:04    [7376956]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Glory
Member

Откуда:
Сообщений: 104760
alolya
alexeyvg,

указанный вами dbid - это идентификатор базы используемый в данный момент!!! Это не то, что мне нужно.

Почему не то, если в первом сообщении вы утверждали, что "В любой момент, к базе (к базе!!! а не к серверу) может быть подключен только один user1 или user2. "
Если у user2 в данный момент текущей базой является база xxx, то почему user1 не может подключиться и использовать базу yyy ?
4 июл 09, 19:13    [7376965]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

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

user1 иожет быть подключен сейчас к MASTER базе или TEMPDB, а user2 подключен к своей базе по умолчанию, к примеру MyBASE. И в один из моментов user2 отключился на небольшйо промежуток времени от MyBase и подключился к TEMPDB, а User1 в этот момент переключился на MyBase. Но такая ситуация для меня не допустима. Нужно чтобы USER1 и USER2 не могли одновременно иметь подключения к серваку. Так как такие логины на 10 базах, входящих в сервер, то я различаю их по Базе данных по умолчанию, которая установлена в строке подключения. Т.е. в сервере могут быть по 5 одновременных USER1 и User2 , но у всех у них разные базы данных по умолчанию.
4 июл 09, 19:33    [7376980]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Glory
Member

Откуда:
Сообщений: 104760
alolya
Нужно чтобы USER1 и USER2 не могли одновременно иметь подключения к серваку.

Чего вы тогда парите мозги про базы какие-то ? Триггер на логон должен проверять наличие соединения для логина
4 июл 09, 20:29    [7377018]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
Glory, и каким образом вы это можете сделать? На данном сайте этот впорос кем то уже поднимался, но так ответа и не было найдено.
4 июл 09, 21:24    [7377052]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

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

К серваку может быть подсоединено 5 логинов User1 , и 5 логинов User2. При чем, в рамках одной базы не могут быть одновременно подсоединены user1 и user2. Триггер на LOGON работает на сервер и не дает нужной проверки без учета базы данных по умолчанию для каждого из логинов. А вытащить эту самую базу по умолчанию , указанную в натсройках подключения клиентского ПО я не моуг ни из одной системной таблицы
4 июл 09, 21:29    [7377054]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Glory
Member

Откуда:
Сообщений: 104760
IF ORIGINAL_LOGIN()= 'user1' 
    and EXISTS(SELECT * FROM sys.sysprocesses where loginame = 'user2')
ROLLBACK;

IF ORIGINAL_LOGIN()= 'user2' 
    and EXISTS(SELECT * FROM sys.sysprocesses where loginame = 'user1')
ROLLBACK;
4 июл 09, 21:29    [7377055]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Glory
Member

Откуда:
Сообщений: 104760
alolya
Glory,

К серваку может быть подсоединено 5 логинов User1 , и 5 логинов User2. При чем, в рамках одной базы не могут быть одновременно подсоединены user1 и user2. Триггер на LOGON работает на сервер и не дает нужной проверки без учета базы данных по умолчанию для каждого из логинов. А вытащить эту самую базу по умолчанию , указанную в натсройках подключения клиентского ПО я не моуг ни из одной системной таблицы

ТТ просто.
Если пользователь не менял базу, которую указал при коннекте, то она и будет его текущей базой.
А если пользователь поменял текущую базу, то какая разница, что там у него было указано в строке коннекта
4 июл 09, 21:36    [7377066]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
Glory
IF ORIGINAL_LOGIN()= 'user1' 
    and EXISTS(SELECT * FROM sys.sysprocesses where loginame = 'user2')
ROLLBACK;

IF ORIGINAL_LOGIN()= 'user2' 
    and EXISTS(SELECT * FROM sys.sysprocesses where loginame = 'user1')
ROLLBACK;


вы не внимательно прочли мой последний пост. То что вы написали работать не будет так как нужно мне. В вашем случае только пользователи USER1 будет иметь коннект в рамках сервера.
4 июл 09, 22:17    [7377109]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
pil0t
Member

Откуда:
Сообщений: 65
alolya,
а если написать трейсер (как профайлер) отслеживающий запросы, который при попытки использовать запрещенную базу запрещенным пользователем убивает коннект?
5 июл 09, 01:09    [7377331]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Как вариант:

Создать логон триггер, который будет проверять если к базе подключения user1 и user2

и в случае если подключений нет обоих юзеров, и подключается user1, то
1) восстанавливать право подключения user1 (которое может быть убрано п.2)
2) забирать права на доступ user2
3) разрешать подключение user1

В случае, если подключение уже есть какого-то пользователя, то:
1) разрешать подключение аналогичному,
2) давать отлуп другому пользователю.
5 июл 09, 01:50    [7377385]     Ответить | Цитировать Сообщить модератору
 Re: Создать подобие Семафора. Запретить 2 коннекта  [new]
alolya
Member

Откуда:
Сообщений: 76
После суток чтения MSDN нашел решение:

create TRIGGER [connection_limit_trigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN

IF ORIGINAL_LOGIN()= 'user1'
and
(SELECT COUNT(*) FROM sys.dm_exec_sessions SK
WHERE SK.original_login_name='user2' and SK.program_name=
(select SS.program_name from sys.dm_exec_sessions SS
where SS.session_id=@@spid))>0
begin
ROLLBACK;
end

IF ORIGINAL_LOGIN()= 'user2'
and
(SELECT COUNT(*) FROM sys.dm_exec_sessions SK
WHERE SK.original_login_name='user1' and SK.program_name=
(select SS.program_name from sys.dm_exec_sessions SS
where SS.session_id=@@spid))>0
begin
ROLLBACK;
end
END;

важно !!! 1) Чтобы у логинов user1 и user2 были права на чтения всех записей в sys.dm_exec_sessions , например присвоить им ProcessAdmin. В проивном случае триггер работать не будет.
2) чтобы был накатан SP2 на MSSQL иначе триггер не станет.
3) program_name должен быть одинаковый у USER1 и USER2 в рамках той базы к которой они будут подключаться. Прописывается в строке подключения, как параметр APPLICATION NAME
5 июл 09, 03:28    [7377453]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить