Informatica de gestiune -- 2008-2009 -- info.uvt.ro/Tutoriale/MSSQL

Cerinţe de sistem pentru SQL Server 2000[edit]

Pentru a putea lucra cu SQL Server intr-un mod cât mai bun şi cât mai rapid, vă recomandăm să instalaţi versiunile din 2000 care sunt mult mai simplificate şi care uşurează munca pentru realizarea unui proiect de gestiune.

Microsoft SQL Server 2000 necesită următoarea configuraţie minimă de sistem.

Procesor: Intel Pentium sau procesor de 166 MHz sau mai mare
Sistem de operare:
- Windows Server 2003, Standard Edition
- Windows Server 2003, Enterprise Edition
- Windows Server 2003, Datacenter Edition
- Windows 2000 Server
- Windows 2000 Advanced Server
- Windows 2000 Datacenter Server
- Windows NT Server 4.0 cu SP5 sau mai recent
- Windows NT Server 4.0, Enterprise Edition, cu SP5 sau mai recent
Memorie: Enterprise şi Standard Edition: 64 MB ; se recomandă 128 MB
Hard disk: 95–270 MB spaţiu pe disc pentru server; 250MB pentru o instalare tipică.

Câteva informaţii generale despre Windows SQL Server

Recomandări[edit]

Întrucât probabil aveti deja al sistem de operare instalat, vă sugerăm să vă instalaţi o maşină virtuală. Pentru a downloada maşina virtuală este necesar să vă înscrieţi şi să obţineţi un cod pentru versiunea de evaluare. Se poate instala si o maşină virtuală de tip Server VMware care este gratis, dar pentru a facilita transferul de fişiere dintre cele doua Sisteme de operare, ar fi ideal sa optaţi pentru VMware Workstation. Puteti găsi pe dms kitul pentru Microsoft SQL Server Enterprise Edition şi Service Pack 3 aferent acestuia, cât şi o versiune pentru Windows 2003 cu licenţă datorită programul Microsoft Partners. Windows Server 2003 necesită SQL Server 2000 Service Pack 3 sau o versiune mai recentă (Service Pack 4 disponibil online).

Securitatea SQL Server[edit]

Sistemul de securitate din SQL Server poate fi implementat în două feluri: SQL Server şi Windows (Standard) şi Windows Only (integrat). Aceste tipuri de securitate determină modul în care SQL Server gestionează conturile utilizatorilor de pe server şi modul în care interacţionează cu sistemul de securitate propriu al Windows-ului. Pentru a putea alege sistemul de securitate standard sau integrat pentru un server de baze de date, trebuie să urmaţi paşii:

Lansaţi administratorul de sistem SQL (SQL Server Enterprise Manager) din grupul de programe din bara de Start
Alegeţi serverul de a cărui administrare urmează să vă ocupaţi şi selectaţi Actions, Properties din cadrul meniului Server, sau direct click dreapta pe server şi opţiunea Properties.

File:Informatica de gestiune -- 2008-2009 -- info.uvt.ro -- Tutoriale -- MSSQL -- Security -- SQLSecurity.png

Asigurarea securităţii este o parte din treaba fiecărui DBA. Cu SQL Server aveţi posibilitata să utilizaţi atât autentificare SQL Server, Windows cât şi mixtă.

Avantajele utilizării autentificării Windows pentru SQL Server

se poate beneficia de avantajele modelului de securitate Windows şi a politicilor de securitate configurate pe domeniu
se poate confugura securitatea la nivel de domeniu in loc de nivelul de instanta al SQL Server.
bazându-ne pe grupurile de utilizatori care sunt configurate în Windows, se pot acorda acces grupurilor în SQL Server fără a crea login pentru fiecare utilizator.

Dezavantajul mare

este ca utilizatorii care aparţin grupurilor de utilizatori din Windows sunt “mascati” la nivelul SQL server-ului. In logins se pot vedea grupurile care au acces dar nu se poate vedea care utilizatori fac parte din respectivele grupuri.

