SQL Server: come configurare gli schemi di authorization?

Ispirato da varie domande relative allo schema che ho visto …

Il concatenamento della proprietà consente di eseguire ESEGUI su una stored procedure senza autorizzazioni esplicite sulle tabelle che utilizzo, se sia la stored procedure che le tabelle si trovano nello stesso schema.

Se usiamo schemi separati allora dovrei esplicitamente GRANTARE XXX sulle tabelle dello schema diverso. L’esempio di concatenazione della proprietà lo dimostra. Ciò significa che l’utente in esecuzione proc memorizzato può leggere / scrivere direttamente le tabelle.

Sarebbe come avere accesso diretto alle variabili di istanza in una class, ignorando getter / setter, interrompendo l’incapsulamento.

Utilizziamo inoltre la sicurezza a livello di riga per limitare ciò che qualcuno vede e lo applichiamo nelle stored procedure.

Quindi, come possiamo mantenere la separazione degli schemi e prevenire l’accesso diretto alla tabella?

Ovviamente, la domanda non si applica se usi un ORM o non usi procs memorizzati. Ma non sto chiedendo se dovrei usare un ORM o un processo memorizzato nel caso qualcuno sentisse il bisogno di illuminarmi …

Modifica, esempio

CREATE USER OwnsMultiSchema WITHOUT LOGIN GO CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema GO CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema GO CREATE USER OwnsOtherSchema WITHOUT LOGIN GO CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema GO CREATE TABLE MultiSchema1.T1 (foo int) GO CREATE TABLE MultiSchema2.T2 (foo int) GO CREATE TABLE OtherSchema.TA (foo int) GO CREATE PROC MultiSchema1.P1 AS SELECT * FROM MultiSchema1.T1 SELECT * FROM MultiSchema2.T2 SELECT * FROM OtherSchema.TA Go EXEC AS USER = 'OwnsMultiSchema' GO --gives error on OtherSchema EXEC MultiSchema1.P1 GO REVERT GO CREATE PROC OtherSchema.PA AS SELECT * FROM MultiSchema1.T1 SELECT * FROM MultiSchema2.T2 SELECT * FROM OtherSchema.TA Go GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema GO EXEC AS USER = 'OwnsMultiSchema' GO --works EXEC OtherSchema.PA GO REVERT GO 

Modifica 2:

  • Non usiamo “concatenamento della proprietà del database incrociato”
  • La sicurezza a livello di riga è una falsa pista e irrilevante: non la usiamo ovunque

Temo che la tua descrizione o la tua concezione di Ownership Chaining non sia chiara, quindi lasciami cominciare con quello:

“Concatenamento proprietà” fa semplicemente riferimento al fatto che quando si esegue una stored procedure (o vista) su SQL Server, il batch attualmente in esecuzione acquisisce temporaneamente i diritti / permessi del proprietario dello sProc (o del proprietario dello sProc) durante l’esecuzione di quel codice SQL. Quindi, nel caso di uno sProc, l’Utente non può usare quei privilegi per fare qualcosa che il codice sProc non implementa per loro. Nota in particolare che non acquisisce mai l’ identity framework del proprietario, solo i suoi diritti, temporaneamente (tuttavia, ESEGUI COME … lo fa).

Quindi l’approccio tipico per sfruttare questo per la sicurezza è:

  1. Metti tutte le tabelle dati (e tutte le viste non di sicurezza) nel proprio schema, chiamiamolo [dati] (anche se in genere [dbo] viene usato perché è già lì e troppo privilegiato per lo schema dell’utente). Assicurati che nessun utente, schema o proprietario esistente abbia accesso a questo schema [dati].

  2. Creare uno schema chiamato [exec] per tutti gli sProc (e / o eventualmente eventuali viste di sicurezza). Assicurati che il proprietario di questo schema abbia accesso allo schema [dati] (questo è facile se fai diventare dbo il proprietario di questo schema).

  3. Crea un nuovo ruolo db chiamato “Utenti” e dagli il suo accesso EXECUTE allo schema [exec]. Ora aggiungi tutti gli utenti a questo ruolo. Assicurati che i tuoi utenti abbiano solo i diritti di connessione e non abbiano accesso a nessun altro schema, incluso [dbo].

