Bob Lambert

—–

Update 10 January 2010: Thanks to Gints Plivna for observing that we had not posted the slides to this presentation, here they are: Pretty Good Row Level Security Slides.  – Bob

—–

Thanks to those who attended Saturday’s Microsoft Code Camp (see http://richmondcodecamp.org/).  Here are materials for the presentation “Pretty Good Row Level Security” which I did with Nic Morel, my fellow CapTech Ventures Lead Consultant.

Summary:

The presentation reviewed a security solution that limits access at the data level, leverages simple database protocols, requires minimal administration resources, applies to both users and application developers, and overcomes some of the vulnerabilities of default approaches like having the application use a “system” user id.

The solution presents a three layer security architecture:

1. Base tables are accessible only to DBAs.

2. A security layer including cross-reference tables relating user ids to key data like department or sales territory, and table-valued functions that accept user id as a parameter and deliver data from base tables with a join to the security cross reference tables.

3. A data access layer exposed to users consisting of views that access the table-valued functions, providing the current user id as a parameter.  End users and developers only have access to this data access layer.

Email me if you’d like the powerpoint (a little big to attach here, even without the accent graphics), and I’ve pasted in the SQL scripts as a comment on this post.  The second two examples run against the SQL Server AdventureWorks database that ships with 2005 Developer Edition.

In a new post at Insurance Networking News Ara Trembly provides a balanced perspective on IT/business misalignment (Business/IT Misalignment: Whose Responsibility?).  He describes the problem as cultural, more amenable to relational than management solutions.    His conclusion sums it up: “Take a geek/suit to lunch today!”

To me (speaking as an IT professional) IT should take the initiative to solve the problem.  Quoting Trembly, “business executives … make decisions, but they are for the most part mystified at the magical incantations and actions that produce IT results” and “IT people, on the other hand, are jealous of the sheer power wielded over them by business people who just don’t get IT.”  In other words, business people contend with an emotional and a substantive problem, “fear and lack of knowledge,” while IT people have only the emotional problem of jealousy.

If we take the emotions out of the picture (its just a job, right?) then that leaves IT folks with knowledge that business people need in order to maximize the value of IT and efficiency of business processes.  Ever since mainframes roamed the prehistoric rain forests of the ’60s application developers have often been the most knowledgeable about how business processes really work, understanding both the intricacies of the application logic and how business people use the system to get things done.  These individuals can add value to the business discussion by bringing their knowledge to the table in a way that business people can understand.

In many organizations IT manages the forum in which these conversations can occur: the requirements process.  In my experience a good requirements process is long enough for the business and IT teams to get to know each other, offers generous opportunity for both structured and unstructured conversations about business needs, and brings together knowledgeable business and IT participants.  IT is typically able to bring the insights of seasoned application developers to the fore in a well planned requirements effort.

Yes, everyone has responsibility to “cultivate personal relationships based on mutual need and respect,” but IT can and should bring substance to the relationship in requirements definition.

It seems I’m frequently in conversations about using null to represent a business value.  To paraphrase, say there are credit and cash customers, and there’s a suggestion to set “Customer_Type” to “C” for credit and null for cash.  To data and database professionals this is obviously a bad idea, but it’s not obvious from a business point of view.

In a database null means that there is literally no value, or the value is indeterminate.  Null is not the same as zero or blank.  When a database operation involves nulls the result can be difficult to predict for someone not practiced in SQL.  In many cases the answer is null.  For example, 1+0=0 but 1+null=null.  In plain English, what you’re asking the DBMS to do in the latter case is to add 1 to [I don't know what], and of course 1+[I don't know what] equals [I don't know what].

So, if you use null to represent a business value then you might not get the results you’re looking for when you try to get business answers out of your database.  For example, say “C” represents credit customers and null represents cash customers, and you have 2 cash and 1 credit customers.   In SQL Server if you use a Count function to tally all of your cash customers the answer isn’t 2, it is null.

That’s one example of why it’s not a good idea to try to represent a business fact with a null value.  It doesn’t make business sense and in this case the DBMS, correctly, won’t make sense of it for you.

To be clear, whether or not a given database column permits null values is an entirely different question, best left to database designers.  For example, a database table might record which patient occupies which hospital bed.  It may be reasonable and correct to assign a null patient ID if the bed is currently available.  However, there are alternative methods of representing this situation, and the database designer should be free to choose the right alternative taking into account the specifics of the application under development.

While many organizations understand the value of managing the information resource, for many others information management remains abstract and difficult to define.  In an effort to make it concrete here’s a hypothetical proposal to provide an Enterprise Information Architect for a hypothetical organization that really needs one.

Today: inconsistent data of uncertain quality blurs enterprise view and restricts planning

Today managers, planners, and analysts lack the information required to run the organization as a single enterprise rather than a collection of diverse units.

• Data quality in IT applications varies to the point that, outside financials, it is impossible to gather consistent data supporting an enterprise view of operations.
  • Application development efforts have focused narrowly on departmental interests without accounting for enterprise concerns, making application data incomplete in describing business processes and inconsistent with data in other applications.
  • Focus on departmental concerns and tight development timelines has resulted in incomplete validation of data critical to the enterprise but not critical to the application’s focus.  For example, customer demographics are not critical to the sales process and therefore zip codes and telephone numbers are not consistently collected at point of sale, substantially reducing value of market analysis based on sales data.
• Enterprise planners work with only the highest level summaries of operational data, those summaries suffer large margins of error, and planners cannot definitively answer questions required to make critical business decisions.
• Regulators have questioned the validity and repeatability of reporting because of the organization’s heavy reliance on spreadsheets and manual processes in gathering and compiling data for reports.

Solution: enable sound planning and management by identifying data assets and setting processes to manage them

Empower an Enterprise Information Architect to lead an effort that (1) identifies data that describes the organization, (2) defines how to integrate and improve quality of that data, and (3) improves the ability of information technology to maintain data quality.

(1) Lead definition of an Enterprise Information Architecture identifying information required to manage the organization as a single integrated enterprise, and data quality standards that ensure that data supports enterprise goals.

• Identify and define events and objects critical to the enterprise

• Identify and define relationships among those events and objects and attributes that describe them

• Classify data managed by the organization by type (operational, statistical, financial, decision support, etc.) and define standards for managing and integrating each type.
• Compile the above into a plan that explicitly supports the enterprise strategic plan

(2) Working with senior business managers, put in place a program of data quality improvement that plans and executes specific measures and sustained commitment to improving data quality in business processes and IT applications

• Identify the business group responsible for maintaining quality and integrity of each business object, event, relationship, and attribute

• Identify for each data item of interest to the enterprise its “system of origination” and “system of record”.

• System of origination is the application that provides the entry point of a given data object to the organization.
• System of record is the application that is the source of record for the data object.

• Define and deploy standards and practices for for business process and IT application definition that support data quality and integrity standards

(3) Working with senior IT managers define and put in place standards for application requirements definition, data management, and metadata management to

• Define and deploy application development and interface standards that support data quality objectives.
• Ensure that application development efforts support enterprise data quality
• Continually monitor new developments in data management best practices and make that information available to the enterprise.