Cross-Database Access Permission Control – Certificate Signing

Cross Database 牽涉到Ownership Chaining,理論上只要Cross Database就會需要給予額外的權限設定,否則SQL Server會Return權限不足的Error,大致上可以利用以下幾種方法來給與權限:

  1. 直接給予物件權限
  2. 憑證授權 Certificate Signing to Store Procedure
  3. 啟用 Trustworthy On

這篇主要會探討如何利用憑證授權 Certificate Signing來達到Cross DB交易的目標。若是對Trustworthy不清楚,建議可以從上述連結了解。使用 Certificate Signing 有幾項優缺點

Pro:
權限不會過度擴張,代表Cross DB的權限只會給予有授權的SP,若是使用Trustworthy On或是直接給予物件權限的話,則該User或SP會有過多的權限。
Con:
缺點是如果Cross DB的交易非常多,在管理憑證上較為麻煩,縱使有自動化的腳本,也不容易管理。


因此憑證授權非常適合用在少數的特殊情況。E.g: 該Instance底下的公約是不能Cross DB交易,但是今天有一個非常重要的商業需求必須要立即Cross DB交易,那麼憑證授權就是一個相對於其他Solution不錯的選擇。但是如果今天公約在安全性上本來就沒有限制Cross DB, Certificate Signing 就不一定是好的選擇。

1.建立憑証在SourceDB

USE 
[SourceDB]
GO
 
CREATE CERTIFICATE [CrossDBCert]
ENCRYPTION BY PASSWORD = '######'
WITH SUBJECT = 'CrossDBReadPermission',
EXPIRY_DATE = '1/1/3013'
 
GO

2.建立憑証在TargetDB

USE 
[SourceDB] 
GO

DECLARE @cert_id int = cert_id('[CrossDBCert]')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              '######',
              '######')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE [CrossDBCert]
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''######'',
          ENCRYPTION BY PASSWORD = ''######'')'

PRINT convert(varchar(MAX), @sql)
	  
--Target DB
EXEC TargetDB.sys.sp_executesql @sql
go

3.給予TargetDB物件所需的權限

USE 
[TargetDB]
GO

CREATE USER [CrossDBProxy] FROM CERTIFICATE [CrossDBCert]
GO
GRANT SELECT ON [dbo].[birthday] TO [CrossDBProxy]
GO
GRANT Insert ON [dbo].[birthday] TO [CrossDBProxy]
GO

4.在 SourceDB將需要CrossDB的SP簽署權限

-- Grant Permission to SP
USE 
[SourceDB]
GO
 
 
ADD SIGNATURE TO dbo.[UpdataBirthday_1.0.0] BY CERTIFICATE [CrossDBCert]
   WITH PASSWORD = '######'
GO

使用 Certificate signing 一但SP有重新RELEASE都需要再重新簽署權限

如果要查詢哪些SP有憑證權限的可以利用以下腳本查詢

SELECT [Object Name] = object_name(cp.major_id),
       [Object Type] = obj.type_desc,   
       [Cert/Key] = coalesce(c.name, a.name),
       cp.crypt_type_desc
FROM   sys.crypt_properties cp
INNER JOIN sys.objects obj        ON obj.object_id = cp.major_id
LEFT   JOIN sys.certificates c    ON c.thumbprint = cp.thumbprint
LEFT   JOIN sys.asymmetric_keys a ON a.thumbprint = cp.thumbprint
ORDER BY [Object Name] ASC

http://www.sommarskog.se/grantperm.html#crossdb

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s