TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Imposing security on a database typically involves use of three database objects—logins, users, and roles. It’s ingrained in the minds of SQL Server developers to
think about the following tools in this specific order: logins, users, and roles. However, every time I used logins, users,
and roles in that order, I felt like there must be a more effective method.
After several attempts, I devised a plan that’s much more efficient. I’ll
explain what’s wrong with the current way of using SQL, and then I’ll describe
my effort to improve upon this logic.

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

Let’s suppose that 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 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.