pourquoi nouveau login en sysadmin

[Francky8]

Bonjour,
Je viens de récupérer l'administration d'un serveur sur lequel il y a 2 instances SQL Server 2005 : une instance par défaut (édition standard) et une instance SQLEXPRESS (édition sqlexpress). L'édition SQLEXPRESS ne pose aucun problème. Par contre, je rencontre un problème de sécurité sur l'instance par défaut (SQL Server 2005 Standard). Lorsque je crée un login sql server "de base" sans aucun droit particulier (juste le role serveur public), ce login a finallement beaucoup de droits : il peut par exemple lancer la ps xp_cmdshell, il peut créer une base, la supprimer, ... On dirait qu'il a les droits sysadmin. A noter une chose : si je demande l'affichage des journaux sql server avec ce nouveau login, j'obtiens quand meme un message d'interdiction : seuls les membres du role securityadmin peuvent utiliser cette ps. Je suis en sécurité mixte. Le login que je crée est un login sql server (sécurité interne). Je ne vois pas comment le login créé avec juste le role serveur public peut finallement récupérer des droits "forts". Merci pour votre aide.
Franck

[dbaffaleuf]

Pour vérifier quels sont les logins qui sont membres du rôle sysadmin:

Code :

1
2
sp_helpsrvrolemember 
	@srvrolename = 'sysadmin'

[mikedavem]

ou éventuellement utiliser IS_SRVROLEMEMBER() pour connaitre à quel rôle de serveur appartient votre login ou encore plus simple depuis la console SSMS > clic droit sur le login > proprietes > section Server Roles.

++

[Francky8]

J'ai vérifier les logins appartenant au role sysadmin et le login créé n'apparait pas dans la liste. Ce login n'apparait pas non plus dans les autres role de serveur. Je ne vois pas ou ce login prend ces droits "forts".
Franck

[Francky8]

Lorsque je regarde les droits du login, il n'a que le role serveur public et aucun autre droit serveur ou base.
Franck

[mikedavem]

Vous pourriez nous poster le résultat des requêtes suivantes :

Code :

1
2
3
4
5
6
7
8
9
SELECT 
   sp.name AS login_name,
   sp.type_desc AS [login_type],
   spm.permission_name,
   spm.state_desc
  FROM sys.server_principals AS sp
  INNER JOIN sys.server_permissions AS spm
   ON sp.principal_id = spm.grantee_principal_id
 WHERE sp.name = '<monlogin>';

et (sur la base de données master)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
 DB_NAME() AS database_name,
 p.name AS [user_name],
 pm.class_desc AS ObjectType,
 CASE pm.class 
  WHEN 1 THEN o.name
  WHEN 3 THEN s.name
  WHEN 0 THEN DB_NAME()
 END object_name,
 pm.permission_name
FROM sys.database_principals AS p
INNER JOIN sys.database_permissions AS pm
 ON pm.grantee_principal_id = p.principal_id
LEFT JOIN sys.objects AS o
 ON pm.major_id = o.object_id
LEFT JOIN sys.schemas AS s
 ON pm.major_id = s.schema_id
 WHERE p.name = '<monuser>';

++

[Francky8]

Bonjour,
merci pour votre aide ...
les 2 requetes n'ont donné aucun résultat (0 ligne).
Je suis dba depuis plusieurs années et je n'ai jamais rencontré ce type de problème.

Franck

[Francky8]

Sorry
j'ai exécuter les 2 requetes à toute vitesse sans remplacer le nom login et user.
Je viens de repasser les 2 requetes.
La requete (courte) avec le login ->
login_name : testfg
login_type : SQL_LOGIN
permission_name : CONNECT SQL
state_desc : GRANT

Pour l'autre requete, afin d'avoir un résultat, j'ai ajouté mon login en tant que user (public) sur la base master. Résultat :
database_name : master
user_name : testfg
ObjectType : DATABASE
object_name : master
permission_name : CONNECT

Franck

[mikedavem]

Ok ..

Je pense que votre problème vient du rôle de serveur public qui doit avoir certains privilèges octroyés.

