1 / 25

Module 11 Authorizing Users to Access Resources

Module 11 Authorizing Users to Access Resources. Module Overview. Authorizing User Access to Objects Authorizing Users to Execute Code Configuring Permissions at the Schema Level. Lesson 1: Authorizing User Access to Objects. What Are Principals? What Are Securables ?

neil
Download Presentation

Module 11 Authorizing Users to Access Resources

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. Module 11 Authorizing Users to Access Resources

  2. Module Overview • Authorizing User Access to Objects • Authorizing Users to Execute Code • Configuring Permissions at the Schema Level

  3. Lesson 1: Authorizing User Access to Objects • What Are Principals? • What Are Securables? • GRANT, REVOKE, DENY • Securing Tables and Views • Column-level Security • WITH GRANT Option • Demonstration 1A: Authorizing User Access to Objects

  4. What Are Principals? Securables Permissions Principals Windows Group Domain User Account Local User Account Windows SQL Server Login Server Role SQL Server User Database Role Application Role Database

  5. What Are Securables? • Resources that can be secured • Securables are contained within scopes • Server • Database • Schema

  6. GRANT, REVOKE, DENY • GRANT is used to assign a permission • DENY is used to explicitly deny a permission • Used where permissions inherited through group or role membership • Should only be used in exceptional circumstances • REVOKE removes either a GRANT or a DENY

  7. Securing Tables and Views • Several object permissions apply to tables and views • SELECT • INSERT, UPDATE, DELETE • REFERENCES USEMarketDev; GO GRANTSELECTONOBJECT::Marketing.Salesperson TOHRApp; GO GRANTSELECTONMarketing.Salesperson TOHRApp; GO

  8. Column-level Security • Permissions can be assigned at the column level • Multiple column permissions can be assigned in a single statement • A column-level GRANT overrides a table-level DENY GRANTSELECTONMarketing.Salesperson (SalespersonID,EmailAlias) TO James; GO DENYSELECTONMarketing.Salesperson TOHolly; GO GRANTSELECTONMarketing.Salesperson (SalespersonID,FirstName,LastName) TO Holly; GO

  9. WITH GRANT Option • Permissions granted with the WITH GRANT OPTION can be granted to other principals by the grantee • CASCADE is used to also revoke permissions granted by the grantee • Can apply to DENY also GRANTUPDATEONMarketing.Salesperson TO James WITHGRANTOPTION; GO REVOKEUPDATEONMarketing.Salesperson FROM James CASCADE; GO

  10. Demonstration 1A: Authorizing User Access to Objects • In this demonstration, you will see: • How to view the complete list of server principals • How to view the complete list of database principals • How to grant permissions on a table • How to grant permissions at the column level

  11. Lesson 2: Authorizing Users to Execute Code • Securing Stored Procedures • Securing User-defined Functions • Securing Managed Code • Managing Ownership Chains • Demonstration 2A: Authorizing Users to Execute Code

  12. Securing Stored Procedures • Stored procedures require: • EXECUTE permission before they can be called • ALTER permission for modification • VIEW DEFINITION for documentation access USEMarketDev; GO GRANTEXECUTEONReports.GetProductColors TO Mod11User; GO

  13. Securing User-defined Functions • Users require EXECUTE permission before using scalar UDFs • Users require SELECT permission for TVFs • REFERENCES permission is used for CHECK constraints, DEFAULT values or computed columns GRANTEXECUTEONdbo.FormatPhoneNumber TOpublic; GO

  14. Securing Managed Code • SQL CLR based code has additional permission requirements above those required for T-SQL code • CLR assemblies are registered with one of three permission sets: • SAFE (the default) • EXTERNAL_ACCESS • UNSAFE • EXTERNAL_ACCESS and UNSAFE permission sets require additional configuration on the database

  15. Managing Ownership Chains

  16. Demonstration 2A: Authorizing Users to Execute Code • In this demonstration you will see: • How to assign permission to execute stored procedures • How to assign permissions for executing functions

  17. Lesson 3: Configuring Permissions at the Schema Level • Overview of User-schema Separation • Object Name Resolution • Granting Permissions at the Schema Level • Demonstration 3A: Configuring Permissions at the Schema Level

  18. Overview of User-schema Separation • Schemas • Concept changed in SQL Server 2005 • No longer equivalent to database users • Containers for database objects • Created via CREATE SCHEMA • Listed by querying sys.schemas view • Users have default schemas • Built-in Schemas • dbo • guest • sys • INFORMATION_SCHEMA

  19. Object Name Resolution • If the schema name is omitted, rules apply to how the name will be resolved • Each user has a default schema (does not apply to Windows groups) • Users with no defined default schema will have dbo as their default schema • First search is in the user's default schema • If not found, the dbo schema is searched also • Whenever referencing an object in a statement, users should specify both the schema and the object name • SELECT ProductID FROM Production.Product

  20. Granting Permissions at the Schema Level • Instead of assigning individual permissions on tables, views, stored procedures, etc. permissions can be granted at the schema level • Applicable to all relevant objects within the schema • Easier to manage USEMarketDev; GO GRANTEXECUTE ONSCHEMA::Marketing TO Mod11User; GO GRANTSELECT ONSCHEMA::DirectMarketing TO Mod11User; GO

  21. Demonstration 3A: Configuring Permissions at the Schema Level • In this demonstration, you will see how to: • Revoke permissions on a stored procedure • Assign EXECUTE permission at the schema level • Assign SELECT permission at the schema level • Explore covering or implied permissions.

  22. Lab 11: Authorizing Users to Access Resources • Exercise 1: Assign Schema-level Permissions • Exercise 2: Assign Object-level Permissions • Challenge Exercise 3: Test Permissions (Only if time permits) Logon information Estimated time: 45minutes

  23. Lab Scenario You have created the SQL Server logins and Database users and assigned them to appropriate roles. You now need to grant permissions to the database users and roles so that users can access the resources they need within the MarketDev database, based on the supplied security requirements.

  24. Lab Review • What makes fixed database roles of limited usefulness for most practical security architectures? • When should permissions be assigned directly to a user?

  25. Module Review and Takeaways • Review Questions • Best Practices

More Related