T-SQL Tuesday #18 - CTEs - The permission hierarchy problem

May 10, 2011 00:01 by Matt Whitfield

Recently, I was looking at a problem which involved an arbitrary tree structure for permissions. The idea was similar to NTFS security, in that the permission that was defined furthest from the root of the tree would apply to any items below it.

So, consider the following structure:


  • Bob
  • James
  • Tim


  • Administrator
  • Reporter
  • Engineer


Company A

So, Bob is the manager at Company A. He gets the admin role at all levels. James is the manager for Company A/North. He gets the admin role for Company A/North, but he is allowed to run reports on Company A's other regions. Tim is an engineer for RecursiveTown - and he just has the engineer role for that town.

Let's have some (very) basic table structures that meet our needs, data-wise:

CREATE TABLE [dbo].[Users] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Roles] (    ID     INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    [Name] NVARCHAR (50));CREATE TABLE [dbo].[Containers] (    ID                INT           IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,    ParentContainerID INT           FOREIGN KEY REFERENCES [dbo].[Containers] (ID)                                     ON DELETE NO ACTION ON UPDATE NO ACTION,    [Name]            NVARCHAR (50));CREATE TABLE [dbo].[UserContainerRoles] (    ID          INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,    ContainerID INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Containers] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    RoleID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Roles] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION,    UserID      INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Users] ([ID])                              ON DELETE NO ACTION ON UPDATE NO ACTION);

Simple... Let's look at the data that might be within them:






1<NULL>Company A



So - how can we, succinctly, get a list of the containers and roles that apply to a particular user?

Enter the recursive CTE. Now, recursive CTEs can be a bit interesting. They can also suck, performance-wise. But, for smaller data-access patterns, such as this one, they can provide some very flexible solutions to some problems that previously were not easy to solve at all.

Recursive CTEs follow a simple pattern, but it can seem a bit daunting at first. They all follow this pattern:


The Anchor is the statement that returns the basic result set - the first level of recursion. This is the result set that we start with. In the example above, it is the containers to which the user has been given a direct role assignment through the UserContainerRoles table.

The Recursor is the interesting part - it's a query that references the Anchor, and provides the next level of result set. Very nice, but what happens next? Well, next the result set that was last returned by the recursor is passed to the recursor again - and this loop continues until either the recursor part returns no more rows, or the maxrecursion limit is reached.

Now I'm assuming that the fact that you're still reading means you probably just want to see the code. Who am I to stand in your way?

CREATE FUNCTION [dbo].[fn_GetContainerRolesForUserID](@UserID int)  RETURNS TABLEASRETURN (WITH   Hierarchy (ContainerID, RoleID, HierarchyLevel)  AS     (SELECT [c].[ID],                 [ucr].[RoleID],                 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [dbo].[UserContainerRoles] AS [ucr]                 ON [ucr].[ContainerID] = [c].[ID]          WHERE  [ucr].[UserID] = @UserID          UNION ALL          SELECT [c].[ID],                 [h].[RoleID],                 [h].[HierarchyLevel] + 1 AS HierarchyLevel          FROM   [dbo].[Containers] AS [c]                 INNER JOIN                 [Hierarchy] AS [h]                 ON [c].[ParentContainerID] = [h].[ContainerID])  SELECT [ContainerID],         [Name],         [RoleID]  FROM   (SELECT [ContainerID],                 [Name],                 [RoleID],                 [HierarchyLevel],                 ROW_NUMBER() OVER (PARTITION BY [h].[ContainerID]                                     ORDER BY [HierarchyLevel]) AS __RN          FROM   [Hierarchy] AS [h] INNER JOIN                  [dbo].[Containers] AS [c]            ON     [h].[ContainerID] = [c].[ID]) AS iDat  WHERE  [__RN] = 1)

The only non-obvious thing about this query is the use of the ROW_NUMBER function. This is what causes a permission defined at a lower level to take precedence over one defined at a higher level - this is because HeirarchyLevel increases each time it passes through the recursor section of the query, so we want to choose the container role with the lowest hierarchy level for each container.

So, let's look at each of our results:

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](1)

1Company A1

Ok, so Bob has the admin role at all levels - just what we wanted to see...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](2)

1Company A2

James has the Admin role for North, but the Reporter role everywhere else - again, just what we want...

SELECT [ContainerID], [Name], [RoleID]   FROM [dbo].[fn_GetContainerRolesForUserID](3)


This just shows that Tim has the Engineer role at RecursiveTown only - excellent...

Simple, right? Happy T-SQL Tuesday!

Tags: , ,

community | t-sql

T-SQL Tuesday #13 - What The Business Wants Is Not What The Business Wants

December 14, 2010 00:00 by Matt Whitfield

This is a really interesting topic chosen by Steve Jones of SQLServerCentral fame, and, for me, is a story of communication skills.

What the business wants can be expressed in a few different ways, by varying people. For example, a high availability system might be described as having no downtime, or it might be described as having geographic redundancy. These requirements might be voiced in a few different ways:

  T-SQL Tuesday
'We need a system that is always available for our customers'


'This system is business critical and cannot be unavailable at all'


'We need to use replication to make sure our system is disaster proof'


'The system has to be hosted in the cloud so that if the office network goes down it is still available'

Based on the premice that a little knowledge is dangerous, those statements are listed in order of increasing danger.

What do I mean by that? Well, the sentiment behind each of those statements is:

'We recognise that our systems and the data within them are valuable, and we want to protect them as much as is reasonably practicable given our budget'.

But the statements above start to not only specify the what - but the how. I have seen many, many systems where dubious architectural decisions have been taken at an early stage because 'that was the way Mr. Manager wanted it'.

Is it reasonable for us, not only as DBAs, but as computer scientists, to allow Mr. Manager to specify the how without a reasonable understanding of what was being asked for?

