Storing user permissions to hierarchically structured data in SQL Server

Question

I'm developing a system that supports creating accounts and multiple users within every account that can each access a certain subset of data of their "parent" account.

The data is stored in a SQL Server database in the following tables:

• Accounts: Id(guid, primary key), Title(nvarchar(200))
• Users: Id(guid, primary key), Login(nvarchar), AccountId(guid, foreign key).

Now, I need to implement a concept of "Folders" and "Files" within accounts. Each account can have a number of files (an object with a name and a few other non significant properties) that can be arranged into folders (like a file system), and every user might have a permission to access a folder. "A user has permission to view a folder" means that this user can see all files within this folder and within all its subfolders. These folders/files also have to support a lot of moving around the hierarchy, renaming, editing, etc.

I have come up with the following table structure for this scenario:

• Folders: Id(guid, primary key), Name(nvarchar(200)), AccountId(guid, foreign key)), ParentFolderId(guid, foreign key)

• Files: Id(guid, primary key), Name(nvarchar(200)), FolderId(guid, foreign key),

• UserFolderPermissions: Id(guid, primary key), FolderId(guid, foreign key), UserId(guid, foreign key)

This structure does the job of storing data correctly, but I also need to run some complex queries against it, like finding a complete path to a file or retrieving all files that current user has permissions to access. For that, I created a view that outputs all folders, files, and users who have permissions to access them:

WITH FolderWithPath AS 
    (
        SELECT        
            folder.Id, 
            folder.Name, 
            folder.AccountId, 
            folder.ParentFolderId, 
            CAST('/' + folder.Name + '/' AS NVARCHAR(4000)) AS Path
        FROM            
            dbo.Folders folder          
        WHERE        
            (ParentFolderId IS NULL)

        UNION ALL

        SELECT        
            self.Id, 
            self.Name, 
            self.AccountId, 
            self.ParentFolderId, 
            CAST(parent.Path + self.Name + '/' AS NVARCHAR(4000)) AS Path
        FROM            
            dbo.Folders AS self 
            INNER JOIN FolderWithPath AS parent ON self.ParentFolderId = parent.Id
    )

SELECT        
    file.Id AS FileId, 
    folder.AccountId, 
    folder.Id AS FolderId, 
    folder.Path AS Path, 
    userFolderPathPermissions.UserId AS AuthorizedUserId
FROM            
    FolderWithPath AS folder 
    LEFT OUTER JOIN dbo.Files AS file ON file.FolderId = folder.Id
    LEFT OUTER JOIN 
    ( 
        SELECT 
            UserFolderPermissions.UserId, 
            folder1.Path, 
            folder1.AccountId 
        FROM 
            FolderWithPath folder1 
            INNER JOIN UserFolderPermissions AS UserFolderPermissions ON folder1.Id = UserFolderPermissions.FolderId
    ) userFolderPathPermissions ON folder.AccountId = userFolderPathPermissions.AccountId AND folder.Path LIKE userFolderPathPermissions.Path +'%'

Now, this does seem to work, but I have certain performance concerns. Is it the best database structure to fit my needs? Is the query inside the view the optimal way to access the necessary data?

Due to different security issues, I am unable to test it in a real environment with millions of users performing hundreds of operations per second, so I would appreciate any advice regarding the code above.

Answer 1

I would only make minor adjustments to your SQL.

1. Refrain from using KEYWORDS or RESERVED words as column or table aliases. If you already have them as column names, enclose them in square brackets e.g. [Name], [Path]

2. I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.

3. If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.

4. I find it more convenient to see column alias's referenced from the left side e.g.
   , [Path] = CAST(pfdr.[Path] + fdr.[Name] + '/' AS NVARCHAR(4000))

5. Use a table expression instead of a correlated sub-query. It makes the select statement a bit more readable.

6. Check the Estimated Execution Plan Ctrl + L in Microsoft SQL Server Management Studio (SSMS) to check the performance of the query by pasting both SQL statements in a query window.

Here's an example.

Revised SQL

---

WITH 
folder_paths
AS 
(
    SELECT 
          fdr.[Id]
        , fdr.[Name]
        , fdr.[AccountId]
        , f.[ParentFolderId]
        , [Path] = CAST('/' + fdr.[Name] + '/' AS NVARCHAR(4000))
    FROM 
        dbo.[Folders] AS fdr
    WHERE 
        1=1
        AND (fdr.[ParentFolderId] IS NULL)
    UNION ALL
    SELECT 
          fdr.[Id]
        , fdr.[Name]
        , fdr.[AccountId]
        , fdr.[ParentFolderId]
        , [Path] = CAST(pfdr.[Path] + fdr.[Name] + '/' AS NVARCHAR(4000))
    FROM 
        dbo.[Folders] AS fdr
        INNER JOIN folder_paths AS pfdr ON fdr.[ParentFolderId] = pfdr.[parent.Id]
)
,
user_folder_permissions
AS
(
    SELECT 
          fprm.[UserId]
        , fdr.[Path]
        , fdr.[AccountId]
    FROM 
        folder_paths AS fdr
        INNER JOIN dbo.[UserFolderPermissions] AS fprm ON fdr.[Id] = fprm.[FolderId]

)
SELECT 
      [FileId] = fs.[Id]
    , fdr.[AccountId]
    , [FolderId] = fdr.[Id]
    , [Path] = fdr.[Path]
    , [AuthorizedUserId] = fprm.[UserId]
FROM 
    folder_paths AS fdr
    LEFT JOIN dbo.Files AS fs ON fs.[FolderId] = fdr.[Id]
    LEFT JOIN user_folder_permissions AS fprm ON fdr.[AccountId] = fprm.[AccountId] AND fdr.[Path] LIKE fprm.[Path] + '%'