Database design question

When you have user logins and want to restrict data access for every user only to data relevant or owned by them how do you implement this?

Example:
When a user logs on, he will only have access to data that he himself have created or that have reading rights to which he qualifies.

Lets say I have ten different tables and all data from all users are stored in these tables. Do I need an extra field in every table that denotes who has access to specific rows?

What is the most sensible way to implement this scenario?

We are using ASP.NET, C#, Windows Server 2003, SQL Server 2000

Thanks!

ValeinOne way to implement this is to have all requests for reading or writing data handled through Stored Procedures. Pass in the user information, and then you can have the stored procedures do whatever is needed to determine what data is appropriate to be viewed/modified.

You then give no rights to the underlying tables, just execute rights on the stored procedure.|||Hi Douglas and thanks for answering!

I still don't see how the stored procedures would be able to tell what data a certain user should be allowed to view/update unless access information is stored with the data in the tables.

If there are two articles in a table and a user should only be able to read one of them, how can a stored procedure know what article to return to the user unless every article has access information tagged to them?

Stian Danielsen|||Of course the stored procedure does not "know" what a given user should have access to, you need to add logic to allow that to happen in the stored procedure!

You build queries that, for instance, link the articles with the user table (you do have such a table, correct?) and so the user would only get articles they wrote (for editing, for instance). You need to do something more clever for letting folks read articles beyond what they have written, but again, that can be done with tables that relate articles to groups of users, for instance.|||Thanks again for replying.

Yes, I have a user table and yes, I obviously need to do logic in the stored procedures to make it work. Still, joining the user table with the articles table would require a relation between the two. Most likely a user id column or a user group id column (or both) in the articles table and perhaps also an access specifications column (denoting read and/or write access).

This is a solution, but in my case it does seem a bit cumbersome. See, I don't only have an articles table - I have about 6 or 7 tables that need such access control. Seems to me I need the access rights columns in every one of those tables in order to link the tables up with the user table.

My question was: Is this the way to do it, or is there a better way?
I cannot think of one at the moment.

Thanks again,

Valein|||::This is a solution, but in my case it does seem a bit cumbersome.

You could also have some hardware (named crystal ball) attached to the server and see whether the SP can work with this to gfind out which user can read thwhich data.

See, there HAS to be some tables to manage this information. THis is not cumbersome - this is rquired for anythig than a crystal ball.

::See, I don't only have an articles table - I have about 6 or 7 tables that need such access
::control.

THen make a security management subsystem and integrate the tables there :-)

::My question was: Is this the way to do it, or is there a better way?
::I cannot think of one at the moment.

ONLY way for now - properbly forever.

BTW - no need for a stored procedure, a view should be able to handle the read filtering, too :-) Easier to use.|||This may be too simplistic for your application, but it's one approach.

I do this with a session item for the uid captured during the login process. Each DB entry the user makes it stores the uid in the table. In the select statement it pulls just the records that contain the uid of the person logged in.

selectwhatever fromyourtable where uidcolumn='" & Session("uid") & "'",yourconnection)

|||Thanks a lot for all your replies guys!

It's great to know that the solution I was going for is the only one plausible.
I suspected as much, but it can't hurt to make sure, can it? :-)

Thanks again!

Valein