Sql-Server
查找簽名的過程、函式、觸發器、程序集以及哪些證書/非對稱密鑰
我創建了一些證書(通過CREATE CERTIFICATE)和非對稱密鑰(通過CREATE ASYMMETRIC KEY),並使用它們對各種儲存過程、使用者定義函式(UDF)、觸發器和程序集(通過ADD SIGNATURE)進行簽名和會簽. 但現在我需要找出哪些證書和/或非對稱密鑰已用於簽署哪些特定模組。
最簡單的方法似乎是使用sys.crypt_properties目錄視圖,它包含證書/非對稱密鑰和簽名模組之間的關係,以及使用(即簽名或會簽)。還有一些其他方法可以找到哪些模組已簽名,但似乎沒有一種方法表明簽名與會簽。
SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName], so.[name] AS [ObjectName], so.[type_desc] AS [ObjectType], --- scp.crypt_type_desc AS [SignatureType], ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName], --- scp.thumbprint FROM sys.crypt_properties scp INNER JOIN sys.objects so ON so.[object_id] = scp.[major_id] LEFT JOIN sys.certificates sc ON sc.thumbprint = scp.thumbprint LEFT JOIN sys.asymmetric_keys sak ON sak.thumbprint = scp.thumbprint WHERE so.[type] <> 'U' ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];
在我的測試數據庫中,此查詢返回以下內容:
Schema ObjectName ObjectType SignatureType CertOrAsymKeyName thumbprint ------ ---------- ---------- ------------- ----------------- ---------- dbo fnPaymentCalc SQL_SCALAR_FUNCTION COUNTER SIGNATURE BY ASYMMETRIC KEY KeyTest1 0x2333B2FA6AA8004E dbo ModuleTest2 SQL_STORED_PROCEDURE SIGNATURE BY CERTIFICATE CrossDatabaseCert 0x49BA174584C78C878D923690C15898A809CBACDF dbo TestSig SQL_STORED_PROCEDURE SIGNATURE BY CERTIFICATE bob 0x778B3DB4ED981FC27AB301ACE7A1AB8424F64792 dbo TestSig SQL_STORED_PROCEDURE SIGNATURE BY ASYMMETRIC KEY KeyTest1 0x2333B2FA6AA8004E