Think very carefully before implementing a flexible solution merely for the sake of flexibility. Does it really need to be that flexible? Will the functionality really be exploited in full right away, or can it be slowly extended later as required?
Maintainability As an application ages and goes through revisions, modules and routines will require maintenance in the form of enhancements and bug fixes. The issues that make routines more or less maintainable are similar to those that influence testability, with a few twists. For determining the level of maintainability, we are also concerned with exposed interfaces, but for slightly different reasons.
From a maintainability point of view, the most important interface issue is coupling. Tightly coupled routines tend to carry a higher maintenance cost, as any changes have to be propagated to multiple routines instead of being made in a single place. The issue of maintainability also goes beyond the interface into the actual implementation. A routine may have a stable, simple interface, yet have a convoluted, undocumented implementation that is difficult to work with.
Generally speaking, the more lines of code in a routine, the more difficult maintenance becomes; but since large routines may also be a sign of a cohesion problem, such an issue should be caught early in the design process if developers are paying attention. On one hand, flexibility of an interface can increase coupling between routines by requiring the caller to have too much knowledge of parameter combinations, overrideable options, and the like.
On the other hand, routines with flexible interfaces can sometimes be more easily maintained, at least at the beginning of a project.
In some cases, making routines as generic as possible can result in less total routines needed by a system, and therefore less code to maintain. However, as features are added, the ease with which these generic routines can be modified tends to break down due to the increased complexity that each new option or parameter brings.
Oftentimes, therefore, it may be advantageous early in a project to aim for some flexibility, then refactor later when maintainability begins to suffer. Maintainability is also tied in with testability in one other key way: the better a routine can be tested, the easier it will be to modify. Breaking changes are not as much of an issue when tests exist that can quickly validate new approaches to implementation. Security In an age in which identity theft makes the news almost nightly and a computer left open on the Internet will be compromised within 30 seconds, it is little wonder that security is considered one of the most important areas when developing software applications.
Security is, however, also one of the most complex areas, and complexity can hide flaws that a trained attacker can easily exploit. Complex security schemes can also have a huge impact on whether a given piece of software is testable and maintainable. From a testing standpoint, a developer needs to consider whether a given security scheme will create too many variables to make testing feasible. For instance, if users are divided into groups and each group has distinct security privileges, should each set of tests be run for each group of users?
From a maintenance point of view, complexity from a security standpoint is equally as dangerous as complexity of any other type of implementation. The more complex a given routine is, the more difficult and, therefore, more expensive it will be to maintain. In a data-dependent application, much of the security responsibility will generally get pushed into the data tier.
The security responsibilities of the data tier or database will generally include areas such as authentication to the application, authorization to view data, and availability of data. Encapsulating these security responsibilities in database routines can be a win from an overall application maintainability perspective, but care must be taken to ensure that the database routines do not become so bogged down that their maintainability, testability, or performance suffer.
Fast food, fast cars, and instant gratification of all types are well engrained into our overall mindset. And that need for speed certainly applies to the world of database development. It sometimes feels as though users would rather have any data as fast as possible, than the correct data a bit slower. Building a truly high-performance application often involves sacrifice.
Functionality might have to be trimmed less work for the application to do means it will be faster , security might have to be reduced less authorization cycles means less work , or inefficient code might have to be rewritten in arcane, unmaintainable ways in order to squeeze every last CPU cycle out of the server.
So how do we balance this need for extreme performance—which many seem to care about to the exclusion of all else—with the need for development best practices?
Unfortunately, the answer is that sometimes, we can only do as well as we can do. The user just wants the software to work fast—and we have no choice but to deliver. Keeping a close watch on issues of coupling, cohesion, and proper encapsulation throughout the development cycle can not only reduce the incidence of performance problems, but will also make fixing most of them a whole lot easier. But this approach generally backfires, producing software full of maintenance baggage.
These pieces of code must be carried around by the development team and kept up to date in order to compile the application, but often go totally unused for years at a time. In one year-old application I worked on, the initial development team had been especially active in prepopulating the code base with features reserved for the future.
Alas, several years, a few rounds of layoffs, and lots of staff turnovers later and no members of the original team were left. The developers who worked on the two million—line application were afraid of removing anything lest it would break some long-forgotten feature that some user still counted on.
Adding new features tomorrow should always be a secondary concern to delivering a robust, working product today. Summary Applications depend upon databases for much more than mere data persistence, and database developers must have an understanding of the entire application development process in order to create truly effective database systems.
By understanding architectural concepts such as coupling, cohesion, and encapsulation, database developers can define modular data interfaces that allow for great gains in ongoing maintenance and testing. Database developers must also understand how best to map data from object-oriented systems into database structures, in order to effectively be able to both persist and manipulate the data. This chapter is merely an introduction to these ideas. The concepts presented here will be revisited in various examples throughout the remainder of the book.
Is it the ability to quickly code complex routines? The ability to implement business requirements correctly, within budget, and on schedule? The hallmark of a truly great developer, and what allows these qualities to shine through, is a thorough understanding of the importance of testing. By creating unit tests early on in the development process, developers can continuously validate interfaces and test for exceptions and regressions. Carefully designed functional tests ensure compliance with business requirements.
And performance testing—the kind of testing that always seems to get the most attention—can be used to find out whether the application can actually handle the anticipated amount of traffic. There is no good reason that database developers should not write just as many—or more— tests than their application developer counterparts.
It makes little sense to test a data-dependent application without validating the data pieces that drive the application components! This chapter serves as a brief introduction to the world of software testing and how testing techniques can be applied in database development scenarios. Keep in mind that some of this material may not apply to more broadly focused testing scenarios. Introduction to Black Box and White Box Testing A number of testing methodologies are defined within the world of quality assurance, but by and large the types of tests that can be done can be split into two groups.
Black box testing refers to tests that make assumptions only about inputs and outputs, and as such do not validate intermediate conditions.
White box testing, on the other hand, includes any test in which the internal implementation of the routine or function being tested is known and validated by the tester. The black box variety includes unit tests, most types of functional and security tests, and basic performance testing.
As testing progresses once issues are identified, testing get more pinpointed, and the tests tend to shift from black box to white box. From a database development perspective, if data access is properly encapsulated, virtually all tests necessary to thoroughly analyze a database will be black box tests. The only exceptions to this will be times when data validation is necessary, or when performance tuning requires thorough knowledge of the access methods.
For instance, retrieving and reviewing query plans during a performance test is an example of white box testing against a stored procedure.
Unit tests are black box tests that verify the contracts exposed by interfaces. For instance, a unit test of a stored procedure should validate that given a certain set of inputs, the stored procedure returns the correct set of output results, as defined by the interface of the stored procedure being tested.
It means correct only insofar as what is defined as the contract for the stored procedure; the actual data returned is not important. Phrased another way, unit tests test the ability of interfaces to communicate with the outside world exactly as their contracts say they will.
On the other hand, just as its name implies, a functional test verifies the functionality of whatever is being tested. But at the database layer, this can mean only one thing: is the stored procedure returning the correct data? The logic required for this kind of validation means that a functional test is a white box test in the database world, compared to the black box of unit testing. The output of the GetAggregateTransactionHistory stored procedure will be the same whether you pass in a valid customer ID for a customer that happens to have had no transactions, or an invalid customer ID.
Either way, the procedure will return no rows. Depending on the requirements of a particular situation, it might make sense to make the interface richer by changing the rules a bit, only returning no rows if an invalid customer ID is passed in.
That way, the caller will be able to identify invalid data and give the user an appropriate error message rather than implying that the nonexistent customer made no transactions. A customer ID should be passed in, and the unit test should interrogate the output result set or lack thereof and ensure that there are two columns of the correct name and data type and zero or one rows.
No verification of data is necessary; it would be out of scope, for instance, to find out whether the aggregate information was valid or not—that would be the job of a functional test. The reason that we draw such a distinction between unit tests and functional tests is that when testing pure interface compliance, we want to put ourselves in the position of someone programming against the interface from a higher layer.
Is the interface working as documented, providing the appropriate level of encapsulation and returning data in the correct format?
Programming full white box tests against every interface may not be feasible, and it might be simpler to test the validity of data at a higher layer, such as via the user interface itself. In the case of the GetAggregateTransactionHistory stored procedure, writing a functional test would essentially entail rewriting the entire stored procedure again—hardly a good use of developer time. Unit Testing Frameworks Unit testing is made easier through the use of unit testing frameworks, which provide structured programming interfaces designed to assist with quickly testing software.
These frameworks generally make use of debug assertions, which allow the developer to specify what conditions make a test true or false. A debug assertion is a special kind of macro that is turned on only when a piece of software is compiled in debug mode. It accepts an expression as input and throws an exception if the expression is false; otherwise, it returns true or void, in some languages.
If a routine expects that a variable is in a certain state at a certain time, an assertion can be used in order to help make sure that assumption is enforced as the code matures. If, at any time in the future, a change in the code invalidates that assumption, an exception will be thrown should the developer making the change hit the assertion during testing or debugging.
They allow the tester to control what conditions make the unit test return true or false. If any assertion throws an exception in a unit test, the entire test is considered to have failed. Unit testing frameworks exist for virtually every language and platform, including T-SQL. Personally, I find unit testing in T-SQL to be cumbersome compared to other languages, and prefer to write my tests in a. NET language using the. NET unit testing framework, NUnit. Given that unit testing stored procedures is still somewhat of a mystery to many developers, I will provide a few hints.
When writing stored procedure unit tests in NUnit, the following basic steps can be followed: 1. What are the result sets that will be returned? What are the data types of the columns, and how many columns will there be? Does the contract make any guarantees about a certain number of rows? Next, write code using ADO. NET to execute the stored procedure. I find that using the stored procedure to fill a DataSet is generally the easiest way of exposing its output for interrogation.
Be careful at this stage; you want to test the stored procedure, not your ADO. NET data access framework. You might be tempted at this point to call the stored procedure using the same method that the application uses to call it.
However, this would be a mistake, as you would end up testing both the stored procedure and that method. That means one assertion per column name, one per column data type, one for the row count if necessary, etc. StoredProcedure; comm. Fail "Exception occurred! IsTrue ds. IsTrue dt. Each time through the loop, the code is tried. Otherwise, execution jumps to the CATCH block, where a check is made to ensure that the error number is deadlock victim.
If so, the counter is decremented so that the loop can be tried again. If the exception is not a deadlock, another exception is thrown so that the caller knows that something went wrong.
However, I hope that all readers keep in mind that exception handling is no substitute for proper checking of error conditions before they occur. Whenever possible, code defensively.
Proactively look for problems, and if they can be both detected and handled, code around them. If you can predict a condition and write a code path to handle it during development, that will usually provide a much more robust solution than trying to trap the exception once it occurs and handle it then. SQL Server is a database management system, and as such one of the main goals is management and manipulation of data.
The Myths of Transaction Abortion The biggest mistake that some developers make is the assumption that if an exception occurs during a transaction, that transaction will be aborted. By default, that is almost never the case. ActiveTransactionCount 1 row s affected Another mistake is the belief that stored procedures represent some sort of atomic unit of work, complete with their own implicit transaction that will get rolled back in case of an exception.
The statement has been terminated. SomeColumn 1 row s affected Even if an explicit transaction is begun in the stored procedure before the inserts and committed after the exception occurs, this example will still return the same output. By default, unless a rollback is explicitly issued, in most cases an exception will not roll anything back. It will simply serve as a message that something went wrong. In addition to making exceptions act like batch-level exceptions, the setting also causes any active transactions to immediately roll back in the event of an exception.
I recommend turning this setting on in any stored procedure that uses an explicit transaction, in order to guarantee that it will get rolled back in case of an exception. Roll back the transaction.
Should a transaction enter this state, any attempt to either commit the transaction or roll forward do more work will result in the same exception. This exception will keep getting thrown until the transaction is rolled back.
This function returns 0 if there are no active transactions, 1 if the transaction is in a state in which more work can be done, and -1 if the transaction is doomed. A solid understanding of how exceptions behave within SQL Server makes working with them much easier. Especially important is the difference between statement-level and batchlevel exceptions, and the implications of exceptions that are thrown within transactions.
Overuse can make detection and debugging of problems exceedingly difficult. Errors and exceptions will always occur, but by thinking carefully about how to handle them, they can be dealt with easily and effectively. By and large, data security can be broken into two areas: authentication is the act of verifying the identity of a user to a system that controls resources, and authorization is the act of giving the user access to those resources. These two realms can be delegated separately in many cases; as long as the authentication piece works properly, the user can be handed off to authorization mechanisms for the remainder of a session.
SQL Server authentication is a big topic, with a diverse range of subtopics including network security, operating system security, and so-called surface area control over the server. This is an area that production DBAs must be especially concerned with but that developers can mostly ignore. Developers need to be much more concerned with what happens after authentication: that is, how the user is authorized for data access and how data is protected from unauthorized users.
This chapter introduces some of the issues of data privilege and authorization in SQL Server, from a development point of view. Included here is an initial discussion on privileges and general guidelines and practices for securing data using SQL Server permissions. For more information on security, readers should refer to Chapter 5, which discusses encryption. Note that although authentication issues are generally ignored in these pages, you should try to not completely disregard them in your day-to-day development work.
Development environments tend to be set up with very lax security in order to keep things simple, but a solid development process should include a testing phase during which full authentication restrictions are applied.
Unfortunately, in practice this is more of an ideal goal than an actual prescription for data security; many systems do not allow privilege to be easily escalated dynamically, and Windows-based solutions have not historically been engineered to use escalation of privilege as a means by which to gain additional access at run time. In many non-Windows operating systems that were originally designed for multiuser access, it has long been possible to impersonate other users when access to a resource owned by that user is required.
It is important to note that impersonation is slightly different than reauthentication; instead of logging out and resending credentials, thereby stopping whatever is running, impersonation allows a process to temporarily escalate its privileges, taking on the rights held by the impersonated principal.
Windows systems can handle some degree of impersonation as well, but Microsoft has only recently provided APIs—such as the. Granting permission to a resource means adding a user to the list, after which the user can access the resource again and again, even after logging in and out of the system. This kind of access control provides no additional security if, for instance, an attacker takes over an account in the system.
By taking control of an account, the attacker automatically has full access to every resource that the account has permission to access.
By controlling access with impersonation, the user is required to effectively request access to the resource dynamically, each time access is required. In addition, rights to the resource will only be maintained during the course of impersonation.
Once the user reverts i. In effect, this means that if an account is compromised, the attacker will have to also compromise the impersonation context in order to gain access to more secure resources. The idea of security through least privilege involves creating users with few or no permissions, and allowing them to briefly escalate their privileges when greater access is required.
This is generally implemented using proxies, users or other security principals that have access to a resource but cannot be authenticated externally.
Use of low-privileged external users complemented by higher-privileged proxy users provides a buffer against attack due to the fact that the only accounts that an attacker can directly compromise from the outside have no permissions. Accessing more valuable resources requires additional work on the part of the attacker, giving you that much more of a chance to detect problems before they occur.
Creating Proxies in SQL Server SQL Server allows creation of both server-level principals logins that cannot log in, and database-level principals users that are not associated with a login. It is only possible to Xch Certificates are covered in depth in Chapter 5, but for now think of a certificate as a trusted way to verify the identity of a principal without a password.
The following syntax can be used to create a certificate in master. Note that before a certificate can be created in any database, a master key must be created.
Again, see Chapter 5. However, to use the permissions, it must be mapped to a database user. Another type of proxy principal that can be created is a database user not associated with a server login. However, it is impossible to log in to the server and authenticate as Bob. Instead, you must log in using a valid server-level login and authenticate to the database with whatever database user is associated with your login. Only then can you impersonate Bob, taking on whatever permissions the user is assigned.
Data Security in Layers: The Onion Model Generally speaking, the more levels an attacker must penetrate in order to access a valuable resource, the better the chance is that an attack will not be successful. Developers should strive to carefully construct multiple layers of protection for any sensitive data, in order to ensure that if one security measure is breached, other obstacles will keep an attacker at bay.
The first layer of defense is everything outside of the database server, all of which falls into the realm of authentication. Another layer that can be added for additional security here is use of stored procedures.
By assigning permissions only via stored procedures, it is possible to maintain greater control over when and why escalation should take place—but more on that will be covered later in this chapter.
Of course, the stored procedure itself must have access to whatever tables and columns are required, and these resources can be further locked down if necessary, using encryption or row-level security schemes. Figure shows some of the layers that should be considered when defining a SQL Server security scheme, in order to secure the sensitive data as well as possible.
The remainder of this chapter deals primarily with how best to control access to resources using stored procedures as the primary access layer into the data once a user is authenticated. Figure Layering security provides multiple levels of protection against attack.
A stored procedure layer provides an ideal layer of indirection between the data and the data access, allowing for additional security to be programmed in via parameters or other inline logic. For instance, it is trivial to log every access to sensitive data via a stored procedure, by including logging code in the procedure.
Likewise, a stored procedure might be used to force users to access data on a granular basis, by requiring parameters that are used as predicates to filter data. These security checks are difficult or impossible to force on callers without using stored procedures to encapsulate the data access logic.
Data Organization Using Schemas In versions of SQL Server prior to , all database objects were both owned by database users and referenced by owner name. So to select from a table that was not owned by dbo, the table name would have to be prefixed with the name of the database user that owned it unless you were logged in as that user. In addition to owner name prefixing, other difficult security scenarios were also created.
For instance, there was no easy way to assign permissions to all of the tables owned by a certain user, meaning that ownership did not work as a method by which to easily logically segment tables into groups. Schemas are essentially containers into which any database object can be placed, and security Xch By dividing your database into schemas, you can easily group related objects and control permissions, without having to worry about what objects might be added or removed over time.
As new objects are added to a schema, existing permissions propagate, thereby allowing you to set up access rights for a given schema once, and not have to manipulate them again as the database changes. If an owner is not specified, the user that creates the schema will be automatically used by SQL Server.
Remember, owner names are never used to qualify objects, as of SQL Server The beauty of schemas becomes obvious when it is time to apply permissions to the objects in the schema.
Assuming that each object is equivalent from a permissions point of view, only a single grant is necessary to give a user access to every object within a schema. The individual object owners can be changed later, but in most cases I recommend that you keep everything in any given schema owned by the same user. This is especially important for ownership chaining, covered later in this chapter.
A new 77 Xch The AdventureWorks sample database that ships with SQL Server makes great use of schemas, and I highly recommend that you take a look at what Microsoft has done in that example and try to design new databases along similar lines.
Legacy database applications that use multiple databases in order to create logical boundaries between objects might also benefit from schemas. The multiple databases can be consolidated to a single database that uses schemas. The benefit is that the same logical boundaries will exist, but because the objects are in the same database, they can participate in declarative referential integrity and can be backed up together. This command can be used by any user, and access is controlled by a permissions setting rather than a fixed role.
This means that it is impossible to encapsulate impersonation within a stored procedure using SETUSER and guarantee that the caller will not be able to take control of the impersonated credentials. For example, the user can alter the table, since it owns it.
If you have impersonated another user inside of another context i. To see the effects of nested impersonation, create a second user. You will lose any permissions that the outer user has that the impersonated user does not have, in addition to gaining any permissions that the impersonated user has that the outer user lacks.
For logging purposes, it is sometimes important to record the actual logged in principal. Each of the privilege escalation examples that follow use stored procedures to show the functionality. However, please be aware that these methods work for any kind of module that SQL Server supports. A module is defined as any kind of code container that can be created inside of SQL Server: a stored procedure, view, user-defined function, trigger, or CLR assembly.
Ownership Chaining The most common method of securing SQL Server resources is to deny database users any direct access to SQL Server resources and provide access only via stored procedures or views. If a database user has access to execute a stored procedure, and the stored procedure is owned by the same database user that owns a resource being referenced within the stored procedure, the user executing the stored procedure will be given access to the resource, via the stored procedure.
This is called an ownership chain. This option is one way of creating the kind of proxy users mentioned previously. However, this only works because Louis owns both tables, and both are in the same database; if either of those conditions were not true, the ownership chain would break, and Hugo would have to be authorized another way to select from the table. The ownership chain would also fail if the execution context changed within the stored procedure.
In the case of a stored procedure in one database requesting access to an object in another database, it is possible to maintain an ownership chain, but it gets quite a bit more complex, and security is much more difficult to maintain. That property tells SQL Server that either database can participate in a cross-database ownership chain, either as source or target—but there is no way to control the direction of the chain, so setting the option could open up security holes inadvertently.
Doing so will result in a more secure, more flexible solution. For example, moving databases to separate servers is much easier if they do not depend on one another for authentication.
In addition, with the inclusion of schemas in SQL Server , splitting objects into multiple databases is no longer as important as it once was. Consider avoiding multiple databases altogether, if at all possible. Privilege Escalation Without Ownership Chains Ownership chaining will not work if the object owner does not match the module owner, or if dynamic SQL is used.
Using the EXECUTE AS clause with stored procedures is an easy and effective method of escalating permissions, but is not nearly as flexible as what can be done using certificates. With certificates, permissions are additive rather than impersonated—the additional permissions provided by the certificate add to, rather than replace, the permissions of the calling principal.
The examples in this section only focus on stored procedures, but the same principles apply to the other object types. SensitiveData END When this stored procedure is executed by a user, all operations within the procedure will be evaluated as if they are being run by the Louis user rather than by the calling user as is the default behavior.
This includes any dynamic SQL operations, or manipulation of data in tables that the Louis user has access to. When the stored procedure has completed execution, context will be automatically reverted back to that of the caller. A stored procedure might need to be created that accesses both tables, but using ownership chaining will not work; if the procedure is owned by Kevin, that user would need to be given access to HilarysData in order to select from that table.
Likewise for Hilary and the KevinsData table. One solution in this case is to combine EXECUTE AS with ownership chaining and create a stored procedure that is owned by one of the users, but executes under the context of the other.
But because the stored procedure is executing under the context of the Kevin user, permissions will also cascade for the KevinsData table. In this way, both permission sets can be used, combined within a single module. For more complex permissions scenarios, it is necessary to resort to signing stored procedures using certificates.
Stored Procedure Signing Using Certificates As mentioned previously in the chapter, proxy logins and users can be created based on certificates. Creating a certificate-based proxy is by far the most flexible way of applying permissions using a stored procedure, as the permissions are additive.
One or more certificates can be used to sign a stored procedure, and each certificate will apply its permissions to the others already present, rather than replacing the permissions as happens when impersonation is done using EXECUTE AS. But a side effect of having created the user based on a certificate is that the certificate itself can also be used to propagate permissions granted to the user.
This is where stored procedure signing comes into play. This can be done by signing the procedure using the same certificate that was used to create the Greg user. The flexibility of certificate signing becomes apparent when you consider that you can sign a given stored procedure with any number of certificates, each of which can be associated with different users and therefore different permission sets. This means that even in an incredibly Xch Keep in mind when working with certificates that any time the stored procedure is altered, all signatures will be automatically revoked by SQL Server.
Therefore, it is important to keep signatures scripted with stored procedures, such that when the procedure is modified, the permissions can be easily kept in sync. It is also important to know how to find out which certificates, and therefore which users, are associated with a given stored procedure. The sys. Each certificate has a byte cryptographic hash, its thumbprint, which is used to find out which certificate was used to sign the module, via the sys.
Finally, each database principal has a security identifier, the final 32 bytes of which is the thumbprint if the principal was created from a certificate. Date Published:.
File Size:. System Requirements Supported Operating System. Install Instructions Click the Download button on this page to start the download, or choose a different language from the drop-down list and click Go.
Do one of the following: To start the installation immediately, click Open or Run this program from its current location. To copy the download to your computer for installation at a later time, click Save or Save this program to disk.
For example, there's no reason you can't architect and test database routines just as you would architect and test application code. And nothing should stop you from implementing the types of exception handling and security rules that are considered so important in other tiers, even if they are usually ignored in the database.
You'll learn how to apply development methodologies like these to produce high—quality encryption and SQLCLR solutions. Furthermore, you'll discover how to exploit a variety of tools that SQL Server offers in order to properly use dynamic SQL and to improve concurrency in your applications. Finally, you'll become well versed in implementing spatial and temporal database designs, as well as approaching graph and hierarchy problems.
Skip to main content Skip to table of contents. Advertisement Hide. This service is more advanced with JavaScript available. Offers a unique perspective on database application development, not just syntax but hands-on demonstration of expert best practices Provides in-depth coverage of a host of advanced database development techniques Written by an authoritative expert, for those who seek true expertise.
Front Matter Pages i-xx. Software Development Methodologies for the Database World.
0コメント