Discussion :

[StringBuilder]

Bonjour,

Je m'apprête à créer les éléments suivants dans une base de données.

Il s'agit de stocker toutes les tentatives de connexion à un logiciel en loguant l'IP.
Après 3 tentatives erronées, le but est de bloquer l'utilisateur de manière exponentielle afin de l'empêcher de faire un brute force.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
37
38
39
40
41
42
43
44
45
 
create table connection
(
	id int identity primary key,
	instant datetime2 not null default SYSDATETIME(),
	[ip] binary(4) not null,
	[status] bit not null
);
go
 
create unique index uix_connection_error on connection([ip], instant) where [status] = 0;
go
 
create unique index uix_connection_success on connection([ip], instant) where [status] = 1;
go
 
create procedure dbo.CreateConnection
(
	@ip binary(4),
	@status bit
)
as
begin
	insert into connection ([ip], [status]) values (@ip, @status);
end;
go
 
create function dbo.GetNextLoginAttemptDate(@ip binary(4))
returns datetime2
as
begin
	return
	(
		select case when count(*) <= 3 then SYSDATETIME() else DATEADD(MINUTE, POWER(2, count(*) - 3), max(c1.instant)) end
		from connection c1
		where c1.[ip] = @ip
		and c1.[status] = 0
		and
		(
			c1.instant > (select max(c2.instant) from connection c2 where c2.[ip] = @ip and c2.[status] = 1)
		 or not exists (select * from connection c2 where c2.[ip] = @ip and c2.[status] = 1)
		)
	);
end;
go

J'ai deux questions en fait...

Une première à propos des sous-requêtes :
=> N'y a-t-il pas moyen de se passer des sous-requête ? Le but étant, pour une IP donnée, de récupérer le nombre de tentatives erronées depuis la dernière réussite... (s'il y en a eu une)

La seconde, à propos des index :
=> La colonne "status" est peut déterminante, puisqu'elle ne peut avoir que 2 valeurs. Elle n'a donc pas trop sa place dans un index. De plus, l'index va vite être fragmenté puisqu'on va avoir régulièrement des échecs. Ainsi, j'ai préféré faire des index filtrés pour avoir d'un côté les succès, et de l'autre les erreurs. Est-ce une ineptie ? Devrais-je préférer un unique index sur (ip, statut, instant) ?

[SQLpro]

Possible, mais rare. Exemple : indexer en unique une colonne nullable

A +