SQL Server >> 安全相关函数

安全相关的函数这些可能会有用： CERTENCODED \CERTPRIVATEKEY \LOGINPROPERTY \ORIGINAL_LOGIN \PWDCOMPARE \SESSION_USER \SESSIONPROPERTY \SUSER_ID \SUSER_NAME \SYSTEM_USER \USER_NAME \DATABASE_PRINCIPAL_ID \IS_MEMBER \IS_ROLEMEMBER \IS_SRVROLEMEMBER

用户身份：IS_MEMBER \IS_ROLEMEMBER \IS_SRVROLEMEMBER

IS_MEMBER：可以验证用户是否是某NT工作组或者AD安全组的成员

IS_ROLEMEMBER：可以验证用户是否是某数据库角色成员

IS_SRVROLEMEMBER：可以验证用户是否是某服务器角色成员

证书编码：CERTENCODED \CERTPRIVATEKEY

CERTENCODED：把证书转成二进制编码，例如SELECT CERTENCODED(CERT_ID('证书名称'))

CERTPRIVATEKEY：通常配合CERTENCODED，生成证书的私钥，输出成VARBINARY(MAX)，然后可以在通过私钥二进制代码后（像创建程序集一样）在另外一个数据库创建相同的证书。然后再通过ENCRYPTBYCERT和DECRYPTBYCERT进行数据加密和解密。

实际使用场景就是可以把一些敏感数据字段用密钥加密后，到了另外一个数据库用私钥解密，私钥解密指定解密密码达到安全性的要求。

DECLARE @CERTENC VARBINARY(MAX);  
DECLARE @CERTPVK VARBINARY(MAX);  
SELECT @CERTENC = CERTENCODED(CERT_ID('SOURCE_CERT'));  
SELECT @CERTPVK = CERTPRIVATEKEY(CERT_ID('SOURCE_CERT'),  
       'CertEncryptionPa$$word');  
SELECT @CERTENC AS BinaryCertificate;  
SELECT @CERTPVK AS EncryptedBinaryCertificate;  
GO  

-- Create the duplicate certificate in the TARGET_DB database  
USE TARGET_DB  
GO  
CREATE CERTIFICATE TARGET_CERT  
FROM BINARY = <insert the binary value of the @CERTENC variable>  
WITH PRIVATE KEY (  
BINARY = <insert the binary value of the @CERTPVK variable>  
, DECRYPTION BY PASSWORD = 'CertEncryptionPa$$word');  
-- Compare the certificates in the two databases  
-- The two certificates should be the same   
-- except for name and (possibly) the certificate_id  
SELECT * FROM SOURCE_DB.sys.certificates  
UNION  
SELECT * FROM TARGET_DB.sys.certificates;  

USE SOURCE_DB;  
  
DECLARE @CLEARTEXT nvarchar(100);  
DECLARE @CIPHERTEXT varbinary(8000);  
DECLARE @UNCIPHEREDTEXT_Source nvarchar(100);  
SET @CLEARTEXT = N'Hello World';  
SET @CIPHERTEXT = ENCRYPTBYCERT(CERT_ID('SOURCE_CERT'), @CLEARTEXT);  
SET @UNCIPHEREDTEXT_Source =   
    DECRYPTBYCERT(CERT_ID('SOURCE_CERT'), @CIPHERTEXT)  
-- Encryption and decryption result in SOURCE_DB  
SELECT @CLEARTEXT AS SourceClearText, @CIPHERTEXT AS SourceCipherText,   
       @UNCIPHEREDTEXT_Source AS SourceDecryptedText;  
  
-- SWITCH DATABASE  
USE TARGET_DB;  
  
DECLARE @UNCIPHEREDTEXT_Target nvarchar(100);  
SET @UNCIPHEREDTEXT_Target = DECRYPTBYCERT(CERT_ID('TARGET_CERT'), @CIPHERTEXT);  
-- Encryption and decryption result in TARGET_DB  
SELECT @CLEARTEXT AS ClearTextInTarget, @CIPHERTEXT AS CipherTextInTarget, @UNCIPHEREDTEXT_Target AS DecriptedTextInTarget;   
GO  

获取登录用户身份：ORIGINAL_LOGIN \SESSION_USER \SUSER_ID \SUSER_NAME \SYSTEM_USER \USER_NAME \DATABASE_PRINCIPAL

ORIGINAL_LOGIN：因为可以通过EXECUTE AS  XXX切换用户身份执行上下文，ORIGINAL_LOGIN的作用就是获取原始登录用户名称的

SESSION_USER：获取会话用户，会话用户是数据库用户，不是登录用户

SUSER_ID\SUSER_NAME：这两个是一样的，一个是获取登录用户ID，一个是获取登录用户名称

SYSTEM_USER：也是获取登录用户，不过这个的作用是用在创建表字段的时候指定默认约束的

USER_NAME\DATABASE_PRINCIPAL_ID：这两个也是一样的，一个获取数据库用户名称，一个获取数据库用户ID