Database design for user rights

[sngapoonage]

Hi everyone,

I am building a database of users for our website. It is an MSSQL database. Currently I have all the usual fields eg First Name, Last Name, email etc. There are different things that our users need to have access to on our site. At the current time the number of 'functions' they need to be allowed or not allowed to use is 6. My problem comes from the fact that this number will need be extended as more functionality comes to the site.

In a previous attempt I had a 'bit' column directly in the user table for each of the functions. As you can imagine this is in no way extendable because if something new gets added I would need to change all the code and the database itself for a new functionality.

My current thinking is that I will have a seperate table for user functions. This will basically just be the ID of the user and a number representative of the function. In searching if a user allowed to do something it would be something like

SELECT Count(*) FROM user_functionality WHERE userId = @userid AND function = @function

If that row exists(count = 1) they are allowed, if not(count = 0) they are not allowed.
I can see this will work, but is it a good way to go about doing this? My user table will start with 500 users from an existing spreadsheet but is expected to grow by about 100 per year (not much I know).

Thanks for your help.

[BitBlt]

There are a couple of ways of doing this depending on how deep you want to get into SQL Server security group setup. I'll assume that you don't want to go that way because it can get messy very quickly, so I won't go into it.

The method you suggest is a good one. I've used a variant of it myself on apps. Just make sure you build an administrator screen into your app, or you will wind up owning the app until you die or the app retires. Not a good position.

One other bit of advice: if you are using stored procedures to implement your allowed functions, you can use this syntax as the first line of code in each sp (assuming @userid and @function are supplied somewhere):

if exists(select 1 from user_functionality WHERE userId = @userid AND function = @function)
begin
(...your code here...)
end
else
begin
raiserror ("Function not authorized", 16, 1)
return 1
end

Then in your application code just check for sql errors. This method has 2 advantages. First, you are only making a single database call to either succeed or fail, rather than one database call to test and another to actually do work. Second, in the statetment itself, no actual data is ever retrieved from the user_functionality table so execution time is much faster.

If userid and function are NOT supplied, you can use this code snippet to acquire them on the fly:

declare @function as sysname
declare @user as sysname
select @function = name from sysobjects where id = @@procid
select @user = suser_sname()

Then you just have to make sure that the names in your user_functionality table match those.

Hope these suggestions are useful! Good luck!

[sngapoonage]

Hey thanks for the answer. Yeah the entire system will be run by a user management web application on intranet so there will be no need to really touch the database directly once it's all running.

I will try your stored procedure suggestion, sounds like a good way to go. I generally use ADO.Net (because I use ASP.Net) to access my database but I don't do much work with stored procedures. I have read equally supported sides to the story (stored procedures are faster / slower, easier / harder to manage). What's your opinion on their use?

Also sorry, one more question might be a bit silly: Do you think I should make a table linking the "functions" number with an actual named thing, purely for readability purposes? Say for example my user has access to view embargoed files, it might come up in the functionality table as

USERID FUNCTION
1 4

Would it make sense to create a table like

FUNCTIONID FUNCTION
4 Access embargoed files

which would rarely be accessed but could be viewed by the next administrator to clarify what the functionality actually is?

EDIT: Actually I see in your example your using the name directly in the table, I suppose this would work because if you're not actually retrieving data, it doesn't matter about decreasing the size of the column?

[BitBlt]

I always use stored procs for security. Also, it's easier to control SQL Injection attacks. Plus, I don't have to worry about some user "accidentally" honking up the data when he bypasses the app to go after data directly.

As far as your second question, I don't think it's silly at all. But if you ever expect anyone to be able to maintain your system besides YOU, then you should be polite and make it as maintainable as possible...which probably means use the decode table as you said.

For your "edit" addendum, it doesn't really matter. I was using it as an example of how you COULD do it, not necessarily how you HAVE to do it. The suggestion is so that you can tie the names of your stored procedures to the names of your "functions"...more consistency, less confusion, better maintainability. You could do it any way you like, even to the point of having your table look like:
FUNCTIONID FUNCTIONSPNAME FUNCTION
4 usp_access_embargoedfiles Access Embargoed Files
And you are correct about the size of the column not being an issue. Even if it was, it is going to be a tiny table as far as number of rows, so performance shouldn't be impacted anyhow.

Happy coding!

[sngapoonage]

Thanks again, those were both very clear answers. Now here is an actual silly question: How do I mark your posts as answers? (This is my first post to this website)

EDITING AGAIN: Never mind I found the up arrow.