lukasurban.blog

SQL Server Cross Database Access

03.04.2023

Slovníček

SQL Server	Databázový server původně vyvinutý společností Sybase a poté koupený a rozvíjený společností Microsoft. První verze je z roku 1989 a jedná se o jednu z nejrozšířenějších databází.
T-SQL	Transact SQL je rozšíření jazyka SQL, které používá SQL Server. Má vlastní syntaxi a funkce, podobně jako PL/SQL od společnosti Oracle.
Cross Database Access	Nastavení serveru tak, aby databázový účet mohl přistupovat do více než jedné databáze zároveň.

Obsah

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

Úvod

Jedná se o jednoduché nastavení, kdy účtu přidáme oprávnění přímo na databázové objekty, ke kterým má mít přístup. V případě, kdy bychom chtěli databázové přístupy limitovat pomocí uložených procedur, tak aby účet mohl provést nějakou předdefinovanou operaci, ale neměl přístup na podkladové databázové objekty, tak se nám začíná situace trochu komplikovat. Řekněme, že nějaký účet chce spustit dotaz, který čte data z tabulek, které jsou v různých databázích na stejném databázovém serveru (instanci). Máme následující možnosti jak situaci vyřešit

Nastavit účtu přístup na databázové objekty napřímo
Podepsat proceduru certifikátem (module signing)
Povolit Trustworthy a
- proceduru spustit pod databázovým vlastníkem
- proceduru spustit pod jiným účtem
Povolit Ownership Chaining

Zatímco první dvě možnosti jsou doporučené, povolení Trustworthy a Ownership Chainingu se nedoporučuje z bezpečnostních důvodů. Každá z možností má nějakou výhodu a v praxi se lze setkat s různou kombinací těchto nastavení. Pro každou možnost uvedu příklad.

Nastavení účtu přístup na databázové objekty napřímo

Účet může provádět libovolné příkazy podle nastaveného 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 zrovna spouští (caller).

Jedná se o nejjednodušší a nejvíce flexibilní variantu. Nevýhodou může být, že nelze specifikovat a povolit pouze konkrétní operace. V případě procedury může nastat situace, kdy pod jedním účtem fungovat bude a pod jiným účtem ne, protože se oprávnění 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čovaná varianta, pokud chcete mít kontrolu nad tím, které operace může účet spouštět. Pokud dojde k úpravě procedury, je potřeba jí znova podepsat. Nevýhodou je, že nastavení není úplně intuitivní. Procedura bude fungovat stejně, bez ohledu na to který účet (uživatel) jí 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.

V případě, že server běží v Docker kontejneru, certifikáty budou uložené tam. Do souborového systému se připojíme pomocí příkazu

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

Tip: certifikát není potřeba ukládat fyzicky do souboru, ale je možné 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 nám umožňuje nastavit obdobné chování procedury jako v případě použití certifikátu. Výhodou je, že nastavení je mnohem jednodušší, ale nevýhodou je, že nám vznikne záznam ve Vulnerability Assessment reportu (VA1102). Zde jsou popsána možná rizika.

Procedura spuštěná 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

Procedura spuštěná 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 asi nejvíce neintuitivní a tím jak se oprávnění řetězí tak 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é instanci. Č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ýkoliv dotazů se neváhejte ozvat 🚀🚀🚀