Que donne la requête suivante dans la base master :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 SELECT 
 DB_NAME() AS database_name,
 p.name AS [user_name],
 pm.class_desc AS ObjectType,
 CASE pm.class 
  WHEN 1 THEN o.name
  WHEN 3 THEN s.name
  WHEN 0 THEN DB_NAME()
 END object_name,
 pm.permission_name
FROM sys.database_principals AS p
INNER JOIN sys.database_permissions AS pm
 ON pm.grantee_principal_id = p.principal_id
LEFT JOIN sys.objects AS o
 ON pm.major_id = o.object_id
LEFT JOIN sys.schemas AS s
 ON pm.major_id = s.schema_id
 WHERE p.name = 'public';

Vérifiez également les permissions explicites que possède cet utilisateur dans la base master ...

++

[Francky8]

David,
J'ai passé votre requete mais je récupère bcp de lignes.
Je peux vous donner un résultat plus court et intéressant ->
requete : select * from fn_my_permissions(NULL,'SERVER')
(requete passée avec une connexion du login concerné)

Sur instance sans le problème :
server CONNECT SQL
server VIEW ANY DATABASE

Sur instance avec le problème :
server CONNECT SQL
server SHUTDOWN
server CREATE ENDPOINT
server CREATE ANY DATABASE
server ALTER ANY LOGIN
server ALTER ANY CREDENTIAL
server ALTER ANY ENDPOINT
server ALTER ANY LINKED SERVER
server ALTER ANY CONNECTION
server ALTER ANY DATABASE
server ALTER RESOURCES
server ALTER SETTINGS
server ALTER TRACE
server ADMINISTER BULK OPERATIONS
server AUTHENTICATE SERVER
server EXTERNAL ACCESS ASSEMBLY
server VIEW ANY DATABASE
server VIEW ANY DEFINITION
server VIEW SERVER STATE
server CREATE DDL EVENT NOTIFICATION
server CREATE TRACE EVENT NOTIFICATION
server ALTER ANY EVENT NOTIFICATION
server ALTER SERVER STATE
server UNSAFE ASSEMBLY
server CONTROL SERVER

Comme vous le pensiez, qq'un ou qq chose a rajouté des droits à public apparemment. J'ai vérifié la version : 9.00.3042 -> 2005 fixed SP2. Est-ce que cette instance a été soumise à la faille corrigée par le SP3 ??? C'est la question que je me pose.

Ce que je compte faire :
1. faire des "DENY nom_de_la_permission TO public" (par exemple deny SHUTDOWN to public) en ne laissant que les 2 permissions "connect sql" et "view any database"
2. passer le SP3 sur les instances
Pour l'instant, je n'ai pas mis le SP4 sur les autres instances sql2005 que je gère. Pensez-vous qu'il est intéressant de passer plutot au sp4 ?

Pensez-vous que l'action 1 suffise à rétablir la situation c.a.d à avoir le role public tel qu'il est par défaut ? Faudrait-il que je fasse d'autres vérifications ?

Merci pour votre retour
et surtout ... merci pour votre aide ...

Franck

[mikedavem]

1. faire des "DENY nom_de_la_permission TO public" (par exemple deny SHUTDOWN to public) en ne laissant que les 2 permissions "connect sql" et "view any database"

Je ne ferais pas de DENY explicite personnellement mais plutôt un REVOKE .. TO public.

2. passer le SP3 sur les instances

Oui cela me parait nécessaire ..

Pour l'instant, je n'ai pas mis le SP4 sur les autres instances sql2005 que je gère. Pensez-vous qu'il est intéressant de passer plutot au sp4 ?

Le SP4 n'apporte pas grande chose si vous êtes dans une configuration et une utilisation classique de SQL Server. Cependant si vous voulez profiter du support Microsoft .... vous n'aurez pas le choix

Pensez-vous que l'action 1 suffise à rétablir la situation c.a.d à avoir le role public tel qu'il est par défaut ? Faudrait-il que je fasse d'autres vérifications ?