We've all been there - sitting in a meeting room and Mr. Manager comes up with another gem:

'We could use an EAV table to store that kind of information'

Everyone in the room knows that's not the way to go for the scenario at hand, but, strangely, nobody speaks up. Everyone has that slightly sinking feeling at the pit of their stomach, but nobody says a word.

And there, right there, is the failing. Both in Mr. Manager's 'I've just read about technology X and will suggest it every time I feel slightly out of my depth' and, in each of the people who failed to speak.

That sinking feeling doesn't mean 'Oh dear this system is going to be a mess'. That feeling means 'I need to speak up now in order to avoid a steaming pile of rubbish'.

You might not feel that you have the right environment in which to speak. Or maybe you feel like you shouldn't 'embarrass' Mr. Manager. Well, you're wrong. Ok, you can take times and places to deliver your message, but the message that needs to be delivered at the initial stage of the project is:

'We need, right now, to focus on the what, and leave the how for later - getting the what right is critically important to our success'.

Sometimes, a picture can paint a thousand words. If you're having trouble, get out a flip chart and write, in big letters:

'We need to avoid this:'

Below that, attach a print-out of this classic project picture:

The project

That should make the point, while not embarrassing anyone, and getting things off to a good-humoured start.

Tags: , ,


T-SQL Tuesday #12 - Why are DBA skills necessary?

November 2, 2010 00:00 by Matt Whitfield

So, I thought I would try and participate in T-SQL Tuesday, and the topic for this month is 'Why are DBA skills necessary?'. This one is a topic that's close to my heart, as it's an area where I have been bitten in the past while at the same time I continue to see businesses struggle on regardless today.

Let's rewind to way back when (ok, so I'm only talking about 9 years ago) when my career with SQL Server really started to take shape. I'd been tinkering around with SQL Server for 4 years prior to that, but never really got into it.

The task I had was to design a database for an on-line transaction processing system, which would process loyalty transactions from an unspecified number of client devices. When I say system, I do mean system. I mean hardware, software & database. In fact, I even designed the logo. This was a small shop.

At the time, I didn't have much experience as a DBA at all, and designed the system based on what I perceived to be best practices. Actually, my idea of best practice back then was 'interesting' to say the least - focusing on just the database design - there was a total lack of referential integrity, a unique constraint to me was not being able to use the coffee machine because it was being serviced and a transaction was something I paid money in a shop for. I really had no idea.

There were several design mistakes that I made along the way, which all had to be rectified at one time or another. Let's get an example of what I'm talking about:

  • Not thinking up-front about where the clustered index would go was probably the mistake that caused the biggest headache.
  • Not understanding the difference between @@IDENTITY and scope_identity() was fun when data was linked incorrectly - and this wouldn't have happened had referential integrity been enforced.
  • Not having a clear naming scheme - sometimes xxxID meant a reference to ID in table xxx, and sometimes it was a client-supplied match value (think TerminalID - was this a link to the tblCfgTerminals table, or was it the number that the client terminal used to identify itself?).

So, I was making some pretty newbie mistakes - and these were mistakes that I had to live with over a considerable period of time.

Hang on, what on earth does this have to do with DBA skills being important?

Here's the core of what I'm trying to get at - which is that DBA skills are absolutely essential in a mentoring capacity. Not important, essential. And not only DBA skills, but DBA experience. Would a freshly certified MCDBA (or whatever this week's exam is called) have been able to see the mistakes I was making? Would they have been able to tell me a story that really engaged me with their advice, or would it have been the 'because this web page says so'?

Had I had a seasoned and battle-hardened DBA from which to learn, then I would have still made mistakes, no doubt - but I would have made far less mistakes. The time I spent fixing those mistakes could have been spent on learning some new facet that I hadn't yet understood.

Coming from a development background, as I have, I would have found the presence of a DBA hugely beneficial.

So, fast forward back to the present day, and I still see projects and solutions being delivered where it's clear that there has been a total lack of a DBA's touch in the development of systems.

Some of the things I see today make me understand that actually, the mistakes I was making back at the start of my career were not uncommon. In fact, they were very common. Looking at a few soundbite examples of things I've seen recently:

  • A company who have client devices that have well-structured configuration. Instead of this being stored in tables, it's stored in XML with a non-defined schema - and stored in the database as ntext. I thought the developer was joking when I asked 'can we see which devices have x set to y' and he said 'not easily'.
  • Monitoring systems that dump events into tables with no indexes at all, and then create new tables with the same schema arbitrarily - meaning you have to look in a different table based on the date you're looking for, and there is no way of telling which table you have to look in before you look.
  • Reports being generated with nested cursors when relatively simple DML performs the same operation 100 or 1000 times quicker.
  • People using in-line SQL in applications because they don't realise that stored procedures exist.
  • People realising that stored procedures do exist, but running everything in dynamic SQL because they haven't quite understood.

The list goes on. There is a whole world of epic-database-fail out there, and that world needs us. It needs us to use our skills and experience to educate and influence, in a positive way, to try and make sure that the world of the database is understood much more widely, and that fundamental mistakes can be designed out and not rectified later.

The onus is on us to translate the failures of design into terms which management can easily understand - monetary terms. How costly would a system fail be because not enough thought was put in at the beginning? Is it even possible to put a cost on damage to reputation when dealing with failures of a service that is growing in popularity? It's in these terms that we must explain the importance of designing something right from the get go.

Nobody wants to build a house with no foundations, and nobody wants to buy a car where brakes are an optional extra. I think it's time to realise that the following statement is just as true:

'Nobody wants to build a data system without getting a DBA involved, right at the start'.

Tags: , , ,

community | t-sql

SQL Everywhere

Tag cloud


<<  September 2018  >>

View posts in large calendar