Så hanterar du behörigheter i Azure SQL Databases

By | oktober 14, 2015

En sak som saknas i Azure SQL Databases jämfört med ”vanliga” SQL Server är ett grafiskt användargränssnitt för att hantera behörigheter. Det finns inte heller någon särskilt användbar dokumentation från Microsoft tillgänglig kring hur man hanterar behörigheterna. Här kommer därför en sammanfattning.

Logins och users

Logins är inte samma sak som Users. Detta är egentligen inte så komplicerat, men verkar ändå vara något som många blandar ihop.

  • Logins, som är på servernivå, och
  • Users, som är på databasnivå

Ett och samma login kan kopplas till olika users i olika databaser. Kopplingen sker via fältet SID (Security IDentifier). I bilden nedan så är alltså Login Adam kopplad till olika Users i de tre olika databaserna.

Azure SQL Permissions Login Users

En sak som förmodligen kan förvirra är att i Connection String när man ansluter till Azure SQL Databases så står det User ID, men det är vanligtvis ett Login som anges (undantag finns).

Data Source=#####;Initial Catalog=Database2;User ID=Adam;Password=#####;

Enligt vår bild så skulle vi alltså med denna Connection String ansluta till databasen ”Database 2” och med user ”Thor”.

Contained Database Users

Det finns dock speciella Users som kallas ”Contained Database Users”. Dessa är inte kopplade till något Login. Detta är undantaget jag nämnde ovan, och för dessa så är det givetvis deras användarnamn istället för ett login som skall anges i Connection String.

Behörigheter Logins

För att få en lista över alla logins kan vi köra detta SQL-script.

SELECT * FROM sys.sql_logins;

Observera att scriptet måste köras mot master-databasen i Azure.

Vi får då en lista över loginnamn och deras sid, samt viss ytterligare metadata.

Azure SQL Login list

Logins kan ha serverbehörigheter genom att tillhöra olika serverroller. För att tilldela serverbehörigheter så måste loginet kopplas till en användare i databasen master. Exempel på hur vi ger Login Skrivtorget behörighet till serverrollen Loginmanager:

CREATE USER Skrivtorget FROM LOGIN Skrivtorget;
EXEC sp_addrolemember 'loginmanager', 'Skrivtorget';

Observera att scriptet måste köras mot master-databasen i Azure.

Vi kan få en lista över serverbehörigheterna med följande SQL-script.

/* 
Script to check server role membership of Azure database logins. 
Must be run on master database.
Johan Åhlén 2015 
*/
SELECT
r.name AS [role_name]
,s.name AS [login_name]
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
INNER JOIN sys.sql_logins s
ON m.SID = s.SID
WHERE m.name <> 'dbo';

Observera att scriptet måste köras mot master-databasen i Azure.

För att ge behörigheter används proceduren sp_addrolemember och för att ta bort behörigheter används proceduren sp_droprolemember.

Behörigheter Users

För att skapa en User i en databas som är kopplad till ett Login (alltså har samma SID), så använde CREATE USER. Detta exempel skapar ”Thor”, som är kopplad till Login ”Adam”.

CREATE USER Thor FROM LOGIN Adam;

Observera att scriptet måste köras i den databas där du vill skapa din User.

Det går också att skapa Contained Database Users genom en variant på ovanstående:

CREATE USER Thor WITH password='<Strong_Password>';

Users kan tillhöra databasroller som ger behörigheter. För att se vilka roller som users tillhör så kan följande SQL-script användas:

/* 
Script to check role membership of Azure database users. 
Johan Åhlén 2015 
*/
SELECT 
 r.name AS [role_name]
 ,m.name AS [user_name]
 ,m.sid AS [user_sid]
FROM sys.database_role_members rm 
INNER JOIN sys.database_principals r 
    ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m 
    ON rm.member_principal_id = m.principal_id
WHERE m.name <> 'dbo';

För att ge behörigheter till databasroller används proceduren sp_addrolemember och för att ta bort behörigheter används proceduren sp_droprolemember.

Behörigheter går också att styra till enskilda objekt (tabeller, scheman, procedurer, etc). Följande SQL-script visar alla dessa behörigheter:

/* 
Script to check role membership of Azure database users. 
Johan Åhlén 2015 
*/
SELECT 
 pe.[permission_name]
 ,pe.class_desc
 ,OBJECT_NAME(pe.major_id) AS [object_name]
 ,pe.state_desc AS [permission_state]
 ,pr.name AS [user_name]
 ,pr.[sid] as [user_sid]
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
  ON pe.grantee_principal_id = pr.principal_id
WHERE pe.grantee_principal_id <> 0;

Resultatet kan se ut så här:

Azure SQL User rights

Dessa objektbehörigheter tilldelas med hjälp av GRANT-kommandot. Exempelvis:

GRANT Select ON dbo.BoxenLog TO johanswebappar

På motsvarande sätt används REVOKE för att ta bort behörigheter:

REVOKE Select ON dbo.BoxenLog TO johanswebappar

För att få en lista över alla users i en viss databas kan vi använda vyn sys.sysusers.

SELECT * FROM sys.sysusers;

En komplett bild över behörigheterna för Azure SQL databases finns här:

Azure SQL databases permissions chart

Azure Active Directory Authentiation

Ett nytt sätt att autentisera sig mot Azure SQL databases är att använda Azure Active Directory. Fördelen med detta är att du slipper hantera lösenord.

För att använda denna typ av autentisering används Contained Database Users och en särskild syntax vid skapandet av dessa users:

CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;

Connection string ser också lite annorlunda ut:

Data Source=#####;Initial Catalog=Database2;Authentication=Active Directory Integrated;

Det går dock fortfarande att specificera användarnamn och lösenord i Connection String för Azure Active Directory Authentication.

Azure Active Directory Authentication är en ny tjänst som i skrivande stund fortfarande är i ”preview”.

Mer info

För mer info rekommenderar jag de här länkarna: