lukasurban.blog

Nastavení přístupu mezi SQL Server databázemi

03.04.2023

Slovníček

SQL Server	Databázový server, původně vyvinutý společností Sybase, byl později zakoupen a dále rozvíjen společností Microsoft. První verze pochází z roku 1989 a dnes patří mezi nejrozšířenější databázové systémy.
T-SQL	Transact-SQL je rozšíření jazyka SQL, které využívá SQL Server. Nabízí vlastní syntaxi a funkce, podobně jako PL/SQL používaný v databázích Oracle.
Cross-Database Access	Nastavení serveru, které umožňuje databázovému účtu přístup k více databázím.

Obsah

Úvod
Nastavení přístupu účtu na databázové objekty
Podepsání procedury certifikátem (module signing)
Trustworthy
Ownership Chaining
Závěr

Úvod

Jedná se o nastavení oprávnění, při kterém můžeme spustit tzv. cross-database dotaz (query), který přečte data z více než jedné databáze. Nejjednodušší je nastavit účtu oprávnění přímo na databázové objekty, ke kterým má mít přístup. Pokud bychom však chtěli omezit databázové přístupy následovně:

Uživatel může provést pouze předem dané operace.
Uživatel nemá přímý přístup na databázové objekty.

musíme použít uložené procedury (stored procedures) a situace se trochu zkomplikuje. Celkem máme následující možnosti:

Nastavení přístupu účtu na databázové objekty.
Podepsání procedury certifikátem (module signing).
Povolení Trustworthy a:
- Spuštění procedury pod databázovým vlastníkem.
- Spuštění procedury pod jiným účtem.
Ownership Chaining.

Zatímco první dvě možnosti jsou doporučené, povolit Trustworthy a Ownership Chaining se z bezpečnostních důvodů nedoporučuje. Každá z možností má své výhody a v praxi se lze setkat s různými kombinacemi těchto nastavení. Pro každou možnost uvedu příklad.

Nastavení přístupu účtu na databázové objekty

Účet může provádět libovolné příkazy na základě nastavených oprávnění.
Je možné zapouzdřit dotaz nebo příkazy do procedury.
- Oprávnění se vždy vyhodnocují podle toho, kdo proceduru aktuálně spouští (caller).

Jedná se o nejjednodušší a nejflexibilnější variantu. Nevýhodou však je, že není možné specifikovat a povolit pouze konkrétní operace. U procedur může nastat situace, kdy bude fungovat pod jedním účtem, ale pod jiným nikoli, protože oprávnění se vždy vyhodnocují pro každý účet zvlášť.

-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';  
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';  
CREATE LOGIN ServiceAccount WITH PASSWORD = 'Pa$$w0rd';  

-- Create databases
CREATE DATABASE SourceDatabase;
GO
CREATE DATABASE TargetDatabase;
GO

-- Setup database owners
ALTER AUTHORIZATION ON DATABASE::[SourceDatabase] TO [SourceDatabaseOwner];
ALTER AUTHORIZATION ON DATABASE::[TargetDatabase] TO [TargetDatabaseOwner];

-- Add ServiceAccount to source database and grant permissions
USE SourceDatabase;
GO
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GRANT EXECUTE ON SCHEMA::dbo TO ServiceAccount;

-- Add ServiceAccount to target database and grant permissions
USE TargetDatabase;
GO
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GRANT SELECT ON SCHEMA::dbo TO ServiceAccount;

-- Create target table
USE TargetDatabase;
GO
create table dbo.[Data] (Id int identity primary key, Content nvarchar(255));
insert into dbo.[Data] (Content) values ('Foo'), ('Bar');

-- Create source procedure
USE SourceDatabase;
GO
create or alter procedure dbo.GetData
as
begin
  select id, content from TargetDatabase.dbo.Data
end;
GO

-- works under sa
exec dbo.GetData;

-- works under service account
Use SourceDatabase;
GO
EXECUTE AS LOGIN = 'ServiceAccount';
exec dbo.[GetData];
REVERT;

-- Add AnotherServiceAccount to source database and grant permissions
-- (to test that under another service account it does not work)
CREATE LOGIN AnotherServiceAccount WITH PASSWORD = 'Pa$$w0rd';
CREATE USER AnotherServiceAccount FOR LOGIN AnotherServiceAccount;  
GRANT EXECUTE ON SCHEMA::dbo TO AnotherServiceAccount;

-- Correctly does not work under another service account
-- throws EXECUTE permission was denied on the object 'GetData', database 'SourceDatabase', schema 'dbo'.
EXECUTE AS LOGIN = 'AnotherServiceAccount';
exec dbo.[GetData];
REVERT;

-- We can display current user context, to be sure that we reverted impersonification.
SELECT SUSER_NAME(), USER_NAME();

Podepsání procedury certifikátem (module signing)

