Le problème

Récemment, sur une instance SQL Server mutualisée, des utilisateurs avaient besoin d’accéder à des données dans MSDB (ça marche aussi avec d’autres bases utilisateurs).
Evidemment, je ne pouvais pas donner accès comme cela à tout MSDB car les données y sont communes à tous les utilisateurs.

L’idée de base est donc de faire une procédure stockée donnant accès uniquement aux bonnes données dans MSDB.

Pour cela, je vais m’appuyer sur le chainage SQL (ownership chain).

Le principe

L’ownership chain, c’est le fait qu’il n’y a pas de contrôle de sécurité sur le second objet accédé dès l’instant où il a le même propriétaire.

Par exemple, une vue accédant à une table ayant le même propriétaire (pas forcément le même SCHEMA), vous n’avez besoin de donner les droits uniquement sur la vue.

Tous les détails sur la MSDN.

Le code

 
/* on crée d''abord la base de données utilisateur */

CREATE DATABASE LabSecurity
GO

Use LabSecurity
GO

/* on crée l''utilisateur sur cette base */
CREATE LOGIN LabUser WITH PASSWORD='blabla01!'
CREATE USER LabUser FOR LOGIN LabUser
GO

/* L''utilisateur n''a pas accès à MSDB */
EXECUTE AS LOGIN='LabUser'
	SELECT * FROM msdb.dbo.syscategories
	--> access denied
REVERT;
GO

/* on encapsule l''accès via une procédure stockée */
CREATE PROCEDURE dbo.SelectCategories
AS
BEGIN
	SELECT * FROM msdb.dbo.syscategories
END
GO

GRANT EXECUTE ON dbo.SelectCategories TO LabUser
GO

/* L''utilisateur n''a pas toujours pas accès à MSDB */
EXECUTE AS LOGIN='LabUser'
	exec dbo.SelectCategories
	--> access denied
REVERT;
GO


/* on autorise le chainage inter-base */
ALTER DATABASE LabSecurity SET DB_CHAINING ON
GO

/* L''utilisateur n''a pas toujours pas accès car 
   le propriétaire de la procédure est différent de celui de la base MSDB */
EXECUTE AS LOGIN='LabUser'
	exec dbo.SelectCategories
	--> access denied
REVERT;
GO

/* on vérifie les propriétaires */
SELECT name as dbName, owner_sid, SUSER_NAME(owner_sid) FROM sys.databases
SELECT procedures.name, procedures.principal_id as procedureOwner_sid, SUSER_NAME(procedures.principal_id) as procedureOwner,
	schemas.name as schemaName, schemas.principal_id as schemaOwner_sid, SUSER_NAME(schemas.principal_id) as schemasOwner
FROM sys.procedures
	inner join sys.schemas on sys.schemas.schema_id = sys.procedures.schema_id

/* on fixe les même propriétaires d''objets pour ne pas casser la chaîne */
ALTER AUTHORIZATION ON database::LabSecurity TO sa
GO

/* L''utilisateur a maintenant accès car le chaînage est bon */
EXECUTE AS LOGIN='LabUser'
	exec dbo.SelectCategories
REVERT;
GO

/*  NETTOYAGE */
Use master
GO
DROP LOGIN LabUser
DROP DATABASE LabSecurity

Les points importants sont :

 ALTER DATABASE LabSecurity SET DB_CHAINING ON

En effet, il faut autoriser le chainage entre les bases de données qui est désactivé par défaut. On change la propriété uniquement pour notre base.

 
ALTER AUTHORIZATION ON database::LabSecurity TO sa

Il faut également que les bases de données aient le même propriétaire. MSDB ayant sa comme propriétaire, on doit en faire de même pour la base de données utilisateur.

Les conséquences

Le problème principal de cette technique, c’est que DB_CHAINING n’est pas une bonne pratique.

En effet, on ouvre une brèche dans la sécurité puisque, en fonction des propriétaires des objets on a accès aux autres bases de données de l’instance.

Pour limiter cela, on joue justement avec les SCHEMA et leurs propriétaires. On peut donc imaginer travailler avec un SCHEMA utilisateur et mettre notre procédure stockée dans un SCHEMA particulier avec sa comme propriétaire.

