Identifying the problem with the natural order
Here are three tools SQL Server developers have available to them, listed in the natural order:Logins: A login identifies a user and a password and allows the user to access the database instance.
Users: A user identifies a user from the group of logins and allows the user access to one or more databases on the instance.
Roles: A role identifies a collection of users and grants them all the same set of permissions.
Due to deadline pressures, developers are often forced to think in terms of this order. In my opinion, this is precisely the wrong way to think about security.
Rethinking the natural order
The natural order places roles at the bottom of the list, but when you're designing a database, I believe it makes more sense to think about roles rather than specific users. Therefore, here's how I suggest the list should be ordered:Roles: A role identifies a collection of users and the permissions that collection enjoys.
Users: A user is a member of a role.
Logins: A login permits a user to get into one or more databases.
Design the roles from the bottom up; that is, think of the least powerful role first and then each successive role. Also, it's important to note that a role can be a user. This is the where the architectural power lies.
A sample scenario
Suppose we have five roles:Role 1: Data-entry only. This person cannot add to the various lookup tables, and conversely is dependent upon the required lookups being available.
Role 2: Manager is responsible for adding the lookups required by Role 1 and can do data entry in a pinch. This person has access to a limited number of reports (i.e., stored procedures that deliver the data for the reports).
Role 3: Supermanager has access to all of her underlings' reports, plus some they cannot see.
Role 4: Director has access to everything and saves changes to the database in a structural way.
Role 5: Developer/DBA (in a big organization this might be two roles, with DBA as Role 6).
A note about table access: the cardinal rule is that no-one except the Developer/DBA has direct access to tables. This means that you need to create sprocs, views, and UDFs to provide UIDS (Update, Insert, Delete, Select) access to the tables. You impose permissions upon these objects, not upon the tables themselves.
Here's how we might utilize this information: 1. Map who needs to do what. This mapping may reveal the need for a new sproc, UDF, or view.
2. Create the roles, beginning with the least powerful. We need to have an accurate vision of exactly what powers the least powerful role requires.
For example, Role 1 needs the ability to add to and edit Table1, Table2, and Table3 and delete from Table2 and Table3 but not Table1. She cannot actually address the tables. Therefore, her permissions pertain to the stored procedures that enable these capabilities but not to the tables themselves. This principle applies all the way up the line, with the exception of Developer/DBA.
In the case of Role 2, we would want to add Role 2 as a user of Role 1. Then, Role 2 automatically gains all the powers of Role 1, plus the additionally specified powers of Role 2.
Similarly, for Roles 3, 4, and 5, each new role is recorded once as a member of the preceding role, thereby inheriting all of its permissions. Then, we can add additional permissions.
In Role 5, Developer/DBA inherits everything already declared, plus the ability to alter structures, create and delete objects, and even create and delete databases.
The ultimate role, DBA, can create or destroy entire instances of the database server and any object beneath that level in the tree.
Questioning the current logic
Understandably (and responsibly), you feel the need to meet deadlines, but at what cost? If you haven't got time to do it right, where will you find the time to do it again?To think effectively about roles, users, and logins, you must demand that your manager specifies the roles. (Remember to emphasize the fact that this will help you meet your deadlines.) Then, you can proceed to create the roles; add the higher roles to lower roles as users if required; finally, create the users and assign them to their highest role.
I can attest that this method works and is more efficient than the natural order most SQL Server developers are forced to use. If you've refined your own approach to working around this frustrating problem, I'd love to hear about it. Please describe your method in the article discussion.
Last updated 5 Sep 2024
