Subject Permissions and data access restrictions - how to?
Author Randall Sell

I was hoping to get some feedback if this approach is "normal" in the C/S world. I am trying to stick by the rules and keep the business logic on the backend, although I know exactly how I'd do this as a client app, this is my first go applying it to the backend.

What I'm attempting to do is create 2 different users of the system. One that has no restrictions, and the other that has a pre-defined set of restrictions on the data they can see and the data they can enter (insert/update) into the database.

My approach was to create 2 roles that define the different permissions. Then users can login under a particular role. The user wouldn't have direct access to the tables but instead stored procedures.

The stored procedures would then pass back the data, checking the global CURRENT_USER variable to determine the appropriate SQL (WHERE clause).

Another approach I am considering is a derivative of above - where the stored procedure just returns one of two views, which amounts to essentialyl the same thing AFAICT.

So, is this a viable and common approach to securing one's database and ensuring that users can only access data they are allowed to see? Is there a better approach?

-randall sell

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around

[Non-text portions of this message have been removed]