November, 2012

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

Oggi esploreremo il comportamento delle EntityFramework 5.0 su SQL Server, in particolare il comportamento che si ha utilizzando l’approccio Code First. Per questo esempio creiamo un progetto console con Visual Studio e aggiungiamo (tramite NUGet ad esempio) i riferimenti ad EntityFramework:

Ora aggiungiamo due entità (classi), una chiamata Crewman:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    public class Crewman
    {
        public int ID { get; set; }

        public string Name { get; set; }
        public Rank Rank { get; set; }

        public Crewman ReportingOfficier { get; set; }
    }
}

E l’altra chiamata StarShip:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace ConsoleApplication3
{
    public class Starship
    {
        public Starship()
        {
            Crew = new List<Crewman>();
        }

        public int ID { get; set; }
        
        public string Name { get; set; }
        public string Designation { get; set; }

        public List<Crewman> Crew { get; set; }
    }
}

Il tipo Rank è un enumerato così definito:

public enum Rank
    {
        Captain, FirstOfficer, ChiefEngineer, Crewman, Medic
    }

Notiamo come abbiamo deliberatamente sempre specificato una proprietà pubblica di tipo int con nome ID. Inoltre notiamo come abbiamo creato una relazione fra le entità semplicemente definendo una proprietà come enumerabile di Crewman. Nel modello relazionale potremmo dire che l’entità Starship ha 0..∞ Crewman. Inoltre possiamo dire che ciascun Crewman può avere un Crewman ReportingOfficier.

Per rendere persistenti le entità definiamo una estensione di DbContext in questo modo:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace ConsoleApplication3
{    
    public class StarFleet : DbContext
    {
        public DbSet<Starship> StarShips { get; set; }
        public DbSet<Crewman> Crew { get; set; }

        public StarFleet(string nameOrConnectionString)
            : base(nameOrConnectionString)
        { }
    }
}

Notiamo l’utilizzo dell’insieme DbSet per le proprietà pubbliche da persistere su SQL Server. A questo punto l’utilizzo è semplice, nel metodo main della nostra classe console creiamo una istanza di StarFleet (con il puntamento ad una nostra istanza di SQL) e popoliamola con dati fittizi:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                StarFleet sf = new StarFleet("Server=”ServerHere”;Database=TestEFCF;Trusted_Connection=True");

                {
                    Crewman cKirk = new Crewman() { Name = "J.T. Kirk", Rank = ConsoleApplication3.Rank.Captain };
                    Crewman cSpock = new Crewman() { Name = "Spock", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cKirk };
                    Crewman cBones = new Crewman() { Name = "Leonard McCoy", Rank = ConsoleApplication3.Rank.Medic, ReportingOfficier = cKirk };

                    Starship s = new Starship() { Name = "USS Enterprise A" };
                    s.Crew.Add(cKirk);
                    s.Crew.Add(cSpock);
                    s.Crew.Add(cBones);

                    sf.StarShips.Add(s);
                }
                //-----------------------------
                {
                    Crewman cPicard = new Crewman() { Name = "Jean Luc Picard", Rank = ConsoleApplication3.Rank.Captain };
                    Crewman cData = new Crewman() { Name = "Data", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cPicard };
                    Crewman cCrusher = new Crewman() { Name = "Wesley Crusher", Rank = ConsoleApplication3.Rank.Captain, ReportingOfficier = cData };
                    Starship s2 = new Starship() { Name = "USS Enterprise C" };
                    s2.Crew.Add(cPicard);
                    s2.Crew.Add(cData);
                    s2.Crew.Add(cCrusher);

                    sf.StarShips.Add(s2);
                }
                //------------------------------
                sf.SaveChanges();

            }
            catch (Exception exce)
            {
                Console.WriteLine(exce.ToString());
            }
        }
    }
}

Notiamo subito che il Database non è necessario che esista: in caso negativo il framework cercherà di crearne uno ex-novo (ovviamente la login deve possedere i relativi privilegi perché questa creazione vada a buon fine). Eseguendo il programma otterremo un database con le seguenti tabelle:

