Security Presentation

--Permissions
USE master
GO
CREATE LOGIN TestLogin WITH PASSWORD = ‘p@ssw0rd1', CHECK_POLICY = ON, CHECK_EXPIRATION = ON
GO
USE AdventureWorks
GO
CREATE USER TestLogin FOR LOGIN TestLogin
GO
USE AdventureWorks
GO
CREATE USER TestUser WITHOUT LOGIN
GO


--Encryption

USE AdventureWorks
GO

--Review the symmetric keys in the database
SELECT * FROM sys.symmetric_keys
GO

--If you do not already have a database master key created, execute the following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password111'
GO


--Different hash algorithms produce different hash values
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('MD5', @HashValue)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('SHA1', @HashValue)
GO

--Hash values are case sensitive
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'sql'
SELECT HashBytes('SHA1', @HashValue)
SELECT @HashValue = 'SQL'
SELECT HashBytes('SHA1', @HashValue)
GO

CREATE TABLE SalaryHashes
(Salary INT NOT NULL,
MD2Hash VARBINARY(500) NOT NULL,
MD4Hash VARBINARY(500) NOT NULL,
MD5Hash VARBINARY(500) NOT NULL,
SHAHash VARBINARY(500) NOT NULL,
SHA1Hash VARBINARY(500) NOT NULL)

DECLARE @salary INT,
@salarylimit INT

SET @salary = 10000
SET @salarylimit = 300000

WHILE @salary <= @salarylimit
BEGIN
INSERT INTO SalaryHashes
(Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash)
SELECT @salary, Hashbytes('MD2',cast(@salary as varchar(6))), Hashbytes('MD4',cast(@salary as varchar(6))), Hashbytes('MD5',cast(@salary as varchar(6))),
Hashbytes('SHA',cast(@salary as varchar(6))), Hashbytes('SHA1',cast(@salary as varchar(6)))

SET @salary = @salary + 10
END

SELECT Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash
FROM SalaryHashes
GO

/* End of hash encryption */
/* Start symmetric keys */

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = 'password111'
GO

SELECT * FROM sys.symmetric_keys
GO

CREATE TABLE SymmetricKeyDemo
(ID int IDENTITY(1,1),
PlainText varchar(30) NOT NULL,
EncryptedText varbinary(80) NOT NULL)
GO

--Symmetric key must be opened before being used
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = 'password111'
GO

INSERT INTO SymmetricKeyDemo
(PlainText, EncryptedText)
VALUES('SQL Server', EncryptByKey(Key_GUID('MySymmetricKey'),'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, cast(DecryptByKey(EncryptedText) AS varchar(30))
FROM SymmetricKeyDemo
GO

CLOSE SYMMETRIC KEY MySymmetricKey
GO

/* End of symmetric keys */
/* Start of certificates */

CREATE TABLE CertificateDemo
(ID int IDENTITY(1,1),
PlainText varchar(30) NOT NULL,
EncryptedText varbinary(500) NOT NULL)
GO

CREATE CERTIFICATE MyCert AUTHORIZATION dbo
WITH SUBJECT = 'Test certificate'
GO

SELECT * FROM sys.certificates
GO

INSERT INTO CertificateDemo
(PlainText, EncryptedText)
VALUES('SQL Server',EncryptByCert(Cert_ID('MyCert'), 'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, CAST(DecryptByCert(Cert_Id('MyCert'),
EncryptedText) AS varchar(max))
FROM CertificateDemo
GO

/* End of cerficates */