Options to retrieve role based content pieces from the database

I have a need to bring some role based content (several pieces resulting into one object) from the database to a service layer. Access to each content piece will depend on the role that the user has. If the user role has no access to a piece, that one will be empty. This is not a huge application, just a web service method exposing some content based on roles.

I could do this in three ways.

1) Based on the user role, make database calls for each piece of content.

Pros - The roles are managed in the code thus helping business logic (?) stay in the code. Only brings back data that is needed.

Cons - Multiple db calls. Code needs to be modified when a new role is added. (unless some really complicated business logic is used.)

2) Make a single db call and bring back all the content pieces as separate results sets. Loop through the sets and obtain pieces based on the user role.

Pros - Single db call. Roles are managed within the code.

Cons - Code needs to be modified for new roles. Extra data is brought back though it may not be needed. Those unneeded queries can add a couple of seconds.

3) Send the roles to the db and get each piece based on the role access.

Pros - single db call. Only brings back what is needed. No need to change code for new roles as only stored procedure needs to change.

Cons - Business logic in the database?

It looks to me that #3 > #2 > #1. (overriden > to mean better than)

Does anyone have any insights into which approach may be better?

Update -based on some comments, some more details are below.

User role is obtained from another system. #3 would ideally pass it to the db, and in crude terms for the db, return data as- if user_role ="admin", get all pieces, for "editor" get content pieces 1,3 and 55. Again, this is not a big application where the role management is done in the db. Its a web service method to expose some data for several companies.

We obviously cannot use this model for managing the roles across an application. But for a method level access control as in this 开发者_C百科scenario, I believe #3 is the best way. Since the roles come from another system than where the content resides, the logic to control access to different content pieces has to reside somewhere. The database looks like the right place to have a maintainable,scalable, less hassle solution in this particular scenario. Perhaps, even create a look up table in the content db to hold roles and content piece access to give a sense of "data" and "logic" separation, rather than having a udf to perform the logic.

If no one can think of a valid case against #3, I think I'll go ahead with it.

I would always pick option 3 and enforce it in the database itself.

Security is best handled at the closest point to the actual data itself for a lot of reasons. Look at it this way: It is more common for an additional application to be added in a different language than it is to toss a database model. When this happens all of your role handling code would have to be duplicated.

Or let's say the application is completely bypassed during a hack. The database should still enforce it's security.

Finally, although people like separating "business logic" from their data, the reality is that most data has no meaning without said logic. Further "security logic" isn't the same thing as regular "business logic" anyway. It is there to protect you and your clients. But that's my $0.02.

Looking at your other options:

2) You are sending too much data back to the client. This is both a security and performance no no. What if your app isn't the one making the data request? What if you have a slight bug in your app that shows too much to the user?

1 and 2) Both require redeploy for even slight logic changes (such as fixing the mythical bug above). This might not be desired. Personally, I prefer making minor adjustments to stored procedures over redeploying code. On a sizeable enough project it might be difficult to know exactly what all is being deployed and just generally has a higher potential of problems.

UPDATE

Based on your additional info, I still suggest sticking with #3.

It depends on how your database is structured.

If you can manage access rights in the database, you might have a table design along the lines of

Content Table
ContentId    Content   

Role Table
RoleID       RoleName

ContentAccess Table
ContentID    RoleID

Then passing in the role as a query parameter is absolutely not "business logic in the database". You would obviously write a query to join the "content" and "contentaccess" tables to retrieve those rows in the content table where there's a matching record in ContentAccess for the current user's role.

If your application uses code to determine if a user is allowed to see a specific piece of content, that doesn't work. The crudest example of this would be "if user_role = "admin" then get all content, if user_role = "editor" get items 1, 3 and 55". I'd argue that this is not really a maintainable design - but you say the application is not really that big to begin with, so it might not be a huge deal.

Ideally, I'd want to refactor the application to "manage access rights as data, not code", because you do mention maintainability as a requirement.

If you don't want to do that, option 1 is the way to go; you could perhaps refine it to an "in" query, rather than multiple different queries. So, you run whatever logic determines whether a user role can see the content, and then execute a query along the lines of "select * from content where content_id in (1, 3, 55)".

Different people have different feelings about stored procedures; my view is to avoid using stored procedures unless you have a proven, measurable performance requirement that can only be met by using stored procedures. They are hard to test, hard to debug, it's relatively rare to find developers who are great at both Transact SQL and C# (or whatever), and version control etc. is usually a pain.

How many new roles per year do you anticipate? If few roles, then stick everything in code if it makes the code simpler. If a lot, use option #3.

If you really dislike multiple calls, you can always do a SELECT ... UNION or defer the retrieval to a simple stored procedure.

Alternatively, consider just getting one of myriad RBAC frameworks and let it take care of the problem.