Avatar billede dane022 Seniormester
28. september 2021 - 19:47 Der er 16 kommentarer og
1 løsning

Row level security

Hej

Jeg prøver at rode med row level security for første gang. Jeg har oprettet ud fra denne beskrivelse https://codingsight.com/introduction-to-row-level-security-in-sql-server/ og det virker indenfor sql, at adgangen begrænses til de rækker som brugeren må se.

Problemet opstår når jeg vil hente data til Excel. Der hentes ikke noget og det skyldes at det er brugernavnet "dbo" jeg genkendes som i sql verdenen og ikke mit windows login. Hvorfor ved jeg ikke og det er her jeg har brug for hjælp.

VBA kode:
Sub Hent()
   
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    sConnString = "Provider=SQLNCLI11;Data Source=.\SQLEXPRESS;" & _
                  "Initial Catalog=University;" & _
                  "Integrated Security=SSPI;"
   
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    conn.Open sConnString
    Set rs = conn.Execute("SELECT Id, name, role FROM persons")
   
    With Worksheets("test").Range("a2:c50000")
        .ClearContents
        .CopyFromRecordset rs
    End With

    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub
Avatar billede arne_v Ekspert
28. september 2021 - 19:58 #1
Integrated Security=SSPI

betyder brug Windows credentials.

Det er muligt at konfigurere SQLServer til at acceptere brugernavn og password.

DBO er ikke et rigtigt brugernavn men databasens owner.

Hvordan forbinder du når det virker udenfor Excel?
Avatar billede dane022 Seniormester
28. september 2021 - 20:13 #2
Tak for forklaring

Planen er at RLS skal virke med den Windows bruger man er logget på med og ikke et særskilt login.

Det virker udenfor Excel, dvs. når jeg er logget direkte på databasen og via query åbner tabel og tester de brugernavne der er oprettet via vejledningen på hjemmesiden. Hvis jeg åbner tabellen som normalt i sql og uden at angive bruger, får jeg ingen rækker vist.
Avatar billede arne_v Ekspert
28. september 2021 - 20:34 #3
Hvis du udenfor Excel bruger et nruegrnavn og password som virker kunne du erstatte:

sConnString = "Provider=SQLNCLI11;Data Source=.\SQLEXPRESS;" & _
                  "Initial Catalog=University;" & _
                  "Integrated Security=SSPI;"

med:

sConnString = "Provider=SQLNCLI11;Data Source=.\SQLEXPRESS;" & _
                  "Initial Catalog=University;" & _
                  "Uid=xxxxxx;Pwd=yyyyyy;"

hvor xxxxxx og yyyyyy er brugernavn og password.
Avatar billede dane022 Seniormester
28. september 2021 - 21:01 #4
Løsningen skal kunne bruges af flere brugere, så jeg ser det ikke som en mulighed at lægge password ind i koden.

Det jeg ikke forstår er, hvorfor er det ikke mit Windows brugernavn (brx) sql bruger til at afgøre hvilke rækker jeg må se? Hvorfor ser jeg kun rækker hvor role = dbo?
Avatar billede arne_v Ekspert
29. september 2021 - 00:34 #5
Det er stadigvæk ikke klart for mig hvad det er som virker og hvilket brugernavn SQLServer ser for det.
Avatar billede dane022 Seniormester
29. september 2021 - 10:10 #6
Lad mig prøve at forklare

I SQL databasen University, tabellen Person, er en af kolonnerne navngivet "Role". I kolonnen kan der stå Brx, teacher osv. Tanken bag setuppet, er at brugeren "teacher" kun ser rækker med Role=teacher osv.

Hvis jeg kører denne query i SQL:
EXECUTE AS USER = 'teacher';
Use University
SELECT * FROM Persons; -- All Records
REVERT;
GO

...virker det som det skal. jeg får kun vist data hvor Role=teacher. Gør jeg det samme med andre brugernavne, virker det også.

Det jeg ikke forstår er, hvis jeg forsøger at gøre det via Excel, så genkendes jeg ikke som min Windows bruger Brx. Det der sandsynligvis mangler, er at få denne linje: "EXECUTE AS USER = 'teacher';" ind i vba koden
Avatar billede arne_v Ekspert
29. september 2021 - 14:31 #7
Du kan vel bare udføre den EXECUTE AS i din VBA kode.

Der er en VBA funktion til at hente brugernavn.
Avatar billede dane022 Seniormester
30. september 2021 - 08:31 #8
Jeg kender godt til selve VBA koden for at hente brugernavnet, f.eks. Environ("username"), men jeg ved ikke hvad man skal skrive/flette det ind i SQL delen
Avatar billede arne_v Ekspert
30. september 2021 - 14:33 #9
Prøv:

conn.Execute("EXECUTE AS USER = '" & Environ("username") & "'")

conn.Execute("SELECT Id, name, role FROM persons")
Avatar billede arne_v Ekspert
30. september 2021 - 14:33 #10
Men jeg er dog lidt bekymret oversikkerheden hvis man selv lan ændre hvem man er!
Avatar billede dane022 Seniormester
30. september 2021 - 15:40 #11
Godt input. Dit forslag virker.

Jeg har også spurgt et andet sql orakel om hele opsætningen og han er inde på at ændringen skal ske i sql og ikke excel, hvor Row Level Security skal spørge på (suser_sname) og ikke user account (user_name). Dette vil sikkert også imødekomme den sikkerhedsbekymring du har, så man ikke kan ændre hvem man er.

Det andet sql orakel er ansat ved at firma og koster konsulentløn hvis han skal hjælpe mig videre. Det lyder på ham som om det er en større omgang, men jeg ved reelt ikke om det er tilfældet og i hvor høj grad og hvor meget det kræver.

Jeg værdsætter din hjælp indtil videre.
Avatar billede dane022 Seniormester
04. oktober 2021 - 14:33 #12
Hej Arne. Har du kommentarer til det med suser_sname som den anden havde skrevet? her tænker jeg på om det er en stor, evt. for stor, opgave ?
Avatar billede arne_v Ekspert
05. oktober 2021 - 00:47 #13
suser_sname siger mig slet ikke noget.

Jeg ville nok have opbygget den security via en stored procedure.
Avatar billede dane022 Seniormester
07. oktober 2021 - 20:27 #14
Som jeg forstår det, så returnerer suser_sname windows login, så det er på den måde smartere end at jeg skal oprette alle som brugere af databasen. Det kan ende med at der er 2-300 brugere.

Det lyder smart med en stored procedure, men jeg kan ikke greje hvad denne skal ændres til, for at den bruger windows login i stedet for SQL login:

EXECUTE AS USER = 'brugernavn';
Use University
SELECT * FROM Persons;
REVERT;
GO
Avatar billede arne_v Ekspert
07. oktober 2021 - 20:40 #15
EXECUTE AS USER = suser_sname;

vil jeg tro.
Avatar billede arne_v Ekspert
07. oktober 2021 - 20:41 #16
Ideen med en SP er at forhindre at folk bare retter den SQL i VBA koden.
Avatar billede dane022 Seniormester
08. oktober 2021 - 08:20 #17
Får denne fejl "The name "suser_sname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."

Ved brug af:
EXECUTE AS USER = suser_sname
Use University
SELECT * FROM Persons;
REVERT;
GO
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester