I've got an interesting design question. I'm designing the security side of our project, to allow us to have different versions of the program for different costs and also to allow Manager-type users to grant or deny access to parts of the program to other users. Its going to web-based and hosted on our servers.
I'm using a simple Allow or Deny option for each 'Resource' or screen.
We're going to have a large number of resources, and the user will be able to set up many different groups to put users in to control access. Each user can only belong to a single group.
I've got two approaches to this in mind, and was curious which would be better for the SQL server in terms of performance.
The presence of an entry in the access table means access is allowed. This will not need a column in the database to store information. If no results are returned, then access is denied.
I think this will mean a smaller table, but would queries search the whole table to determine there is no match?
A bit column is included in the database that controls the Allow/Deny. This will mean there is always a result to be found, and makes for a larger table.
If it's only going to be Allow/Deny, then a simple linking table between Users and Resources would work fine. If there is an entry keyed to the User-Resource in the linking table, allow access.
UserResources ------------- UserId FK->Users ResourceId FK->Resources
and the sql would be something like
if exists (select 1 from UserResources where UserId = @uid and ResourceId=@rid) set @allow=1;
With a clustered index on (UserId and ResourceId), the query would be blindingly fast even with millions of records.