Ograničavanje broja konekcija na SQL Server

Više puta mi se pojavila potreba da kontrolišem broj konekcija iz aplikacije na bazu podataka, samim tim i da ograničim pristup u odredjenim situacijama.

Na primer, ukoliko pravite program koji registrujete na odredjeni/dozvoljni broj konekcija, ili jednostavno prateći poslovnu logiku želite da zabranite korisniku da bude prijavljen na više mesta, ovo je rešenje koje možete da iskoristite.

SELECT  sistem.loginame,
        sistem.hostname,
		sistem.program_name
FROM    master..sysprocesses AS sistem
WHERE	sistem.hostname <> ''

Ovo će nam dati sledeći rezultat

Ovim upitom vidimo aktivne konekcije na bazu podataka/server, što nam daje neke potrebne informacije za početak, ali definitivno nedovoljne da definišemo i ograničimo pristup po potrebama.

Kao što vidite na rezultatu upita, pročitali smo

  1. LoginName
  2. HostName
  3. ProgramName

E, sad, ono što je jedan od najbitnijih podataka u celoj ovoj priči je, koja je to aplikacija otvorila konekciju? To možemo da vidimo iz kolone “program_name”.
Naravno, da bismo mogli da razgraničimo šta je “naša” konekcija, a šta je neki sistemski proces, treba nam naziv programa koji je “otvorio” konekciju. Iz rezultata primera vidimo da dve konekcije “drži” MSSQL, i treća je otvorena od programa “GHC”.

Kako definisati naziv programa u konekciji? Lako!
Jednostavno je potrebno dodeliti property na kreiranju ConnectionString-a kao u primeru:

private string CreateConnectionString(string server, string database, string username, string password)
{
        SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
        cb.DataSource = server;
        cb.InitialCatalog = database;
        cb.MultipleActiveResultSets = true;
        cb.UserID = username;
        cb.Password = password;
        cb.Pooling = true;
        // Vrednost naziva ne sme biti NULL, pa obratite paznju ako ga prosledjujete kao parametar
        cb.ApplicationName = System.Reflection.Assembly.GetEntryAssembly().GetName().Name;
        return cb.ToString();
}
U primeru smo iskoristili Reflection da dobijemo ime programa, vi ga možete proslediti metodi kao dodatni parametar, ali vodite računa da nije NULL, jer ćete dobiti Exception za to, ApplicationName property ne može biti NULL!

Dobro, sada imamo naziv programa, server login i ime računara sa koga je konekcija kreirana, i dalje nedovoljno…

Ono što fali je da znamo da je to naš korisnik.

Mi smo to rešavali na sledeći način. Tačnije, logika po kojoj smo uvek pravili security na aplikaciji nam se poklopila sa ovakvim zahtevima.

Naime, ono što je važno je da naša baza korisnike “mapira” na server login-e, što u praksi znači da će za svakog korisnika aplikacije, biti kreiran Sql Server Login sa istim username-om.

Ne zbog ovog ograničavanja, to dalje širi priči i otvara dosta dobrih mogućnosti kasnije, kao npr. kreiranje rola na bazi i automatsko dodeljivanje user-a roli, samim tim postavlljanje predefinisanog seta privilegija na tabele, procedure, itd…

Dakle, kada smo ustanovili da imamo korisnike u našoj bazi paralelno sa SQL Server login-ima, ostaje nam da malo promenimo upit da nam vrati podatke o našim korisnicima.

Pretpostavimo da je bazi postoji tabela “User”, koja u sebi, izmedju ostalog, ima i kolonu “Username”, koju koristimo za identifikaciju korisnika svakako. Vrednost kolone “Username” odgovara SQL Server login-u sa istim “LoginName”-om. Sada upit izgleda ovako:

SELECT  u.FirstName,
        u.LastName,
        sistem.loginame,
        sistem.hostname,
		sistem.program_name
FROM    master..sysprocesses AS sistem INNER JOIN
        [User] as u ON u.Username COLLATE DATABASE_DEFAULT = sistem.loginame COLLATE DATABASE_DEFAULT
WHERE   hostname <> ''

I rezultat je ono što nam treba:

I za kraj još jedna mala napomena i objašnjenje.

Obratite pažnju na COLLATION servera i baze podataka. Ovim upitom su JOIN-ovane tabele iz “master” baze i vaše baze, čiji COLLATION-i mogu biti različiti, što će dovesti do ozbiljnih problema pri pravljenju upita koji u sebi imaju npr. WHERE klauzulu, jer server neće moći da uporedi string-ove na različitim COLLATION-ima.

Zato koristimo COLLATE DATABASE_DEFAULT u upitu, što rešava ove probleme i dozvoljava da se upit izvrši iako kolacije nisu iste.
Pogledajte blog Pinal Dave-a za više informacija o problemu sa COLLATION-ima (i svemu ostalom vezano za SQL Server), da ja ne pričam ispričane priče.

Ovako ćete u svakom momentu imati mogućnost da proverite koliko konekcija je otvoreno ka serveru i od strane kojih korisnika, ostaje Vam da pustite mašti na volju, da napravite npr. neku lepu formu koja će korisnika nakon prijave obavestiti da je negde već prijavljen ili da je broj dozvoljenih konekcija prekoračen i sl.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s