Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Составление SQL-запросов > Запрос с группировкой по интервалу времени


Автор: Akella 23.5.2013, 11:33
Не знаю, как бы правильно назвать тему.
Предисловие.
Есть база в онлайне, служебная. PHP+MySQL.
Сервер фиксирует некоторые действия пользователей, как то вход, выход, просмотр и редактирование записей и т.д.
В базу можно залогинится под одним и тем же логином с разных компьютеров. Грубо говоря, работник организации может дать кому-нибудь свои логин и пароль и тот легко войдёт в базу.
Сессия длится 20 минут.

Как отобразить список логинов в виде таблицы, которые в базе залогинены были одновременно с двух компьютеров?
Я так понимаю, что нужно сделать выборку, где IP разные, логин один и тот же + интервал времени 20 мин. Т.е. в этом интервале получились такие вот условия: разные IP.

На вопрос, почему разрешён двойной логин, сразу отвечу, что даже если запретить, то пользователи могут работать попеременно. А так хотя бы можно выявить несанкционированный вход и предъявить остальным голову на палке, чтобы боялись!

Автор: Akina 23.5.2013, 11:44
Что-то такое?
Код

select t1.*, t2.*
from table t1, table t2
where t1.login=t2.login
and t1.ip != t2.ip
and t1.dt_login > t2.dt_login
and timediff(t1.dt_login, t2.dt_login) < '20:00:00'


Добавлено через 6 минут и 4 секунды
Цитата(Akella @  23.5.2013,  12:33 Найти цитируемый пост)
В базу можно залогинится под одним и тем же логином с разных компьютеров. Грубо говоря, работник организации может дать кому-нибудь свои логин и пароль и тот легко войдёт в базу.
Сессия длится 20 минут.

Давай рассмотрим такой сценарий. Юзер логинится на компе А, работает 5 минут. Комп понадобился другому юзеру, который быстрым переключением пользователей грузит свой сеанс (сеанс первого - в фоне, и запаролен). Юзер переходит к компу Б и логинится оттуда... не зная, что для его головы приготовлена палка.
Варианты - первый комп повис, вышел из строя, нужно выполнить срочно другую операцию, но нет времени обрывать первую, и т.п.

Добавлено через 7 минут и 7 секунд
Цитата(Akella @  23.5.2013,  12:33 Найти цитируемый пост)
даже если запретить, то пользователи могут работать попеременно.

Фраза не понята... она тут каким боком?

Добавлено через 10 минут и 4 секунды
Цитата(Akella @  23.5.2013,  12:33 Найти цитируемый пост)
Грубо говоря, работник организации может дать кому-нибудь свои логин и пароль и тот легко войдёт в базу.

Нужно регистрировать пару (локальный логин - логин к БД). Проверять соответствие. А далее - использование чужих данных легко ловится, т.к. народ забывает, что тот, чей пароль они пользуют, вышел через проходную или вообще на больничном или в отгуле.

Автор: Akella 23.5.2013, 12:01
Цитата(Akina @  23.5.2013,  11:44 Найти цитируемый пост)
Давай рассмотрим такой сценарий. Юзер логинится на компе А, работает 5 минут. Комп понадобился другому юзеру, который быстрым переключением пользователей грузит свой сеанс (сеанс первого - в фоне, и запаролен). Юзер переходит к компу Б и логинится оттуда... не зная, что для его головы приготовлена палка.


Согласен.
Мало того, если человек1 вошёл и не нажал кнопку Выход + в течение 20 минут входит человек2 под тем же логином, это фиксируется отдельно. К сожалению, бывают огрехи, т.к. человек1 может не нажать кнопку Выход, может просто закрыть браузер, может произойти обрыв связи. Т.е. не известно на 100% достоверно, действительно ли это был человек2 или всё же это человек1 снова открыл браузер, перезапустил модем и т.д.

А бывает ещё, что человек1 вошёл в базу с планшета, и потом сразу же с настольного ПК.

Я ж не говорю ,что голову на палку надевают моментально  smile 
Потом просто разбираются с его руководителем. Пару вопросов и всё, от человека отстали.
Всё не так строго, как кажется.

Но очень хочется найти недобросовестных.

Добавлено через 1 минуту и 20 секунд
Цитата(Akina @  23.5.2013,  11:44 Найти цитируемый пост)
Цитата(Akella @  23.5.2013,  12:33 Найти цитируемый пост)
даже если запретить, то пользователи могут работать попеременно.

Фраза не понята... она тут каким боком?


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

Добавлено через 11 минут и 13 секунд
Цитата(Akina @  23.5.2013,  11:44 Найти цитируемый пост)
Нужно регистрировать пару (локальный логин - логин к БД). Проверять соответствие. 


Что такое локальный логин? Логин винды или линукса? А кто ж его даст браузеру?
Программист ещё как-то фиксирует код компьютера. Я не знаю, на основе каких  данных это берётся, но в таблице аудита видны числа типа: 2913868958

Добавлено через 13 минут и 43 секунды
Цитата(Akina @  23.5.2013,  11:44 Найти цитируемый пост)
Нужно регистрировать пару (локальный логин - логин к БД). Проверять соответствие. А далее - использование чужих данных легко ловится, т.к. народ забывает, что тот, чей пароль они пользуют, вышел через проходную или вообще на больничном или в отгуле. 


доступ к базе имеют и с домашних компьютеров, с планшетов, откуда угодно, где есть интернет и браузер smile

Автор: Akina 23.5.2013, 14:06
Цитата(Akella @  23.5.2013,  13:01 Найти цитируемый пост)
доступ к базе имеют и с домашних компьютеров, с планшетов, откуда угодно, где есть интернет и браузер 

