Sql Server Security

Sql Server Security

• 1.
  SQL Server 2005 Security Vinod Kumar M Technology Evangelist Microsoft Corporation www.ExtremeExperts.com
• 2.
  Agenda SQL ServerSecurity Model Authentication Architecture User-Schema Separation Cryptography Support Authorization Row-Level Security Module Execution Context Demo
• 3.
  SQL Server SecurityModel Network connection request/pre-login handshake Login authentication request to SQL Server Switch to a database and authorize access Attempt to perform some action Establish login credentials Connect to the SQL Server computer Verify permissions for all actions within a database Establish a database context
• 4.
  SQL Server AuthenticationMechanism SQL Server Client App MDAC Client 1. Connect 2. Establish Socket 6. Acknowledgement 3. Hello 5. Login Packet (Name + Password) 4. Protocol Negotiate Sent in Clear in Microsoft® SQL Server 2000 Can Specify Secure Sockets Layer (SSL) and/or (5+ is encrypted) Mutual Auth (Client cert-store looked) Protocol Changes in SQL 2005 Server authorizes access Policy Changes in SQL 2005
• 5.
  Windows Authentication MechanismServer authorizes access SQL Server Client App MDAC Client 1. Connect 2. Establish Socket 10. Acknowledgement 3. Hello 4. Protocol Negotiate Local LSA Local LSA DC LSA 5. Initial Security Ctxt 8. Acpt Security Ctxt 6. Cred. Info. Info. 9. Cred. 7. Credential Info. Protocol Changes in SQL 2005 Policy Changes in SQL 2005
• 6.
  Unified Users andSchema – A Problem User Database Object Owned By User 2 Drop user may require application change!! Table View Stored Proc Function Name resolution Eg: Select * from Foo User.foo Dbo.foo
• 7.
  User-Schema Separation – The Solution User Database Object Schema contained in Owned by Owned By User 2 Owned by Default Schema User1 Default Schema S1 User2 User3 Drop user does NOT require application change!! Table View Stored Proc Function Name Resolution Select * from foo S1. foo Dbo.foo
• 8.
  User-Schema Separation Databasecan contain multiple schemas Each schema has an owning principal – user or role Each user has a default schema for name resolution Most database objects live in schemas Object creation inside schema requires CREATE permission and ALTER or CONTROL permission on the schema Ownership chaining is still based on owners not schemas Owns Has default schema Owns Owns Schema3 Database Example: creation of table in schema requires CREATE TABLE permission and ownership of schema or ALTER or CONTROL on schema Role1 User1 Approle1 Schema1 Schema2 SP1 Fn1 Tab1
• 9.
  Cryptography Support OverviewSet of built-ins for encryption , decryption , signing and verification Key management infrastructure Keys managed by SQL Server Keys managed by end-user All keys are always stored encrypted Key Types Supported Symmetric Keys RC4, RC2, DES Family, AES Family Asymmetric Keys Rivest-Shamir-Adelman Encryption (RSA) Certificates Base set of functionality needed for applications Sample scripts for column level encryption
• 10.
  Encryption Hierarchy
• 11.
  Authorization Terminology
• 12.
  Authorization Model -Permissions New permissions for finer grained control Permissions associated with semantics Not with statements Permissions can imply others Example: CONTROL It implies all other permissions Four states of permissions Grant (+) Deny (-) Revoke (take away) - + Deny Deny Revoke [deny] Revoke Grant Grant
• 13.
  Permission Implications DatabaseEndpoint Schema Table Control Control Connect Control Control Alter Alter Control Select Select Alter Select EXECUTE at database Level means you can Execute any procedure CONTROL at Schema Level means you can Do anything in schema
• 14.
  Row-level Security Todaywe have permissions at table and column level SQL 2005: Finer-grained access control at the row level
• 15.
  What if thereare multiple predicates? The user query is augmented as follows… All GRANTS are Or’d. Negatives of all DENY’s are OR’d The two sets are AND’ed For SELECT Only GRANTs and DENY’s of SELECT considered For UPDATE GRANTSs and DENY’s of UPDATEs and SELECTs considered Remember the update restrictions are based on the pre-image…not what is being updated to. For DELETE GRANTs and DENYs of DELETEs and SELECTs are considered
• 16.
  Module Execution ContextAbility to choose execution context of modules Module: Stored procs, functions, assemblies Permissions checked against current execution context Ownership chaining rules still apply Option available for dynamic SQL as well Alternative to the absence of ownership chaining Execution context maintained in the sys.sql_modules catalog view
• 17.
  Execution Context User3 Select Perms checked for User3 Execute Perms checked for User3 User1.Proc1 User1.T1 Execute Perms checked for User3 NO Perms checked for User3 User2.Proc1 User1.T1 ‘ Execute AS ‘X’ ’ Execute Perms checked for User3 Select Perms checked for ‘X’. Not for user3 SQL 2005 SQL 2000 User 3 User2.Proc1 User1.T1
• 18.
  Impersonation Implicit (fourtypes) Execute as Caller Execute under the caller’s context No extra permissions needed Default behavior like SQL 2000 Execute as Principal Execute under the specified context Impersonate on Principal Syntax: execute as ‘domain\user’ Execute as Owner Execute under the module owner’s context Impersonate on Owner Syntax: execute as owner Execute as Self Run under the context that is creating/modifying the module Syntax: execute as self
• 19.
  Demo …
• 20.
  Questions ?