SQL Server database client security framework.

I am starting development on a SQL Server Client that will allow a user edit and retrieve records that they they create. These records will live in a single database with other users.

Here are the basic requirements:

1. Based on the users login, control what records they have access to.
2. Based on the users login, control whether or not they can even use the client.
3. Based on the users login, control what CRUD operations they can perform.

Are these functions that should be administered on the server side by creating users and roles, or could it be done within the database with the appropriate tables etc. Such as having a table of containing user names, passwords, privilege ratings, etc. Then based on what information is returned from that table, create variables that can be used in a where clause in a select statement to restrict what records are returned.

As I mentioned earlier, I am in the planning stage, so I am not tied into any particular way of thinking.

Thanks,

Bob Holahan

I like the make my User-Interface control record access.

Like a drop down for what buildings can be seem in a school district. Or a drop down of what "types" of batches can be looked at.

Then you have times where an admin user can see all STAFF. So a STAFF drop down on a form makes sense - and if a user can only see themselves that drop down is appropriately limited to a single user (or whatever the business requirement is).

With all that said I've always put the majority of my permissions control into tables in the SQL database itself.

[sidenote] This also makes the portability of the DB a whole lot better - file this under disaster recovery [/sidenote]

I am a proponent of 100% stored procedure access to my MS SQL databases. This means I usually have parameters to feed the WHERE clause. And those parameters are going to come from the UI.

Giving permission to EXECUTE SPROCS would use MS SQL permissions (usually through WINDOWS AD GROUPS assigned to DATABASE roles).

If there are times I need to expose a VIEW to EXCEL for users I can easily JOIN out to these permissions tables (using SYSTEM_USER) and properly limit the WHERE clause to what the users can see.

Note in this case MS SQL permissions are also exploited to FIRST OFFER ACCESS for this VIEW to certain users (again usually through WINDOWS AD GROUPS assigned to DATABASE roles).