Лог изменений данных

@kyvaldenius · Регистрация: 28.09.2015

Студворк — интернет-сервис помощи студентам

День добрый!
Такой вопрос.

Необходимо сделать историю изменений данных которая хранит сами данные действие время и пользователь который совершил действие

Вопрос как определить пользователя ?
Пользователи берутся из таблицы авторизации ( то есть это не системный пользователь, не учетка )

Первый вариант это использовать хранимки ( передавать пользователя и данные которые меняются)- но это не камильфо

Второй вариант триггеры но вопрос как получить пользователя ???

*Знаю похожий пример Firebird
(используя current_connection и дату точно определить своего пользователя (тоесть при запуске программы мы записываем пользователя дату и current_connection а затем в тригерре по дате и current_connection выбираем пользователя))

но аналога current_connection в mssql нет

@invm · 26.03.2019, 11:18

Сообщение от kyvaldenius

Вопрос как определить пользователя ?
Пользователи берутся из таблицы авторизации

Стандартные грабли для слишком умных архитекторов.

Сообщение от kyvaldenius

но аналога current_connection в mssql нет

Да неужели?

T-SQL
1
2
3
4
@@spid
connectionproperty
sys.dm_exec_sessions
sys.dm_exec_connections

Совсем не подходят?

ЗЫ: Если SQL Server 2016 или старше, то можно использовать sp_set_session_context

@kyvaldenius · 26.03.2019, 11:39 **[ТС]**

так тут есть момент
current_connection - перезаписывается после restora

@@spid
connectionproperty
sys.dm_exec_sessions
sys.dm_exec_connections
а эти все как только пользователь отключился номер может быть присвоен другому

то есть у нас есть 10 пользователей и они заходят по очереди и у всех @@SPID будет равен 54

Добавлено через 17 секунд
"Стандартные грабли для слишком умных архитекторов." не много не понял

@invm · 26.03.2019, 12:22

Сообщение от kyvaldenius

то есть у нас есть 10 пользователей и они заходят по очереди и у всех @@SPID будет равен 54

Потому что идентифицировать соединене нужно по @@SPID, login_name и login_time, а не только по @@SPID

Сообщение от kyvaldenius

"Стандартные грабли для слишком умных архитекторов." не много не понял

Использование собственных велосипедов вместо штатных механизмов, почти всегда приводит к проблемам.
С чем вы и столкнулись.
Теперь придется изобретать маппинг между "пользователем из таблицы авторизации" и текущим соединением.
Причем при реализации данного маппинга еще придется учитывать возможное наличие connection pooling, иначе опять будут проблемы.

@kyvaldenius · 26.03.2019, 13:06 **[ТС]**

"идентифицировать соединение нужно по @@SPID, login_name и login_time, а не только по @@SPID" а можно пример ?

Какие есть стандартные механизмы?
стандартный механизм это использовать AD и подключаться к бд под учеткой домена тогда можно и стандартные функции использовать типа user_name
а кроме этого

@invm · 26.03.2019, 13:37

Сообщение от kyvaldenius

а можно пример ?

Пример чего? Таблицы маппинга и работы с ней?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create table dbo.SessionToUserMapping
(
 session_id int constraint PK_SessionToUserMapping primary key,
 login_name sysname,
 login_time datetime,
 custom_user_id int
);
 
--регистрация пользователя
merge into dbo.SessionToUserMapping t
using (
 select
  session_id, login_name, login_time, @custom_user_id as custom_user_id
 from
  sys.dm_exec_session
 where
  session_id = @@SPID
) s on t.session_id = s.session_id
when not matched then
 insert
  (session_id, login_name, login_time, custom_user_id)
 values
  (s.session_id, s.login_name, s.login_time, s.custom_user_id)
when matched then
 update
  set
   login_name = s.login_name,
   login_time = s.login_time,
   custom_user_id = s.custom_user_id;
 
--получение пользователя
select
 m.custom_user_id
from
 dbo.SessionToUserMapping m join
 sys.dm_exec_sessions s on s.session_id = m.session_id and s.login_name = m.login_name and s.login_time = m.login_time;

Сообщение от kyvaldenius

стандартный механизм это использовать AD

Для использования аутентификации Windows не обязательно поднимать AD.

Сообщение от kyvaldenius

а кроме этого

Есть еще SQL-аутентификация.

