SQL Server でデータベース マスター キーを利用したデータの暗号化(EncryptByKey:証明書、対称キー)
SQL Server 2005 以降の EncryptByKey 関数では、データベース マスター キー(と証明書、対称キー)を利用して暗号化することもできます。これは次のように利用します。
-- ログイン アカウント作成(user1) USE master CREATE LOGIN user1 WITH PASSWORD = 'P@ssword' -- データベースの作成 CREATE DATABASE DbCerSymKeyTest go -- データベース ユーザー作成(user1) USE DbCerSymKeyTest CREATE USER user1 FOR LOGIN user1 go -- テーブルの作成 CREATE TABLE テスト1 ( id int IDENTITY(1, 1) ,ClearText nvarchar(100) ,EncryptData varbinary(max) ) go -- user1 へ INSERT と SELECT 権限を GRANT GRANT INSERT, SELECT ON テスト1 TO user1 -- データベース マスタ キーの作成 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>' go -- 証明書の作成(証明書の所有者を user1 へ設定) CREATE CERTIFICATE Cert1 AUTHORIZATION user1 WITH SUBJECT = 'テスト1_暗号化用' ,START_DATE = '2009/10/20' go -- 対称キーの作成 (証明書を利用、アルゴリズムは AES_128) CREATE SYMMETRIC KEY Sym_Key1 AUTHORIZATION user1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Cert1 go -- user1 でのログインをシミュレート EXECUTE AS LOGIN = 'user1' -- 対称キーのオープン OPEN SYMMETRIC KEY Sym_Key1 DECRYPTION BY CERTIFICATE Cert1 -- 対称キーの GUID を Key_GUID 関数で取得して、 -- EncryptByKey でデータを暗号化して INSERT DECLARE @kGuid UNIQUEIDENTIFIER SET @kGuid = Key_GUID('Sym_Key1') INSERT INTO テスト1 VALUES ( '平文', EncryptByKey(@kGuid, N'暗号化される?') ) -- 対称キーの CLOSE CLOSE SYMMETRIC KEY Sym_Key1 -- 普通に SELECT すると暗号化されていることを確認できる SELECT * FROM テスト1 -- 復号化 -- 対称キーの OPEN OPEN SYMMETRIC KEY Sym_Key1 DECRYPTION BY CERTIFICATE Cert1 -- DecryptByKey で復号化 SELECT ID, ClearText ,CONVERT( nvarchar, DecryptByKey(EncryptData) ) FROM テスト1 -- 対称キーの CLOSE CLOSE SYMMETRIC KEY Sym_Key1 -- シミュレートの終了 REVERT
他のユーザー user2 から対称キーを利用してみると、
USE master CREATE LOGIN user2 WITH PASSWORD = 'P@ssword' USE DbCerSymKeyTest CREATE USER user2 FOR LOGIN user2 go -- user2 でのログインをシミュレート EXECUTE AS LOGIN = 'user2' -- 対称キーの OPEN でエラーが発生する OPEN SYMMETRIC KEY Sym_Key1 DECRYPTION BY CERTIFICATE Cert1 WITH PASSWORD = '<StrongPassword>' /* メッセージ 15151、レベル 16、状態 1、行 4 対称キー 'Sym_Key1' を 検索 できません。存在しないか、権限がありません。 */ -- シミュレートの終了 REVERT
対称キーのアルゴリズムで指定できるのは、次のとおりです。
DES、TRIPLE_DES、TRIPLE_DES_3KEY、RC2、RC4、RC4_128、DESX、AES_128、AES_192、AES_256
EncryptByKey の詳細(ヘルプ)はこちら
http://msdn.microsoft.com/ja-jp/library/ms174361.aspx
DecryptByKey の詳細(ヘルプ)はこちら
http://technet.microsoft.com/ja-jp/library/ms181860.aspx
CREATE MASTER KEY の詳細(ヘルプ)はこちら
http://technet.microsoft.com/ja-jp/library/ms174382.aspx
CREATE CERTIFICATE の詳細(ヘルプ)はこちら
http://technet.microsoft.com/ja-jp/library/ms187798.aspx
CREATE SYMMETRIC KEY の詳細(ヘルプ)はこちら
http://technet.microsoft.com/ja-jp/library/ms188357.aspx
OPEN SYMMETRIC KEY の詳細(ヘルプ)はこちら
http://msdn.microsoft.com/ja-jp/library/ms190499.aspx
暗号化アルゴリズムの選択の原則についてはこちら
http://msdn.microsoft.com/ja-jp/library/ms345262.aspx
SQL Server の暗号化階層はこちら
http://msdn.microsoft.com/ja-jp/library/ms189586.aspx