Une alternative

Nous allons voir maintenant une autre solution pour donner accès à MSDB sans activer l’option db chaining.
Pour cela, nous allons utiliser des certificats.

Le code  :

/* on crée les objets */
CREATE DATABASE LabSecurity
GO

Use LabSecurity
GO

CREATE LOGIN LabUser WITH PASSWORD='blabla01!'
CREATE USER LabUser FOR LOGIN LabUser
GO

CREATE PROCEDURE dbo.SelectCategories
AS
BEGIN
	SELECT * FROM msdb.dbo.syscategories
END
GO

GRANT EXECUTE ON dbo.SelectCategories TO LabUser
GO

/* On crée un certificat dans la base de données utilisateur.
   On doit sauvegarder le certificat car on en aura besoin dans MSDB */
Use LabSecurity
GO
CREATE CERTIFICATE pivotCertificate
    ENCRYPTION BY PASSWORD = 'blabla01!'
    WITH SUBJECT = 'Certificate utilisé pour signer les procédures interbases',
    START_DATE = '20020101', EXPIRY_DATE = '21000101'
go

BACKUP CERTIFICATE pivotCertificate TO FILE = 'C:\temp\pivotCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\pivotCert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'blabla01!',
                  DECRYPTION BY PASSWORD = 'blabla01!')
go

/* on signe la procédure stockée avec le certificat */
ADD SIGNATURE TO dbo.SelectCategories BY CERTIFICATE pivotCertificate
    WITH PASSWORD = 'blabla01!'


/* on restaure également le certificat dans MSDB */
Use msdb
GO
CREATE CERTIFICATE pivotCertificate FROM FILE = 'C:\temp\pivotCert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\pivotCert.pvk',
				  ENCRYPTION BY PASSWORD = 'blabla01!',
                  DECRYPTION BY PASSWORD = 'blabla01!')
GO

/* On crée un utilisateur dans MSDB correspondant au certificat 
	et on lui donne les droits sur les éléments dans MSDB */
CREATE USER pivotCertificateUser FOR CERTIFICATE pivotCertificate
GO

GRANT SELECT ON dbo.syscategories TO pivotCertificateUser

/* on teste avec LabUser 
	Pour rappel :
	  -il n''y a pas de chainage entre les bases,
	  -nous n''avons pas changé les propriétaires,
	  -nous n''avons pas donné de droits à LabUser sur MSDB
*/
Use LabSecurity
GO

EXECUTE AS LOGIN='LabUser'
	exec dbo.SelectCategories
REVERT;
GO

/*  NETTOYAGE */
Use master
GO
EXEC xp_cmdshell 'DEL C:\temp\pivotCert.*' --> xp_cmdShell doit être autorisé (allez dans la Facette configuration)
DROP LOGIN LabUser
DROP DATABASE LabSecurity

USE MSDB
GO
DROP USER pivotCertificateUser
DROP CERTIFICATE pivotCertificate

Les points importants sont :

 CREATE CERTIFICATE pivotCertificate

On crée le même certificat dans les deux bases de données. Pour cela, on doit créer le second à partir d’un BACKUP CERTIFICATE du premier.

 CREATE USER pivotCertificateUser FOR CERTIFICATE pivotCertificate

On crée un utilisateur dans MSDB pour ce certificat et on lui donne les droits adéquats. Vous noterez qu’il n’y a pas besoin de LOGIN.

 ADD SIGNATURE TO dbo.SelectCategories BY CERTIFICATE pivotCertificate

En signant la procédure stockée avec le certificat, en arrivant sur MSDB, elle sera vue comme l’utilisateur “certifié” par le certificat.

Cela permet aussi de protéger la procédure car toute modification cassera la signature et donc on ne peut pas la “détourner”.

En conclusion

Pour conclure, pour accéder aux objets d’une autre base, je vous recommande d’utiliser des certificats. Cela permet d’avoir un mécanisme cross-database contrôlé sans ouvrir complètement le chaînage. Et cela vous permet de protéger facilement la procédure par une signature.

La première solution n’est pas totalement à exclure mais elle est à utiliser avec vigilance car le jeu des propriétaires peut laisser de grosses failles ouvertes.