@kyvaldenius · 26.03.2019, 13:45 **[ТС]**

спасибо за пример буду разбирать

@kyvaldenius · 27.03.2019, 10:22 **[ТС]**

По поводу примера не понял один момент когда мы "ищем пользователя " то дата login_time будет разной

SQL
1
2
3
4
5
6
--получение пользователя
SELECT
 m.custom_user_id
FROM
 dbo.SessionToUserMapping m JOIN
 sys.dm_exec_sessions s ON s.session_id = m.session_id AND s.login_name = m.login_name AND s.login_time = m.login_time;

Сейчас происходит так пользователь авторизируется в программе и происходит запись в таблицу SessionToUserMapping

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

Добавлено через 20 минут
когда надо делать "регистрация пользователя" При первом запуске программы?

@invm · 27.03.2019, 11:09

Сообщение от kyvaldenius

дата login_time будет разной

login_time не может быть разным при разных вызовах для одного @@SPID при условии, что соединение одно и то же.

Сообщение от kyvaldenius

когда надо делать "регистрация пользователя" При первом запуске программы?

После установления соединения с SQL Server.

@kyvaldenius · 27.03.2019, 11:44 **[ТС]**

Как вариант в место login_time datetime, использовать date тогда время не будет играть значения и ограничить чтоб выбирался только самый последний ( актуальны) пользователь который подключился

Добавлено через 3 минуты
"После установления соединения с SQL Server. "

то есть подключение держится на протяжении всей работы программы?
или в каждом методе перед тем как сделать какое либо действие необходимо вызывать регистрацию пользователя?

@invm · 27.03.2019, 12:02

Сообщение от kyvaldenius

Как вариант в место login_time datetime, использовать date тогда время не будет играть значения и ограничить чтоб выбирался только самый последний ( актуальны) пользователь который подключился

login_time необходим чтобы гарантированно получить маппигнг для текущего соединения. На вас, видимо, это не заботит. Поэтому делайте как считаете правильным.

Сообщение от kyvaldenius

то есть подключение держится на протяжении всей работы программы?
или в каждом методе перед тем как сделать какое либо действие необходимо вызывать регистрацию пользователя?

Мне неведомо как вы управляете подключениями. И как нужно ими управлять.
Чтобы предложенный механизм работал нужно обеспечить корректный маппинг для каждого физического соединения с сервером. Учитывая, что на каждое логическое соединение (соответсвующий экземпляр класса в приложении) может быть несколько физических, открываемых неявно - я не знаю как вы это будете реализовывать.

@kyvaldenius · 27.03.2019, 13:31 **[ТС]**

=)

ладно я вас понял )
спасибо за пример кода дальше надо его как-то приспособить

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

спасибо за помощь

@kyvaldenius · 28.03.2019, 08:16 **[ТС]**

все таки вопрос остается открытым.
думал использовать ip но не помогает

Добавлено через 33 минуты
Перефразирую вопрос а то уже можно запутаться.

Как сделать историю изменения данных с пользователем ( из собственной таблицы) с учетом что подключение к БД отрывается только при работе с данными (подключение постоянно не висит) ?

