by Bill Wunder

If you're not starting to get the idea that you and I have a ton of learning in our futures if we expect to be prepared to lead the charge to SQL Server 2005 me thinks you've likely been spending a little too much time out behind the shed with (the other) Yukon Jack. In case you didn't notice, Itzik Ben-Gan's SQL Server "Yukon" Beta 1 Transact-SQL Enhancements white paper in the MSDN Library claims to be a mere 68 pages in length even though it disclaims, "This paper does not cover every new Transact-SQL feature.". And on top of  the T-SQL we have to get our brains around the wherefores and whys of managed .Net code within the SQL Sever 2005 memory space as potentially powerful additions to the T-SQL tool set for building stored procedures, functions, and User Defined Data Types, ADO.Net Data Sets, new administrative programmability powers with SMO and WMI, the intrinsic asynchronous message based interoperability of System Broker, a rich new world of XML integration in both the administrative depths and the development interoperability layers, a completely rewritten DTS and the emerging Reporting Services among the other exciting details of a dramatically evolved next generation BI Suite, web services that are truly part of the SQL Server architecture (That's right, IIS not required!), and even some giant and sweeping authorization model changes that are going to render much of our security models obsolete and profoundly vulnerable.

Whoa, what did I say? The authorization model is changing. My security model isn't good enough anymore? All the work I've been doing to ram the notion down my developers throats that all objects need to belong to dbo is obsolete. It's not really as safe as I'd been telling them all this time? I've been leading them down the garden path and now we're all about to be eaten by the dragon? Gulp!

Yep, afraid so. Turns out that an account that can access stuff in the database that belongs to dbo is probably over privileged and increases your risk to such threats as the T-SQL variation of the ubiquitous buffer overruns that are sweeping across the Internet: SQL injection. We haven't really seen a huge hacker focus on SQL injection because there remain some lower effort higher payoff vulnerabilities in the "easy pickin's" category, but in the not too distant future SQL injection will become the lowest hanging fruit and the simplified model we have pursued for so long will be something of an open doorway for the bad guys. SQL Injection is not the only concern. Even inside the organization, overloading an over privileged account carries significant risks. This is part of the reason that cross database ownership chaining was changed with SQL Server 2000's Service Pack 3.

You can find a good discussion of the new hierarchal approach to security hardening - scopes -  and the increased number of securable resources in the white paper Introduction to SQL Server "Yukon" Relational Engine Security Features. In particular notice that the paper talks about user-schema separation, that the notion of a certificate authority within SQL Server is briefly documented by a CREATE CERTIFICATE statement, and that the INSERT, UPDATE, SELECT, DELETE, EXECUTE statements and CREATE PROCEDURE statement include a new ability to allow one use to operate as another user while executing in the context of that DML statement or procedure call.

Adoption of these changes in the authorization model may not be so easy to assimilate in our existing applications. Fortunately, Microsoft is implementing things in ways intended to keep anything from breaking - with no changes necessary at upgrade - in the current model and allow an easy transition to the more secure scope hierarchy of the new model over time. Hopefully with the Beta 2 the documentation will be firmed up enough so we can begin to solidify our post upgrade migration strategies to this improved authorization model. I plan to keep an eye out for more in-depth white papers and SQL Server Books Online through the Beta releases of SQL Server 2005 so I can develop a full understanding of just how the new model is best used.

In the mean time we do have some resources available to begin to understand what the Microsoft vision is for this new authorization model. Perhaps the most relevant is the AdventureWorks database. AdventureWorks isn't exactly new. There is an AdventureWorks database available for SQL 2000  that is touted as an example the "best practices for designing an integrated enterprise-class schema, and provide a sample database for that schema". In addition there are a number of Reporting Services for SQL Severe 2000 examples that use the SQL Server 2000 version of the database. What's new in the AdventureWorks database for SQL Server 2005 is schema separation. All the resources in the AdventureWorks 2000 database are owned by dbo. In SQL Server 2005 AdventureWorks is a completely different beast. There are 5 schemas. None of the user tables are owned by dbo. There are no explicit permission yet granted in the sample database so we can't get a feel for how the authorization model might be best used. Still the schemas defined will give us a good starting point to explore the possibilities.

I think getting familiar with the AdventureWorks Database in SQL Server 2005 is going to be helpful not only in understanding schema separation, but also in getting a good feel for the Microsoft vision for that integrated enterprise-class schema. AdventureWorks is well beyond pubs and Northwind in complexity and as an example of a robust OLTP database design. Since there is not a diagramming tool in the SQL Workbench I thought I'd pull out the Visio 2003 Professional I recently covered (Working with Visio Professional Database Diagrams.) and provide a good image of the AdventureWorks database. Showing you the database diagram is going to quickly show you the data design, the schema separation, and the unobtrusive power of Visio 2003 Professional in reverse engineering existing database resources.

Visio 2003 Professional doesn't work with the SQL Server ODBC driver against SQL Server 2005. Instead it is necessary to configure a connection using the "Generic OLE DB Provider". This provider exposes the standard OLE DB "Data Link" udl dialog. If you have Visio installed on a machine that has the MDAC v.9 that ships with SQL Server 2005 you can configure the OLE DB Data Link connector using the "Microsoft OLE DB Provider for SQL Server". If the Visio machine is running an earlier version of MDAC you'll want to use the "Microsoft OLE DB Provider for ODBC" and configure an ODBC DSN that connects to the AdventureWorks database. Either can be used to produce the database model shown with a few modifications necessary before you add any tables to the .vsd drawing. Actually I could add the objects with no modifications, but for clarity, I edited the physical name of each table to include the schema name before building the diagram. Then I added each schema as a separate layer to the .vsd drawing and assigned a different color for each layer. This allowed me to easily distinguish which schema a particular table belonged to while at the same time show all the tables in the database in a single diagram. Using the Visio UI it is easy to hide any or all schemas so you can refer to only a single schema at a time if desirable. Don't have the same flexibility when saving the .vsd as html, but as you can see it's still very easy to identify which tables belong to which schema.

There are five schemas in the AdventureWorks database. The color coding used to identify the schemas are:

    Rusty Red for HumanResources

    Plum Purple for Sales

    Shady Green for Person

    Glittering Gold for Purchasing

    Midnight Blue for Production

Please browse around in the database diagram for a minute and get familiar with the AdventureWorks tables, then I'll state a few observations. Not much luck with giving you a viewable section of the diagram with the page real estate available from the article UI, but you can scroll or drag the the pan box around in the "Pan and Zoom" thumbnail to position the visible window over any part of the diagram. If that proves too frustrating (was for me) you can also open the diagram in a new browser window by selecting this link.

The most interesting aspect of this data model is that the foreign keys span the schemas. For example note there is a foreign key from [Purchasing].[PurchaseOrderHeader] to [HumanResources].[Employee] and a foreign key from [Sales].[SalesPerson] to [HumanResources].[Employee]. both are reasonable relationships, but you'd have to agree that seeing referential integrity between schemas is a bit foreign (pun intended) to our SQL Server 2000 sensibilities. What is inherently cool about this model is that if all folks in purchasing were authorized to view the [Purchasing] Schema and all folks in [Sales] were authorized to view only the [Sales] schema then we could still assure a consistent data set but protect visibly to sensitive data in the [HumanResources] schema. Make sense? Furthermore, if a query were necessary that required access by [Sales] folks to data in the [HumanResources] Schema, we could easily build a procedure to fetch that data and

    GRANT Execute on [Sales].[spSalesQuery] to [Sales] as [HumanResources]

to that procedure and the permission would allow the query but still completely protect the sensitive data points.
 

Perhaps some will find a script to demonstrate how this might work to be informative.

First, as the sysadmin I need to establish the authorization model

~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create login Jane
with password = 'password'
, default_database = AdventureWorks

use AdventureWorks

create role Sales

grant control
on schema :: Sales
to Sales

grant control
on schema :: Purchasing
to Purchasing

create user Jane
for login Jane
with default_schema = Sales

exec sp_addrolemember Sales, Jane

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Then I will to create a couple of test procedure that will reach across the schema boundaries  again as someone with dbo access so I can be sure I have the ability to extend permissions in both the Sales and Purchasing Schema

Sure enough,  Execute as Owner lets me view data in another schema. What's most interesting is that when I use execute as self, even though I'm expecting to be Jane when I execute the procedure, the system is recognizing my domain account rather than the SQL authenticated login. Not sure, but I think that just may be a bug in the Beta.



There are more reasons than schema separation that you will want to invest the time in understanding the AdventureWorks data. There is actually enough data pre-populated in the data set to make sample queries written against this data useful when looking at query plans for example, and there promises to be rich body of training material to help us learn and understand the philosophical and practical BI changes coming in SQL Server 2005. Spending a little time now getting to know the AdventureWorks dataset is going to be worth your while.

Bill