I den sista (just nu i alla fall) delen av min säkerhetsserie ska jag gå igenom delar av vad vi reviderar på databasnivå. Det varierar en hel del beroende på krav från olika kravställare och certifieringstyper men nedan är de punkter vi oftast har med. I många fall måste en revision på databasnivå göras tillsammans med utvecklare eller applikationsleverantörer för att kunna verifiera.
För att köra en fråga mot alla databaser på en instans använder jag proceduren sp_MSforeachdb. Observera att den är odokumenterad från Microsofts sida och kan komma att ändras eller tas bort från produkten utan varning. Men den är väldig smidig och effektiv att använda så jag har valt att använda den trots allt.
Databasägare – Alla databasägare, dvs alla medlemmar i rollen db_owner och login kopplade mot usern dbo, har fulla rättigheter på databasnivå. Därför är det viktigt att verifieras och dokumentera vilka login som har rättigheten. Oftast behövs bara betydligt lägre rättigheter än vad db_owner rollen har. T.ex. db_datareader, eller db_datawriter eller läs/skriv/execute rättigheter på objektnivå.
EXEC master.sys.sp_MSforeachdb 'SELECT ''?'' AS [Databasnamn];
EXEC [?].dbo.sp_helprolemember ''db_owner'';
EXEC [?].dbo.sp_helpuser ''dbo'';'
Rättigheter för guest användaren – guest användaren ger alla login som inte är mappade mot en specifik användare rättigheter i en databas. Därför finns det en stor risk att obehöriga får tillgång till data som de inte ska ha. Tyvärr är det inte möjligt att ta bort kontot från en databas men det går att hindra databasåtkomst genom att återkalla rättigheten CONNECT för användaren med ”REVOKE CONNECT FROM guest”. Denna kontroll är extra viktig att göra i samband med SQL Server 2012 contained databases.
Observera dock att användaren ska ha rättigheter i systemdatabaserna.
EXEC master.sys.sp_MSforeachdb
' IF EXISTS (SELECT 1 FROM [?].sys.database_permissions WHERE grantee_principal_id = 2 AND state = ''G'') SELECT ''?'' AS Databasnamn,
p.class_desc AS [Rättighetsnivå],
p.permission_name AS [Rättighet],
CASE WHEN p.class = 0 THEN ''?'' ELSE OBJECT_NAME (major_id, DB_ID(''?'')) END AS Objektnamn,
CASE WHEN DB_ID(''?'') IN (1,2,4) AND p.permission_name = ''CONNECT'' THEN ''Default för systemdatabas'' ELSE ''Kontrollera!'' END AS Status
FROM [?].sys.database_permissions p INNER JOIN [?].sys.database_principals u
ON p.grantee_principal_id = u.principal_id
WHERE u.name = ''guest'' AND p.[state] = ''G'''
Databasanvändare, rättigheter och roller – Alla användare verifieras så att de har de rättigheter och rolltillhörigheter som de ska ha. För vissa användare saknas länkningen mot ett login, dessa ska verifieras och i de fall ett motsvarande login finns bör de återkopplas annars bör de tas bort från databasen. Observera att from SQL Server 2012 finns det en ny typ av användare, contained user eller user with password, vilket gör det möjligt att ha sql användare utan login.
Nedan fråga listar alla användare i alla databaser, vilka grupper de är medlemmar i samt om login-länkningen är bruten.
EXEC master.sys.sp_msforeachdb '
DECLARE @contained table(principalId int)
IF EXISTS (SELECT 1 FROM [?].sys.system_views t WHERE t.name = ''dm_db_uncontained_entities'')
BEGIN
INSERT INTO @contained (principalId)
SELECT major_id
FROM [?].sys.dm_db_uncontained_entities
WHERE class = 4
END ELSE BEGIN
INSERT INTO @contained (principalId)
SELECT p.principal_id
FROM [?].sys.database_principals p
END
SELECT ''?'' AS Databas, u.name AS [användarNamn], l.name AS [loginNamn], l.type_desc AS [loginTyp], roles.roleNames AS [rollNamn],
CASE WHEN l.name IS NULL AND uc.principalId IS NOT NULL THEN ''Saknar login'' ELSE ''Ok'' END AS [loginStatus]
FROM [?].sys.database_principals u
LEFT OUTER JOIN [?].sys.server_principals l ON u.sid = l.sid
LEFT OUTER JOIN
(SELECT rm1.member_principal_id, REPLACE(LTRIM(SUBSTRING(
(
SELECT '' '' + r.name
FROM [?].sys.database_role_members rm
INNER JOIN [?].sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE rm.member_principal_id = rm1.member_principal_id
FOR XML PATH('''')
)
,0,250)),'' '', '', '') AS roleNames
FROM [?].sys.database_role_members rm1
INNER JOIN [?].sys.database_principals r ON rm1.role_principal_id = r.principal_id
GROUP BY rm1.member_principal_id) AS roles ON u.principal_id = roles.member_principal_id
LEFT OUTER JOIN @contained uc ON u.principal_id = uc.principalId
WHERE u.type IN (''U'', ''S'')
AND u.principal_id > 4'
Uppdelning av produktions databaser – produktionsdatabaser bör alltid segregeras från test och utvecklingsdatabaser. Dels är det en säkerhetsrisk pga. möjligheten att göra frågor tvärsöver databaser och att det finns risk för att produktionsdatabasen av misstag uppdateras vid utveckling/testning. Dessutom kan testning och utveckling vara väldigt resurskrävande.
Dokumentera vad alla databaser används till:
SELECT name AS databasNamn, ’’ AS [användning]
FROM sys.databases
WHERE database_id > 5
Passa även på att kontrollera så att inga av Microsofts exempeldatabaser finns på servern:.
SELECT name FROM master.sys.databases
WHERE name IN ('pubs', 'Northwind') OR name LIKE 'Adventure Works%'
Förvaring as databasbackupper – Kontrollera och dokumentera var alla databasbackupper förvaras. Glöm inte att även dokumentera om och hur filbackupper av dessa sköts.
SELECT bs.database_name as databasNamn
,CASE bs.type
WHEN 'D' THEN 'Full backupp'
WHEN 'I' THEN 'Differentiell backupp'
WHEN 'L' THEN 'Transaktionsloggsbackupp'
END AS backuppTyp
,CASE WHEN SUBSTRING([physical_device_name],2,1) = ':' THEN LEFT([physical_device_name],3) + '...'
WHEN LEFT([physical_device_name],2) = '\' THEN LEFT([physical_device_name], CHARINDEX('', physical_device_name, CHARINDEX('
, physical_device_name)+2))
ELSE physical_device_name
END AS backuppKatalog
, MAX(CAST(bs.backup_finish_date AS smalldatetime)) AS senasteBackupp
FROM [msdb].[dbo].[backupset] bs
INNER JOIN [msdb].[dbo].[backupmediafamily] bf ON bs.media_set_id = bf.media_set_id
WHERE DATEDIFF(MONTH, bs.backup_finish_date, GETDATE()) <= 6
GROUP BY bs.database_name
, bs.type
, CASE WHEN SUBSTRING([physical_device_name],2,1) = ':' THEN LEFT([physical_device_name],3) + '...'
WHEN LEFT([physical_device_name],2) = '\' THEN LEFT([physical_device_name], CHARINDEX('', physical_device_name, CHARINDEX('
, physical_device_name)+2))
ELSE physical_device_name
END
ORDER BY 1, 2, 3, 4
Det finns även annat som kan behöva dokumenteras och gås igenom utifrån olika kravbilder från beställaren eller certifieringar etc. men i stort täcks det mesta in av det som jag gått igenom.
/Björn