Database Roles
Within each database, SQL Server does have fixed database roles,
ones which are standard and included in every database. These differ from the
database roles you can create and use yourself in that they have pre-assigned
permissions. The fixed database roles are:
- db_owner
- db_securityadmin
- db_accessadmin
- db_backupoperator
- db_ddladmin
- db_datareader
- db_datawriter
- db_denydatareader
- db_denydatawriter
db_owner
We'll start with the biggest role: db_owner. A member of the
db_owner role can do anything inside the database. Now there is a difference
between a member of the db_owner role and the dbo user. That difference is that
if someone maps into the database as the dbo user, that person bypasses all
security checks. An example of this is anyone who is a member of the sysadmin
fixed server role. They map in as dbo. And as a result, they don't receive
security checks.
If a user is not dbo but is a member of the db_owner role, it does
receive a security check. Of course, unless you've explicitly used DENY to
block access, that user can do what he or she wants. The DENY stops them cold
(it does not stop dbo). However, a member of the db_owner role could remove the
DENY, so effectively that person can do anything, even if you put roadblocks in
place. Therefore, the db_owner role should be given out only when necessary.
Some applications will require it, which is a headache, but rarely will actual
people need it (unless the application is connecting using their credentials).
So you should be able to keep a reasonable tight control over this role. Like
sysadmin, which is returned as a member of every fixed server role if you use
the IS_SRVROLEMEMBER() function, if you query for someone who is a member of
the db_owner role to determine if that user is a member of any other fixed
database role, it will return true, even if the user is not explicitly a member
of that role. For instance, a user who is a member of db_owner but who is not a
member of db_securityadmin will still return a 1 if you execute the following
query:
SELECT IS_MEMBER('db_securityadmin');
Things to remember:
- The db_owner
role allows a user to do anything within the database.
- DBAs who are
already members of the sysadmin fixed server role come in as dbo and don't
need this role explicitly granted to them.
- Normal users
should not be a member of this role.
- Applications
might require their user account to be a member of this role.
db_securityadmin
Like the securityadmin fixed server role, the db_securityadmin
fixed database role manages security. In this case, it manages role membership
(with the exception of db_owner) as well as permissions on securables. As a
result, it's another role you want to keep a close eye on. Generally speaking,
I've not seen a lot of folks use this role. Typically the DBAs manage security
within the database and they're already coming in as dbo. There may be some
rare instances where it would be used, but I would flag those as exceptions.
Therefore, if you see any members of this role within a database, it's worth
checking out.
- The
db_securityadmin role can manage role membership and permissions on
securables.
- Again, since
DBAs usually manage security and are usually coming in as dbo, this role
is little used.
- Normal users
should not be a member of this role.
- Applications
should tend not to need this role.
- Since it's
little used, you should audit its membership for exceptions.
db_accessadmin
The db_accessadmin role also manages security, but handles access
to the database, as the name implies. The db_accessadmin role grants, denies,
or revokes permission to enter the database for logins. Combined with
db_securityadmin, and you can completely manage security into and throughout
the database. Like db_securityadmin, though, access into the database is
usually handled by DBAs. If they aren't members of the sysadmin fixed server
role, they are members of the securityadmin fixed server role. As a result,
this role should also be rarely used.
- The
db_accessadmin role can allow access into or block access to the database
for logins.
- Again, since
DBAs usually manage security and have an appropriate server-level role,
this role is little used.
- Normal users
should not be a member of this role.
- Applications
should tend not to need this role.
- This is another
role you should audit for membership exceptions.
db_backupoperator
The db_backupoperator allows a member of the role to take backups
of the database. However, it's only going to allow native backups, as in the
standard backups through SQL Server itself. If you're using a third party
product, chances are it is usually the methods which allow for high speed
backups. Unfortunately, these methods require the login executing them to be a
member of the sysadmin fixed server role. As a result, this role tends to be of
limited usefulness. Add to it that you're backing up to a local drive, and it's
rare to see a non-DBA having this level of access, even in a development
system. Because of all these things, this is another role that is typically not
used much.
- The
db_backupoperator role allows a user to take backups of the database.
- Most 3rd party
backup utilities utilize methods that require sysadmin rights, which this
doesn't give.
- Another role
that is little used because this functionality is usually handled by DBAs
or a service account.
- Normal users
should not be a member of this role.
- Applications
should tend not to need this role, though I have seen exceptions.
db_ddladmin
The db_ddladmin is another powerful role because it allows a user
to create, drop, or modify any objects within a database, regardless of who
owns it. So a user could alter a stored procedure owned by dbo, for instance.
This role is sometimes given to developers on non-production systems as they
built custom applications. However, there is typically no reason anyone should
be a member of this role on a production database. One thing the db_ddladmin
does not do is allow the user to alter permissions on the objects. So a member
of this role can create or modify the object, such as a stored procedure, but
not alter the permissions on it unless he or she is the owner. So, for
instance, a member of this role could create a stored procedure in a schema
owned by dbo, but couldn't grant the ability to execute it.
- The db_ddladmin
role can create, drop, and alter objects within the database, regardless
of who the owner is.
- The db_ddladmin
role cannot alter security.
- It is not
unusual to grant this role to developers in a non-production environment.
- Normal users
should not be a member of this role.
- Applications
should not need this role.
- No one should
normally be a member of this role on a production database.
db_datareader
The db_datareader role allows a user to be able to issue a SELECT
statement against all tables and views in the database. DENY for a user (or a
role the user is a member of) will still block the SELECT, however. But if
there are no permissions set, whatsoever, the user will have the ability to
SELECT against the table or view. The catch with this role is that the
permission is implicit. That means if you query sys.database_permissions, you
will not see any permission granted, either to the db_datareader role or
directly to the user. Therefore, if you need to audit for everyone who has
SELECT access to particular tables in a database, you'll have to query the
membership of this group via the use of sp_helprolemember:
EXEC sp_helprolemember 'db_datareader';
It is not unusual to see the db_datareader role used in databases.
It's an easy way to grant SELECT permissions to everything without having to
worry about it. However, due to the fact that it uses implicit permissions, I
prefer to create a user-defined database role and explicitly grant permissions.
With that said, here are things to remember:
- The
db_datareader role gives implicit access to SELECT against all tables and
views in a database.
- In SQL Server
2005 and up, an explicit DENY will block access to objects.
- It is not
unusual to see this role used in production for developers.
- It is not
unusual to see this role used in production for normal users.
- Applications
will occasionally need this role.
- Creating a
user-defined database role and explicitly defining permissions is still
preferred over the use of this role.
db_datawriter
The db_datawriter role is like the db_datareader role in that it
gives implicit access to tables and views within a database. It also can be
blocked by an explicit DENY for the user or for a role the user is a member of.
Unlike db_datareader, however, db_datawriter gives INSERT, UPDATE, and DELETE
permissions . Again, since the permission is implicit, you will not see these
rights show up in sys.database_permissions. And like with db_datareader, you'll
have to check the membership of this role to determine actual permissions in
the event of an audit.
- The
db_datawriter role gives implicit access to INSERT, UPDATE, and DELETE
against all tables and views in a database.
- In SQL Server
2005 and up, an explicit DENY will block access to objects.
- Typically
developer are not members of this role in production unless all users are.
- While less
common than with db_datareader, it is not all that unusual to see this
role used in production for normal users.
- Applications
will occasionally need this role.
- Creating a
user-defined database role and explicitly defining permissions is still
preferred over the use of this role.
db_denydatareader
Unlike the previous two roles, db_denydatareader denies access. In
this case, the db_denydatareader is the same as having a DENY for SELECT on all
tables and views in the database. Because DENY trumps everything else, this is
not a role I've seen used frequently. If there are no permissions for a given
user on an object, such as the user has no SELECT permissions on a table, then
SQL Server blocks access. Therefore, if a user doesn't have SELECT permission
on TableA, then the user cannot successfully issue a SELECT query against
TableA. An explicit DENY is not needed. And since this affects all tables and
views, that adds to the reason this database role is typically not used. And
like db_datareader and db_datawriter, the DENY is implicit, meaning you'll have
to query for membership in this role to determine who is affected.
- The
db_denydatareader role is denied access to SELECT against any table or
view in the database.
- Typically this
role is not used.
- The DENY is
implicit.
- Creating a
user-defined database role and explicitly defining permissions is still
preferred over the use of this role.
db_denydatawriter
Wrapping up our list of roles is db_denydatawriter. The
db_denydatawriter has an implicit DENY on INSERT, UPDATE, and DELETE for all
tables and views in the database. Again, this is not a role that sees much use,
for the same reasons as db_denydatareader.
- The
db_denydatawriter role is denied access to INSERT, UPDATE, or DELETE
against all tables and views in the database.
- Typically this
role is not used.
- The DENY is
implicit.
- Creating a
user-defined database role and explicitly defining permissions is still
preferred over the use of this role.
Comments
Post a Comment