Intelegerea sistemului de securitate din Microsoft SQL Server

Intelegerea sistemului de securitate din Microsoft SQL Server este esential pentru o buna utilizare a acestuia.

La momentul conectarii la SQL Server, se petrec o serie de lucruri care nu ies in evidenta la prima vedere.

Cand se incearca conectarea la SQL Server, securitatea este verificata in trei locuri. Validarea se efectueaza la nivelul sistemului de operare, in SQL Server (sub forma unui identificator de conectare SQL Server) si la nivelul unei baze de date individuale (sub forma unui nume de utilizator al bazei de date).

Faptul ca exista un identificator de conectare nu spune nimic despre bazele de date pe care le puteti accesa, pentru aceasta va trebuie un nume de utilizator al bazei de date.

Cerere de conectare la retea adresata lui SQL Server

Cerere de autentificare a identificatorului de conectare adresata

lui SQL Server

Cerere de autentificare ca utilizator al bazei de date

Autentificarea Windows

Cand se incearca conectarea de pe un calculator client la un calculator Windows pe care ruleaza Microsoft SQL Server, s-ar putea ca Windows sa solicite validarea conexiunii la retea. Aceasta depinde de biblioteca de retea SQL Server. Daca se foloseste biblioteca Named Pipes sau Multiprotocol, conexiunea la retea trebuie validata de sistemul de operare Windows ca sa se poata comunica cu Microsoft SQL Server.

Autentificarea SQL Server

Pentru a se putea realiza conexiunea la SQL Server, trebuie sa se furnizeze un nume de utilizator si o parola pentru SQL Server, ambele valide. Daca atributele de identificare sunt valide, conexiunea la SQL Server se face cu succes. Daca nu, se interzice accesul - chiar daca autentificarea de retea Windows s-a incheiat cu succes.

Numele de utilizator al bazei de date SQL Server

Pentru a se putea utiliza fiecare baza de date din sistem, trebuie sa se permita in mod explicit accesul la ea. Accesul la o baza de date se obtine in mai multe moduri, care vor fi examinate mai tarziu. Daca nu exista un nume de utilizator pentru o baza de date, se va interzice accesul la aceasta.

Permisiuni

Ultimul nivel de securitate il constituie permisiunile. Dupa ce se realizeaza conexiunea la SQL Server si se acceseaza o baza de date, trebuie sa se ofere dreptul explicit de a accesa obiectele bazei de date (numai pentru citire sau si pentru modificari).

Modurile de securitate SQL Server

Microsoft SQL Server ofera doua moduri de securitate: Windows Integrated Mode si Mixed Mode (care include atat Windows Integrated, cat si SQL Server Authentication). Modul de securitate determina daca de validarea cererilor de conectare la SQL Server se ocupa sistemul de operare sau atat sistemul de operare cat si SQL Server.

Configurarea modului Security

Pentru a stabili modul de securitate folosit de server, adica Windows Integrated Mode sau Mixed Mode (care include atat Windows Integrated, cat si SQL Server Authentication) se utilizeaza un instrument de gestiune din SQL Server si anume SQL Server Enterprise Manager ( meniul Properties - Security ).

Pentru a modifica atributul de securitate, este suficient sa se execute bifarea optiunii dorite. Modificarea este pusa in aplicare dupa repornirea serviciului SQL Server.

Se poate activa facilitatea de monitorizare (audit) pentru server, in mod prestabilit, aceasta este dezactivata. Optiunile disponibile sunt Failure (incercarile esuate de conectare la SQL Server), Success (conexiunile realizate cu succes) sau ambele All. Rezultatele procesului de monitorizare se pot examina atat in jurnalul de erori SQL Server.

Mixed Mode

Daca se foloseste Mixed Mode (modul mixt), un utilizator se poate conecta la SQL Server fie cu Windows Intregrated Mode, fie cu SQL Server Authentication Mode. Mixed Mode este cea mai buna alegere pentru ca ofera posibilitati de conectare cu calculatoare din retele non-Windows. Pentru a intelege ambele moduri de autentificare, trebuiesc examinate indeaproape. Cel mai simplu este sa se inceapa prezentarea cu SQL Server Anthentication Mode, care reprezinta modul traditional de conectare la SQL Server.

SQL Server Authentication Mode

