SQL Server 2016新特性: Always Encrypted (始终加密)
来源:互联网 发布:js中获取文本框中的值 编辑:程序博客网 时间:2024/05/16 06:07
数据,尤其是机密数据的安全性,是我们设计和开发系统所要考虑的。SQL Server 2016引入了加密数据列的新方式,即始终加密(Always Encrypted)。有了始终加密,数据就可以通过ADO.NET在应用层进行加密,这意味着,在数据通过网络发送到SQL Server之前,你可以通过.NET应用程序来加密你的机密数据。这个过程中,网络传输的是密文,存储在DB里的数据也是密文,对我们的数据起到了一定程度的保护作用。下面看看如何使用这一功能(Framework4.6及其以上版本支持该功能)
1、新建列主密钥(Column Master Key)
2、 新建列加密密钥(Column Encryption Key)
3、新建测试表,注意这里指定了机密类型、算法及加密密钥。
CREATE TABLE [dbo].[Patients]([PatientId] [int] IDENTITY(1,1), [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,[FirstName] [nvarchar](50) NULL,[LastName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL,[StreetAddress] [nvarchar](50) NULL,[City] [nvarchar](50) NULL,[ZipCode] [char](5) NULL,[State] [char](2) NULL,[BirthDate] [date] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NOT NULLPRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
4、新建SP,用于Insert数据
CREATE PROCEDURE Insert_Always_Encrypted (@SSN NVARCHAR(11),@FirstName varchar(50),@LastName varchar(50),@BirthDate date )AS INSERT INTO dbo.Patients (SSN, FirstName,LastName, BirthDate)VALUES (@SSN,@FirstName,@LastName,@BirthDate);
5、导出服务器端的证书
6、在客户端导入该证书
7. 测试SP,直接在SSMS里执行是失败的
8、 准备测试的应用程式(注意这里的连接字符串里增加了选项:Column Encryption Setting = Enabled;)
最后附上C#测试源码:
using System.Data;using System.Data.SqlClient;using System.Windows.Forms;// Demo of using Always Encrypted Columnsclass AlwaysEncryptedDemo{ SqlConnection conn; public AlwaysEncryptedDemo() { // Instantiate the connection conn = new SqlConnection( "data source=172.19.121.150;initial catalog=imoltp;integrated security = False; Column Encryption Setting = Enabled; User ID = sa; Password = sa9; "); } // call methods that demo Always Encrypted static void Main() { AlwaysEncryptedDemo scd = new AlwaysEncryptedDemo(); scd.Insertdata(); scd.Selectdata(); } public void Insertdata() { try { // Open the connection for Insertion conn.Open(); // Constructed command to execute stored proceudre string insertString = @"dbo.Insert_Always_Encrypted"; // Declare variable tho hold insdert command SqlCommand icmd = new SqlCommand(insertString, conn); //set command type to stored procedure icmd.CommandType = CommandType.StoredProcedure; // Set value of SSN SqlParameter paramSSN = icmd.CreateParameter(); paramSSN.ParameterName = @"@SSN"; paramSSN.DbType = DbType.String; paramSSN.Direction = ParameterDirection.Input; paramSSN.Value = "555-55-5555"; paramSSN.Size = 11; icmd.Parameters.Add(paramSSN); // Set value of FirstName SqlParameter paramFirstName = icmd.CreateParameter(); paramFirstName.ParameterName = @"@FirstName"; paramFirstName.DbType = DbType.AnsiStringFixedLength; ; paramFirstName.Direction = ParameterDirection.Input; paramFirstName.Value = "Greg"; icmd.Parameters.Add(paramFirstName); // Set value of LastName SqlParameter paramLastName = icmd.CreateParameter(); paramLastName.ParameterName = @"@LastName"; paramLastName.DbType = DbType.AnsiStringFixedLength; ; paramLastName.Direction = ParameterDirection.Input; paramLastName.Value = "Larsen"; icmd.Parameters.Add(paramLastName); // Set value of Birth Date SqlParameter paramBirthdate = icmd.CreateParameter(); paramBirthdate.ParameterName = @"@BirthDate"; paramBirthdate.SqlDbType = SqlDbType.Date; paramBirthdate.Direction = ParameterDirection.Input; paramBirthdate.Value = "2015-01-02"; icmd.Parameters.Add(paramBirthdate); // Exexute Insert icmd.ExecuteNonQuery(); MessageBox.Show("Inserted Demo Record With BirthDate=" + paramBirthdate.Value + "SSN=" + paramSSN.Value); } finally { // Close the connection if (conn != null) { conn.Close(); } } } public void Selectdata() { try { // Open the connection for Selection conn.Open(); // Read Encrypted data string selectString = @"SELECT PatientId, LastName, FirstName, BirthDate, SSN FROM [dbo].[Patients] "; SqlCommand scmd = new SqlCommand(selectString, conn); SqlDataReader dataRead = scmd.ExecuteReader(); while (dataRead.Read()) { MessageBox.Show("Selected Data with ID=" + dataRead["PatientId"].ToString() + " LastName=" + dataRead["LastName"] + " FirstName=" + dataRead["FirstName"] + " BirthDate =" + dataRead["BirthDate"].ToString() + " SSN=" + dataRead["SSN"].ToString()); } } finally { // Close the connection if (conn != null) { conn.Close(); } } }}
1 0
- SQL Server 2016新特性: Always Encrypted (始终加密)
- SQL Server 2016实用新特性
- SQL Server 2016新特性: Temporal table
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- SQL Server 2005新特性
- sql server 2008新特性
- sql server 2008 新特性
- SQL Server 2014新特性
- SQL Server “Denali” ---SQL 2012 新特性
- SQL Server 2012 Always On
- SQL Server 2016新特性: tempdb增强(Multiple TempDB Files)
- SQL Server 2016新特性: In-Memory OLTP
- SQL Server 2016新特性: 对JSON的支持
- SQL Server 2016新特性:DROP IF EXISTS
- SQL SERVER 2005 T_SQL新的特性
- 编程小练习
- C++内存管理
- 关于getbean方法的一些总结
- 恒博新闻动态+标题一、标题二
- 清理缓存
- SQL Server 2016新特性: Always Encrypted (始终加密)
- bzoj 2034: [2009国家集训队]最大收益 贪心优化特殊图最优匹配
- 缓冲区溢出
- 项目2-两个成员的类模板
- 面试题90:旋转数组
- Android数据库的增删改查
- actionBar的使用
- 第六周项目二带武器的游戏角色
- Rad Studio 10.1 Berlin,Delphi 10.1 Berlin,C++ Builder 10.1 Berlin 官方ISO下载(附激活)