Discussion :

[StringBuilder]

Bonjour,

Je travaille sur un site extranet.
J'ai des utilisateurs "internes" (salariés de l'entreprise) et des utilisateurs "externes" (clients, fournisseurs, partenaires).

J'ai décidé de faire en sorte que l'application web se connecte à la base de données avec un compte dédié à chaque utilisateur.

Un utilisateur "interne" peut créer un compte pour un autre utilisateur (interne ou externe).
Un niveau de droit plus fin sera mis en place dans l'application elle-même, au niveau base de données on va partir du principe que c'est suffisant pour le moment (puisqu'on les utilisateurs créés ne peuvent qu'exécuter des procédures stockées qui gèrent ces droits, sans accéder directement aux tables)

J'ai donc créé deux rôles "interne" et "externe", ainsi qu'une procédure stockée "createPerson", que les utilisateurs peuvent lancer.

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
46
47
 
use mabase;
go
 
CREATE ROLE interne;
GRANT EXECUTE TO interne;
GRANT ALTER ANY USER TO interne;
--EXEC sp_addrolemember 'securityadmin', interne; // La je suis embêté, je ne sais pas comment autoriser le rôle "INTERNE" de ma base à créer des logins sur le serveur...
GO
 
CREATE ROLE externe;
GRANT EXECUTE TO externe;
GO
 
create procedure dbo.CreatePerson
(
    @person_id int output,
	@person_email varchar(256),
    @person_name varchar(50),
	@person_internal bit,
	@user_name nvarchar(128),
    @user_password varchar(50)
)
as
begin
	declare @user_uid smallint;
 
	execute('CREATE LOGIN mydb_' + @user_name + ' WITH PASSWORD = ''' + @user_password + '''');
	execute('CREATE USER ' + @user_name + ' FOR LOGIN mydb_' + @user_name + ' WITH DEFAULT_SCHEMA = dbo');
 
	select @user_uid = u.[uid]
	from sys.sysusers u
	where u.[name] = @user_name and u.islogin = 1;
 
	if (@person_internal = 1)
	begin
		exec sp_addrolemember 'interne', @user_name;
	end
	else
	begin
		exec sp_addrolemember 'externe', @user_name;
	end;
 
    insert into dbo.person (email, [name], user_uid)
    values (@person_email, @person_name, @user_uid);
end;
go

Comme en témoigne la ligne en commentaire sur la création du rôle "INTERNE", je ne sais pas comment faire pour octroyer le droit de créer des logins sur le serveur à mon rôle de base de données.

Une suggestion ?

[SQLpro]

Tu ne peut le faire qu'au niveau du compte de connexion, pas au niveau de l'utilisateur SQL.

A +

[StringBuilder]

Bonjour Frédéric et merci pour ta réponse.

Désolé, la mienne est un peu tardive.


J'ai donc tenté de modifier mon script de façon à déclarer login par login la possibilité de créer des logins.

Cependant, il doit toujours me manquer un truc, car lorsque je crée un compte avec ma procédure stockée en utilisant un compte sysadmin/db_owner, aucun souci, mais si je l'exécute avec un compte créé par la procédure elle-même, j'obtiens un message d'erreur lors de la tentative de rattachement au rôle "interne" ou "externe".

Voici mon code :

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
 
CREATE ROLE interne;
GRANT EXECUTE TO interne;
GRANT ALTER ANY USER TO interne with grant option;
GO
 
CREATE ROLE externe;
GRANT EXECUTE TO externe;
GO
 
create procedure dbo.CreatePerson
(
    @person_id int output,
	@person_email varchar(256),
    @person_name varchar(50),
	@person_internal bit,
	@user_name nvarchar(128),
    @user_password varchar(50)
)
as
begin
	declare @user_uid smallint;
 
	execute('CREATE LOGIN mydb_' + @user_name + ' WITH PASSWORD = ''' + @user_password + '''');
	execute('CREATE USER ' + @user_name + ' FOR LOGIN mydb_' + @user_name + ' WITH DEFAULT_SCHEMA = dbo');
 
	select @user_uid = u.[uid]
	from sys.sysusers u
	where u.[name] = @user_name and u.islogin = 1;
 
	if (@person_internal = 1)
	begin
		execute('ALTER ROLE interne ADD MEMBER ' + @user_name);
		execute('use master;grant alter any login to mydb_' + @user_name + ' with grant option');
	end
	else
	begin
		execute('ALTER ROLE externe ADD MEMBER ' + @user_name);
	end;
 
    insert into dbo.person (email, [name], user_uid)
    values (@person_email, @person_name, @user_uid);
end;
go

Ensuite, si je me connecte à la base avec par exemple mon compte Windows (membre du groupe administrateur Windows local, sysadmin et db_owner) :

Code sql :

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

1
2
3
4
 
declare @void int;
 
exec dbo.CreatePerson @void, 'test1@email.com', 'Test 1', 1, 'test1', 'pwd';

Tout va bien : le login "mydb_test1" est créé sur le serveur, le user "test1" est créé dans la base, et je peux me connecter avec le compte.

Ensuite, si je me connecte à la base avec le compte "test1", créé ci-dessus :

Code sql :

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

1
2
3
4
 
declare @void int;
 
exec dbo.CreatePerson @void, 'test2@email.com', 'Test 2', 1, 'test2', 'pwd';

Tout va presque bien : j'ai ce message d'erreur :

Code :

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

1
2
3
4
5
 
Msg*15151, Niveau*16, État*1, Ligne*1
Impossible de modifier le rôle 'interne', car il n'existe pas ou vous ne possédez pas d'autorisation.
 
(1*ligne(s) affectée(s))

Et évidement, le user "test2" n'est pas membre du rôle "interne".

Du coup je peux me connecter à la base avec mais il n'a aucun droit

[StringBuilder]

J'ai fini par m'en sortir en ajoutant ce droit au rôle "interne" :

Code sql :

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

1
2
 
GRANT ALTER ANY ROLE TO interne with grant option;