SQL Server 2005 Encryption System (i)

server| Encryption | system

SQL Server 2005 Encryption System (i)
SQL Server 2005 introduced a complete set of encryption methods, the specific terminology of the lazy do not write, we can see Bol mody.

The general structure is to use SQL Server service account to generate a service master key when installing SQL Server 2005, and then the database administrator can create DATABASE master key on the databases. Of course, you can create a certificate, a symmetric key, or an asymmetric key for database user. These three objects can be used to encrypt user data, but it is generally recommended to use the certificate signing code, using a certificate or asymmetric key to encrypt the symmetric key, the use of symmetric key to encrypt user data.

Here's a piece of code, because considering me and Wang Hui brothers at that time in Chengdu Hotel in the study of this thing is hard to find a complete sample code, later or Gtec Xu Qiang big to a link, and then we have a patchwork of code, dare not to enjoy, to share with you.

A good example of signing code with a certificate is to allow users to modify some of the data without giving the database user large permissions, which is also a manifestation of permission granularity in SQL Server 2005:

--------------------------------------------------------------------------------

--Create an experimental database
Use master
IF EXISTS (SELECT [name] from sys.databases WHERE [name] = ' Sales ')
DROP DATABASE Sales
CREATE DATABASE Sales
IF EXISTS (SELECT principal_id from sys.server_principals WHERE [name] = ' Ryan ' and [type] = ' S ')
DROP LOGIN Ryan
CREATE LOGIN ryan with PASSWORD = ' P@ssw0rd '
IF EXISTS (SELECT principal_id from sys.server_principals WHERE [name] = ' Teddy ' and [type] = ' S ')
DROP LOGIN Teddy
CREATE LOGIN Teddy with PASSWORD = ' P@ssw0rd '

--Create user Ryan and create DATABASE master key
Use Sales
IF EXISTS (SELECT * from sys.database_principals WHERE [name] = ' Ryan ' and [type] = ' S ')
DROP User Ryan
CREATE USER Ryan for LOGIN ryan with default_schema = dbo
Go

CREATE MASTER KEY Encryption by PASSWORD = ' P@ssw0rd '
Go

--Create a certificate
IF EXISTS (SELECT [name] from sys.certificates WHERE [name] = ' Cert_maintain ')
DROP Certificate Cert_maintain
CREATE Certificate Cert_maintain
With SUBJECT = ' Certificate for Database maintainance ',
start_date = ' 01/01/2006 ',
expiry_date = ' 12/31/2015 '
Go

SELECT * from Sys.certificates

--Create a database user with a certificate and grant the user permission to administer the database user
CREATE USER user_maintain for certificate Cert_maintain
GRANT ALTER any USER to User_maintain

--Create a stored procedure
IF EXISTS (SELECT [name] from Sys.procedures WHERE [name] = ' Usp_adduser ')
DROP PROCEDURE Dbo.usp_adduser
Go
CREATE PROCEDURE Dbo.usp_adduser
@UserName varchar (50)
As
IF EXISTS (SELECT * from sys.database_principals WHERE [name] = @UserName and [Type] = ' S ')
EXEC (' DROP USER ' + @UserName)

EXEC (' CREATE USER ' + @UserName)
Go

--Complete the preparation and start testing the encryption
GRANT EXEC on Dbo.usp_adduser to Ryan
EXECUTE as LOGIN = ' Ryan '
BEGIN TRY
EXEC Dbo.usp_adduser ' Teddy '
End TRY
BEGIN CATCH
SELECT error_message () as ' ERROR Msg '
End CATCH
REVERT

--Signing stored procedure code with a certificate
ADD SIGNATURE to Dbo.usp_adduser by certificate Cert_maintain
ALTER certificate Cert_maintain REMOVE PRIVATE KEY

--Try executing the script here
EXECUTE as LOGIN = ' Ryan '
EXEC Dbo.usp_adduser ' Teddy '
REVERT