или как использовать встроенную авторизацию SQL/Microsoft
( для авторизации необходимо определять права пользователям на различный функционал ( желатьльно пользователей объединять в группы)?

@invm · 28.03.2019, 10:24

Сообщение от kyvaldenius

Как сделать историю изменения данных с пользователем ( из собственной таблицы) с учетом что подключение к БД отрывается только при работе с данными (подключение постоянно не висит) ?

Так же как с постоянным подключением. Разницы никакой. Что именно нужно делать я уже писал.

Сообщение от kyvaldenius

или как использовать встроенную авторизацию SQL/Microsoft
( для авторизации необходимо определять права пользователям на различный функционал ( желатьльно пользователей объединять в группы)?

Для проверки прав уровня приложения можно проверять членство в роли (ролях).

@iap · 28.03.2019, 10:30

Сообщение от invm

Если SQL Server 2016 или старше

Наверно, не старше, а моложе?

@invm · 28.03.2019, 10:35

Сообщение от iap

Наверно, не старше, а моложе?

Версия считается старше, если выпущена позже.

@kyvaldenius · 28.03.2019, 11:01 **[ТС]**

invm

"Так же как с постоянным подключением. Разницы никакой. Что именно нужно делать я уже писал."

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

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

если мы откажемся от времени а оставим только дату
тогда

когда на одном компьютере :
Пример
мы заходим под пользователем "1" (прошла регистрация пользователя, история пишется все хорошо )

мы перезаходим под пользователем "2" - ( проходим регистрацию пользователя, но при получении пользователя будет выбираться всегда первый тоесть действие совершил 2 а в истории 1

-получение пользователя

SQL
1
2
3
4
5
6
7
SELECT
 m.custom_user_id
FROM
 dbo.SessionToUserMapping m JOIN
 sys.dm_exec_sessions s ON s.session_id = m.session_id 
[B]AND s.login_name = m.login_name 
AND s.login_time = m.login_time;[/B]

AND s.login_name = m.login_name
AND s.login_time = m.login_time;

будут одинаковые как у пользователя 1 так и у 2

Добавлено через 4 минуты
пример

Таблица MAPPING

Добавлено через 3 минуты
пример :

Таблица MAPPING

session_id login_name login_time id_user

57 Admin 2019-03-28 1
60 Admin 2019-03-28 3

история
comment UserLogin TypeAction TimeAction

тест 1 INSERT 2019-03-28 10:50:33.297
те 3 UPDATE 2019-03-28 10:51:05.467
е 3 UPDATE 2019-03-28 10:51:21.963
е 3 DELETE 2019-03-28 10:51:48.120
12 3 DELETE 2019-03-28 10:52:52.700

@invm · 28.03.2019, 11:06

Сообщение от kyvaldenius

Пример
мы заходим под пользователем "1" (прошла регистрация пользователя, история пишется все хорошо )
мы перезаходим под пользователем "2" - ( проходим регистрацию пользователя, но при получении пользователя будет выбираться всегда первый тоесть действие совершил 2 а в истории 1

А маппинг сам обновится?

@kyvaldenius · 28.03.2019, 11:10 **[ТС]**

когда пользователь запустил программу и авторизировался то запускаем

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--регистрация пользователя
MERGE INTO dbo.SessionToUserMapping t
USING (
 SELECT
  session_id, login_name, login_time, @custom_user_id AS custom_user_id
 FROM
  sys.dm_exec_session
 WHERE
  session_id = @@SPID
) s ON t.session_id = s.session_id
WHEN NOT matched THEN
 INSERT
  (session_id, login_name, login_time, custom_user_id)
 VALUES
  (s.session_id, s.login_name, s.login_time, s.custom_user_id)
WHEN matched THEN
 UPDATE
  SET
   login_name = s.login_name,
   login_time = s.login_time,
   custom_user_id = s.custom_user_id;

login_time - дата так как если использовать время оно будет всегда разное из-за не постоянного подключения

@invm · 28.03.2019, 11:27

Сообщение от kyvaldenius

login_time - дата так как если использовать время оно будет всегда разное из-за не постоянного подключения

Конечно разное.
Я вам уже объяснял несколько раз когда нужно обновлять маппинг. Вы не слышите.

Еще раз - обновлять маппинг нужно, когда открывается соединение с сервером БД или когда меняется ваш внутренний пользователь при открытом соединении с БД.
Если вы не в состоянии реализовать такое поведение - передавайте пользователя параметром при каждой манипуляции с БД.

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

Новые блоги и статьи Все статьи Все блоги /
Программный отбор элементов справочника Номенклатура по группе 1С Maks 22.03.2026 Установка программного отбора элементов справочника "Номенклатура" из модуля формы документа. В качестве фильтра для отбора справочника служит группа номенклатуры. Отбор под наименованию группы (на. . .	Как я обхитрил таблицу Word Alexander-7 21.03.2026 Когда мигает курсор у внешнего края таблицы, и нам надо перейти на новую строку, а при нажатии Enter создается новый ряд таблицы с ячейками, то мы вместо нервных нажатий Энтеров мы пишем любые буквы. . .	Krabik - рыболовный бот для WoW 3.3.5a AmbA 21.03.2026 без регистрации и смс. Это не торговля, приложение не содержит рекламы. Выполняет свою непосредственную задачу - автоматизацию рыбалки в WoW - и ничего более. Однако если админы будут против -. . .	Программный отбор элементов справочника Сотрудники по перечислениям 1С Maks 21.03.2026 Установка программного отбора элементов справочника "Сотрудники" из модуля формы документа. В качестве фильтра для отбора служит предопределенное значение перечислений. Процедура. . .
Переходник USB-CAN-GPIO Eddy_Em 20.03.2026 Достаточно давно на работе возникла необходимость в переходнике CAN-USB с гальваноразвязкой, оный и был разработан. Однако, все меня терзала совесть, что аж 48-ногий МК используется так тупо: просто. . .	Оттенки серого Argus19 18.03.2026 Оттенки серого Нашёл в интернете 3 прекрасных модуля: Модуль класса открытия диалога открытия/ сохранения файла на Win32 API; Модуль класса быстрого перекодирования цветного изображения в оттенки. . .	SDL3 для Desktop (MinGW): Рисуем цветные прямоугольники с помощью рисовальщика SDL3 на Си и C++ 8Observer8 17.03.2026 Содержание блога Финальные проекты на Си и на C++: finish-rectangles-sdl3-c. zip finish-rectangles-sdl3-cpp. zip	Символические и жёсткие ссылки в Linux. algri14 15.03.2026 Существует два типа ссылок — символические и жёсткие. Ссылка в Linux — это запись в каталоге, которая может указывать либо на inode «файла-ИСТОЧНИКА», тогда это будет «жёсткая ссылка» (hard link),. . .

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278

	Лог изменений данных 26.03.2019, 11:03. Показов 8212. Ответов 36 Метки нет (Все метки) День добрый! Такой вопрос. Необходимо сделать историю изменений данных которая хранит сами данные действие время и пользователь который совершил действие Вопрос как определить пользователя ? Пользователи берутся из таблицы авторизации ( то есть это не системный пользователь, не учетка ) Первый вариант это использовать хранимки ( передавать пользователя и данные которые меняются)- но это не камильфо Второй вариант триггеры но вопрос как получить пользователя ??? *Знаю похожий пример Firebird (используя current_connection и дату точно определить своего пользователя (тоесть при запуске программы мы записываем пользователя дату и current_connection а затем в тригерре по дате и current_connection выбираем пользователя)) но аналога current_connection в mssql нет 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	26.03.2019, 11:39 [ТС]
	так тут есть момент current_connection - перезаписывается после restora @@spid connectionproperty sys.dm_exec_sessions sys.dm_exec_connections а эти все как только пользователь отключился номер может быть присвоен другому то есть у нас есть 10 пользователей и они заходят по очереди и у всех @@SPID будет равен 54 Добавлено через 17 секунд "Стандартные грабли для слишком умных архитекторов." не много не понял 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	26.03.2019, 13:06 [ТС]
	"идентифицировать соединение нужно по @@SPID, login_name и login_time, а не только по @@SPID" а можно пример ? Какие есть стандартные механизмы? стандартный механизм это использовать AD и подключаться к бд под учеткой домена тогда можно и стандартные функции использовать типа user_name а кроме этого 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	26.03.2019, 13:45 [ТС]
	спасибо за пример буду разбирать 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	27.03.2019, 11:44 [ТС]
	Как вариант в место login_time datetime, использовать date тогда время не будет играть значения и ограничить чтоб выбирался только самый последний ( актуальны) пользователь который подключился Добавлено через 3 минуты "После установления соединения с SQL Server. " то есть подключение держится на протяжении всей работы программы? или в каждом методе перед тем как сделать какое либо действие необходимо вызывать регистрацию пользователя? 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	27.03.2019, 13:31 [ТС]
	=) ладно я вас понял ) спасибо за пример кода дальше надо его как-то приспособить просто момент того что подключение надо держать на протяжении всей работы проги это очень странно и порадовало по сравнению постоянным соединением проблемы из-за не точно определения пользователя не такая страшная спасибо за помощь 0

@kyvaldenius 16 / 12 / 4 Регистрация: 28.09.2015 Сообщений: 278
	28.03.2019, 08:16 [ТС]
	все таки вопрос остается открытым. думал использовать ip но не помогает Добавлено через 33 минуты Перефразирую вопрос а то уже можно запутаться. Как сделать историю изменения данных с пользователем ( из собственной таблицы) с учетом что подключение к БД отрывается только при работе с данными (подключение постоянно не висит) ? или как использовать встроенную авторизацию SQL/Microsoft ( для авторизации необходимо определять права пользователям на различный функционал ( желатьльно пользователей объединять в группы)? 0