SQL Server Authentication Mode este modul in care SQL Server accepta un identificator si o parola de la un utilizator si le valideaza sau nu, fara nici un ajutor din partea sistemului de operare. Nu este la fel de sigura ca Windows Integrated Mode si nu se recomanda pentru sisteme SQL Server pe care sunt stocate informatii sensibile.    

Informatiile despre identificatori se pastreaza in SQL Server (in baza de date master, tabelul de sistem sysxlogins).

Parole Parolele SQL Server Authentication Mode sunt amplasate in coloana de parole a tabelului sysxlogins din baza de date master.

De spus ce este rolul ( introducerea lui )

Trebuie sa tinem cont de urmatoarele aspecte

Coloana de parole poate fi vizualizata numai de un membru al rolului sysadmin (inclusiv identificatorul sa). Nu este accesibila nici unui alt utilizator sau identificator, cu exceptia cazului in care acest lucru s-a stabilit in mod explicit.

Algoritmul de criptare functioneaza intr-un singur sens. Cand o parola este criptata, decriptarea ei nu este posibila. in momentul conectarii, parola pe care o furnizati este criptata si apoi comparata cu parola criptata din tabelul sysxlogins. Daca cele doua sunt identice, se permite accesul la server. Daca nu, se obtine mesajul de eroare Login Failed si nu se poate face conectarea in plus, daca folositi un identificator de securitate Windows Integrated, in SQL Server nu se stocheaza nici un fel de parole.

Administrarea identificatorilor SQL Server Authentication Mode

Primul pas in configurarea serverului pentru acces este crearea identificatorilor. Se pot adauga identificatori folosind procedura de sistem memorata sp_addlogin:

