Användare, roller och rättigheter i SQL Server - del 2
Förord
Att hantera rättigheter i SQL Server är ett ganska stort område, med många möjligheter. Många väljer dock de enkla och säkerhetsmässigt sämre vägarna eftersom de inte kräver så mycket administration. I dessa artiklar ska jag visa vilka sätt man kan hantera rättigheter på, samt visa att det faktiskt inte är så mycket jobb att administrera dem.Innehåll
»»
»
»
»
»
»
Relaterade artiklar
» Användare, roller och rättigheter i SQL Server - del 1» Inloggning i SQL Server
Introduktion
Detta är andra delen i en serie artiklar som behandlar hantering av användare, roller och rättigheter i SQL Server. Artiklarnas innehåll är följande:Del 1: Inloggningar och användare
Del 2: Roller
Del 3: Rättigheter
Del 4: Olika lösningar för rättighetshantering
Roller
Har man många användare i en databas så kan det vara mödosamt att administrera rättigheter för var och en av dem, framförallt om man har en komplex databas med många objekt man ska styra säkerheten för. Här kommer roller till räddningen. Roller fungerar som Windows användargrupper i att man kan konfigurera rättigheter för alla användare som är med i rollen på en gång. Lägger man senare till en användare i en roll så får även den nya användaren automatiskt alla rättigheter som är konfigurerade för rollen. I SQL Server 6.5 och tidigare fanns det något som hette grupper, men från och med version 7 byttes de ut mot roller. Det finns dessutom fyra olika typer av roller: serverroller, databasroller, egendefinierade roller och slutligen applikationsroller. De två första består av ett antal fördefinierade roller, medan de sista är två typer av roller som man skapar och konfigurerar rättigheter för själv.
Serverroller
Fixed server roles (som de heter på engelska) är ett antal roller som är förkonfigurerade med rättigheter för ett antal vanliga uppgifter som rör hela servern till skillnad från en specifik databas. Därmed skiljer sig även denna typ av roller från de övriga tre eftersom man inte sätter användare i serverroller utan istället inloggningskonton. Tabell 1 visar vilka serverroller som finns i SQL Server och vilka rättigheter de har.| Tabell 1 | |
| Serverroll | Beskrivning |
|---|---|
| sysadmin | Kan utföra allt i SQL Server |
| serveradmin | Kan administrera serverkonfiguration, samt stänga ner servern |
| setupadmin | Administrerar länkade servers samt procedurer som startas automatiskt |
| securityadmin | Hanterar inloggningskonton samt rättigheter för CREATE DATABASE, kan även läsa felloggar och ändra lösenord |
| processadmin | Hanterar processer som kör i SQL Server |
| dbcreator | Kan skapa, ändra samt ta bort databaser |
| diskadmin | Hanterar filer på disk |
| bulkadmin | Kan exekvera BULK INSERT |
För att lägga till ett login i en serverroll i Enterprise Manager högerklickar man på inloggningskontot och väljer Properties. På fliken Server Roles kryssar man för de serverroller man vill lägga till kontot i. Man kan naturligtvis vara med i flera serverroller. T-SQL kan förstås också användas för att konfigurera serverroller.
-- Lägg till ett login i en serverroll
EXEC sp_addsrvrolemember 'SOUTHPARK\eric',null bulkadmin'
-- Ta bort ett login från en serverroll
EXEC sp_dropsrvrolemember 'SOUTHPARK\eric',null bulkadmin'
-- Lista vilka rättigheter en serverroll har
EXEC sp_srvrolepermission 'bulkadmin'
-- Lista alla login som ingår i en serverroll
EXEC sp_helpsrvrolemember 'bulkadmin'
Man kan även arbeta med serverroller i Enterprise Manager på sidan Server Roles i Security-mappen. I Properties-dialogen kan man lägga till inloggningskonton till en serverroll, samt på fliken Permissions även se exakt vilka rättigheter varje serverroll har (bild 1 visar rättigheterna för rollen securityadmin).
Databasroller
Som jag sa tidigare så är det bara serverroller som sätter rättigheter för inloggningskonton, övriga typer av roller är specifika för varje databas och hanterar därmed även användare i just den specifika databasen som rollen finns i. Alla databaser har ett antal fördefinierade roller som kallas just databasroller. Dessa är förkonfigurerade med rättigheter att utföra olika uppgifter som rör en databas. Tabell 2 listar dessa databasroller och deras rättigheter.| Tabell 2 | |
| Databasroll | Beskrivning |
|---|---|
| db_owner | Har fullständiga rättigheter i databasen |
| db_accessadmin | Kan lägga till och ta bort användare i databasen |
| db_securityadmin | Administrerar alla rättigheter, ägare av objekt, samt hanterar roller och medlemskap i roller |
| db_ddladmin | Kan utföra alla DDL (Data Definition Language) kommandon, men kan EJ administrera rättigheter till objekt |
| db_backupoperator | Kan köra BACKUP, CHECKPOINT samt BACKUP kommandon |
| db_datareader | Kan läsa data (SELECT) från alla användartabeller i databasen |
| db_datawriter | Kan modifiera (INSERT, UPDATE, DELETE) data i alla användartabeller |
| db_denydatareader | Kan EJ läsa data från användartabeller |
| db_denydatawriter | Kan EJ modifiera data i användartabeller |
I alla databaser finns det även en roll som heter public vilken alla användare i en databas alltid är med i. Även om denna roll alltid finns i en databas och ej går att ta bort är det ändå inte samma typ av roll som övriga databasroller, utan den är snarare en egendefinierad roll som ej går att administrera. Denna roll kan alltså användas för att sätta rättigheter som alla användare ska ha som grund.
För att lägga till en användare i en databasroll i Enterprise Manager kan man antingen göra det via Users eller Roles. I bägga fallen handlar det om att ta fram dialogrutan Properties, och sedan är det bara frågan om man ska lägga till roller för en användare eller användare i en roll. Ska man lägga till användare till roller i T-SQL görs det med nedanstående kommandon.
-- Lägg till en användare i en databasroll
EXEC sp_addrolemember 'db_securityadmin',null eric'
-- Ta bort en användare från en databasroll
EXEC sp_droprolemember 'db_securityadmin',null eric'
-- Lista vilka rättigheter en databasroll har
EXEC sp_dbfixedrolepermission 'db_securityadmin'
-- Lista alla användare som är medlemmar i en databasroll, kan
-- även användas för att visa alla roller en användare är medlem i
EXEC sp_helpuser 'db_securityadmin'
Egendefinierade roller
Precis som de fördefinierade databasrollerna så består egendefinierade roller av ett antal användare. En egendefinierad roll är inget annat än en namngiven 'enhet' som man kan sätta rättigheter och dela ut medlemskap för till användare i databasen.Man hanterar medlemskap i rollen på precis samma sätt som beskrivits ovan för de fördefinierade databasrollerna. För att skapa en egendefierad roll i Enterprise Manager högerklickar man på Roles i den databas man vill skapa rollen och väljer New Database Role. I dialogrutan ger man rollen ett namn och man kan direkt lägga till medlemmar i rollen. Observera att även andra egendefinierade roller kan vara medlemmar i roller. För att administrera roller i T-SQL gör man på följande vis. Observera att man måste stå i den databas där rollen ska skapas när man utför detta.
-- Skapa en ny roll
EXEC sp_addrole 'thirdgraders'
-- Ta bort en roll (rollen måste vara tom!)
EXEC sp_droprole 'thirdgraders'
En nyskapad roll har inga rättigheter tilldelad sig. Hur man haterar detta ska jag visa i nästa del av artikelserien.
Applikationsroller
Den sista typen av roller som finns i SQL Server kallas applikationsroller. Den är inte särskilt utnyttjad, faktiskt känner många ej ens till den. En applikationsroll används precis som egendefinierade roller för att slippa administrera rättigheter för flera personer som gemensamt använder en databas och ska ha samma rättigheter i den. Dock skiljer den sig på ett väsentligt sätt från dessa, den har inga användare kopplade till sig!Som namnet antyder är detta istället en roll som används av applikationer. Kanske applikationen själv hanterar säkerheten och rättigheter som gäller den, eller så kanske den helt enkelt inte ska ha någon användarhantering. Istället aktiverar applikationen efter inloggning en applikationsroll som den vill uppfattas som, vilket innebär att alla rättigheter för den användare applikationen loggat in med försvinner. Istället gäller nu de rättigheter som är konfigurerade för den applikationsroll som aktiverats. En annan fördel med detta är att man behöver inte ha ett inloggningskonto och en användare med rättigheter att hantera de objekt i databasen som applikationen använder, utan man kan ha ett konto och en användare helt utan några andra rättigheter än bara precis tillgång till databasen. Därmed finns det ingen möjlighet för någon som känner till det användarnamn och lösenord som applikationen loggar in med att använda någon annan applikation för att logga in och få direkt tillgång till databasen. Skulle någon logga in med dessa uppgifter i t ex Query Analyzer skulle de ändå inte ha några rättigheter att utföra något. Mer om detta i sista delen av artikelserien.
För att skapa en applikationsroll i Enterprise Manager väljer man New Database Role som för egendefinierade roller, men man väljer att skapa en Application Role istället för Standard Role. Därmed försvinner också möjligheten att lägga till användare till rollen. I rutan Passowrd kan man fylla i ett lösenord för applikationsrollen, vilket sparas krypterat. Detta lösenord måste isåfall uppges när någon vill aktivera applikationsrollen. Exemplet nedan visar hur man hanterar applikationsroller i T-SQL.
-- Skapa en applikationsroll
EXEC sp_addapprole @rolename = 'resistancegroup', @password = 'vive le resistance'
-- Aktivera en applikationsroll
EXEC sp_setapprole @rolename = 'resistancegroup', @password = 'vive le resistance'
EXEC sp_setapprole @rolename = 'resistancegroup', @password = {Encrypt N'vive le resistance'}, @encrypt = 'odbc'
-- Ändra lösenordet för en applikationsroll
EXEC sp_approlepassword @rolename = 'resistancegroup', @newpwd = 'somethingelse'
-- Ta bort en applikationsroll
EXEC sp_dropapprole @rolename = 'resistancegroup'
Observera de två olika sätten att aktivera en applikationsroll. I det första alternativet skickas det lösenord man anger som andra parameter i klartext till SQL Server, där det krypteras och valideras mot det krypterade värde som finns lagrat. Problemet med det är att om någon lyssnar på nätverkstrafiken från klientapplikationen till SQL Server kan de sniffa upp lösenordet. Istället kan man då använda det andra alternativet för att kryptera lösenordet med ODBCs Encrypt-funktion innan det skickas till SQL Server. Dock kan det endast användas av applikationer som använder ODBC eller OLE DB för att ansluta till SQL Server, inte DB-Library.
När man aktiverat en applikationsroll så är den aktiv till dess att man stänger uppkopplingen till SQL Server.
I nästa del ska jag beskriva hur man sätter rättigheter för användare och roller.
0 Kommentarer