Katholieke Hogeschool Kempen

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.