Ma cosa succeed dietro le quinte? Attiviamo una sessione di eXtended Events (non più profiler :)) usando il template “Query detail tacking”:

Rieseguiamo il programma (supponendo di aver ripulito il db) e vedremo che i passi effettuati dall’entity framework (tralasciamo la tabella [dbo].[__MigrationHistory] che è privata del framework e non ci interessa in questo contesto).

Vedendo che non esistono le tabelle richieste, il framework le crea per noi:

CREATE TABLE [dbo].[Starships] (      
    [ID] [int] NOT NULL IDENTITY,      
    [Name] [nvarchar](max),      
    [Designation] [nvarchar](max),      
    CONSTRAINT [PK_dbo.Starships] PRIMARY KEY ([ID])  
    ) 

CREATE TABLE [dbo].[Crewmen] (      
    [ID] [int] NOT NULL IDENTITY,      
    [Name] [nvarchar](max),      
    [Rank] [int] NOT NULL,      
    [ReportingOfficier_ID] [int],      
    [Starship_ID] [int],      
    CONSTRAINT [PK_dbo.Crewmen] PRIMARY KEY ([ID])
)

In seguito vengono creati gli indici:

CREATE INDEX [IX_ReportingOfficier_ID] ON [dbo].[Crewmen]([ReportingOfficier_ID]) 

CREATE INDEX [IX_Starship_ID] ON [dbo].[Crewmen]([Starship_ID])

E poi le constraint:

ALTER TABLE [dbo].[Crewmen] 
ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Crewmen_ReportingOfficier_ID] 
FOREIGN KEY ([ReportingOfficier_ID]) REFERENCES [dbo].[Crewmen] ([ID]) 


ALTER TABLE [dbo].[Crewmen] 
ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Starships_Starship_ID] 
FOREIGN KEY ([Starship_ID]) REFERENCES [dbo].[Starships] ([ID])

Notiamo subito un paio cose interssanti:

L’ordine di creazione delle tabelle è quello relazionale e non quello del codice.
L’entity framework crea per noi gli indici sulla chiave esterna.

Mentre la prima è attesa, la seconda è una ottima notizia: utilizzando SSMS 2012 avremmo avuto solo la constraint e non l’indice:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Starships SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Crewmen ADD CONSTRAINT
    FK_Crewmen_Starships FOREIGN KEY
    (
    Starship_ID
    ) REFERENCES dbo.Starships
    (
    ID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 
    
GO
ALTER TABLE dbo.Crewmen SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Gli inserimenti avvengono in questa maniera:

exec sp_executesql N'insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID])
values (@0, @1, @2, @3)

select [ID]
from [dbo].[Crewmen]
where @@ROWCOUNT > 0 and [ID] = scope_identity()',

N'@0 nvarchar(max) ,@1 int,@2 int,@3 int',
@0=N'Leonard McCoy',
@1=4,
@2=3,
@3=1

Notiamo come ogni inserimento siano in realtà due statement separati:

insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID])
values (@0, @1, @2, @3)

select [ID]  from [dbo].[Crewmen]  where @@ROWCOUNT > 0 and [ID] = scope_identity()

(NB: potreste vedere comparire anche un SELECT StatMan([SC0])… ma questo non è dovuto ovviamente a EF).

Evidentemente EF usa il secondo statement per sincronizzare l’entità persisita via codice con l’ID generato da SQL. Domanda: come mai l’EF non usa la clausola OUTPUT?

La risposta più probabile è che si aspetti zero record in caso di fallimento (da where @@ROWCOUNT > 0) mentre lo statement OUTPUT restituisce righe anche se l’operazione fallisce. Per maggiori dettagli vedere http://msdn.microsoft.com/en-us/library/ms177564(v=sql.110).aspx.

Nei post successivi continueremo con l’analisi delle interazioni EF5.0-SQL.

Happy coding,

Francesco Cogno

Entity Framework 5.0 e Microsoft SQL Server (parte 1)

Contained database authentication e user with password con SID identici

Learning Resources for SQL Server

SQL Server on MIcrosoft Connect

SQL server 2008 R2

Contained database authentication e user with password con SID identici

Entity Framework 5.0 e Microsoft SQL Server (parte 1)