Sto riprogettando un'applicazione web con SQL Server esistente. Questa applicazione viene utilizzata per tracciare oggetti (nomi, indirizzi, numeri di telefono, ecc.) E dati correlati (informazioni di valutazione, informazioni sui tribunali, informazioni sulla scuola, ecc ...). Uno dei requisiti è che alcuni utenti non dovrebbero poter vedere determinate colonne di dati. L'esempio più comune di questo è una colonna di intervento / controllo (gli utenti che raccolgono dati non dovrebbero essere in grado di vedere a quale gruppo appartiene un soggetto).
Voglio consentire ai supervisori di gestire queste autorizzazioni a livello di colonna per i propri utenti. A tal fine, sto prendendo in considerazione uno schema di dati in cui ho una tabella di autorizzazione che avrebbe una riga per ogni combinazione di utente / colonna e se sono autorizzati a visualizzare quella colonna.
+----------+-----------+------------+---------+
| Username | TableName | ColumnName | CanRead |
+----------+-----------+------------+---------+
| lukes | Person | Name | 1 |
| lukes | Person | Email | 0 |
| lukes | Person | Phone | 1 |
+----------+-----------+------------+---------+
Gli utenti non avrebbero accesso diretto alle tabelle, avrebbero solo accesso alle viste. Potrei creare una vista Permission come questa, che mi dice quali colonne l'utente può vedere per ogni tabella:
create view viewPermission as
select x.tablename, x.name canReadName, x.email canReadEmail, x.phone canReadPhone
from (
select username, tablename, columnname, cast(canread as tinyint) canread
from Permission
where username = SYSTEM_USER
) p
pivot(
max(canread)
for columnname in ([name],[email],[phone])
) x
Quindi nella vista per la tabella Persona (ad esempio):
create view viewPerson as
select case when p2.canreadName = 1 then t.name else '<not allowed>' end name,
case when p2.canreademail = 1 then t.email else '<not allowed>' end email,
case when p2.canreadPhone = 1 then t.phone else '<not allowed>' end phone
from Person t
cross join viewPermission p2
where p2.tablename = 'Person'
Funziona nei miei test iniziali. La mia domanda è, è un modo orribile per fare questo? Quali sono le alternative? So che potrei gestire la visibilità delle colonne all'interno della mia app, ma gli utenti hanno anche accesso diretto al database per la creazione di query ad hoc in Excel e Crystal. Non credo che incorporare questi controlli di permessi nelle viste dovrebbe degradare terribilmente le prestazioni , ma non ne sono sicuro.