A distance dur de vous dire mais le point 1 sera déjà un bon début. Il faudra également vérifier si la situation reste stable ou si quelqu'un ou quelque chose ne va pas modifier à nouveau ce que vous avez fait.

++

[Francky8]

David,

Je vais suivre tous vos conseils afin de rétablir la situation.
Je viens de récupérer depuis qq jours un certain nombre de serveurs SQL Server sous mon périmètre. Je viens d'apprendre par exemple que l'instance de développement dont on a parlé a été "utilisé" par un stagiaire et il se pourrait qu'il ait ajouté des droits nécessaires "à l'arrache" par manque de connaissances de SQL Server peut-etre. Ceci expliquant cela.
En tout cas, merci pour votre aide et votre rapidité dans les réponses.
A une prochaine fois

Franck

[Francky8]

Avec deny, la permission est bien retirée.
Par exemple : DENY ALTER ANY LOGIN TO public
Mon login (de base) ne peut plus créer de login.

Par contre, si j'utilise REVOKE, la permission est conservée.
Par exemple : REVOKE ALTER ANY LOGIN FROM public
Mon login (de base) peut créer un login.

Je ne vois pas ou je fais une erreur ???

Franck

[mikedavem]

REVOKE supprime bien le droit à un utilisateur. DENY l'interdit explicitement.
Si vous utilisez REVOKE sur le rôle public et que votre login peut encore créer des bases de données alors c'est que ce dernier fait parti d'un rôle ou autre qui a ce droit.

Vérifiez si votre login ne fait pas parti d'un autre rôle dans la base master ou autre ...

++

[Francky8]

Je ne comprends vraiment pas. Mon login n'a aucun droit sur aucune base. Il a été créé avec le minimum de droit possible. Malgré cela, le revoke ne supprime pas le droit. Seul deny permet d'empecher le login d'utiliser le droit concerné.

Franck

[Francky8]

Bonjour,
Je crée un nouveau login sans aucun droit (le minimum).
Ce login n'appartient à aucun role serveur a part public bien sur.
Si je revoke un droit à public alors aucun effet --> le login continue à avoir le droit revoqué ---> il semble que le login ait donc ce droit par un autre "moyen" que public.
Si je fait un deny du droit sur public alors le login perd le droit ... ce qui parait normal dans ce cas.
J'ai l'impression que la création d'un login sans aucun droit donne quand meme un certain nombre de droits. Ceci semble "caché". ???

Franck

[Francky8]

J'ai pu voir via SSMS les droits octroyés à public
Management Studio / clic droit sur nom instance / Propriétés / Autorisations
public / vérifier les autorisations explicites pour public

. sur instance sans le problème (instance SQLEXPRESS) -> seul « View any database » est coché au niveau « Octroyer »

. sur instance avec le problème (instance par défaut) -> plusieurs autorisations cochées au niveau « Octroyer »
Alter any login
Connect SQL
Control server
Create any database
View any database

J'ai ensuite passer le script de revoke pour enlever les 4 autorisations "en trop" ->
revoke alter Alter any login to public
revoke Connect SQL to public
revoke Control server to public
revoke Create any database to public

Et cette fois, tout fonctionne correctement.

Lorsque je faisais mon revoke auparavant, je ne le faisais que sur "alter any login" et ensuite je faisais un test de création de login et cela fonctionnait car l'autorisation "Control server" donnait le droit.
Désolé ... autant pour moi ... c'est vrai que ce qu'il me manquait c'était de voir ces "fameux" 4 droits accordés sur public. Ensuite, tout a été plus simple.

Cette fois, tout semble ok. Merci pour votre aide.

Franck

[mikedavem]

Ok reprenons .. nous allons trouver Désolé je vais vous faire certainement répéter certaines choses :

- Est ce que votre login est un login de type Windows ?
- Si oui est ce que celui-ci n'appartiendrait pas à un groupe AD qui aurait des droits sur le serveur SQL ?
- Est ce que ce login a un mappage utilisateur dans master une fois que vous l'avez créé ? Si oui est ce que celui-ci a des droits particuliers dans master ?

Edit = Vous avez répondu avant moi ... donc ne pas tenir compte du message

++