Doporučená varianta, pokud chcete mít kontrolu nad tím, které operace může účet provádět. Pokud však dojde k úpravě procedury, je nutné ji znovu podepsat. Nevýhodou je, že nastavení není zcela intuitivní. Procedura bude fungovat stejně, bez ohledu na to, který účet (uživatel) ji spouští.

-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';

-- Create databases
CREATE DATABASE SourceDatabase;
GO
CREATE DATABASE TargetDatabase;
GO

-- Setup database owners
ALTER AUTHORIZATION ON DATABASE::[SourceDatabase] TO [SourceDatabaseOwner];
ALTER AUTHORIZATION ON DATABASE::[TargetDatabase] TO [TargetDatabaseOwner];

-- Create table with data
USE TargetDatabase;
GO
CREATE TABLE dbo.[Data] (Id INT IDENTITY PRIMARY KEY, Content NVARCHAR(255));
INSERT INTO dbo.[Data] (Content) VALUES ('Foo'), ('Bar');
GO

-- Create stored procedure executing under certificate account and accessing target database
USE SourceDatabase;
GO
CREATE OR ALTER PROCEDURE dbo.GetData
AS
BEGIN
    -- SELECT SESSION_USER AS [CurrentUser];
    SELECT Id, Content FROM TargetDatabase.dbo.[Data];
END;
GO

-- create certificate in source database
use SourceDatabase 
go

CREATE CERTIFICATE SignProcedureCert
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
WITH SUBJECT = 'Certificate for signing stored procedures'
GO

-- Sign the procedure
ADD SIGNATURE TO dbo.GetData   
   BY CERTIFICATE SignProcedureCert  
    WITH PASSWORD = 'Pa$$w0rd';  
GO  

-- backup certificate
BACKUP CERTIFICATE SignProcedureCert TO FILE ='C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY
(       
    FILE = 'C:\Certs\SignProcedureCert.pvk',
    DECRYPTION BY PASSWORD = 'Pa$$w0rd',
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
)
GO

-- Import certificate to the target database
USE [TargetDatabase];
GO

CREATE CERTIFICATE SignProcedureCert
FROM FILE = 'C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY (FILE = 'C:\Certs\SignProcedureCert.pvk', 
ENCRYPTION BY PASSWORD = 'Pa$$w0rd', 
DECRYPTION BY PASSWORD = 'Pa$$w0rd')

-- Create certificate-based User to allow access to DB
CREATE USER [SignProcedureCertUser] FROM CERTIFICATE [SignProcedureCert];

-- Setup permission for SignProcedureLogin to target database
GRANT SELECT ON SCHEMA::dbo TO [SignProcedureCertUser]

-- Setup works for any account that has execute permission on stored procedure.
USE SourceDatabase;
GO
CREATE LOGIN AnotherAccount WITH PASSWORD = 'Pa$$w0rd';
CREATE USER AnotherAccount FROM LOGIN AnotherAccount
GRANT EXECUTE ON dbo.GetData TO AnotherAccount
EXECUTE AS LOGIN = 'AnotherAccount';
exec dbo.[GetData];
REVERT;

-- We can display current user context, to be sure that we reverted impersonification.
SELECT SUSER_NAME(), USER_NAME();

-- If the stored procedure will be changed, the following exception will be thrown and stored procedure needs to be signed again.
-- The server principal "AnotherAccount" is not able to access the database "TargetDatabase" under the current security context.

Pokud server běží v Docker kontejneru, certifikáty budou uloženy přímo tam. K připojení k souborovému systému použijeme následující příkaz:

docker exec -t -i <container id> /bin/bash

Tip: Certifikát není nutné ukládat fyzicky do souboru, ale můžeme ho uložit do databázové proměnné:

-- Import certificate from source database to target database
DECLARE @certificate nvarchar(max) = CONVERT(nvarchar(max), CERTENCODED(CERT_ID(N'SignProcedureCert')), 1);
DECLARE @query nvarchar(max) = N'USE [TargetDatabase]; CREATE CERTIFICATE [SignProcedureCert] FROM BINARY = ' + @certificate;
EXEC (@query)

Trustworthy

Příznak Trustworthy umožňuje nastavit podobné chování procedury jako v případě použití certifikátu. Výhodou je, že nastavení je mnohem jednodušší, ale vznikne nám záznam ve Vulnerability Assessment reportu (VA1102). Možná rizika jsou podrobně popsána zde.

Spuštění procedury pod databázovým vlastníkem

-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd'  
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd'

-- Create databases
CREATE DATABASE SourceDatabase
GO
CREATE DATABASE TargetDatabase
GO

-- Setup trustworthy to ON
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;

-- Setup database owners
ALTER AUTHORIZATION ON DATABASE::[SourceDatabase] TO [SourceDatabaseOwner];
ALTER AUTHORIZATION ON DATABASE::[TargetDatabase] TO [TargetDatabaseOwner];

