SQL: Permissions and Data Protection

Document Sample
SQL: Permissions and Data Protection Powered By Docstoc
					SQL: Permissions & Data Protection
Part 1
     http://www.LearnNowOnline.com




        Learn More @ http://www.learnnowonline.com
           Copyright © by Application Developers Training Company
Objectives
• Understand permissions in SQL Server and how
  they provide granular control over data and
  objects
• Learn how to provide a final layer of defense
  by encrypting data




           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Agenda
• Permissions
• Data Encryption
• Security Epilogue




           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Permissions
• Like a visa for visiting a foreign country
• Gives a principal some kind of access to a
  securable object
• Follow the principle of least privilege
  • Major step in securing a database




            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
Permission Types
• A few common types                                  • IMPERSONATE
  • CONTROL                                           • INSERT
  • CREATE                                            • SELECT
  • ALTER                                             • TAKE OWNERSHIP
  • ALTER ANY <objecttype>                            • UPDATE
  • DELETE                                            • VIEW DEFINITION




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Permission Statements
• Three types of statements
  • GRANT
  • REVOKE
  • DENY
• Denying permissions is powerful




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Granting Permissions
• Easiest way to grant permissions in
  Management Studio: modify user or role
• Can also modify properties of individual
  objects
  • Same effect, but tedious




            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
Roles, Permissions, and Schemas
• Fully qualified database object name:
  server.database.schema.object
• Schema




• Can assign permissions to schema
• Can set a default schema for a user
           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Default Schemas
• Schema is a container for database objects
  • Owned by a principal




• Benefit: better management of object
  ownership

           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Default Schemas for Users
• SQL Server doesn’t automatically create a
  schema with same name as user
  • Have to explicitly create it, assign ownership, and
    add objects
• Normally, you should assign a default user




            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
Default Schemas for Groups
• Default schemas for users solved a problem
• But created a problematic side effect
• Default schemas for groups solves that new
  problem




           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Execution Context
• SQL Server follows a procedure to ensure user has
  permissions to execute code
• Exception is when code owner has permissions on
  underlying objects
• Steps
  1.   Verify caller has EXECUTE permission
  2.   Check if code owner owns all underlying objects
  3.   If not, check if user has permissions
  4.   If have permissions, execute code
  5.   If doesn’t have permissions, raise error, don’t execute

               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
Ownership Chaining
• Owner of code owns underlying objects
• If not: broken ownership chain
• Generally easier to write code with unbroken
  ownership chains
• Now can change the security execution context
  of code



           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
Changing the Execution Context
• Don’t always want caller’s permissions used to
  validate permissions in broken ownership chain
• Can change in any code except inline table-valued
  function
• Options
  • EXECUTE AS CALLER
  • EXECUTE AS <user>/<login>
  • EXECUTE AS SELF
  • EXECUTE AS OWNER
• Must have IMPERSONATE permission
             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Metadata Security
• Earlier versions of SQL Server made it easy for
  an attacker to explore structure of database
  • Just needed any access to database
• Metadata is as secure as data is




            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
Agenda
• Permissions
• Data Encryption
• Security Epilogue




           Learn More @ http://www.learnnowonline.com
              Copyright © by Application Developers Training Company
End of Part 1

    http://www.LearnNowOnline.com




       Learn More @ http://www.learnnowonline.com
          Copyright © by Application Developers Training Company

				
DOCUMENT INFO
Description: Understand permissions in SQL Server and how they provide granular control over data and objects and earn how to provide a final layer of defense by encrypting data.