Salut !

 Trebuie sa realizez auditul pentru tablele din proiectul la care lucrez. Acest audit implica si user-ul care a realizat o anumita actiune. Auditul il realizez cu ajutrul after triggerelor.
  Problema cu care ma confrunt este preluarea user-ului care a realizat o anume operatie ( tinand cont si de faptul ca triggerele nu pot primii parametrii), userii sunt stocati intr-o tabela separta in aplicatie.
 Ma gandeam sa preiau user-ul curent logat din cookie insa este prea costisitor. Am citit ceva legat de Context info din mssql care ar putea sa imi ofere acest user insa nu sunt sigur de acest lucru si cum sa il folosesc intr-un after trigger.

 Daca aveti idee despre aceasta problema sau cunoasteti niste linkuri legate de aceasta va rog sa ma ajutati.

 Multumesc !

1. Folosesti 1 singura conexiune (Sql Server)pentru fiecare user sau autentificare Windows ?

2. Daca folosesti 1 singura conexiune, poti customiza Application Name din connection string ( adauga numele user-ului logat) ca si hostname sa afli numele pc-ului

3. Daca folosesti autentificare Windows , nu vad problema - exista , de ex.,USER_NAME() ...

---

Ignat Andrei
http://serviciipeweb.ro/iafblog

Poti folosi USER_NAME() sau SUSER_SNAME(), vezi

http://technet.microsoft.com/en-us/library/ms191126(SQL.90).aspx

În 2008 ai deja change data capture. Despre ce versiune de SQL Server este vorba? Este vorba de useri de aplicaţie?
De exemplu, eu cred ca este vorba de un site care probabil conţine schema de bd generata de aspnet_regsql.exe
Am dreptate?

O posibilă soluţie este să vezi ce este cu CDC în 2008 şi să încerci implementarea în versiunea ta numai pentru ce te interesează... O altă idee este ca din aplicaţie să execuţi proceduri cărora le dai ca parametru UserID-ul.

---

Cătălin D.

Salut !

 In aceasta apicatie folosesc o singura conexiune pentru toti useri. Nu sunt interesat sa preia nume de useri precum "dbo" sau nume de login ci reale cum ar fi "Mircea" sau id-ul acestuia din tabela cu useri din baza de date. Nu folosim autentificare windows. 

  Acest lucru ar fi foarte bine daca l-as realiza folosind doar triggere, sau eventual sa chem o procedura stocata sau functie intr-un trigger care imi ofera acel user , in trigger , pentru a fi introdus in tabela de audit alaturi de informatiile corespunzatoare (operatie,data,...).

 Din ce am mai citit pana acum am observat ca acest lucru se poate face cu CLR Triggeres insa nu sunt sigur, alte articole faceau referinta la SqlContextInfo si Global.asax insa nu sunt clare. Folosim mssql 2005 developer edition. Nu folosim tool-ul de membership de la microsoft. Da, useri de aplicatie ma intereseaza pentru audit.

Multumesc !

Din fraza "userii sunt stocati intr-o tabela separta in aplicatie", înţeleg că toţi user-ii se conectează la server prin acelaşi login, deci nu poţi să foloseşti USER_NAME() sau SUSER_SNAME(), care ar fi fost soluţia cea mai elegantă. În acest caz, în afară de HOST_NAME() şi APP_NAME(), ai putea să foloseşti CONTEXT_INFO() în felul următor:

1. De fiecare dată când deschizi o conexiune, scrii numele utilizatorului în context_info, astfel:

DECLARE @user varchar(128)
SET @user='Gigel'

DECLARE @context binary(128)
SET @context=CONVERT(binary(128),@user)
SET CONTEXT_INFO @context

2. În trigger-ul tău de audit, determini utilizatorul curent, folosind ceva de genul:

DECLARE @user varchar(128)
SET @user=REPLACE(CONVERT(varchar(128),CONTEXT_INFO()),CHAR(0),'')

--SELECT @user

Răzvan

katalyn:

În 2008 ai deja change data capture. Despre ce versiune de SQL Server este vorba? Este vorba de useri de aplicaţie?
De exemplu, eu cred ca este vorba de un site care probabil conţine schema de bd generata de aspnet_regsql.exe
Am dreptate?

Nu cred ca CDC e cel mai nimerit in acest caz - mai degraba "SQL Server auditing"... Ca si CDC, auditing-ul este disponobil doar in editia "2008 Enterprise". 

Mircea, vezi daca te ajuta: http://www.sqljunkies.ddj.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk 

...iar daca vrei sa urmaresti schimbarile in schema, vezi si BOL ---> "DDL triggers"

