Module 6 Designing a Strategy for Database Access

1. Module 6 Designing a Strategy for Database Access

2. Module Overview Guidelines for Designing Secure Data Access Designing Views Designing Stored Procedures Designing User-Defined Functions

3. Lesson 1: Guidelines for Designing Secure Data Access Guidelines for Designing a Secure Execution Context Guidelines for Defining a Data Access Policy Discussion: Working with Data Access Policy

4. Guidelines for Designing a Secure Execution Context Associate permissions with schemas ü Use EXECUTE AS to fine-tune permission ü Use application roles and the sp_setapprole stored procedure ü Determine the appropriate level of permission granularity ü Secure Execution Context

5. Guidelines for Defining a Data Access Policy Define the limits to direct data access Prohibit or provide limited direct access to the database Identify the mechanisms for indirect access Include the data access policies in your testing plan

6. Discussion: Working with Data Access Policy Often database developers prefer to have full access to all the data in the database so that they can manage the needs of the applications they are creating. What is the fallacy in this situation?

7. Lesson 2: Designing Views Guidelines for Designing Views Guidelines for Selecting Indexed Views Discussion: Working with Views

8. Guidelines for Designing Views You can You cannot • Use views to hide joins • Use views to hide sensitive data • Create views only in the current database • Ensure that view names are unique for each schema • Associate INSTEAD OF triggers with views • Ensure that nested views do not exceed 32 levels • Associate DEFAULT definitions with views • Associate AFTER triggers with views • Include the COMPUTE, COMPUTE BY, ORDER BY, OPTION, or TABLESAMPLE clauses • Create full-text index definitions and queries • Create views on temporary tables Designing a View

9. Guidelines for Selecting Indexed Views Compare the performance gained versus cost Use indexed views to improve performance of reporting queries Requirements for Creating an Indexed View • All functions used in the view must be deterministic • The view includes tables from the same owner in the same database • ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON • Use the SCHEMABINDING clause in the CREATE or ALTER view statements

10. List advantages and disadvantages of views. Discussion: Working with Views

11. Designing T-SQL Stored Procedures T-SQL Stored Procedures Use T-SQL stored procedures for direct access to all database tables Specify set-based operations and not cursors Specify the use of native T-SQL constructs instead of temporary tables Design T-SQL stored procedures for reusable queries Minimize recompilation risks Use table variables instead of temporary tables Use EXEC ... WITH RECOMPILE Use CREATE PROCEDURE ... WITH RECOMPILE

12. Guidelines for Selecting Stored Procedures Designing T-SQL Stored Procedures Designing CLR Stored Procedures Discussion: Using Stored Procedures Lesson 3: Designing Stored Procedures

13. Guidelines for Selecting Stored Procedures Assess the required stored procedure functionality Consider the requirements for monitoring and accessing data Test and compare common language runtime (CLR) and T-SQL solution

14. Designing CLR Stored Procedures Use CLR stored procedures for accessing external data or resources CLR Stored Procedures Use CLR stored procedures only for complex computational and string processing operations Avoid the use of direct data access or transactions Specify security for CLR procedure access Design CLR stored procedures for monitoring and performance

15. List some of the errors that may cause recompilations. Discussion: Using Stored Procedures

16. Guidelines for Selecting T-SQL or CLR User-Defined Functions Designing T-SQL User-Defined Functions Guidelines for Designing Deterministic and Nondeterministic Functions Designing CLR User-Defined Functions Considerations for Designing User-Defined Aggregate Functions Discussion: Using User-Defined Functions Lesson 4: Designing User-Defined Functions

17. Guidelines for Selecting T-SQL or CLR User-Defined Functions Use T-SQL user-defined functions by default Use CLR functions for computationally intensive processing Use CLR functions to extend built-in SQL Server T-SQL functions Use table-valued parameters to send multiple rows of data to a T-SQL statement Use CLR functions for complex string processing

18. Designing T-SQL User-Defined Functions Minimize the number of rows to which a scalar function applies Evaluate the performance impact of the table variable used with the table-valued function

19. Guidelines for Designing Deterministic and Nondeterministic Functions Guidelines Related to Determinism of a Function • Built-in function determinism • Not always deterministic functions Deterministic Function Nondeterministic Function Returns the same result when provided with the same set of input values Returns different results for the same set of input values

20. Designing CLR User-Defined Functions Use schemas to group CLR UDFs based on user permissions Group CLR UDFs based on required environment permission levels Specify the appropriate environment permission levels Hide the complexity of the .NET Framework Do not design CLR UDFs to embed middle-tier in the database Set the function attribute properties explicitly

21. Considerations for Designing User-Defined Aggregate Functions Specify user-defined aggregate functions (UDAs) to replace cursors in iterating through a result set ü Specify UDAs to extend, but not replace, T-SQL aggregate functions ü

22. Discussion: Using User-Defined Functions What is a common mistake committed when using user-defined functions?

23. Lab 6: Designing a Strategy for Database Access Exercise 1: Designing Security for Data Retrieval Objects Exercise 2: Designing Data Retrieval Objects Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

24. Lab Scenario You are the lead database designer at QuantamCorp working as part of the HR VASE project. The HR VASE project will enhance the current HR system of your organization. This system is based on the QuantamCorp sample database built on SQL Server 2008. You are asked to formulate a list of database requirements that your design must satisfy. The main goals of the project are to: • Provide managers with current and historical information about employee vacation and sick-leave data in their own department. • Provide individual employees permission to view their vacation and sick leave balances. • Provide certain employees in the HR department the permission to view all employee sick leave and vacation data. • Provide IT personnel the ability to view all employees who, while on vacation or sick leave, accessed the network (based on accessing a Microsoft Windows® event log file on another server). In this lab, you will determine which objects will be required to satisfy data access requirements, ensuring that no clients directly access HR database tables. You must then apply appropriate security measures to the data retrieval objects.

25. Module Review and Takeaways • Review Questions • Real-world Issues and Scenarios • Additional Reading Material