SQL Server:如果用户未绑定到登录名，是否可以在两个数据库之间同步用户 GUID?

Question

我们的项目使用没有登录名的 SQL Server 数据库用户来实现安全性和行级筛选。

我们实现了一种非常基本的镜像形式，其中事务数据库每晚备份并恢复到第二个"镜像"副本。Web 服务从镜像中提取数据。

但是，我们需要在事务数据库中记录 Web 服务请求，以便在还原下一个镜像时不会擦除这些请求。

我们尝试通过将镜像中的日志表替换为指向事务数据库中"真实"表的同义词来实现这一点。

但是，尝试写入同义词总是失败，并显示错误消息，例如：

服务器主体 " 在当前安全上下文下无法访问数据库 ">

我猜发生这种情况是因为在还原期间重新创建用户并分配了新的 GUID？

我发现很多答案都在谈论使用sp_change_users_login或ALTER USER将数据库用户重新连接到 SQL Server 登录名，但这些解决方案似乎并不适用，因为这些数据库用户没有登录名。

如果镜像数据库中的用户用户名相同，是否有某种方法可以确保将镜像数据库中的用户识别为事务数据库中的同一用户？

谢谢！

Answer 1

是的，当您CREATEUSER(或LOGIN(时，您可以在创建时定义SID：

USE DB1;
GO
CREATE USER SampleUser WITHOUT LOGIN WITH SID = 0x010500000000000903000000F759D99F7F71EC459908C0A30B39056C;
USE DB2;
GO
CREATE USER SampleUser WITHOUT LOGIN WITH SID = 0x010500000000000903000000F759D99F7F71EC459908C0A30B39056C;

Answer 2

有没有办法确保镜像数据库中的用户 在事务数据库中被识别为同一用户，如果他们 用户名相同吗？

答案是：没有

没有登录名的用户只是数据库主体。对于不同数据库中的任何用户，要成为"一个"，他们需要"指向"同一个服务器主体：登录。它是"连接"数据库用户的登录名。

我猜发生这种情况是因为在还原期间，用户 重新创建并分配了新的 GUID？

还原数据库时，数据库用户的 SID(安全标识符(不会更改。 事实上，sids 不会更改但保持不变，这需要在将数据库还原到另一台服务器时找到的操作(sp_change_users_login 或 ALTER USER(。

跨数据库操作的 3 个选项：

1. 跨数据库所有权链接
2. 模仿
3. 模块签名

我无法判断跨数据库链接是否有效，因为无登录用户在离开数据库时没有任何安全本质。在数据库之外，无登录用户没有安全属性，他们失去了"身份"。

另一方面，模拟和模块签名可以工作，因为安全属性由另一个主体保证。

下面是一个"简单"的 poc，适用于没有登录的数据库用户，使用由非对称密钥签名的 sql 模块执行跨数据库操作。网上有很多使用证书进行模块签名的示例，其工作原理是相同的。您可以在网上找到的大多数文章都在服务器主体的范围内执行已签名的模块(exec 作为登录名，exec signed_module(，而无登录名用户不存在服务器级别。 签名模块需要由可信实体执行才能使签名生效(根据定义，登录是可信的(，对于无登录用户，他们的可信度来自他们自己的数据库。为此，无登录用户所在的数据库必须是可信的。

在 poc 中，mirrordb 中有一个带符号的多行表值函数，用于从事务数据库中选择数据。该函数是视图的基础(视图不能签名(。视图是 DML 的目标。实际的 dml 操作由签名的 INSTEAD OF 触发器执行。

在执行脚本之前，您需要生成并调整非对称密钥的路径。

模拟可以以相同的方式工作(删除与签名相关的操作并使用 EXECUTE AS 创建每个模块(

create database transactionaldb
go
create database mirrordb
go
--target table in transactionaldb
use transactionaldb
go
create table targettable
(
id int identity,
username nvarchar(128) default(user_name()),
somevalue varchar(10),
thedate datetime default(getdate())
)
go
--mirror db, userwithout login
use mirrordb
go
create user userwithoutlogin without login
go
--synonym, just for testing
create synonym targettablesynonym for transactionaldb..targettable
go
--for testing, grant permissions on synonym to loginless user
grant select, insert, update, delete on targettablesynonym to userwithoutlogin
go

--switch to loginless user and select from synonym
execute as user = 'userwithoutlogin'
go
select * from targettablesynonym --The server principal "S-1-2-3-4..." is not able to access the database "transactionaldb" under the current security context.
go
--revert
revert
go

/*
1) cross db ownership chaining 
2) impersonation
3) module signing (below)
*/
--module signing, asymmetric key
--create a strong key/name file using sn.exe  :  sn -k 2048 c:testdataasymkeytest.snk //use key size 2048 for sql2016 on.

--the same for transactionaldb
use transactionaldb
go
--master key transactionaldb
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@st3rkeyTransactional'
GO
CREATE ASYMMETRIC KEY asymkeytest  
AUTHORIZATION dbo  
FROM FILE = 'c:testdataasymkeytest.snk';  
GO
create user asymkeytransactionaldbuser from asymmetric key asymkeytest
go
--grant permissions on targettable to asymkeytransactionaldbuser
grant select, insert, update, delete on targettable to asymkeytransactionaldbuser;
go

use mirrordb
go
--master key mirrordb
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@st3rkeyMirror'
GO
CREATE ASYMMETRIC KEY asymkeytest  
AUTHORIZATION dbo  
FROM FILE = 'c:testdataasymkeytest.snk';  
GO
--a db user from the asymkey? not really needed
--create user asymkeymirrordbuser from asymmetric key asymkeytest
go

select is_master_key_encrypted_by_server, *
from sys.databases
where name in ('transactionaldb', 'mirrordb');
go

use mirrordb
go
--a function in mirror which reads the table from transactional
create or alter function dbo.fnreadtargettablefromtransactionaldb()
returns @result table
(
id int,
username nvarchar(128),
somevalue varchar(10),
thedate datetime
)
as
begin
insert into @result(id, username, somevalue, thedate)
select id, username, somevalue, thedate
from transactionaldb.dbo.targettable;
return;
end
go
--grant select on loginless user
grant select on fnreadtargettablefromtransactionaldb to userwithoutlogin;
go

--switch to loginless user and select from function
execute as user = 'userwithoutlogin'
go
select * from fnreadtargettablefromtransactionaldb(); --The server principal "S-1-2-3-4..." is not able to access the database "transactionaldb" under the current security context.
go
--revert
revert
go
--sign the function with the asymmetric key
add signature to fnreadtargettablefromtransactionaldb by ASYMMETRIC KEY asymkeytest;
--... after signing
execute as user = 'userwithoutlogin'
go
select * from fnreadtargettablefromtransactionaldb(); --The server principal "S-1-2-3-4..." is not able to access the database "transactionaldb" under the current security context.
go
--revert
revert
go
--the signed module/function is accessing the transactionaldb but it is NOT trusted
--it could be trusted if:
--   a. it was called in the context of a server principal (login, by definition it is trusted)
--   b. if the source db of the signed module is trustful
--make the source db of the signed module trustful
alter database mirrordb set trustworthy on;
--test again
execute as user = 'userwithoutlogin'
go
--synonym (needs permissions now, at the destination)
select * from targettablesynonym
--signed function, working, permissions from the asymmetric key
select * from fnreadtargettablefromtransactionaldb(); --works
go
--revert
revert
go

use mirrordb
go
--complete interface
create or alter view transactiontableview
as
select *
from fnreadtargettablefromtransactionaldb()
go
--view permissions
grant select, insert, update, delete on transactiontableview to userwithoutlogin;
go

--instead of insert trigger
create or alter trigger insteadofinsertonview on transactiontableview instead of insert
as
begin
set nocount on;
if not exists(select * from inserted)
begin
return;
end
insert into transactionaldb.dbo.targettable(username, somevalue, thedate)
select user_name(), somevalue, thedate
from inserted;
end
go
--sign
add signature to insteadofinsertonview by ASYMMETRIC KEY asymkeytest;
go

execute as user = 'userwithoutlogin'
go
insert into transactiontableview(somevalue)
select col
from (values('one'), ('2'), ('3')) as t(col);
select * from transactiontableview
go
--revert
revert
go

/***********************************************/
--check the security token of a signed module when db trustworthy is off
alter database mirrordb set trustworthy off;
go
--create a proc
create or alter procedure showsecuritytokens
as
begin
select 'mirror_db' as dbname, *
from mirrordb.sys.user_token
union all
select 'transactional_db' as dbname, *
from transactionaldb.sys.user_token;
end
go
--sign the proc for accessing transactionaldb info
add signature to showsecuritytokens by ASYMMETRIC KEY asymkeytest;
go
--permissions
grant execute on showsecuritytokens to userwithoutlogin
go
--enable guest for transactionaldb
use transactionaldb
go
grant connect to guest
go

use mirrordb
go
--switch to loginless user
execute as user = 'userwithoutlogin'
go
exec showsecuritytokens
--when from an untrusted source: the signed module does not get the GRANTs of the asymmetric key (at the destination) 
/*
thedb_______________principal_id__name_________________________type___________________________usage
transactional_db____5_____________asymkeytransactionaldbuser___USER MAPPED TO ASYMMETRIC KEY__DENY ONLY    
*/
go
--revert
revert
go

--cleanup
/*
use master
go
drop database transactionaldb
go
drop database mirrordb
go
*/