Sql Server Security Basics

Document Sample
Sql Server Security Basics Powered By Docstoc
					SQL Server Security Basics




       Learn More @ http://www.learnnowonline.com
          Copyright © by Application Developers Training Company
Objectives
• Understand potential data threats and how
  SQL Server’s design protects against them
• Learn about SQL Server and Windows
  integrated authentication
• See how SQL Server provides an authorization
  system to control access to data and objects



             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Agenda
• Security Overview
• Authentication
• Authorization




            Learn More @ http://www.learnnowonline.com
               Copyright © by Application Developers Training Company
Security Overview
• Relational data is a tempting target for
  attackers
• SQL Server 2008 provides plenty of features to
  secure your data and server
  • Need to understand the threats
  • Match countermeasures to the threats




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
The Threats
• Identifying threats is a critical first step
   • Type of data will probably influence security measures
• Sometimes the best way to protect data is to never
  put it in a database
• Typical threats
   • Theft of data
   • Data vandalism
   • Protecting data integrity
   • Illegal storage
• Understand threats to protect against them
               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
Security Design Philosophy
• Trustworthy Computing memo, 2002
• Four pillars of security design
   • Secure by design
   • Secure by default
   • Secure in deployment
   • Secure through communications
• “It’s just secure”
   • Implications throughout the product
   • SQL Server is reasonably secure out of the box
   • Your job is to keep it secure
               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
The Two Stages of Security
• Similar to Windows security
  • Authentication: who are you?
  • Authorization: now that we know who you are,
    what can you do?




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Key SQL Server Security Terms
 •   Authentication                        •    Permission
 •   Authorization                         •    Principal
 •   Group                                 •    Privilege
 •   Impersonation                         •    Role
 •   Login                                 •    User




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Agenda
• Security Overview
• Authentication
• Authorization




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Authentication
• Process of verifying that a principal is who or what it
  claims to be
   • SQL Server has to uniquely identify principals in order to
     authorize
• Two paths to authentication
   • Windows authentication
   • SQL Server authentication
• Authentication modes
   • Mixed Mode Authentication
   • Windows Only Authentication Mode

                 Learn More @ http://www.learnnowonline.com
                    Copyright © by Application Developers Training Company
Windows Integrated Authentication
 • SQL Server assumes a trust relationship with
   Windows Server
   • Windows does the heavy lifting for authentication
   • The SQL Server checks permissions on the principal
 • Advantages
   • Single user login
   • Auditing features
   • Simplified login management
   • Password policies
 • Changes only take effect when user connects
               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
Configuring SQL Server Security
Settings
• Select either when install or later
• Settings apply to all databases and server
  objects in an instance of SQL Server
• Changing modes after installation may or may
  not cause problems
  • Windows to Mixed
  • Mixed to Windows



             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
SQL Server Authentication
• Client applications must provide login
  credentials as part of connection string
• Logins stored in SQL Server
• Windows authentication stronger
  • But must use SQL Server authentication with old
    versions of Windows, non-Windows systems




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Windows and SQL Server Logins
• SQL Server logins are not stored in Windows
  • Disabled if you select Windows authentication
• Mixed mode is much more flexible
  • But less secure




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Beware of the sa Login
•   System administrator login
•   Mapped to sysadmin fixed server role
•   Conveys full system administrator privileges
•   Cannot modify or delete
•   Must use a strong password!
•   Use only as access of last resort
•   NEVER use sa for database access through
    client applications
             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Password Policy and Enforcement
• Before SQL Server 2005, no enforcement of
  passwords for SQL Server logins
  • No minimum strength
  • No expiration policy
• SQL Server now hooks into Windows password
  policy
  • Windows Server 2003, Vista, and later versions
  • NetValidatePasswordPolicy API method


              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Contained Databases
• Not a security feature per se
  • But introduces a new authentication scheme
• Solves problem of moving databases
  • Past: move database plus external dependencies
  • Contained databases solves associated problems




              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Contained Databases Authentication
•   Can create a SQL user with a password
•   Windows user in database
•   Not associated with a login
•   Authenticate against contained database
    • Get a token for that database only
    • Security boundary is tightly scoped