Ora i tuoi utenti possono accedere ai dati solo eseguendo gli sProc in [exec]. Non possono accedere ad altri dati o eseguire altri oggetti.

Non sono sicuro che questo risponda alla tua domanda (perché non ero sicuro di quale fosse esattamente la domanda), quindi sentiti libero di reindirizzarmi.


Per quanto riguarda la sicurezza a livello di riga, ecco come faccio sempre con lo schema di sicurezza di cui sopra:

  1. Applico sempre la sicurezza a livello di riga come una serie di viste che racchiudono a specchio ogni tabella e confrontano l’id quadro dell’utente (solitamente con Suser_Sname () o una delle altre) in un elenco di sicurezza immesso da un codice di sicurezza nella riga stessa. Queste sono le viste di sicurezza.

  2. Crea un nuovo schema chiamato [righe], dai al suo proprietario l’accesso allo schema [dati] e nient’altro. Metti tutte le viste di sicurezza in questo schema.

  3. Revoca l’accesso del [exec] proprietario allo schema [data] e concedilo invece l’accesso ai dati allo schema [rows].

Fatto. Ora la sicurezza a livello di riga è stata implementata slittandola in modo trasparente tra gli sProc e le tabelle.


Infine, ecco una procedura memorizzata che utilizzo per aiutarmi a ricordare quanta parte di questa oscura roba di sicurezza funziona e interagisce con se stessa ( oops, versione corretta del codice ):

 CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX] as --no "With Execute as Owner" for this version --create User [UserNoLogin] without login --Grant connect on database :: TestSecurity to Guest --alter database TestSecurity set trustworthy on --Show current user context: select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (sproc)] , suser_sname() as sname , system_user as system_ --Execute As Login = 'UserNoLogin' select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (after exec as)] , suser_sname() as sname , system_user as system_ EXEC('select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (in Exec(sql))] , suser_sname() as sname , system_user as system_') EXEC sp_ExecuteSQL N'select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (in sp_Executesql)] , suser_sname() as sname , system_user as system_' --Revert select current_user as current_ , session_user as session , user_name() as _name , suser_name() as [suser (aftr revert)] , suser_sname() as sname , system_user as system_ 

[MODIFICA: versione corretta del codice)

Il mio 2c: il concatenamento della proprietà è legacy. Risale a giorni in cui non c’erano alternative, e rispetto alle alternative di oggi non è sicuro e grossolano.

Dico che l’alternativa non è permessi dello schema, l’alternativa è la firma del codice. Con la firma del codice è ansible concedere le autorizzazioni necessarie alla firma della procedura e garantire un ampio accesso di esecuzione alla procedura mentre l’accesso ai dati è strettamente controllato. La firma del codice offre un controllo più granulare e più preciso e non si può abusare del modo in cui è ansible il concatenamento della proprietà. Funziona all’interno dello schema, funziona attraverso lo schema, funziona attraverso il database e non richiede l’enorme buco di sicurezza della proprietà del database incrociato che concatena essere aperto. E non richiede il dirottamento della proprietà dell’object per scopi di accesso: il proprietario della procedura può essere qualsiasi utente.

Per quanto riguarda la seconda domanda sulla sicurezza a livello di riga: la sicurezza a livello di riga in realtà non esiste nelle versioni di SQL Server 2014 e precedenti, come una funzionalità offerta dal motore. Esistono varie soluzioni alternative e tali soluzioni alternative funzionano meglio con la firma del codice piuttosto che con il concatenamento della proprietà. Poiché sys.login_token contiene le firme del contesto e le controfirme, è ansible eseguire controlli più complessi di quanto si possa in un contesto di concatenazione della proprietà.

Dalla versione 2016 SQL Server supporta pienamente la sicurezza a livello di riga .

Puoi:

 Grant Execute On Schema::[schema_name] To [user_name] 

per consentire all’utente di eseguire qualsiasi procedura nello schema. Se non vuoi che sia in grado di eseguirli tutti, puoi negare esplicitamente l’esecuzione su una particolare procedura all’utente. Nega avrà la precedenza in questo caso.