[SQL] rechtenprobleem met sp

[majornono]

Ik heb een probleem met rechten in een sp. De rol waaronder ik een sp uitvoer heeft execute rechten op de sp. In deze sp wil ik een dynamische select uitvoeren op een tabel waarop de rol geen rechten heeft.
De sp ziet er versimpelt zo uit (regel 4 toegevogd tbv dit voorbeeld):

SQL:

create procedure haalrecord @ID
    declare @select varchar(8000)

    select * from tabel1 where id = 1

    set @select = 'select * from tabel1 where id=' + convert(varchar(2), @ID)

    exec (@select)
return

Als ik regel 4 uitvoer krijg ik als resultaat de records uit tabel 1 met id = 1
Als ik regel 8 uitvoer krijg een rechten foutmelding: select access denied on blablabla...

Blijkbaar wordt het statement op regel 4 onder een andere role dan regel 8. Heeft iemand ideeen?

Ik heb zelf ook al geprobeerd om sp_executesql te gebruiken ipv alleen exec, maar het probleem hiermee is dat mijn originele @Select variabele ruimschoots de 4000 chars overschrijft(zie declaratie van @Select), het max. aantal karakters van de input van sp_executesql.
Die accepteert ook ntext als input, deze is groot genoeg maar ntext kun je niet als lokale variabele in een sp definieren.

[whoami]

Wie is de owner van die tabel, en wie is de owner van die SP ?

[whoami]

Trouwens, waarom maak je gebruik van 'dynamic SQL'? Dat is niet echt goed voor de performance van je SP.
Je kan toch gewoon dit doen:

code:

CREATE PROCEDURE haalrecord ( @ID INT)
AS
BEGIN
  SELECT * FROM tabel WHERE tabel.id = @ID
END

[majornono]

owner van zowel stored procedure als table is dbo. Omwille van de beveiliging heeft de user alleen execute recht op de sp en geen select recht direct op de tabel.

Zoals ik al zei, het voorbeeld is sterk vereenvoudigd. Het eigenlijke statement is een join op zo'n 10 tabellen. Ik gebruik dynamische sql omdat de select, where en oder by moeten worden aangepast/uitgebreid met parameters van de sp.

[whoami]

Probeer die dynamische SQL eens uit te voeren met de Stored procedure sp_executesql ipv met EXEC

[majornono]

whoami schreef op 13 november 2003 @ 13:17:
Probeer die dynamische SQL eens uit te voeren met de Stored procedure sp_executesql ipv met EXEC

al geprobeerd, gaat niet. De sql is > 4000 chars. Dat past niet in een nvarchar (max 4000).

sp_executesql accepteert als par nvarchar, nchar & ntext. De eerste 2 zijn dus te klein, ntext kun je niet als lokale variabele in een stored procedure definieren (zie openingspost)

[P_de_B]

majornono schreef op 13 november 2003 @ 13:02:
owner van zowel stored procedure als table is dbo. Omwille van de beveiliging heeft de user alleen execute recht op de sp en geen select recht direct op de tabel.
.

Maar als jij dus dynamisch SQL gaat gebruiken heeft 'ie wel SELECT rechten nodig, dit van niet in de scope van het uitvoeren van de SP.

Misschien een view gebruiken in de sp, en rechten op de view zetten?

[majornono]

Ik heb idd. al gelezen dat zonder rechten op de tabel dynamische sql vrij nutteloos is.

Een optie zou idd zijn om een view te maken en de rechten daarop te zetten.
Ik herschrijf de SP omdat deze ontzettend traag is geworden door het gebruik van tijdelijke tabellen en sub sp's die minstens 3 sec. per record duren.

Ik vraag me af wat de winst is als het een left outer join wordt op meerdere views?

Wellicht beter te overwegen om select rechten aan de user toe te kennen?
(al geprobeerd om dit te doen in de sp, maar dat mag ook niet, duh )

[P_de_B]

Ik vraag me af wat de winst is als het een left outer join wordt op meerdere views?

Dat is moeilijk te zeggen. In eerste instantie zou ik zeggen, probeer het eens. Daarnaast moet je natuurlijk kijken of je indexen etc. goed zijn

Wellicht beter te overwegen om select rechten aan de user toe te kennen?

Dat zou wel de makkelijkste oplossing zijn, maar ik neem aan jullie beleid niet voor niks is gekozen?

In het algemeen is het natuurlijk zo dat sp's met veel temp tabellen etc. traag zijn.

[whoami]

OUTER joins en eventueel gebruik maken van COALESCE of ISNULL in je where clausule, dat zou ik eens proberen.

[majornono]

P_de_B schreef op 13 november 2003 @ 15:11:
Dat zou wel de makkelijkste oplossing zijn, maar ik neem aan jullie beleid niet voor niks is gekozen?

In het algemeen is het natuurlijk zo dat sp's met veel temp tabellen etc. traag zijn.

Idd is ons beleid niet voor niets gekozen. Maar de hoofdreden ervoor is dat de asp pagina's geen select uitvoeren en hierdoor de logica in de database blijft. Maar ik vraag me af of dit opweegt tegenover het enorme performanceverlies waar we nu mee te kampen hebben.

Er zit denk ik in 50% van de sp's (zo'n 1000) tijdelijke tabellen, alsmede sub stored procedures

[whoami]

Normaal gezien kan het een grote performance winst opleveren als je gebruik maakt van stored procedures.
Echter, die SP's moeten wel nog goed geschreven worden. String concatenation binnen stored procedures is meestal zo geen goed idee.

Bepaalde berekeningen voer je best op de server (of in de DB uit), andere doe je dan weer best op de client.