Databases Reference
In-Depth Information
Not only can you allow access, you can block access by explicitly denying
permissions to a user or role. This overrides any permissions allowed. The use
of denied permissions should be limited because they can make it difficult to
identify and correct access problems. When possible, simply do not allow access
as a way of preventing access to a resource.
When setting object permissions, you can set the permissions by individual
object or by object type. Whenever possible, keep assignments as general as pos-
sible. For example, if a role needs to be able to view the contents of all tables,
assign the permission through the type (table) rather than individually for each
table. SQL Server 2005 also organizes objects as belonging to an organizational
schema. Each object belongs to one and only one schema. However, a schema
can contain any number of objects. You have the option of assigning permissions
at the schema level and having the permissions apply to all objects contained in
that schema.
Minimizing Permissions
This guideline's relatively easy to understand. Don't give users a greater level of
permission than they need to do their jobs. Users who need to only view data
should be allowed to run SELECT statements, and only SELECT statements,
against data sources. Data entry personnel will need to be able to insert, and
possibly modify, as well as view data.
You need to identify database users and their access permission requirements
during the design phase. Use this to make permission assignments during imple-
mentation. If you don't give the users, or applications, the permission they need
you will find out almost immediately. If you give them more permission than
they need, it's likely that someone will eventually figure that out. You, however,
might not find out until after the user has done damage to the data.
Auditing Changes
It's a good idea to log changes made to users and permissions through auditing.
This gives you a trail to follow should you have problems in the future. It also gives
you a way of finding out if someone is granting permissions without authorization.
Consider this possibility. A worker in the warehouse slips items out the back
without anyone knowing and sells them. In order to hide this activity, the worker
needs to be able to directly change inventory quantities so the thefts are less
likely to be noticed during a physical inventory. The worker finds an administrator
willing to work with him or her for a percentage of the profits. The administrator,
looking to cover his or her own tracks, gives the worker permission to make the
changes. The administrator is trying to keep as far out of the direct loop as pos-
sible. If you are auditing permission changes, you're more likely to discover the
scheme through the permission change, or at least discover it more quickly, than
through the unexplained changes in inventory quantities.
