Dynamic SQL en owner chaining, wat doe ik fout?

[Gonadan]

Situatie is als volgt (SQL Server 2012):
• Database 1 (DB1) bevat views en stored procedures welke toegankelijk moeten zijn voor bepaalde users.
• Database 2 (DB2) bevat de tabellen waar de views en stored procedures interactie mee hebben, deze zijn niet toegankelijk.

Voor de views is dat simpel, rollen definiëren en SELECT op de view wel toestaan en op de tabel niet. Voor de stored procedure is het in theorie ook simpel, ware het niet dat ik dynamische SQL _moet_ gebruiken.

Daardoor gaat de 'chain' die ik heb aangezet in de betreffende databases stuk en mag de gebruiker alsnog niet bij de tabel.

Naar aanleiding van tips heb ik al geprobeerd om een aparte login te maken die owner is van beide databases, en dan de stored procedure als 'OWNER' te laten draaien.

Toch krijg ik volgende foutmelding:
The server principal "blaat_owner" is not able to access the database "DB2" under the current security context.

Wat doe ik fout?

[Kalin848]

Maak je heel toevallig gebruik van application roles en een partially contained database ??
Daarmee gaat het sowieso namelijk niet werken.

Volgens mij is het probleem dat je de dynamische SQL waarschijnlijk draait onder de credentials van de gebruiker (die dus geen rechten op de tabel heeft in db2), deze kan geen objecten aanroepen waar hij geen rechten op heeft.

Waarschijnlijk gaat het d.m.v. 'execute as' wel goed.

Groeten,
Kalin

[Gonadan]

Nee helaas. Daar maak ik geen gebruik van.

DB1 en DB2 hebben dezelfde 'owner', deze is gekoppeld aan schema dbo en role db_owner, alles wat een standaard database owner heeft. Qua rechten moet de gebruiker dus overal bij kunnen.

De stored procedure is ook geautoriseerd om uit te voeren onder die eigenaar (WITH EXECUTE AS OWNER), printen van systemuser geeft dan ook aan dat de stored procedure uitgevoerd wordt onder de 'owner' van de database. De dynamische SQL wordt dan dus ook uitgevoerd onder die 'owner'.

Wat mij onderhand is opgevallen is dat het al eerder mis gaat en dus niet aan de dynamische SQL lijkt te liggen. De 'owner' mag bij een eerdere query naar DB2 al niet bij de database terwijl hij daar owner van is, vreemd. Net alsof de owner van een database niet automatisch bij alle inhoud mag.

[Kalin848]

Het zijn van owner van de db hoeft niet te betekenen dat je bij alle objecten kunt.
Het is zelfs te realiseren dat een SysAdmin alles kan behalve de data inzien.

Heb je al geprobeerd of de owner inderdaad vanuit DB1 bij de data in DB2 kan komen ??

[Gonadan]

Natuurlijk, maar als de 'owneruser' aan de database gemapped is als 'dbo', schema 'dbo' heeft en rol 'db_owner' dan ga ik daar wel een beetje van uit.

Als ik inlog als die user kan ik dan ook gewoon bij de database, opvallend is wel dat hij opnieuw het verbindingsdialoog geeft bij het doen van een query. Alsof er een tweede stap moet plaatsvinden ofzo.

[Kalin848]

Werk je toevallig met contained databases, dat zou wel dit gedrag kunnen verklaren (logins zijn niet aan elkaar gerelateerd, authenticatie vindt plaats op db niveau, niet op server)....

[Gonadan]

Niet specifiek, mijn analyse:
• stored procedure draait als OWNER, maar gebruikt de USER en niet de LOGIN
• een USER heeft geen rechten op IMPERSONATE op een LOGIN
• faalt dus.

Uiteindelijk heb ik het werkend gekregen op de volgende manier:
• aanroepende user (CALLER) IMPERSONATE gegeven op de LOGIN proxyuser
• de stored procedure doet intern een EXECUTE AS LOGIN proxyuser
• proxyuser heeft de benodigde rechten.

Maar omdat dit voelt als 'security by obscurity' heb ik de oplossing afgewezen. Als iemand immers doorkrijgt dat de proxyuser bestaat kunnen ze ook direct EXECUTE AS doen en mijn logicalaag omzeilen.

Nieuwe oplossing gevonden en geïmplementeerd, het werkt.
• Certificate gemaakt, en daaruit een user in DB2.
• USER in DB2 juiste rechten gegeven.
• Certificate gekopieerd naar DB1
• Stored procedure in DB1 gesigned.

Nu werkt het zonder rare impersonate constructies, als je execute op de stored procedure hebt zorgt het certificaat voor de overige rechten om data op te halen.

Voor views werkt het sowieso al vanwege de ownership chain die bestaat tussen de beide databases.