1 / 26

Course Topics

Course Topics. 05 | Security Implementation. George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United. Module 5 Overview. Server Logins and Server-level Roles Database Users and Database-level Roles

Download Presentation

Course Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Course Topics

  2. 05 | Security Implementation George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United

  3. Module 5 Overview • Server Logins and Server-level Roles • Database Users and Database-level Roles • Implementing Permissions

  4. Topic: Server Logins and Server-level Roles

  5. Topic: Server Logins and Server-level Roles • Security Scopes • Authentication Modes • Login Objects • Server Roles and Server-level Permissions

  6. Security Scopes • Server / instance • Database • Schema • Object • Scope-specific permissions exist • Permission statements • GRANT • REVOKE • DENY

  7. Authentication Modes • Two modes • SQL logins are protected better by using password policies • Set during installation and they can be changed later • Switching requires a service restart • Functionally only a registry key change

  8. Login Objects • Purpose • Derived from • Windows • Domain • Machine local • SQL logins • Administration tools • SSMS • CREATE / ALTER / DROP LOGIN • Options

  9. Server Roles and Server-level Permissions • Types of server-level roles • Fixed server roles • User-defined server roles • Server-scoped permissions • Administration tools • SSMS • sp_AddSrvRoleMember • CREATE / ALTER / DROP SERVER ROLE

  10. Implementing Server-level Security

  11. Topic: Database Users and Database-level Roles

  12. Topic: Database Users and Database-level Roles • Database Users • Database-level Roles • Application Roles • Contained Databases

  13. Database Users • Database users • Derived from logins • Types of database-level roles • Fixed • User defined • Application roles • Database-scoped permissions

  14. Database-level Roles • Types of database-level roles • Fixed • User defined • Database-scoped permissions • Administration tools • SSMS • CREATE / ALTER / DROP ROLE • sp_AddRoleMember

  15. Application Roles • The purpose of an Application Role is toprotect against making connectionsto SQL Server outside of front-end application logic (using Excel or Access, for example) • Instead an application calls an Application Rolethat the end-user is unaware of (and that is assigneddatabase permissions) • Administration tools • SSMS • CREATE / ALTER / DROP ROLE • sp_AddRoleMember • sp_SetAppRole and sp_UnSetAppRole

  16. Implementing Database-level Security

  17. Contained Databases • A problem with moving and restoring databases is that they have dependencies on login and other objects • A possible solution is a contained database • Requires a server-level and database-level option • sp_Configure N’Contained Database Authentication’ • ALTER DATABASE DBx Set Containment = Partial • Applicable principals • Windows user • Windows group • SQL login

  18. Creating Users for a Specific Database (Contained Database)

  19. Topic: Implementing Permissions

  20. Topic: Implementing Permissions • Object Permissions • Code and Module Permissions • Applying Permissions • Permission Management Strategies “Principals are assigned Permissions to Securables.”

  21. Object Permissions • Applies to tables, views and table-valued user-defined functions • Primary permissions • ALTER • CONTROL • SELECT, INSERT, UPDATE, DELETE • VIEW DEFINITION

  22. Code and Module Permissions • Applies to scalar user-defined functions and stored procedures • Primary permissions • ALTER • CONTROL • EXECUTE • VIEW DEFINITION

  23. Applying Permissions • SSMS • Begin with a principal • Begin with a securable • T-SQL statements • GRANT • REVOKE • DENY • Security model anomaly (prior to SQL Server 2012) • Column-level GRANT override Table-level DENY

  24. Permission Management Strategies • Various strategies • Aim for “double abstraction” • Leverage roles • Roles can be nested • Leverage schemas

  25. Implementing a Permission Strategy by Assigning a Role Permission to a Schema

More Related