Cum se poate obtine aceasta informatie in SQL Server?

SQL Server oferă o procedură stocată extinsă (xp_logininfo) care permite o “privire” în aceasta problema. Această procedură se regăseşte atât în SQL Server 2000 cât şi în SQL Server 2005. Procedura are următorii parametrii: @acctname - numele utilizatorului windows sau a grupului @option - informatia de afisat ‘all’ - afiseaza informatii pentru permisiuni ‘members’ - afiseaza lista de membrii a grupului @privelege - variabila de iesire si întoarce ‘admin’,'user’ sau null

Exemple:

EXEC xp_logininfo ‘BUILTIN\Administrators’  - Afiseaza informatii despre grup (permission path)
EXEC xp_logininfo ‘BUILTIN\Administrators’, ‘members’  - Afiseaza lista de membrii din grupul Windows

SQL Server foloseşte aşadar două niveluri de acces pe care este important să le înţelegem.

conectarea
- O conectare reprezintă capacitatea de accesare a serverului SQL Server propriu-zis. SQL Server administrează conectările la nivelul întregului server. Toate conectările sunt stocate în tabela SYSLOGINS din cadrul bazei de date master.
utilizatorul
- Utilizatorii reprezintă modalitatea prin care SQL Server controlează cine are drepturi de accesare a resurselor, aşa cum sunt tabelele sau procedurile stocate, din cadrul unei anumite baze de date. Un utilizator poate să facă parte din una sau mai multe baze de date. Toţi utilizatorii sunt înscrişi în tabelele SYSUSERS ale fiecărei baze de date pe care au dreptul să o acceseze.

SQL Server face această distincţie pentru a putea acorda unui utilizator niveluri de acces diferite în funcţie de baza de date, dar valindând totul cu o singură parolă. Dreptul de conectare este cel la care SQL Server asociază parola. În lipsa unei conectări la server, un utilizator nu are acces la niciuna din bazele de date ale serverului, singura excepţie posibilă fiind cazul sistemelor la distanţă care utilizează proceduri stocate la distanţă. OBSERVAŢIE: La instalarea SQL Server-ului sunt creaţi automat doi utilizatori.

Contul de administrator de sistem revine utilizatorului SA, iar utilizatorul Guest este configurat pentru accesul implicit în sistem.
Contul SA fiind un cont special, care vă permite să controlaţi pe deplin diferitele aspecte ale sistemului. Din această cauză este extrem de important să schimbaţi parola vidă implicită cu o parolă mai sigură.

!!!Nu trebuie să lăsaţi niciodată o parolă vidă într-un sistem real!!!

Conexiune[edit]

Definirea unei conectări presupune crearea pe server a unui utilizator de baze de date. Prin stabilirea unei conexiuni sigure cu SQL Server, utilizatorul este:

Mapat pentru o conectare SQL Server existentă în cazul în care numele este identificat
Conectat prin conectarea implicită (de regulă, Guest)
Conectat ca SA dacă utilizatorul este administratorul sistemului

SQL Server gestionează toate celelate drepturi privind baza de date, printre care drepturile asupra tabelelor, vederilor şi altor obiecte, la fel ca un server care rulează în modul de securitate standard.

Utilizarea drepturilor în SQL Server[edit]

Un drept reprezintă permisiunea de accesare a unui obiect, aşa cum este o tabelă, din cadrul unei baze de date. Drepturile se acordă unui utilizator sau unui rol pentru a permite utilizatorului sau membrilor rolului să efectueze operaţii precum extragerea datelor, inserarea de noi linii sau modificarea datelor. Asupra obiectelor dintr-o bază de date există mai multe drepturi. Drepturile se acordă implicit posesorului sau creatorului unui obiect. Acesta poate să acorde drepturi altor utilizatori sau grupuri.

