SECURITY

1. SQL Server SECURITY

2. SQL Server Security Model

3. Principals • logins/users • roles • certificate • assymetric key

4. Login Types • Standard SQL Server login • Windows login • Windows group • Certificate • Asymmetric Key

5. Loginایجاد • CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> } • <option_list1> ::= PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ]

6. <option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name • <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name • <windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language

7. Fixed Server Roles • bulkadmin • dbcreator • diskadmin • processadmin • securityadmin • serveradmin • setupadmin • sysadmin

8. Login Creation • CREATE LOGIN TestLogin WITH PASSWORD = '<specifystrongpassword>', CHECK_POLICY = ON, CHECK_EXPIRATION = ON GO

9. User Creation • CREATE USER user_name [ { { FOR | FROM } { LOGIN login_name | CERTIFICATE cert_name | ASYMMETRIC KEY asym_key_name} | WITHOUT LOGIN ] [ WITH DEFAULT_SCHEMA = schema_name ]

10. Fixed Database Roles • db_accessadmin • db_backupoperator • db_datareader / db_datawriter • db_ddladmin • db_ddladmin • db_denydatareader / db_denydatawriter • db_owner • db_securityadmin • public

11. Impersonation (Context Switching) • { EXEC | EXECUTE ] AS <context_specification> • <context_specification>::= { LOGIN | USER } = 'name' [ WITH NO REVERT ] | CALLER

12. Permissions • SELECT • INSERT • UPDATE • DELETE • EXECUTE • REFERENCES • CONTROL • ALTER • VIEW DEFINITION • TAKE OWNERSHIP

13. Grant Example • GRANT EXECUTE ON Customers.asp_submitorder TO SubmitOrdersRole

14. Security Context Switching • SELECT USER_NAME() • GO • EXECUTE AS USER = 'TestUser' • GO • SELECT USER_NAME() • GO • EXEC Customers.asp_submitorder 1, '1-2RB1-4RO', 5 • GO • SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate FROM Orders.OrderHeader SELECT OrderDetailID, OrderID, SKU, Quantity, UnitPrice, ShipDate FROM Orders.OrderDetail • GO • REVERT

15. Grant Premission Sample • GRANT SELECT ON DATABASE::SQL2008SBS TO TestLogin GO • DENY VIEW DEFINITION ON DATABASE::SQL2008SBS TO TestLogin GO

16. Security Context Switching • EXECUTE AS USER = 'TestLogin‘ • GO • SELECT * FROM sys.objects • GO • SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal, FinalShipDate FROM Orders.OrderHeader • GO • REVERT

17. .NET Assemblies Security • SAFE • EXTERNAL_ACCESS • UNSAFE

18. Encryption Types • 1-Sided – Hashs: MD2,MD4,MD5, SHA,SHA1 • 2-Sided: Symetric Keys (Best Performance), Asymetric Keys(Strong),Certificates

19. Data Encryption • Service Master Key • Database Master Key • CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

20. When Backing up • OPEN MASTER KEY DECRYPTION BY PASSWORD ='password' • BACKUP MASTER KEY TO FILE ='path_to_file' ENCRYPTION BY PASSWORD ='password‘ • RESTORE MASTER KEY FROM FILE ='path_to_file' DECRYPTION BY PASSWORD =‘pwd' ENCRYPTION BY PASSWORD =‘pwd' • CLOSE MASTER KEY

21. Hashing Algorithms • DECLARE @HashValue varchar(100) • SELECT @HashValue = 'SQL Server‘ • SELECT HashBytes('MD5', @HashValue) • SELECT @HashValue = 'SQL Server‘ • SELECT HashBytes('SHA1', @HashValue)

22. Hashing Tips • Hash Algorithms are Case-Sensitive • MD2, MD4,SHA is also available • Remember to Salt Hashs

23. Symmetric Keys • CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = RC4 ENCRYPTION BY PASSWORD = ‘pwd’ • 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 • OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = ‘pwd’ • GO

24. Encryption by Symmetric Key • 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

25. Create Self-Signed Certificate • CREATE CERTIFICATE MyCert AUTHORIZATION dbo WITH SUBJECT = 'Test certificate‘ • GO

26. Certificate Creation • CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ] { FROM <existing_keys> | <generate_new_keys> <existing_keys> ::= ASSEMBLY assembly_name | { [ EXECUTABLE ] FILE = 'path_to_file' [ WITH PRIVATE KEY ( <private_key_options> ) ] }

27. <generate_new_keys> ::= [ ENCRYPTION BY PASSWORD = 'password'] WITH SUBJECT = 'certificate_subject_name' [ ,<date_options> [,...n ] ] • <private_key_options> ::= FILE = 'path_to_private_key' [ , DECRYPTION BY PASSWORD = 'password' ] [ , ENCRYPTION BY PASSWORD = 'password' ] <date_options> ::= START_DATE = 'mm/dd/yyyy' | EXPIRY_DATE = 'mm/dd/yyyy'

28. Using Certificate for Encryption • 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'

29. Certificate Encryption/Decryption • 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