Цитата(Akella @  23.5.2013,  13:01 Найти цитируемый пост)
бывает ещё, что человек1 вошёл в базу с планшета, и потом сразу же с настольного ПК.

Эмм... тогда твоя затея вообще обречена на провал.

Впрочем, текст запроса я тебе дал...

Автор: Akella 23.5.2013, 20:24
Цитата(Akina @  23.5.2013,  14:06 Найти цитируемый пост)
Эмм... тогда твоя затея вообще обречена на провал.

ну с "вообще" - это ты погорячился smile

Автор: Akina 24.5.2013, 11:29
Цитата(Akella @  23.5.2013,  21:24 Найти цитируемый пост)
 с "вообще" - это ты погорячился  

Может, хотя при выбранном тобой подходе я в том не убеждён.

Подумав, я считаю, что ты изначально неверно поставил задачу. И требуется тебе немного иное. Что тебе даёт логин? а ничего... и фиксировать (а точнее, считать инцидентами, требующими разбирательства) следует события, когда логин, от имени которого сделан запрос к данным, совпадает с логином последней аутентификации, а вот адрес - не совпадает. В сочетании с сессией (следует проверить, что при изменении IP-адреса сессия разваливается!) это будет доказательством события одновременной аутентификации с двух разных копий операционной системы.

Автор: Akina 24.5.2013, 11:49
Т.е. что-то типа

Код

select t1.*, t2.*
from login t1, access t2
where t1.ip != t2.ip
and t1.login = t2.login
and t1.dt_login < t2.dt_access
and (t1.login, t1.ip, t1.dt_login) = 
  (
  select t3.login, t3.ip, t3.dt_login
  from login t3
  where t3.login = t2.login
  and t3.dt_login < t2.dt_access
  order by t3.dt_login desc
  limit 1
  );

Автор: Akella 3.6.2013, 16:13
Цитата(Akina @  23.5.2013,  11:44 Найти цитируемый пост)
and timediff(t1.dt_login, t2.dt_login) < '20:00:00'


ты уверен, что не '00:20:00'

Добавлено через 9 минут и 12 секунд
Код

WHERE TIMEDIFF( a1.date_operation, a2.date_operation ) < '0000-00-00 00:20:00.0000'

Автор: Akina 3.6.2013, 16:52
Цитата(Akella @  3.6.2013,  17:13 Найти цитируемый пост)
ты уверен, что не '00:20:00'

не-а... на коленке же ж писано.

Автор: Akella 4.6.2013, 13:06
Цитата(Akina @  24.5.2013,  11:49 Найти цитируемый пост)
and (t1.login, t1.ip, t1.dt_login) = 
  (
  select t3.login, t3.ip, t3.dt_login
  from login t3
  where t3.login = t2.login
  and t3.dt_login < t2.dt_access
  order by t3.dt_login desc
  limit 1
  );


странный запрос, как его понимать?

Автор: Akina 4.6.2013, 13:11
Не понял... что в нём тебе кажется странным?

Автор: Zloxa 4.6.2013, 13:42
Akina, мне говорили что лимит в подзапросах(в том числе и в ин кляузе) - нельзя ((

Автор: Akella 4.6.2013, 13:46
я просто ни разу не видел такого сравнения

Добавлено @ 13:49
В общем запрос вот такой вырисовывается

Код

SELECT DISTINCT
  a1.id,
  a1.date_operation AS date_operation1,
  a1.ip AS ip1,
  a1.comp_code AS comp_code1,
  o1.name AS name_operation1,
  u.login
FROM `audit_user` a1,
     `audit_user` a2,
     users u,
     operation_audit_user o1,
     operation_audit_user o2
WHERE
TIMEDIFF(a1.date_operation, a2.date_operation) < '0000-00-00 00:30:00.0000'
AND TIMEDIFF(a1.date_operation, a2.date_operation) > '0000-00-00 00:00:00.0000'
AND a1.date_operation >= :dt_start
AND a1.date_operation <= :dt_end
AND a1.id_user = u.id
AND o1.id = a1.id_operation
AND o2.id = a2.id_operation
AND a1.ip <> a2.ip
AND a1.comp_code <> a2.comp_code
AND a1.id_user = a2.id_user
AND u.is_deleted = 0
AND u.is_blocked = 0


записей в таблице audit_user 1,3 млн, в таблице пользователей - 1300, в operation_audit_user - 20.

Запрос валит сервер.
Здесь
AND a1.date_operation >= :dt_start
AND a1.date_operation <= :dt_end

выбираю только текущий день

Автор: Akina 4.6.2013, 13:51
Zloxa, в мускуле очень дофига чего можно из того, что нельзя... иногда в пользу, чаще во вред... зато ламерюзер-френдли.

Akella, нейтроны ты тоже ни разу не видел... 

Автор: Zloxa 4.6.2013, 14:00
Цитата(Akina @  4.6.2013,  14:51 Найти цитируемый пост)
Zloxa, в мускуле очень дофига чего можно из того, что нельзя... иногда в пользу, чаще во вред... зато ламерюзер-френдли.

Я просто совсем недавно подобное советовал человеку. Там был не коррелированный скаляр, думал прохляет, но у него не похляло. А тут еще и коррелированный скаляр, думал ваще не попрет smile 

Правда в том случае я делал IN а не "=" - может поэтому?

Автор: Akina 4.6.2013, 14:05
Цитата(Zloxa @  4.6.2013,  15:00 Найти цитируемый пост)
в том случае я делал IN а не "=" - может поэтому?

Нет, явно не поэтому, проблема была в чём-то ещё... надо смотреть конкретный текст.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)