Assigning
Rights to Users:
Part 1 - Worst Practices - Part 1 of a Very Long Series!
Part 2 - Worst Practices - Objects Not Owned by DBO
Part 3 - Worst Practices - Not Using Primary Keys and Clustered Indexes
Part 4 - Worst Practices - Making Databases Case Sensitive (Or Anything Else)
This is one of my pet peeves. One of the few things that I feel is just plain poor
development and administration practice. And it is something that is incredibly
easy to avoid, taking almost no more effort to avoid than it does to implement.
And yet, it is one of the things that I constantly find implemented by virtually
every network administrator, developer, and DBA that has not worked in a
large-scale environment (and some that have).
Why not assign the rights to an individual?
I am constantly amazed that I have to explain this to people. I am even more
amazed that when they hear my argument and agree with me, that they do not
bother to implement it. I am also constantly amazed that anyone even bothers to
allow the assignment of individual rights in their systems, especially
vendors.
First of all, let's agree on one thing. In any company, or any system for that matter,
the system will likely survive longer than any individual person. By nature, a
company or a system has no real lifetime, often extended even beyond what anyone
anticipated. A person, however, has a life that changes, has priorities that
change, interests, emotions, etc. that all lead one to move on to a different
position, different company, or even industry. If you think about all the
systems on which you have worked. How many have you taken from scratch to
retirement? I don't think I have ever seen both the initial development and the
retirement of a system.
So if an individual will not survive the system, wouldn't it make sense to design
the system so that the individuals are easily replaceable? This brings us to
rule 1:
Rule 1: Design the system do that individuals are easily replaceable. Don't integrate
the individual account into the security of the
system.
I realize that it is easy to remove a user from a system. A single
sp_droplogin will accomplish this task. However, it is rare that people
drop out of a system. Usually they move on and are replaced. So now if you have
assigned individual rights, you must grant all the same rights to the new
person. I realize that SQL Server has a GUI for managing the system, but the
rights assignment GUI is one of the worst I have ever seen. For a database with
any large number of objects, this is a cumbersome and frustrating method of
assigning rights.
Set aside the difficulty of using the GUI and assume everything is scripted. Suppose
that you have a script to assign these rights. You merely place the new person's
account in the script and run it and things are set, right? True, but suppose
you create a new table, which usually means that you will also create some
stored procedures or alter others. You now need to add the rights for these
items to your script. Suppose you have just 20 users with 2 different sets of
rights. Now you have at least two commands to run. This process is much more
complicated than assigning the rights to a role. Something that can be much
more easily maintained.
Rule 2: Assigning individual rights leads to an unnecessarily complex rights
assignment/management process.
The last reason for not assigning individual rights is more of a corollary to the
second reason. An unnecessarily complex process is hard on administrators. All
of us are human, and all of us hate to do things that are difficult. While there
are some people who will continue to follow a procedure and adhere to a routine
no matter how dull or inane it seems, most of us will not. Most of us will take
shortcuts, we will circumvent the process, or we will cease to maintain
it.
Rule 3: An unnecessarily complex process will not be
maintained.
So what's the best practice?
]
While building SQL Server 7, the SQL team realized that the single group rule for
users from prior versions was too inflexible and did not meet the needs of many
systems. The security system was enhanced to more closely model the NT network
model where a user can be the member of more than one group and their rights are
the sum of the rights of all the roles of which they are a member. While
redesigning this model, a number of common roles that are often used were
included. For example, the datareader model automatically receives select rights
for all tables in the database.
This model allows an administrator to develop roles that match the rights needs of
various user groups or departments. If a user needs rights from more than one
role, you assign them to multiple roles. This is the best practice for managing
your security. Build a role for each type of access that you have. If new rights
for a few or even a single user are needed, create a new role, assign the rights
to the role and assign the role to the user.
The only catch with designing rights is that the DENY right overrides other rights.
If a user is a member of Role A, which has been granted select rights to table
A, and also a member of Role B which has select rights denied for Table A, they
will not be allowed to select data from Table A.
Conclusions
Good security is essential for any database system. Assigning rights to individuals
is a worst practice that can compromise security by implementing a difficult to
maintain system. Avoid this worst practice by using roles to handle all of your
security needs.
While I do not really see any reason to assign an individual rights, there may be
cases where it is necessary. I would love to hear from any readers who have
cases where they feel individual rights should be assigned. Please respond using
the "Your Opinion" button below and I will be happy to debate the
subject.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also
rate this article.
Steve Jones
©dkRanch.net October 2001
