Katholieke Hogeschool Kempen Campus Geel Departement Handelswetenschappen en Bedrijfskunde 3de jaar Toegepaste Informatica
Beheer van databanken
Labo 2 Hoofdstuk 3 Beheer van database files Hoofdstuk 4 Beheer security
Svenia CLAES – secretaris Britt Jacobs Lies Van der Goten Wesley VanVlerken
Academiejaar 2010 -2011
Inhoudsopgave 1
Creëren/wijzigen van een database in de grafische omgeving ....................................................... 4
2
Creëren van een database in T-SQL ................................................................................................ 7
3
Informatie uit views......................................................................................................................... 9
4
Informatie m.b.v stored procedures ............................................................................................. 11
5
Alter Database ............................................................................................................................... 13
6
Koppelen en ontkoppelen (zelfde instantie) ................................................................................. 14
7
Koppelen en ontkoppelen (andere instantie) ............................................................................... 15
8
Creëren van een database met meerdere bestanden en filegroups ............................................ 15
9
Een database van naam wijzigen .................................................................................................. 16
10
Verwijderen van een database.................................................................................................. 16
11
Creëren van een SQL-script, gebruik van templates ................................................................. 16
12
Aanmaken gebruikers en groep in Windows 2008 ................................................................... 17
13
Logins van Windows toevoegen/testen .................................................................................... 17
14
Logins van SQL Server................................................................................................................ 19
15
Verweesde gebruikers ............................................................................................................... 20
16
Vragen ....................................................................................................................................... 20
17
Gebruikers toevoegen aan een database.................................................................................. 21
18
Tonen van gebruikers en database roles .................................................................................. 21
19
Database role ............................................................................................................................ 21
20
Systeem views ........................................................................................................................... 22
21
Functies ..................................................................................................................................... 22
22
Permissions – Database Scope .................................................................................................. 22
23
Permissions – Schema Scope..................................................................................................... 22
24
Application role ......................................................................................................................... 22
25
Toegangsrechten op een view en stored procedure ................................................................ 22
26
De owner van een object .......................................................................................................... 23
27
Stored procedures ..................................................................................................................... 23
28
Ontkoppelen .............................................................................................................................. 23
Labo 2 Hoofdstuk 3 Beheer van database files Hoofdstuk 4 Beheer security 1 Creëren/wijzigen van een database in de grafische omgeving Rechtsklikken op “Database” en “New Database” kiezen. Bij “Database name” vul je “Lokalenverhuur” in. De Database File Lokalenverhuur wordt een Initial Size van 4 MB gegeven en geen autogrowth. Om de autogrowth aan te passen klik je op de knop met de drie puntjes. Wij doen het vinkje bij “Enable Autogrowth” weg. Het Log bestand geven we een Initial Size van 2 MB en we zetten de autogrowth uit.
Om de eigenschappen van een database te bekijken, klik je rechts op de database en kies je “Properties”. In het “Properties” venster kan je in de page “General” zien hoeveel beschikbare ruimte (Space Available) er is in de database.
De benamingen van de database files vind je terug in de page “Files”.
Als je met de scrollbark verder naar rechts gaat, vind je ook het pad op, waar deze bestanden zijn opgeslagen.
De eigenschappen kunnen aangepast worden in het properties venster. Om Autogrowth 4 MB en maximum grootte 52 MB in te stellen voor het gegevensbestand:
Om Autogrowth 2 MB en maximum grootte 24 MB in te stellen voor het logbestand:
2 Creëren van een database in T-SQL Eigenschappen van de nieuwe database: •
Naam: Artikels
•
Gegevensbestand: o Logische naam: Artikels_Data o Fysische naam: Artikels_Data.mdf o Pad: D:\Data (Map aanmaken als die nog niet bestaat) o Initiele groote: 7 MB o Autogrowth: 3 MB
•
Logbestand: o Logische naam: Artikels_Log o Fysische naam: Artikels_Log.ldf o Pad: D:\Data (Map aanmaken als die nog niet bestaat) o Initiele grootte: 3 MB o Autogrowth: 1 MB o Maximale groote : 10 MB
Eigenschappen van de database Artikels door rechts te klikken op “Artikels” en properties te kiezen.
Aanpassen van de maximale grootte van Artikels_Data naar 20 MB.
De optie Auto Close kan je inschakelen door in de page “Options” de optie “Auto Close” op True te zetten.
3 Informatie uit views Informatie uit views opvragen met SQL-statements: a) Informatie over de database in de instantie van SQL Server
b) De mogelijke opties die ingesteld kunnen worden
c) Informatie van de database Lokalenverhuur •
Grootte van de database
•
Eigenaar van de database
•
Wanneer de database aangemaakt is
•
Het compability level
•
Ingestelde collation name
•
Read Only eigenschap
•
Auto Close eigenschap
4 Informatie m.b.v stored procedures a) USE AdventureWorks2008R2 EXEC sp_spaceused De grootte van de database en de vrije ruimte worden getoond samen met hoe de database grootte is opgedeeld. Voor de grootte van de database kan er EXEC sp_helpdb AdventureWorksLT2008R2 gebruikt worden. b) USE AdventureWorks2008R2 EXEC sp_spaceused ‘Production.Product’ Geeft weer hoe het gebruikte geheugen verdeeld is van de Tabel Product uit het schema Production c) Stored procedure sp_dboption •
Mogelijke opties
•
Ingestelde opties van Lokalenverhuur
•
Optie Read Only inschakelen EXEC sp_dboption 'Lokalenverhuur', 'read only', 'true'
Read only uitschakelen
Je kan ook ALTER DATABASE gebruiken in plaats van sp_dboption.
5 Alter Database Wijzigingen aanbrengen in de database Lokalenverhuur met behulp van ALTER DATABASE. a) Logische naam van het gegevensbestand aanpassen
b) Logische naam van het logbestand aanpassen
c) Optie ANSI NULL als default ingeschakeld instellen
d) Optie Auto Shrink niet geselecteerd instellen
6 Koppelen en ontkoppelen (zelfde instantie) Om de database Artikelen te ontkoppelen: •
Rechtsklikken op Artikels – Tasks – Detach
•
Vervolgens in het venster dat verschijnt op OK klikken.
De database files verplaatsen naar D:\Verplaatst. Om de database opnieuw koppelen: •
Rechtsklikken om Databases – Attach
•
Op Add klikken
•
Op OK klikken
•
Op OK klikken
Pad van de bestanden is aangepast.
7 Koppelen en ontkoppelen (andere instantie) Kunnen we niet doen, omdat we niet op de andere instantie kunnen werken.
8 Creëren van een database met meerdere bestanden en filegroups Database Administratie met volgende bestanden: •
AdminData01, AdminData02 ,AdminData03 o Initiële grootte van 5 MB o Auto growth van 5 MB o Maximale grootte van 20 MB
•
AdminLog o Initiële grootte van 3 MB o Auto growth van 2 MB o Maximale grootte van 10 MB
Filegroup FG01 aanmaken via de page Filegroup en dan op Add klikken. AdminData02 en AdminData03 in de Filegroup FG01 zetten
Schema “Medewerker” aanmaken met T-SQL:
Tabel Medewerker in het schema Medewerker aanmaken met T-SQL en in de filegroup FG01 zetten
Filegroups tonen en het aantal bestanden (filecount) dat erin zitten.
Meer informative van de filegroup FG01 opvragen.
9 Een database van naam wijzigen Naam van de database Administratie wijzigen naar AdministratieOud door rechtsklikken en rename te kiezen. Bij het wijzigen van de database naam worden er geen wijzigingen doorgevoerd bij de logische namen van de bestanden en ook niet bij hun fysische namen. De database terug Administratie noemen met T-SQL:
10 Verwijderen van een database Verwijderen van de database Artikels door rechtsklikken en delete te kiezen. De bestanden werden ook van de harde schijf gewist.
11 Creëren van een SQL-script, gebruik van templates SQL-code voor het creëren van een database genereren door rechtsklikken op de database, Script Database As, Create to en dan file te kiezen.
12 Aanmaken gebruikers en groep in Windows 2008 We maken volgende groepen en gebruikers lokaal aan op onze computer: •
•
Groep CustomerMgmt o Carl o David o Kathy o Lisa Group Users o Paul o Max
13 Logins van Windows toevoegen/testen Om in SQL Server de logins toe te voegen open je de Security node, rechtsklikken op Logins en kies New Login. We voegen volgende logins toe: Windows – gebruiker of – groep Carl Paul Kathy CustomerMgmt
Default database
Language
Master Master Master Master
Default Default Dutch Default
1. Aanmelden als David a. Krijgt toegang omdat hij in de CustomerMgmt group zit. 2. Aanmelden als Max a. Krijgt geen toegang omdat hij noch de groep toegevoegd werden als SQL Server logins. 3. Verwijderen van de group CustomerMgmt in SQL Server a. Carl en Kathy kunnen nog wel inloggen omdat die users individueel werden toegevoegd in SQL Server. b. David en Lisa kunnen niet meer inloggen omdat ze niet individueel werden toegevoegd. 4. Groep CustomerMgmt terug toevoegen, Carl Deny Access geven. a. Carl heeft geen toegang meer, omdat de individuele instellingen de groepsinstellingen overschrijven. 5. Carl terug toegang geven door een T-SQL statement te gebruiken a. ALTER LOGIN [CustomerMgmt\Carl] enable 6. Paul toegang weigeren door een T-SQL statement te gebruiken a. DENY CONNECT SQL TO [Users\Paul] 7. Login van Kathy deactiveren door een T-SQL statement te gebruiken a. ALTER LOGIN [CustomerMgmt\Kathy] DISABLE 8. Login van Kathy terug activeren door een T-SQL statement te gebruiken a. ALTER LOGIN [CustomerMgmt\Kathy] ENABLE 9. Carl toevoegen tot de serverrol Database creators door een T-SQL statement te gebruiken a. sp_addsrvrolemember ‘Carl’, ‘dbcreator’ 10. Carl de serverrol Database creators ontnemen a. sp_dropsrvrolemember ‘Carl’, ‘dbcreator’ 11. select * from sys.server_principles
14 Logins van SQL Server 1. Mixed authentication mode inschakelen: a. Rechtklikken om de servernaam, properties kiezen b. Naar de page security gaan en het keuzerondje bij “SQL Server and Windows Authentication mode” aanduiden 2. Grafische volgende SQL Server-logins aanmaken: Naam Wachtwoord Default database Language Piet piet Master Default Annabelle anne Master Default In de security node rechtsklikken op Logins en dan kiezen voor New Login. 3. Met T-SQL statements volgende SQL-Server –logins aanmaken Naam Wachtwoord Werner werner Ria ria CREATE LOGIN Werner with PASSWORD = 'werner', default_database = master, default_language = Default, check_policy = off CREATE LOGIN Ria with PASSWORD = 'ria', default_database = master, default_language = Dutch, check_policy = off
Default database Master Master
Language Default Dutch
4. Met T-SQL het wachtwoord van Annebelle wijzigen alter login Annebelle with password = 'annebelle' 5. Met T-SQL de default database van Werner wijzigen alter login Werner with default_database = AdventureWorks2008R2 6. Met T-SQL de standaardtaal van Ria wijzigen alter login Ria with default_language = English 7. Met T-SQL de login account van Ria verwijderen drop login Ria Ria kan zich niet meer aanmelden. Andere login accounts zoals die van Werner kunnen zich wel nog aanmelden. 8. Select * from sys.server_principals
15 Verweesde gebruikers Nieuwe gebruiker Willy toevoegen in Windows en vervolgens toegang geven tot SQL Server. Willy verwijderen als gebruiker in Windows. Met volgends SQL statement kan je de gebruikers weergeven die niet meer bestaan in Windows: •
exec sp_validatelogins
Willy verwijderen als login in SQL Server: •
DROP user Willy
16 Vragen Wachtwoorden van Windows - gebruikers kunnen niet gewijzigd worden in SQL Server. De wachtwoorden van SQL Server – gebruikers kunnen wel gewijzigd worden SQL Server.: alter login Annebelle with password = 'annebelle'
17 Gebruikers toevoegen aan een database Database Planten in de map D:\Data zetten en koppelen in SQL Server door rechtsklikken en attach te kiezen. De gebruiker Annabelle toegang geven tot de database Planten, als default schema dbo instellen en aan de database role db_owner toevoegen. alter login Annebelle with default_database = Planten, default_schema = dbo, role db_owner De Group CustomerMgmt toegang geven tot de database Planten alter login CustomerMgmt with default_database = Planten Kathy, Carl, Piet en Werner gebruikers maken van de database AdventureWorksR2, en als default schema HumanResources. alter login Kathy, Carl, Piet, Werner with default_database = AdventureWorksR2, default_schema = HumanResources,
18 Tonen van gebruikers en database roles Met de view sys.databases_principles kan je onder andere de gebruikers, databaserollen, gegevens van gebruikers en ingebouwde database roles zien. Select * from sys.databases_principles
19 Database role Je kan een nieuwe database role toevoegen door: •
De node Roles in Security te openen, rechtsklikken op Database Roles en New Database Role te kiezen.
•
De nieuwe database role noemen we CustomerMgmt en is van toepassing op de database AdventureWorks2008R2.
•
We voegen Carl en Kathy toe bij de Role Members.
Via T- SQL-statement of stored procedures • • • • •
Databaserol Marketing aanmaken create role Marketing Gebruikers Piet en Werner toevoegen aan de databaserol Marketing exec sp_addrolemember Marketing, Piet exec sp_addrolemember Marketing, Werner Piet verwijderen uit de databaserol Marketing exec sp_droprolemember Marketing, Piet Gebruikers van de datarol Marketing tonen exec sp_helprolemember Marketing Databaserol Marketing verwijderen exec sp_droprole Marketing
20 Systeem views De view sys.all_views toont alle door gebruikers gemaakte en systeem views. De view sys.dm_os_loaded_modules toont een rij voor elke module op de server. De view sys.configurations toont een rij per server-configuration optie in het systeem.
21 Functies De functie SUSER_SNQME() geeft de login naam terug van een gebruiker met een bepaalde SID. De functie USER_NAME() geeft de database user name terug van een bepaalde SID.
22 Permissions – Database Scope Kunnen we niet doen omdat we de database AdventureWorks2008R2 niet kunnen installeren.
23 Permissions – Schema Scope Kunnen we niet doen omdat we de database AdventureWorks2008R2 niet kunnen installeren.
24 Application role Kunnen we niet doen omdat we de database AdventureWorks2008R2 niet kunnen installeren.
25 Toegangsrechten op een view en stored procedure Kunnen we niet doen omdat we de database AdventureWorks2008R2 niet kunnen installeren.
26 De owner van een object Kunnen we niet doen omdat we de database AdventureWorks2008R2 niet kunnen installeren.
27 Stored procedures Met sys.fn_builtin_permissions kan je de toegangsrechten zien die op de database, schema en gebruiker gekoppeld zijn. SELECT * FROM sys.fn_builtin_permissions (DEFAULT)
28 Ontkoppelen Om de database Planten te ontkoppelen, klik je rechts en kies je detach.
