I was working on a project many years ago where we were integrating authorization into our system. We already had authentication solved, but we wanted to restrict presenting the user with features they could not exercise. We wanted to restrict those features both in the user experience, and in the database access.
The project manager had developed a very sophisticated security implementation, looking very much like granting permission in a file system. You could be a member of a role, and receive all the privileges of that role. You could also have permissions assigned to you individually. When this was reviewed by the business analyst it became clear that the detail capability was important, but the detail implementation for all users was too complicated, or onerous.
This was my first introduction to role based authorization. The business analyst decomposed the different needs down to a few roles. There were only a few roles, but many code assets were secured or exposed through those few roles. Users were then granted access to one or more roles, based on the requirements of their job.
SQL Server integrated with this same concept prior to SQL Server 2005 by using roles, and granting specific permissions and ownership of database objects to those roles. With SQL Server 2005, the whole idea of securables was simplified with the introduction of Schemas. A securable object is owned by a schema. It may be shared with other schemas, roles or users. Here is an example of how I use this.
I have a number of reference tables that are fixed, the contents are only managed through database migrations. So, they need to be read only to the user data access. So, I create a schema for those static reference tables. Then, for you user application database role, I grant select permissions on that schema. So, the Database Role is granted to my application database user, allowing me to restrict the capabilities they have on the reference tables, without having to specify this capability to every database login.
Role based security can simplify your life. The difficult part is the multiple layers in which it is defined. It helps to maintain a chart of some sort reflecting who can do what in your database, and what specific database object grants that permission. If you didn’t use roles for this, the chart is blown up by the number of database logins in your database.
Cheers,
Ben