Creating a generalized table in SQL Server

Question

We have a client-server system that uses SQL Server 2012 as a database. The system is updated and enhanced on a regular basis.

We recently realized we have a recurring pattern of table design: a table that links the Users table to other tables in the database, forming a basis for views and permissions. Example:

CREATE TABLE Documents_Users (
    ID int not null identity(1,1) not for replication,
    UserID int not null, -- foreign key to the Users table
    DocumentID int not null, --- foreign key to the Documents table
    [...]
)

We were thinking, that instead of creating all of these tables, we might create one large table like this:

CREATE TABLE UserPermissions (
    ID int not null identity(1,1) not for replication,
    UserID int not null, -- foreign key to the Users table
    ObjectID int not null, -- pointer to any object ID in another table
    TableID int not null, -- actual id of the referenced table in sys.objects
    [...]
)

Pros: one table solves all of our permission needs for all current and future tables.

Cons:

1. no foreign keys. we will have to create a job that runs every once in a while and deletes orphan pointers (if we don't just delete them in stored procedures).

2. Views will need to query along the lines of

   select * from [some_table] where ID in ( select ObjectID from UserPermissions where TableID = object_id('someTableName') )

Is it worth it, and a good practice, to create a generalization like this? Would there be a performance gain from having one table and one index to read, instead of possibly many different tables?

Answer 1

My gut feeling is that this is probably not a very good idea.

A better alternative would be to use inheritance¹:

• connect user to a base table,
• then "inherit" other tables from the base table.²

Any future tables you add, you can just inherit from the existing base table and they will automatically "plug into" the security logic you have already implemented.

For what it's worth, we are currently in the process of implementing a rather powerful security mechanism on top of just such architecture and it works well so far.

---

¹ Also known in ER modeling as: "category" or "generalization hierarchy" or "subclassing" etc.

² Unfortunately, inheritance is not directly supported in relational databases today (not even PostgreSQL, which only partially implements it), so you'll need to emulate it (here is one example and you may also want to read this), which brings its own problems, but is still incredibly useful in certain scenarios.

Answer 2

I'm facing the same choice right now, and I think I'll go for the generic solution. I'm not a fan of generic solutions, because the semantics are less obvious, and before you know it, you're in an incomprehensible mess. But I tend to think it is permitted if it serves a domain-wide, clearly defined purpose, such as logging, language, and like here, security.

For the objections I'd think that deleted orphans are not in the way of anything, so cleaning once in a while would be a good enough solution to start with. As for the queries, i'd create an inline table valued function, aka a parameterized view (instead of a view for each table) that takes the UserId as an argument. One could optionally restrict reading rights on the tables themselves, and only allow rights on the views.

I think the performance gain would be marginal, but still better with the generic solution; the main reason for me would be less cluttered diagrams.

Answer 3

I would not do it because what happens when a user-to-X permission needs attributes based on the type of X. i.e. UserDocumentPermission might have read, write, delete, add, etc while UserCarPermission might have drive, start and park permissions.

Who cares if you have a lot of tables. You can just use naming convention to make it easy to remember.