Multumesc mult pentru postari , sunt foarte ajutatoare , din ce am mai citit de pe net si inspirat din aceste postari cred ca pot construi acest mecanism.

 Singura problema care ma mai macina acum este modul in care o sa trimit user curent al aplicatiei catre server. O solutie la care m-am gandit ar fi ca imediat dupa ce am deschis conexiunea sa rulez o procedura stocata care sa il primeasca ca parametru si sa modifice contextul corespunzator pe server.

 Momentan insa cred ca exista o metoda mai eleganta de a trimite user-ul , ma gandesc sa il trimit intr-un anume mod prin conexiune , daca se poate --- realizez distinctia intre login-ul de pe server ( de ex "sa") si cel a userului aplicatie pe care vreau sa il introduc pe conexiune. Are sens sa incerc acest lucru , credeti ca se poate ? M-ar ajuta intr-un anume mod CLR in acest sens ? Exista evenimente in Global.asax care imi pot fi de folos ?

Multumesc !  

 

mircea:

 Momentan insa cred ca exista o metoda mai eleganta de a trimite user-ul , ma gandesc sa il trimit intr-un anume mod prin conexiune , daca se poate --- realizez distinctia intre login-ul de pe server ( de ex "sa") si cel a userului aplicatie pe care vreau sa il introduc pe conexiune. Are sens sa incerc acest lucru , credeti ca se poate ? M-ar ajuta intr-un anume mod CLR in acest sens ? Exista evenimente in Global.asax care imi pot fi de folos ?

Multumesc !  

 

Daca folosesti 1 singura conexiune, poti customiza Application Name din connection string ( adauga numele user-ului logat)

---

Ignat Andrei
http://serviciipeweb.ro/iafblog

Nu cred ca este corect sa folosesti trigger de audit in combinatie cu management de user ID propriu aplicatiei. Trigerele acela for fi declansate si de operatiuni din SSMS, de joburi de SQL Agent, de activitati din SSIS sau orice altceva asemanator. Aceste operatiuni nu se vor executa in contextul unui user de aplicatie. Mai corect ar fi sa separi auditul in doua:

• 'data audit' care se face din aceste trigere si poate retine USER_NAME si SUSER_SNAME si face audit la toate operatiunile, inclusiv cele din SSMS sau SSIS
• 'application audit' care face audit la operatiunile facut de aplicatie si ca atare este apelat explicit de aplicatie (de exemplu intr-un Page_Unload) si retine datele relevante pentru aplicatie, inclusiv identitatea de 'user ID' specifica aplicatiei. Ceva de genul exec usp_record_audit @user='Mircea', @action='Hack Database', @severity='Fatal'

Ceea ce tu numesti 'elegant' altzi ar numi un 'hack' sau 'improvizatie de amator'.

---

http://rusanu.com

Mersi pentru comentarii !

 O sa iau serios in calcul modul de audit prezentat de tine Remus , mersi pentru modul de organizare oferit pentru audit. Acum am inceput sa lucrez si am reusit sa creez schimbarea de context folosind procedura stocata, insa aplicatia este in faza de design si s-ar putea sa propun o varianta de audit inspirata din ce a prezentat Remus , thx .

 Andrei am incercat sa introduc username-ul in stringul de conexiune, in c#, insa nu gasesc nimic acolo "Application Name" , si chiar nu stiu cum sa il "citesc" pe server. Aceasta proprietate se afla pe server ? , nu stiu de unde sa o setez. Te-as ruga sa-mi postezi niste linkuri care expun acest lucru de unde sa pot invata, sau sa lamuresc cumva acest lucru.

Multumesc !

mircea:

 Andrei am incercat sa introduc username-ul in stringul de conexiune, in c#, insa nu gasesc nimic acolo "Application Name" , si chiar nu stiu cum sa il "citesc" pe server. Aceasta proprietate se afla pe server ? , nu stiu de unde sa o setez. Te-as ruga sa-mi postezi niste linkuri care expun acest lucru de unde sa pot invata, sau sa lamuresc cumva acest lucru.

Foloseste un SqlConnectionStringBuilder si assigneaza-i proprietatea ApplicationName:

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(Settings.Default.connString);
scsb.ApplicationName = userName;
using(SqlConnection conn = new SqlConnection(scsb.ConnectionString))
{
conn.Open ();
....
}

In server i-l citesti din sys.dm_exec_sessions (cred ca e program_name). Ideea e ca trebuie sa autentifici user-ul intr-o conexiune, apoi sa deschizi o noua conexiune in care e setat, nu se poate seta 'din mers'.

---

http://rusanu.com

rremus:

In server il citesti din sys.dm_exec_sessions (cred ca e program_name).

Sau, mai simplu, cu funcţia APP_NAME().

Răzvan

Multumes mult pentru sfaturi !!! Sunt de mare ajutor ! , si m-au ajutat foarte mult sa pot construi mecanismul.

Am mai stat si pe alte forumuri insa pe acesta a fost cea mai mare activitate. Sunt foarte placut impresinat de acest lucru.

Multumesc inca o data !