Tárolt eljárás: hol és miért jobb?

A tárolt eljárásokat az SQL szerver tárolja és futtatja. Ahhoz, hogy el tudjuk dönteni szükségünk van e rá, nézzük meg milyen előnyei vannak:

• Biztonság
• Teljesítmény

Ha az alkalmazás tervezésekor már látszik, hogy fentiek közül valamelyik sokkal többet nyom a latba, mint az egyéb követelmények, akkor érdemes elgondolkodni rajta. Ebben a post-ban az első pontról fogok írni.

Biztonság:

A tárolt eljárás attól ad nagy biztonságot, hogy a user annak futtatására kapja a jogot és nem a táblákra (select, insert, update, delete jogokat). Ezzel a tipikus SQL injection kizárva.

Akkor is nagyon hasznos, amikor már betörtek az alkalmazás szerverekre és ellopták az adatbázis kapcsolat konfigurációs (felhasználó, jelszó, stb) fájljait. Ekkor a tárolt eljárások futtatására lesz joga a támadónak és nem pl egy “SELECT * FROM jelszavak;” parancs futtatására. A tárolt eljárás pedig mindig azt adja vissza, amit beleprogramoztunk. Eddig tudott minket védeni az SQL szerver, innen jövünk mi.

Hol tudjuk ezt a gyakorlatban nagyon jól használni? Pl.: Ott, ami most is az orrunk előtt van, a weben :). Ha van egy webalkalmazásunk, akkor az egész világnak esélyt adunk rá, hogy feltörje azt.

Legyen is egy ilyen webalkalmazásunk, ahol be tudnak login-olni a usereink. Tételezzük fel, hogy tárolt eljárásaink vannak. A támadó bejutott a webszerverre (az adatbázis másik fizikai szerveren van), ellopta az alkalmazás logikáját, tudja futtatni a tárol eljárásainkat. Idővel kitalálja, hogyan kell őket paraméterezni, hogy el tudja lopni pl.: a felhasználó / jelszó párosokat. Már ezzel is sokat nyertünk, de itt lenne a vége, ennyire futotta? Közel sem! Nézzük meg egy példán keresztül mit tehetünk még!

A rosszul megírt eljárás bekéri a user azonosítóját, visszaadja az adatait, majd az alkalmazás elvégzi a hitelesítést:

create procedure usp_user_get_by_id
	@user_id int
as
begin
	set nocount on;
	
	select
		user_name,
		password,
		create_date
	from users
	where user_id = @user_id
end
go

 

A jól megírt eljárás elvégzi a hitelesítést majd visszaadja az adatokat:

create procedure usp_user_get_by_id_password
	@user_id int,
	@password varchar(64)
as
begin
	set nocount on;
	
	select 
		user_name,
		create_date
	from users
	where user_id = @user_id
		and password = @password
	
end
go

 

Mindössze annyi történt, hogy a logika átkerült az alkalmazásból az adatbázisba. Így az érzékeny adatnak nem kellett elhagynia az adatbázis szervert. Ezzel tehát még egy védelmi falat húztunk fel. És ehhez semmi nagy varázslat nem kellett, csak szem előtt tartani, hogy a biztonság a legfontosabb az alkalmazásunkban.

Tehát ami védett: tárolt eljárás + a logika az sp-ben volt.

A logika a tárolt eljárásban egyébként is egy izgalmas téma, vannak nagyon jó felhasználási területei, ma bemutattam egyet.

Megtartottam az első SQL Workshop-om :)

Pár hete, igaz cégen belül, de jó tapasztalás volt. Kérték, hogy érdekes legyen, ne az SQL működésébe menjünk bele, így hát megpróbáltam univerzális (adatbázis kezelőtől független) szabályokat a miértekkel és esettanulmányokat bemutatni, az egyiket itt közzé is tettem. Egyértelműen az esettanulmányok voltak sikeresebbek.

Maga az előadás nem volt profi, viszont hasznos, utólag a tények ezt mutatják. A tervezett hossz 1 óra volt, de a sok kérdés miatt, 2×1 óra lett belőle.

Az előadás után sokat töprengtem, mire helyre állt a fejemben, hogy hogyan lehetne jobbá tenni egy-egy ilyen előadást. Egyelőre ezekre jutottam:

• Az előadásban felváltva legyenek a pihentetőbb, gyakorlati (érdekes) részek (pl. esettanulmány) és a fárasztóbb, elméleti részek, amik a tudást adják
• A tanagyag egyszerű, lényegre törő és logikus legyen, gyakorlati példákkal alátámasztva
• Legyenek vicces részek, mert nevetni mindenki szeret
• Legyen egy jegyzet, amit megkaphat a hallgató

 

Persze el is lehetne olvasni egy (szakmai) könyvet, hogy milyen is egy jó előadás, de most úgy gondolom, hogy még nem érné meg a befektetett időt 🙂

Megjött (már egy ideje): dm_exec_function_stats

Az SQL Server 2016-al  végre megjött a dm_exec_function_stats DMV is. Még nem tud mindent, de azért már jó, hogy van. És jó tudni róla 🙂

Részletesen pl.: itt mutatják be.