使用透明数据加密(TDE)来保护数据库
来源:互联网 发布:数据库字段拆分 编辑:程序博客网 时间:2024/04/30 15:50
这篇文章说明了如何使用透明数据加密(TDE)来保护数据库,包括备份的文件。要了解这个功能可以被用来提高数据库应用程序的安全性,请查看这篇文章。
Step1:
USE master ;
GO
IF EXISTS( SELECT
*
FROM
sys.databases
WHERE
name = 'TdeDemo' )
DROP DATABASE TdeDemo ;
GO
CREATE DATABASE TdeDemo ;
GO
Next, create the server-level certificate which will protect the database key used to encrypt the database's files. This certificate in turn will be protected by the master key which if it does not exist will need to be created:
创建主密钥
USE master ;
GO
IF NOT EXISTS( SELECT
*
FROM
sys.symmetric_keys
WHERE
name LIKE '%[_]DatabaseMasterKey%' )
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'997jkhUbhk$w4ez0876hKHJH5gh' ;
END
GO
创建或获取由主密钥保护的证书
CREATE CERTIFICATE MyTdeCert
WITH SUBJECT = 'My TDE Certificate' ;
GO
With the server-level components in place, the database can now be encrypted. This is done by first creating the database (symmetric) encryption key within the database and then enabling TDE:
USE TdeDemo ;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER
CERTIFICATE MyTdeCert ;
GO
ALTER DATABASE TdeDemo
SET ENCRYPTION ON ;
GO
Database encryption may take a while to complete. While in progress, the sys.dm_database_encryption_keys data management view will show the database in an encryption_state of 2:
SELECT
DB_NAME(database_id) AS DB ,
encryption_state
FROM
sys.dm_database_encryption_keys
WHERE
database_id = DB_ID() ;
GO
Once TDE encryption has been fully applied, the encryption_state will become 3:
SELECT
DB_NAME(database_id) AS DB ,
encryption_state
FROM
sys.dm_database_encryption_keys
WHERE
database_id = DB_ID() ;
GO
Now to demonstrate the protection of database backup files through TDE, backup the database and its certificate. Please note that these are being backed up locally to the same location. This is not a secure practice but is expediant for this demo:
USE master ;
GO
BACKUP CERTIFICATE MyTdeCert
TO FILE = 'c:\temp\MyTdeCert'
WITH PRIVATE KEY (
FILE = 'c:\temp\MyTdeCertPrivateKey',
ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
) ;
GO
BACKUP DATABASE TdeDemo
TO DISK = 'c:\temp\TdeDemo.bak'
WITH INIT ;
GO
By dropping the database and the server-level certificate, we can simulate a restore to a different server:
DROP DATABASE TdeDemo ;
GO
DROP CERTIFICATE MyTdeCert ;
GO
With the certificate missing, the restore operation will fail:
RESTORE DATABASE TdeDemo
FROM DISK = 'C:\temp\TdeDemo.bak' ;
GO
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x686A8264E4A17572FBAE6A1D091A47D600847FB6'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It's not until the certificate is recovered to the server that the backup file can be restored:
CREATE CERTIFICATE MyTdeCert
FROM FILE = 'c:\temp\MyTdeCert'
WITH PRIVATE KEY (
FILE = 'c:\temp\MyTdeCertPrivateKey',
DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
) ;
GO
RESTORE DATABASE TdeDemo
FROM DISK = 'C:\temp\TdeDemo.bak' ;
GO
Processed 168 pages for database 'TdeDemo', file 'TdeDemo' on file 1.
Processed 2 pages for database 'TdeDemo', file 'TdeDemo_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.157 seconds (8.415 MB/sec).
To reset the environment:
USE master ;
GO
DROP DATABASE TdeDemo ;
GO
DROP CERTIFICATE MyTdeCert ;
GO
- 使用透明数据加密(TDE)来保护数据库
- [Oracle] 数据库安全之 - 透明数据加密技术(TDE)
- 透明数据加密 (TDE)
- oracle透明数据加密技术(TDE)
- Oracle10G:透明数据加密技术(TDE)
- Oracle10G:透明数据加密技术(TDE)
- 了解透明数据加密 (TDE)
- 什么是透明数据加密(TDE)?
- Oracle 透明数据加密TDE
- 电子商务使用数据加密来保护数据库
- 电子商务使用数据加密来保护数据库
- 电子商务使用数据加密来保护数据库
- Oracle透明数据加密(TDE)真实环境使用分析
- SQLServer 数据加密解密:将 TDE 保护的数据库移到其他实例(二)
- 电子商务使用数据加密来保护数据库 (二)
- oracle 透明数据加密(TDE)学习笔记(1)--wallet使用和管理
- Oracle-11g 中使用表空间透明数据加密(TDE)
- 何时可以开启透明数据加密(TDE)?
- 中国将成为最大智能手机市场
- 开源在中国需要什么?
- hdu2824
- C/C++ -- 编程中的内存屏障(Memory Barriers) (1)
- 十大Javascript框架,你常用其中的几个?
- 使用透明数据加密(TDE)来保护数据库
- 10大正在走下坡路的IT公司
- 从零开始学习Windows WDF驱动程序开发
- linux 多线程 多进程同步
- 利用Cookies实现ASP.NET跨域单点登录
- java中PipedOutputStream和PipedInputStream类用法
- Hack and / - Linux Troubleshooting
- Amazon DynamoDB
- soundpool