implementing Access Control Lists vs using database roles and users
First off before you grill me to the floor, i would like to say i have spent considerable amount of time reading and researching this topic. I have read some on stackoverflow itself such as
what i found interesting was that it necessarily explains to me how to create a good acl implementation and i followed methods from both of these. Also i referred to Database Concepts by Korth to understand query optimization and performance impacts.
But my real question comes down to this dumb approach. Why isnt it just better to create Roles on the Database and grant permissions to tables and then access the database using a respective role. Since i am using SQL SERVER, all i have to do is specifiy the username and password on the connection string and get it over with. I just want to know the pros and cons of such an approach rather than spending loads of time writing and testing your own ACL implementation (agreed the learning is fun :D). So please advice.
Answer
OK, good, found the question: "Why isn't it just better to create roles on the database and grant permissions to tables and then access the database using a respective role?". :)
The difference is between the front end, the site administration, and the database administration.
Consider forums. You may have a list of roles or titles, each of which have a different set of permissions for what they can and can't do. This may including reading, posting, replying, creating new threads, and creating topics.
Forum users will only see these roles, while site administrators will be able to see permissions for these roles. Only the database administrator will be able to see how these roles correlate with database accounts.
Some accounts may have insert privileges in one instance, but not the same instances in another. By linking a user account with a role with set ACL permissions, the role can tie in with the SQL accounts to provide the appropriate access in the background.
I hope that answers your question. :)