Posesorul unei baze de date (DBO) are drepturi depline asupra tuturor obiectelor din baza de date pe care o posedă
Administratorul de sistem (SA) are drepturi depline asupra obiectelor din toate bazele de date aflate pe server

SQL Server pune la dispoziţie comenzile GRANT şi REVOKE, care permit acordarea, respectiv retragerea de drepturi pentru un anumit utilizator.

SQL Server Enterprise Manager oferă o modalitate simplă de creare a unei conectări la o bază de date, în acest sens fiind necesar să fie efectuaţi următorii paşi:

Lansaţi Enterprise Manager-ul din grupul de programe SQL Server localizat la Start-All Programs
Selectaţi serverul pe care doriţi să creaţi noua conectare şi selectaţi obiectul Logins din catalogul Security, apoi alegeţi Action şi New Login. Fereastra care va va apare trebuie să arate astfel:

File:Informatica de gestiune -- 2008-2009 -- info.uvt.ro -- Tutoriale -- MSSQL -- Security -- login1.png

Introduceţi informaţiile pentru noua conectare şi opţional, indicaţi în pagina Database Acces, bazele de date care vor putea fi accesate prin această conectare

File:Informatica de gestiune -- 2008-2009 -- info.uvt.ro -- Tutoriale -- MSSQL -- Security -- login2.png

Introduceti parola şi efectuaţi un click pe OK pentru a arăta că informaţiile sunt corecte şi pentru a crea conectarea. Confirmaţi parola atribuită utilizatorului pentru a vă asigura că aţi introdus-o corect.

După ce conexiunea este realizată şi deja putem lucra cu SQL Server putem folosi un mic exemplu pentru a evidenţia modul în care se lucrează cu tabelele unei baze de date:

-- Prepare tablers and data
------------------------------------------------------------------
CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
GO
CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
GO

INSERT INTO t1
SELECT 1, 'title 1', 5 UNION ALL
SELECT 2, 'title 2', 5 UNION ALL
SELECT 3, 'title 3', 5 UNION ALL
SELECT 4, 'title 4', 5 UNION ALL
SELECT null, 'title 5', 5 UNION ALL
SELECT null, 'title 6', 5

INSERT INTO t2
SELECT 1, 1, 'data 1' UNION ALL
SELECT 2, 1, 'data 2' UNION ALL
SELECT 3, 2, 'data 3' UNION ALL
SELECT 4, 3, 'data 4' UNION ALL
SELECT 5, 3, 'data 5' UNION ALL
SELECT 6, 3, 'data 6' UNION ALL
SELECT 7, 4, 'data 7' UNION ALL
SELECT 8, null, 'data 8' UNION ALL
SELECT 9, 6, 'data 9' UNION ALL
SELECT 10, 6, 'data 10' UNION ALL
SELECT 11, 8, 'data 11'

------------------------------------------------------------------
-- we want to get all data in t1 that has a child row in t2
------------------------------------------------------------------

-- join gives us more rows than we need, because it joins to every child row
SELECT    t1.* 
FROM    t1 
        JOIN t2 ON t1.id = t2.t1Id
-- distinct would solve that but it's not pretty nor efficient
SELECT    DISTINCT t1.* 
FROM    t1 
        JOIN t2 ON t1.id = t2.t1Id

-- now this is a weird part where someIntCol is a column in t1 
-- but the parser doesn't seem to mind that
SELECT    t1.* 
FROM    t1 
WHERE    t1.id IN (SELECT someIntCol FROM t2)

-- here in and exists both get correct results
SELECT    t1.* 
FROM    t1 
WHERE    t1.id IN (SELECT t1id FROM t2)

SELECT    t1.* 
FROM    t1 
WHERE    exists (SELECT * FROM t2 WHERE t1.id = t2.t1id)

------------------------------------------------------------------
-- we want to get all data in t1 that doesn't have a child row in t2
------------------------------------------------------------------

