Contained database authentication e user with password con SID identici
Con SQL 2012 è possibile creare user che si autenticano direttamente nei database. In SSMS li vediamo nominati “user with password”. Questa modalità, molto utile per l’indipendenza del database dall’istanza su cui riesiede, ha tuttavia alcuni effetti collaterali. Il più evidente è il cosiddetto “login masquerading” dovuto all’algoritmo di probing di SQL in fase di autenticazione (maggiori informazioni qui http://msdn.microsoft.com/it-it/library/ff929055.aspx). In parole povere è possibile creare uno user with password con lo stesso nome di una login a livello d’istanza. SQL Server sceglierà di autenticare prima lo user with password se:
- È specificato un initial database nella stringa di connessione.
- L’initial database ha il supporto al contenimento parziale attivato.
Capite bene che ciò vuol dire che l’identità presentata a SQL non è più la coppia username-password ma diventa la tupla username-password-initial database.
Non gestendo correttamente questa nuova modalità ci si espone ovviamente a diversi rischi.
Oggi però voglio parlarvi di uno scenario proposto da un cliente particolarmente brillante: cosa succede se io creo due user with password con SID identici su due diversi database? Supponendo che insistano sulla stessa istanza, posso usare lo statement USE [database] per passare da uno all’altro?
Ovviamente se così fosse sarebbe un problema di sicurezza non trascurabile: ad esempio restorando in produzione un database si potrebbe aprire una porta di accesso ad altri database preesistenti. Lo scenario sarebbe:
- In produzione esiste un database (chiamato Prod) con autenticazione autocontenuta.
- Su Prod esiste uno user with password che ha i permessi di db_owner. La password, ovviamente, è criptata e segretata. Il SID dello user, invece, è in chiaro e facilmente ottenibile.
A questo punto avviene questo:
- In sviluppo viene creato un db (chiamato Dev) con autenticazione autocontenuta.
- Lo sviluppatore aggiunge una login with password con il SID dello user di Prod (sintassi CREATE USER … WITH SID=…). Lo chiamiamo SvilLogin.
- Lo sviluppatore chiede il restore di Svil sulla stessa istanza di Prod.
Ciò che suddede (correttamente) è che sarà possibile collegarsi a Svil in produzione utilizzando SvilLogin.
Ciò che non deve succedere è che sia possibile accedere a Prod autenticandosi su Svil. Di fatto USE Prod deve fallire in questo contesto.
Controlliamo che ciò non succeda. Creiamo per prima cosa due DB con autenticazione autocontenuta:
CREATE DATABASE Cont1;
GO
CREATE DATABASE Cont2;
GO
ALTER DATABASE Cont1 SET CONTAINMENT=PARTIAL;
GO
ALTER DATABASE Cont2 SET CONTAINMENT=PARTIAL;
GO
A questo punto creiamo le user with password (SQLCmd mode):
:SETVAR user UserWithSameSID
:SETVAR password Passw0rd
USE Cont1;
GO
CREATE USER [$(user)] WITH PASSWORD='$(password)';
GO
DECLARE @sid VARBINARY(MAX);
SELECT @sid = SID FROM Cont1.sys.database_principals WHERE name = '$(user)';
DECLARE @stmt NVARCHAR(4000);
SET @stmt = 'CREATE USER [$(user)] WITH PASSWORD=''$(password)'', SID=' + CONVERT(VARCHAR(255), @sid, 1) + ';'
PRINT @stmt;
USE Cont2;
EXEC sp_executeSQL @stmt;
GO
SELECT * FROM Cont1.sys.database_principals WHERE name = '$(user)'
UNION ALL
SELECT * FROM Cont2.sys.database_principals WHERE name = '$(user)'
Come si nota i due user hanno SID identici... anzi hanno anche lo stesso principal_id (incidentalmente):
Ora proviamo a collegarci a Cont1 con l'utenza appena creata. A connessione avvenuta proviamo a eseguire:
SELECT * FROM sys.databases;
Il risultato è l'atteso, infatti vediamo oltre al nostro DB anche master e il TempDB ma non Cont2:
Anche USE [Cont2] non funziona come ci attendiamo:
Ora la domanda lecita è: dove SQL tiene traccia del db iniziale (che completa la tupla username-password-initial db)?
Guardiamo nella sys.dm_exec_sessions e troviamo un campo che fa al caso nostro: authenticating_database_id. Nel caso delle autenticazioni a livello di database questo campo sarà ovviamente diverso da 1:
Happy Coding,
Francesco Cogno