Hasznos kategória bejegyzései

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.

Kezdjük a végén?!

Az adatok valahol megszületnek, ami általában egy alkalmazás szerver szokott lenni. Amikor adatbázisba készülünk letárolni, akkor hosszú vándorútra indulnak a születési helyüktől az adatbázisig. Vannak, amik “messzebbről” indulnak és hamarabb érkeznek meg és van fordítva is. Itt már lehet sejteni, hogy az adatok nem feltétlen abban a(z) (idő)sorrendben kerülnek be az adatbázisba, ahogy létrejönnek. Az esetek többségében ez nem is probléma, mert nem lényeges, viszont van, amikor a legfontosabb tényezővé válik. Ezzel az esettel már többféle formában is találkoztam, most egy egyszerűbb példán keresztül mutatnám be.

 

Legyen egy felhasznalok táblánk, ami az alábbiként néz ki és azt rögzíti, hogy ki és mit csinált a weboldalon:

id: sorszam

felhasznalo: akire vonatkozik ez a sor

tevekenyseg: amit csinált a felhasználó

tevekenyseg_kezdete: amikor elkezdte a tevékenységét a felhasználó az alkalmazás szerint

sor_keletkezesi_ideje: amikor az adatbázisba került a sor

 

Az elvárt működés, hogy belép a felhasználó, végez tevékenységeket, végül kilép.

Nézzünk egy képet, ami erre rácáfol:

01_order

 

A fenti képen tisztán látszik, hogy Kati utolsó három sorának a “tevekenyseg_kezdete” mező ugyanazt a dátumot tartalmazza (aminek nem kell így legyen, hogy a probléma valós maradjon), viszont a “kilépés” tevékenysége (id = 5) hamarabb érkezett be az adatbázisba, mint a “negyedik tevékenység” sora (id = 6).

Most, hogy ismerjük a problémát már bármilyen mesét köré lehet szőni, hogy ez miért rossz. A mienk legyen az, hogy, ha a felhasználó kilép, akkor elindul egy folyamat, ami minden tevékenységéért jóváír neki x összeget. Itt meg kimaradna egy tevékenység.

Ha ezt még nem ismertük, akkor jó ha tudunk róla. Viszont rossz hír, hogy nincs általános megoldás ahány eset annyi féle.

 

 

SQL Server Linuxon

Juhúú, megjelent az SQL Server Linuxon. Install segédlet itt: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup

Mint látszik jelenleg 3 platformon érhető el:

  • Red Hat Enterprise Linux
  • Ubuntu
  • Docker Engine

Én Ubuntura raktam fel teszt jelleggel. Ebben a postban néhány fontos konfigurációs  beállítást mutatok be, hogy az SQL Server ne nyűg, hanem használható társ legyen.

Telepítás után rendelkezésre áll az sqlcmd konzol. Gyorsan felejtsük el, keressünk valami használható toolt helyette. Ilyen pl.: a Heidi SQL. Én windows alól, távolról kapcsolódtam az Ubuntura telepített szerverhez SSMS-el. Szerintem ez a legjobb tool a kezeléséhez.

Csatlakozzunk az SQL szerverhez, példa itt: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd

 

Most állítsuk be, hogy ne zabálja ki alólunk a memóriát a szerver:

use master
go

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
-- Min memory
EXEC sys.sp_configure N'min server memory (MB)', N'512'
GO
-- Max memory
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
-- Optimize memory for ad hoc queries
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

--Nézzük meg, hogy sikerült e:
select
name,
minimum,
value_in_use
from sys.configurations
where name like '%server memory%'
or name like '%optimize for ad hoc workloads%'
go

 

02_memory_config

 

Állítsuk be a modell adatbázist SIMPLE recovery-re. Ez azért fontos, mert az újonnan létrehozott adatbázisok ennek az adatbázisnak a mintájára fognak létrejönni. Aki nem tudja, hogy ez mire való, annak érdemes átállítani. A lényege, hogy az adatbázisok nem fogják felzabálni a diszket.

 

-- Deault recovery modell
ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT
GO

 

Ha esetleg valami baj van a szerverrel, azt az error logban lehet megnézni, nekem default ide települt: var/opt/mssql/log

 

Ami engem érdekelt telepítés után, az az OP rendszer és az SQL Server kapcsolata. Itt olyan jellegű SQL utasításokra gondolok, amik Windows-on pl.: a registryben matatnak vagy a fájlokhoz köthetőek valahogy. Voltak, amik nem ment ment, szóval majd doksikat kell néznem 🙂 De ezt leszámítva eddig minden más rendben volt.