sp_addlogin [@loginame =] 'identificator' [,[@passwd =] 'parola' [,[@defdb=] 'baza_date' [,[@deflanguage =] 'limba'

In sp_addlogin:

-identificator este numele cu care se doreste sa se conecteze utilizatorul. Acest nume trebuie sa fie un identificator valid SQL Server (incepe cu o litera sau cu unul din caracterele #, @ sau _ , contine oricare din aceste caractere, litere si cifre - maximum 128 de caractere Unicode).

-parola este parola aferenta acestui identificator. Parola este nula daca nu alegeti una acum.

-baza_date este baza de date prestabilita pe care doriti sa o acceseze utilizatorul cand se conecteaza. Daca nu specificati acest parametru, baza de date prestabilita va fi master.

-limba este limba prestabilita ce va fi folosita pentru acest utilizator cand se conecteaza. Daca nu specificati valoarea parametrului, limba prestabilita este US_English.

Pentru a adauga un identificator pe server, se deschide SQL Server Query Analyzer ( instrument de gestionare si de lucru din SQL Server) si dupa ce se realizeaza conectarea se executa urmatoarea comanda Transact-SQL (T-SQL):

EXEC sp_addlogin 'numele', 'parola'

Pentru a putea schimba parola se foloseste procedura de sistem memorata     sp_password:

sp_password [[@old =] 'veche' [,[@loginame =] 'identificator'

In sintaxa precedenta:

- veche este parola cea veche;

- noua este parola cea noua

- in ceea ce priveste identificator, daca se face conectarea ca membru al rolului sysadmin sau securityadmin, se poate schimba parola oricui. De fapt, nici nu e nevoie sa se cunoasca vechea parola a unei persoane; se poate pur si simplu sa se declare ca fiind NULL.

Un detaliu important este ca membrii rolului securityadmin pot schimba parola oricui, mai putin parolele membrilor rolului sysadmin. Membrii rolului sysadmin pot modifica intr-adevar parola oricui, fara nici un fel de exceptii.

Ca o masura de siguranta, utilizatorii de rand nu pot face acest lucru si trebuie sa-si cunoasca vechile parole pentru a le putea schimba. Aceasta procedura nu trebuie sa fie dificila, pentru ca ei trebuie sa se conecteze inainte de a o executa.

Schimbarea parolei in mod regulat este o idee foarte buna. Din nefericire, Microsoft SQL Server nu ofera nici o metoda de a impune restrictii privind parola si alte masuri de securitate. Acesta este unul din motivele pentru care se doreste implementarea securitatii integrate. In sistemul de operare Windows se pot specifica dimensiunea minima a parolelor, frecventa schimbarii si un minimum de reguli privind complexitatea parolelor.

Alte doua proceduri de sistem memorate folosite pentru a gestiona identificatorii utilizatorilor sunt: sp_helplogins si sp_droplogins.

Procedura memorata sp_helplogins ofera un raport cu identificatorii creati pe server.

Procedura de sistem memorata sp_droplogins elimina un identificator din tabelul sysxlogins. Dupa stergerea identificatorului, utilizatorul respectiv nu se mai poate conecta la SQL Server.

sp_droplogin identificator

Pentru procedura sp_droplogin, identificator are aceeasi semnificatie ca si pentru celelalte proceduri memorate.

Windows Authentication Mode

Windows Authentication Mode este cealalta optiune din modul de securitate Mixed.

O conexiune realizata prin autentificare Windows se numeste conexiune de incredere (trusted connection).

In Windows Authentication Mode, dupa ce se realizeaza conectarea prin retea la SQL Server, trebuie sa se prezinte atributele de securitate din Windows (cunoscute sub numele de jeton de acces). Atributele respective se construiesc in cursul procesului de conectare la o retea Windows. Acestea sunt verificate in mod automat, fara a se afisa nici un mesaj.

Se poate acorda accesul la SQL Server direct prin conturile de securitate Windows, sau indirect prin grupurile Windows.

Cel mai mare avantaj al optiunii Windows Authentication Mode este ca utilizatorii nu trebuie sa-si mai faca probleme cu conectarea separata la SQL Server. Acest lucru este in concordanta cu conceptul conform caruia utilizatorii trebuie sa parcurga procedura de conectare la retea o singura data si sa retina o singura parola. in plus, se poate profita de faptul ca majoritatea utilizatorilor retelei au conturi de conectare la Windows, ceea ce reduce eforturile de administrare a conturilor pentru SQL Server.

Primul pas in configurarea securitatii Windows Authentication Mode nu are nici o legatura cu SQL Server - ci implica mai intai crearea grupurilor Windows pentru utilizatori, crearea conturilor utilizatorilor (daca nu exista deja) si adaugarea lor in grupurile recent create, apoi trebuie sa li se atribuie permisiune de conectare la SQL Server.

Permisiuni de cont Windows pentru conectarea la SQL Server

Dupa ce au fost definiti utilizatorii si grupurile, este momentul sa se acorde acestor grupuri acces la SQL Server. Pentru aceasta, se folosesc procedurile de sistem memorate sp_grantlogin, sp_revokelogin si sp_denylogin. Mai intai se acorda permisiuni de conectare grupurilor Windows, apoi, conform necesitatilor, utilizatorilor individuali. Prin aceasta metoda, numarul de comenzi necesare si eforturile de administrare se mentin la un minim, in conditiile unui control individual asupra privilegiilor de conectare.

Pentru a acorda permisiuni de conectare, folositi procedura de sistem memorata sp_grantlogin:

sp_grantlogin [@loginame =] 'identificator'

In aceasta sintaxa:

identificator este numele grupului sau utilizatorului de Windows caruia ii este acordat dreptul sa se conecteze la SQL Server.

Identificatorul trebuie sa fie de forma: BAZA_DATE_SECURITATE Nume_utilizator.

Acum, orice utilizator de Windows care este membru al unui grup caruia ia fost acordat accesul la SQL Server se poate conecta cu succes, in virtutea apartenentei la grupul respectiv.

Daca se foloseste aceasta modalitate de conectare o sa se observe ca desi grupul este entitatea careia i s-au acordat drepturi de conectare la SQL Server, in bara de stare va apare utilizatorul. Acest tip de conectare va ofera o pista de urmarire (audit trail) si permite un control ulterior mult mai bun asupra permisiunilor.

Se poate ridica dreptul unui utilizator sau al unui grup Windows de a se conecta la SQL Server, folosind procedura de sistem memorata sp_revokelogin:

sp_revokelogin [@loginame =] 'identificator'

In aceasta procedura:

- identificator este numele grupului sau al utilizatorului de Windows caruia se doreste sa-i fie ridicat dreptul de a se conecta la SQL Server.

sp_revokelogin inlatura un drept de conectare acordat anterior

Daca se doreste interzicerea dreptului de conectare la SQL Server unui anumit utilizator sau unui grup se foloseste procedura de sistem memorata sp_denylogin:

sp_denylogin [@loginame =] 'identificator'

In aceasta procedura:

- identificator este numele grupului sau al utilizatorului de Windows caruia se doreste interzicerea dreptul de conectare la SQL Server.

Interzicerea drepturilor are intotdeauna prioritate in raport cu drepturile acordate.

Tot ce am prezentat pana aici se poate realiza dupa fie folosind procedurile de sistem memorate ( prezentate anterior ) fie folosind instrumentul de gestiune grafica oferit de Microsoft SQL Server si anume SQL Server Enterprise Manager.

Utilizatorii bazei de date

SQL Server contine doua tipuri de identificatori:

- identificatori Windows - pentru grupuri sau utilizatori individuali;

- identificatori SQL Server, stocati in tabelul de sistem sysxlogins din baza de date master.

Dupa ce se configureaza securitatea identificatorilor si se definesc identificatorii, se poate trece la configurarea accesului la baza de date. Faptul ca exista un identificator pentru SQL Server nu ofera acces la toate bazele de date de pe server. Pentru aceasta este nevoie de un nume de utilizator al bazei de date.

Fiecare baza de date are o cale de acces separata, amplasata in tabelul de sistem sysusers al bazei de date respective. In esenta, identificatorii sunt asociati cu un nume de utilizator in fiecare baza de date pe care trebuie sa o acceseze utilizatorul.

Se poate realiza aceasta asociere sau se poate crea un utilizator al bazei de date folosind folosind procedura de sistem memorata sp_grantdbaccess:

sp_grantdbaccess [@loginame =] 'identificator' [@name_in__db =] 'nume_din_bd OUTPUT

In aceasta sintaxa:

identificator este identificatorul care a fost adaugat (utilizator SQL Server sau utilizator / grup Windows).

nurne_din_bd este numele pe care doreste sa-1 foloseasca o persoana in timpul lucrului cu baza de date (numele de utilizator). Daca nu este specificat, se va folosi identificatorul.

Se recomanda ca ori de cate ori este posibil, numele de utilizator sa fie acelasi cu identificatorul, pentru ca securitatea bazelor de date sa fie mai usor de urmarit. Daca numele ramane acelasi, confuzia dispare.

Daca a fost adaugat un grup, fiecare membru al acestuia poate accesa baza de date, dar numai grupul are un nume de utilizator al bazei de date - adica nu exista cate un nume pentru fiecare utilizator al grupului, ci numai pentru grupul in sine, la fel ca la identificatori.

Pentru a ridica dreptul de acces la baza de date, se va executa procedura de sistem memorata sp_revokedbaccess:

sp_revokedbaccess [@name in db =] 'nume_din_bd'

In aceasta procedura memorata:

- nume_din_bd este numele utilizatorului bazei de date care trebuie inlaturat. Numai membrii rolurilor db_accessadmin si db_owner (sau ai rolului fix de server sysadmin) pot executa aceste proceduri memorate.

Pentru a vedea ce utilizatori acceseaza o anumita baza de date si ce identificator are fiecare dintre ei, se poate executa procedura de sistem memorata sp_helpuser:

sp_helpuser [[@name in bd =] 'nume utilizator']

Aici, nume_utilizator este optional si reprezinta fie un nume de utilizator, fie un nume de rol.

Daca nu se specifica un nume de utilizator, va fi generat un raport cu toti utilizatorii si toate rolurile. in caz contrar, se va obtine un raport pentru utilizatorul sau rolul specificat.

Cand este creata o baza de date, aceasta are deja un utilizator. Unul dintre utilizatori se numeste dbo (de la database owner - proprietarul bazei de date). Utilizatorul dbo este asociat in mod prestabilit cu identificatorul de conectare sa. Cand se instaleaza SQL Server, identificatorul sa este considerat proprietarul tuturor bazelor de date. Daca altcineva, cu un alt identificator, creeaza o baza de date, proprietarul acesteia va fi identificatorul respectiv.

In cadrul unei baze de date, proprietarul ei poate face absolut orice. Are aceeasi libertate ca membrii rolului sysadmin. Insa numai membrii rolului fix de server sysadmin au anumite privilegii la nivel de sistem.

S-a precizat anterior ca un identificator nu permite accesul la o baza de date daca nu i-a fost atribuita in mod explicit permisiunea, dar exista o exceptie - numele de utilizator guest.

Este un utilizator special, care nu respecta regulile obisnuite. Cand o baza de date contine un cont de utilizator guest, orice persoana care solicita acces la baza de date si nu are un nume de utilizator in baza de date (un cont individual sau unul creat datorita apartenentei la un grup) poate obtine accesul prin intermediul acestui cont. Prin urmare, in cursul procesului de instalare, in fiecare baza de date prestabilita de pe server se creeaza un cont guest.

Numele de utilizator guest poate fi eliminat din toate bazele de date existente cu exceptia bazei de date master

Numele de utilizator guest

Daca conexiunea se face cu un identificator pentru care nu este creat un nume de utilizator specific in baza de date si nu exista nici contul guest, se obtine un mesaj de eroare.

Mesajul de eroare precizeaza ca identificatorul nu este asociat corect cu un utilizator din baza de date si implica faptul ca nu exista contul guest. Pentru a se remedia problema, fie se adauga contul guest executand comanda: EXEC sp_grantdbaccess 'guest', fie (preferabil) se adauga in baza de date un nume de utilizator corespun­zator cu identificatorul.

Atentie daca baza de date model contine un cont guest, fiecare baza de date nou creata va avea un utilizator guest.

Pana acum s-au examinat doua modalitati de a accesa o baza de date: definind un nume de utilizator asociat cu un identificator sau prin intermediul contului guest. O alta posibilitate este prin dbo - adica numele asociat cu identificatorul celui care a creat baza de date. Sau se poate prelua numele altui utilizator, prin tehnica alias.

Adaugarea unui alias

Se poate adauga un alias al unui nume de utilizator al bazei de date folosind procedura de sistem memorata sp_addalias. Cu ajutorul acestui alias, un identificator poate inlocui un alt utilizator al bazei de date, in loc sa fie asociat cu propriul sau nume de utilizator sau de grup.

sp_addalias identificator, nume_utilizator

In sintaxa comenzii sp_addalias:

- identificator este identificatorul de conectare al utilizatorului pe care doriti sa-1 asociati.

- nume_utilizator este utilizatorul bazei de date in numele caruia se efectueaza accesul.

Tehnica alias a fost creata pentru a fluidiza procesul de securizare a bazei de date. Se poate asocia oricati identificatori cu un acelasi nume de utilizator din baza de date. Daca un identificator este deja asociat cu un nume de utilizator din baza de date, nu poate fi asociat cu un alt nume din aceeasi baza de date. Subliniem ca rolurile ofera un model de securitate mult mai corect si mai bun.

Daca se doreste renuntarea la alias, se foloseste procedura de sistem memorata sp_dropalias:

sp_dropalias identificator

In aceasta sintaxa, identificator este identificatorul utilizatorului.

Modificarea proprietarului bazei de date

Se poate modifica proprietarul unei baze de date atunci cand se doreste ca responsabilitatea pentru aceasta sa fie preluata de un anumit administrator al bazei de date.

Cand se doreste efectuarea acestei modificari, identificatorul nu trebuie sa existe in baza de date ca nume de utilizator.

Pentru a modifica proprietarul, se executa procedura de sistem memorata sp_changedbowner:

sp_changedbowner [@loginame = ]'identificator' [,[@map =] indicator_reasociere_ alias

In aceasta sintaxa:

- identificator este identificatorul SQL Server care va fi asociat.

- indicator_reasociere_alias este un parametru optional care, daca nu este specificat, determina stergerea tuturor utilizatorilor asociati cu dbo cand se modifica proprietarul bazei de date. Daca se specifica parametrul TRUE, toti utilizatorii asociati cu vechiul proprietar vor fi reasociati cu noul proprietar.

Deci ca modalitati distincte de a accesa o baza de date dupa ce se realizeaza conectarea la SQL Server avem:

- sa - administratorul (sau orice alt membru al rolului de server sysadmin) poate sa acceseze intotdeauna baza de date si sa figureze ca dbo, chiar daca proprietarul bazei de date a fost schimbat cu procedura sp_changedbowner.

- numele de utilizator al bazei de date - modul "normal' de a accesa o baza de date este de a folosi un nume de utilizator asociat cu un identificator SQL Server (un nume de utilizator sau un nume de grup Windows ). Acest lucru este valabil si in cazul grupurilor Windows carora le-a fost acordat drepturi de acces.

- alias - se poate realiza asocierea cu un utilizator valid al bazei de date in cadrul bazei de date, in acest fel se obtin permisiunile si privilegiile utilizatorului respectiv.

- guest - daca toate celelalte metode dau gres, SQL Server verifica daca in tabelul de sistem sysusers al bazei de date exista un cont guest. In caz afirmativ, se permite accesul ca guest, altfel, accesul este interzis.

Roluri

Rolurile reprezinta elementul de legatura intre toate aspectele sistemului SQL Server. Rolurile pot fi asemanate cu grupurile SQL Server.

Rolurile SQL Server permit gruparea numelor utilizatorilor bazelor de date. Nu are importanta daca numele de utilizatori reprezinta grupuri Windows, utilizatori Windows sau identificatori SQL Server. Rolurile pot chiar contine alte roluri ca membri.

Rolul public

In fiecare baza de date, exista un rol predefinit numit public. Toti utilizatorii, toate grupurile si toate rolurile sunt membri ai rolului public si nu pot fi inlaturati.

Este destul de comod sa facem referiri la toti utilizatorii, fara a-i denumi in mod explicit.

Roluri la nivel de server

Subliniem din nou ca identificatorul sa este atotputernic: poate face orice cu o instanta SQL Server. Acest lucru se datoreaza faptului ca sa este membru al unui rol la nivel de server, denumit sysadmin. SQL Server are opt roluri la nivel de server. In orice moment, un identificator poate fi facut membru al unuia sau mai multor roluri insa nu se pot inlatura sau adauga roluri la nivel de server.

Rolurile de server disponibile

Lista urmatoare descrie setul complet de roluri la nivel de server disponibile in SQL Server :

- sysadmin - membrii acestui rol pot face orice in SQL Server. Ei figureaza drept proprietari (dbo) ai bazei de date (chiar atunci cand nu sunt). In esenta, pot trece peste toate permisiunile si sistemele de securitate.

- serveradmin - membrii acestui rol pot stabili optiuni de configurare cu procedura de sistem memorata sp_configure si pot inchide serverul. De asemenea, pot configura serviciul Full-Text. Operatorii serverului sunt niste candidati foarte potriviti pentru acest rol.

- setupadmin - membrii acestui rol pot instala si configura servere legate si pot marca o procedura memorata pentru executie la momentul pornirii.

- securityadmin - membrii acestui rol pot crea si controla identificatori de conectare la server, precum si permisiuni pentru crearea bazelor de date. Pot configura atributele de securitate ale serverelor legate. De asemenea, pot redefini parolele identificatorilor SQL Server (cu exceptia membrilor rolului sysadmin). Cel mai probabil, membrii acestui rol vor fi operatorii serverului si personalul de asistenta.

- processadmin - membrii acestui rol pot controla procese executate pe serverul de baze de date. In general, aceste procese implica "anihilarea' interogarilor cu probleme - iar personalul de asistenta poate avea nevoie de acest drept.

- dbcreator - membrii acestui rol pot crea, modifica si elimina baze de date de pe server. De asemenea, pot restaura baze de date pe server. Administratorii bazelor de date pot fi membri ai acestui rol.

- bulkadmin - membrii acestui rol pot executa instructiunea BULK INSERT.

- diskadmin - membrii acestui rol pot gestiona fisiere si cresterea fisierelor de pe server. Niste candidati potriviti sunt administratorii bazelor de date.

Cum se atribuie un identificator unui rol la nivel de server

Pentru a atribui un identificator unui anumit rol la nivel de server, se foloseste SQL Server Enterprise Manager sau procedura de sistem memorata sp_addsrvrolemember:

sp_addsrvrolemember [@loginame =] 'identificator' ,[@rolename =] 'rol'

In aceasta sintaxa:

- identificator este identificatorul SQL Server care va fi adaugat la rolul respectiv.

- rol este numele rolului la nivel de server pe care o sa-l atribuim identificatorul.
Un identificator poate fi membru a zero, unu sau mai multe roluri. Insa rolul sysadmin le cuprinde pe toate celelalte - atat rolurile la nivel de server, cat si rolurile specifice bazelor de date - asa ca nu e nevoie sa se mai atribuie nici un rol daca se selecteaza sysadmin.

Pentru a elimina un identificator dintr-un rol la nivel de server, se va folosi procedura de sistem memorata sp_dropsrvrolemember:

sp_dropsrvrolemember [@loginame =] 'identificator' , [@rolename =] 'rol'

In aceasta sintaxa:

- identificator este identificatorul SQL Server care va fi inlaturat din rol.

- rol este numele rolului la nivel de server din care se doreste inlaturarea
identificatorul.

Roluri la nivelul bazelor de date

Fiecare baza de date contine roluri. Unele dintre acestea sunt fixe; de asemenea, se pot adauga propriile roluri (ceea ce nu este posibil la nivel de server). De retinut ca aceste roluri sunt specifice bazelor de date, asa ca un rol nu poate influenta mai multe baze de date simultan. Insa se pot crea aceleasi roluri in toate bazele de date.

Roluri fixe specifice bazelor de date

Fiecare baza de date contine un ansamblu de roluri predefinite, carora le poate fi atribuit un nume de utilizator. Exista noua asemenea roluri - un numar fixat pentru totdeauna (nu puteti sterge nici unul din ele). La fel ca rolurile la nivel de server, fiecare rol la nivel de baza de date acorda utilizatorilor anumite permisiuni si capabilitati, cum ar fi:

- db_owner - membrii acestui rol pot face orice doresc - dar numai in cadrul propriei baze de date. Un utilizator membru al rolului db_owner primeste aproape toate drepturile si permisiunile utilizatorului dbo (proprietarul bazei de date). Exceptia se refera la comanda restore.

- db_accessadmin - membrii acestui rol pot acorda sau anula dreptul de acces al utilizatorilor la baza de date (de exemplu, prin executarea procedurii de sistem memorate sp_grantdbaccess).

- db_securityadmin - membrii acestui rol pot controla toate permisiunile, rolurile, apartenenta la roluri si proprietarii obiectelor din baza de date.

- db_ddladmin - membrii acestui rol pot crea, modifica si inlatura toate obiectele din baza de date, dar nu pot executa comenzi legate de securitate (grant, revoke, deny).

- db_backupoperator - membrii acestui rol pot executa comenzile checkpoint si backup.

- db_datareader - membrii acestui rol au permisiunea de a selecta date din orice tabel, vedere sau functie din baza de date.

- db_datawriter - membrii acestui rol au drepturi de introducere, actualizare sau stergere pentru orice tabel sau vedere din baza de date.

- db_denydatareader - membrii acestui rol nu pot selecta date din nici un tabel, vedere sau functie din baza de date.

- db_denydatawriter - membrii acestui rol nu pot modifica nici un fel de informatii cu comenzile insert, update sau delete, din nici un tabel sau vedere a bazei de date.

Roluri definite de utilizator la nivelul bazelor de date

Pe langa rolurile predefinite, se pot crea roluri, iar aceste roluri nou create pot fi atribuite utilizatorilor sau altor roluri. Motivul pentru care se creaza roluri la nivelul bazelor de date in SQL Server este acelasi cu motivul pentru care se creaza grupuri la nivelul sistemului de operare - pentru a-i grupa in mod convenabil pe utilizatorii care efectueaza functii similare. Numarul rolurilor create trebuie sa corespunda necesitatilor. Nu exista nici un fel de restrictii privind numarul de roluri carora le poate apartine un utilizator sau un alt rol.

Pentru a crea un rol, se foloseste procedura de sistem memorata sp_addrole:

sp_addrole [@rolename =] 'rol' [@ownername = ] 'proprietar'

In aceasta sintaxa:

rol este numele care va fi atribuit noului rol.

- proprietar este numele utilizatorului SQL Server care va fi proprietarul rolului (fiecare utilizator poate avea propriile sale roluri).

Numai membrii rolului sysadmin la nivel de server sau ai rolurilor db_owner si db_securiry la nivel de baze de date pot adauga un rol nou la o baza de date. Acelasi lucru este valabil si cu privire la inlaturarea rolurilor. Numele rolului trebuie sa fie unic in baza de date respectiva.

Pentru a inlatura un rol dintr-o baza de date, se executa procedura de sistem memorata sp_droprole:

sp_droprole [@rolename =] 'rol'

In aceasta sintaxa, rol este numele rolului creat de utilizator care se doreste a fi inlaturat.

Nu se poate sterge un rol daca acesta are drept membri utilizatori sau alte roluri. De asemenea, nu se pot sterge roluri daca acestea detin obiecte.

Pentru a adauga utilizatori la rolurile fixe sau definite de utilizator, se foloseste procedura de sistem memorata sp_addrolemember:

sp_addrolemember [@rolename] 'rol' , [@membername =] 'cont_baza_date'

In aceasta sintaxa:

- rol este numele rolului la care se doreste sa se adauge un utilizator.

- cont_baza_date este numele utilizatorului, grupului sau rolului care se doreste sa fie adaugat la rolul respectiv.

Pentru a elimina un membru al unui rol, se executa procedura de sistem memorata sp_droprolemember:

sp_droprolemember [@rolename =] 'rol',[@membername=] 'cont_baza_date'

In aceasta sintaxa:

- rol este numele rolului din care se doreste eliminarea unui utilizator.

- cont_baza_date este numele utilizatorului, grupului sau rolului care se doreste sa fie eliminat din rolul respectiv.

Membrii fiecarui rol sunt memorati intr-o combinatie a tabelelor de sistem sysusers si sysmembers. Se pot examina rolurile existente cu ajutorul procedurilor de sistem memorate sp_helprole sau sp_helprolemember. Ambele proceduri primesc un singur parametru: numele rolului intre apostrofuri.

Roluri la nivel de aplicatie

Rolurile la nivel de aplicatie sunt o caracteristica extrem de utila a sistemului SQL Server. Desi la prima vedere par similare cu celelalte roluri prezentate, ele indeplinesc o alta functie.

Unele din destinatiile lor sunt identice cu ale celorlalte roluri; rolurile la nivel de aplicatie reprezinta un mod extraordinar de a grupa utilizatorii astfel incat permisiunile sa poata fi aplicate la un nivel mai inalt decat pentru fiecare utilizator in parte. Insa ele difera prin faptul ca pot fi "activate' de o aplicatie. Dupa ce o aplicatie a activat un rol la nivel de aplicatie, toate permisiunile utilizatorului sunt suspendate, fiind impuse numai permisiunile rolului respectiv. Desigur, pentru activarea rolului este nevoie de o parola.

Un exemplu foarte bun privind utilizarea acestor roluri este o aplicatie pentru salarii. Cu toate ca toti administratorii din departamentul de plati trebuie sa actualizeze periodic salariile angajatilor si informatiile referitoare la prime, este preferabil ca ei sa foloseasca o anumita aplicatie in loc sa interogheze direct baza de date SQL Server (ceea ce poate avea consecinte dezastruoase). La pornirea aplicatiei, utilizatorii se pot conecta la SQL Server in nume propriu (cu identificatorii SQL Server sau cu atributele Windows).    

Apoi se executa codul corespunzator (procedura de sistem memorata sp_setapprole) pentru a activa rolul aplicatiei de salarii. Incepand din acel moment si pana la incheierea conexiunii dintre aplicatie si baza de date, se aplica permisiunile rolului, iar cele ale utilizatorului sunt dezactivate. Prin urmare, daca rolul are permisiunea de a modifica tabelele de plati, dar administratorii respectivi nu au acest drept, ei pot folosi in continuare aplicatia.

Rolurile la nivel de aplicatie sunt extrem de interesante. Pentru a crea un rol la nivel de aplicatie se folosesteprocedura de sistem memorata sp_addapprole:

sp_addapprole [@rolename =] 'rol', [@password =] 'parola'

In aceasta sintaxa:

- rol este numele rolului care se doreste a fi creat.

- parola este parola pe care trebuie sa o prezinte aplicatia pentru a activa rolul.

Pentru a elimina un rol la nivel de aplicatie, se executa procedura de sistem memorata sp_dropapprole

sp_dropapprole [@rolename =] 'rol'

In aceasta sintaxa, rol este numele rolului care se doreste a fi indepartat.

Pentru a utiliza rolul la nivel de aplicatie, se va executa procedura de sistem memorata sp_setapprole:

sp_setapprole | 'parola'

[,[@encrypt =] 'stil_criptare'

In aceasta sintaxa:

- rol este numele rolului care va fi activat.

- parola este parola specificata in executia procedurii sp_addapprole.

- Encrypt N 'parola' cere ca parola sa fie criptata cand este transmisa prin retea (daca specificati numai parola, aceasta este transmisa prin retea necriptata).

- stil_criptare specifica stilul de criptare utilizat. In prezent exista doua valori: none (nici unul) si odbc. Se specifica odbc (Open Database Connectivity) cand se foloseste un client bazat pe ODBC; pentru criptarea parolei se va folosi functia de criptare standard ODBC.

Pentru a crea si a utiliza un rol la nivel de aplicatie, se poate executa urmatorul script :

Intrare

USE nume_bd

EXEC sp_addapprole 'Salarii', 'parola'

GO

EXEC sp_setrapprole 'Salarii', , 'odbc'

Va fi primit urmatorul mesaj, care indica incheierea cu succes a operatiei:

New application role added.

The application role 'Salarii' is now active.