-- Add source database owner to target database
USE TargetDatabase
GO
CREATE USER SourceDatabaseOwner FOR LOGIN SourceDatabaseOwner;  

-- Enable source database owner to authenticate to target database
USE TargetDatabase
GO
GRANT AUTHENTICATE TO SourceDatabaseOwner;

-- Grant permissions
USE TargetDatabase
GO
GRANT SELECT ON SCHEMA::dbo TO SourceDatabaseOwner

-- Create table with data
USE TargetDatabase
GO
create table dbo.[Data] (Id int identity primary key, Content nvarchar(255))
insert into dbo.[Data] (Content) values ('Foo'), ('Bar')

-- Create stored procedure executing under database owner and accessing target database
USE SourceDatabase
GO
create or alter procedure dbo.GetData
with execute as owner as
begin
	select Id, Content from TargetDatabase.dbo.Data
end
GO

-- Works
exec dbo.GetData

Spuštění procedury pod jiným účtem

-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN ServiceAccount WITH PASSWORD = 'Pa$$w0rd';

-- Create databases
CREATE DATABASE SourceDatabase;
GO
CREATE DATABASE TargetDatabase;
GO

-- Setup database owners
ALTER AUTHORIZATION ON DATABASE::[SourceDatabase] TO [SourceDatabaseOwner];
ALTER AUTHORIZATION ON DATABASE::[TargetDatabase] TO [TargetDatabaseOwner];

-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;

-- Add ServiceAccount to source database
USE SourceDatabase
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GO

-- Add ServiceAccount to target database
USE TargetDatabase
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;  
GO

-- Create source db owner in target db and grant authenticate
USE [TargetDatabase];
CREATE USER [SourceDatabaseOwner];
GRANT AUTHENTICATE TO [SourceDatabaseOwner];

-- Grant permissions
USE TargetDatabase
GRANT SELECT ON SCHEMA::dbo TO ServiceAccount;
GO

-- Create table with data
USE TargetDatabase;
GO
CREATE TABLE dbo.[Data] (Id INT IDENTITY PRIMARY KEY, Content NVARCHAR(255));
INSERT INTO dbo.[Data] (Content) VALUES ('Foo'), ('Bar');
GO

-- Create stored procedure executing under service account and accessing target database
USE SourceDatabase;
GO
CREATE OR ALTER PROCEDURE dbo.GetData
WITH EXECUTE AS 'ServiceAccount'
AS
BEGIN
    -- SELECT SESSION_USER AS [CurrentUser];
    SELECT Id, Content FROM TargetDatabase.dbo.[Data];
END;
GO

-- Works
exec dbo.GetData

Ownership Chaining

Tato možnost mi přijde nejvíce neintuitivní a vzhledem k řetězení oprávnění může dojít k nezamýšlenému nastavení. Příklad uvádím spíše jako doplnění předchozích, v praxi jsem se s tímto nastavením nesetkal. Více informací zde.

-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd';
CREATE LOGIN Bob WITH PASSWORD = 'Pa$$w0rd';

-- Create databases
CREATE DATABASE SourceDatabase;
GO
CREATE DATABASE TargetDatabase;
GO

-- Setup database owners
ALTER AUTHORIZATION ON DATABASE::[SourceDatabase] TO [SourceDatabaseOwner];
ALTER AUTHORIZATION ON DATABASE::[TargetDatabase] TO [SourceDatabaseOwner];

-- Set ownership chaining
ALTER DATABASE SourceDatabase SET DB_CHAINING ON
ALTER DATABASE TargetDatabase SET DB_CHAINING ON

-- Create Bob user and some tables in source database
USE SourceDatabase;
GO
CREATE USER Bob FOR LOGIN Bob;
GO
CREATE TABLE Foo (Id int, Content nvarchar(255));
GO

-- Create Bob user and table in target database
USE TargetDatabase;
GO
CREATE USER Bob FOR LOGIN Bob;
GO
CREATE TABLE Bar (Id int, Content nvarchar(255))
GO

-- Create view that access to both databases
USE SourceDatabase;
GO
create or alter view vwFooBar 
as
	select * from foo 
	union all 
	select * from TargetDatabase.dbo.Bar
GO
GRANT SELECT ON vwFooBar TO Bob;

-- Execute as Bob
EXECUTE AS LOGIN = 'Bob';
select * from vwFooBar;
REVERT;

-- View current ownership chaining settings
SELECT is_db_chaining_on, name FROM sys.databases;

Závěr

Ukázali jsme si, jak nastavit cross-database přístup v rámci jedné databázové instance. Článek mi zabral trochu více času, než jsem původně předpokládal, ale myslím, že je dobré mít tyto možnosti popsané u sebe. V případě jakýchkoliv dotazů se neváhejte ozvat 🚀