• If authentication fails at database, doesn’t fall
  back to duplicate login, if any

              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Contained Databases Authentication
 Connection                                                           Matching      Yes                             Yes
  Request                                                              user in                     Password
                                                                      database                      match?
                                                                          ?

                                            SQL Server
                                                                      No                           No

    Initial
   catalog
               Yes      Initial
                       catalog
                                      Yes          Authent-
                                                    ication
                                                                                     *
                                                                                  Authentication
                                                                                                               No          Permis-
                                                                                                                           sion in
                                                                                                                                     Yes

  specified?          contained?                     type?                           failure                              database
                                                                                                                              ?

                                                                                                   No
  No                  No                     Windows

                                                                                                   Matching
                                                                      Matching      Yes            principal        Yes
                                                                       login or                        in
                                                                       group?                      database
                                                                                                       ?

                                                                      No



                      Server-level                                                                               Database
                     authentication                                                                            authentication




                           Learn More @ http://www.learnnowonline.com
                                   Copyright © by Application Developers Training Company
Agenda
• Security Overview
• Authentication
• Authorization




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Authorization
• Principals: user or process allowed to access
  securable objects
• Securables: protected resource
• Permissions: type of access




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Principals
 • Windows-level principals
    • Windows Domain Login
    • Windows Group
    • Windows Local Login
 • SQL Server-level principals
    •   SQL Server   Login
    •   SQL Server   Login mapped to a certificate
    •   SQL Server   login mapped to a Windows login
    •   SQL Server   Login mapped to an asymmetric key
 • Database-level principals
    •   Application Role
    •   Database Role
    •   Database User
    •   Database User mapped to a certificate
    •   Database User mapped to a Windows login
    •   Database User mapped to an asymmetric key
    •   Public Role

                 Learn More @ http://www.learnnowonline.com
                       Copyright © by Application Developers Training Company
Principals
• Scope of a principal determines scope of permission
• Principal can be a login, user, or role
  • Roles are analogous to Windows groups
  • Users in role inherit role’s permissions
  • Simplify security management
• Types of roles
  • Fixed server roles
  • User-defined server roles
  • Fixed database roles
  • User-defined database roles

              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Fixed Server Roles
• Cannot alter, even to add new ones, except to add
  logins to a role
• Server roles
  •   System administrator
  •   Bulk insert administrator
  •   Database creator
  •   Disk administrator
  •   Process administrator
  •   Server administrator
  •   Setup administrator
  •   Security administrator

               Learn More @ http://www.learnnowonline.com
                  Copyright © by Application Developers Training Company
User-Defined Server Roles
• Long awaited security feature
  • Long have had user-defined database roles
  • But nothing at the server level
• Used to be, only way to grant some
  permissions was through a fixed server role
• SQL Server 2012 solves these problems



              Learn More @ http://www.learnnowonline.com
                 Copyright © by Application Developers Training Company
Fixed Database Roles
• Control authorization within a database
• Configure each database individually
• Database roles
   •   db_accessadmin
   •   db_backupoperator
   •   db_datareader
   •   db_datawriter
   •   db_ddladmin
   •   db_denydatareader
   •   db_denydatawriter
   •   db_owner
   •   db_securityadmin


                 Learn More @ http://www.learnnowonline.com
                    Copyright © by Application Developers Training Company
The Public Role
• Every database user assigned to this role
• Be very careful about granting permissions
• Normally restrict permissions for this role




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
The dbo (Database Owner) Role
• Mapped to sysadmin fixed server role
• Not related to db_owner role




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
User-Defined Database Roles
• Standard role
• Application role




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Securable Objects
• Protected resource that you can control access
  to
• Physical object or action




             Learn More @ http://www.learnnowonline.com
                Copyright © by Application Developers Training Company
Securable Objects
  Server
  Database                        Database
  Endpoint                        Application Role                               Schema
  Remote Binding                  Assembly                                       Default
  Route                           Asymmetric Key                                 Function
  Server Role                     Certificate                                    Procedure
  SQL Server Login                Database user                                  Query Stats
                                  Fixed Database                                 Queue
                                  Role                                           Rule
                                  Full-Text Catalog                              Synonym
                                  Message Type                                   Table
                                  Schema                                         Trigger
                                  Service                                        Type
                                  Service Contract                               View
                                  Symmetric Key                                  XML Schema Collection



                     Learn More @ http://www.learnnowonline.com
                        Copyright © by Application Developers Training Company
Learn More!
• This is an excerpt from a larger course. Visit
  www.learnnowonline.com for the full details!


• Learn more about about SQL Server on
  SlideShare
   A Tour of SQL Server




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

				
DOCUMENT INFO
Description: Understand potential data threats and how SQL Server’s design protects against them.