-- join gives us the correct result
SELECT    t1.* 
FROM    t1 
        LEFT JOIN t2 ON t1.id = t2.t1Id
WHERE    t2.id IS NULL

-- IN doesn't get correct results.
-- That's because of how IN treats NULLs and the Three-valued logic
-- NULL is treated as an unknown, so if there's a null in the t2.t1id 
-- NOT IN will return either NOT TRUE or NOT UNKNOWN. And neither can be TRUE.
-- when there's a NULL in the t1id column of the t2 table the NOT IN query will always return an empty set. 
SELECT    t1.* 
FROM    t1 
WHERE    t1.id NOT IN (SELECT t1id FROM t2)

-- NOT EXISTS gets correct results
SELECT    t1.* 
FROM    t1 
WHERE    NOT EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)
GO

DROP TABLE t2
DROP TABLE t1

O bază de date SQL Server poate fi mutată într-o locaţie nouă cu ajutorul funcţiilor Detach şi Attach.

Conexiunea între programul pe care îl veţi realiza şi SQL Server se va realiza prin intermediul driverului JDBC

Implementare[edit]

Java code

 class SQLConnection{
   Connection connection = null;
   try {
         String driverName = "com.jnetdirect.jsql.JSQLDriver"; // NetDirect JDBC driver
         String serverName = "127.0.0.1";
         String portNumber = "1433";
         String mydatabase = serverName + ":" + portNumber;
         String url = "jdbc:JSQLConnect://" + mydatabase; // a JDBC url
         String username = "username";
         String password = "password";
         // Load the JDBC driver
         Class.forName(driverName);
         // Create a connection to the database
         connection = DriverManager.getConnection(url, username, password);
       } 
         catch (ClassNotFoundException e) {
         // Could not find the database driver
         } 
         catch (SQLException e) {
          // Could not connect to the database 
         }
    }

C# code

 // This example needs the 
 // System.Data.SqlClient library
  #region Building the connection string
    string Server = "localhost";
    string Username = "my_username";
    string Password = "my_password";
    string Database = "my_database";
    string ConnectionString = "Data Source=" + Server + ";";
    ConnectionString += "User ID=" + Username + ";";
    ConnectionString += "Password=" + Password + ";";
    ConnectionString += "Initial Catalog=" + Database;
  #endregion
 #region Try to establish a connection to the database
   SqlConnection SQLConnection = new SqlConnection();
   try{
        SQLConnection.ConnectionString = ConnectionString;
        SQLConnection.Open();
        // You can get the server version 
        // SQLConnection.ServerVersion
     }
      catch (Exception Ex)
       {
         // Try to close the connection
           if (SQLConnection != null)
                   SQLConnection.Dispose();
        // Create a (useful) error message
           string ErrorMessage = "A error occurred while trying to connect to the server.";
           ErrorMessage += Environment.NewLine;
           ErrorMessage += Environment.NewLine;
           ErrorMessage += Ex.Message;
        // Show error message (this = the parent Form object)
           MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK,  
                            MessageBoxIcon.Error);
       // Stop here
       return;
       }
    #endregion
    #region Execute a SQL query
    string SQLStatement = "SELECT * FROM ExampleTable";
    // Create a SqlDataAdapter to get the results as DataTable
    SqlDataAdapter SQLDataAdapter = new SqlDataAdapter(SQLStatement, SQLConnection);
    // Create a new DataTable
    DataTable dtResult = new DataTable();
    // Fill the DataTable with the result of the SQL statement
    SQLDataAdapter.Fill(dtResult);
    // Loop through all entries
    foreach (DataRow drRow in dtResult.Rows)
      {
       // Show a message box with the content of 
       // the "Name" column
       MessageBox.Show(drRow["Name"].ToString());
      }
    // We don't need the data adapter any more
    SQLDataAdapter.Dispose();
    #endregion
    #region Close the database link
      SQLConnection.Close();
      SQLConnection.Dispose();
    #endregion