A Security Roadmap - Database User Accounts
(Page 6 of 7 )
Typically, the vast majority of users will not use server roles at all. Instead, they will gain access through permissions assigned in the database itself. In order that permissions may be handled at the level of an individual database, each database maintains its own list of database user accounts. These accounts are completely separate from SQL Server login accounts, and the process of granting access to a database is separate from the process of granting access to the server itself.
SQL Server manages user accounts with a table named sysusers. This table identifies each user with a unique user identifier (UID), and each UID has a direct mapping to a server user identifier (SUID) in the syslogins table in SQL Server 6.5, or a security identifier (SID) from the sysxlogins table in SQL Server 7.0 and 2000. SQL Server 6.5 has a concept called aliasing, which is simply the practice of having multiple login identifiers mapped to a single UID (see Chapter 3 for more details). SQL Server 7.0 supports aliasing for backward compatibility, but SQL Server 2000 no longer supports that function. Chapter 4 covers how SQL Server 7.0 and 2000 map login IDs to database user accounts.
Table 1-1. Sample Output from the sysusers Table
uid status name sid password hasdbaccess islogin isntuser
5 14 TimB 0x0105000000000005150... NULL 1 1 1
12 2 FredJ 0xFD40A377E973F140AAB... NULL 1 1 0 Essentially, the mapping of an SUID to a UID in SQL Server 6.5, and an SID to a UID in SQL Server 7.0 and 2000, is a kind of transparent authentication of the user’s access to a database. Once granted, access to the database becomes a seamless part of the overall login process, and the users never need to know that another authentication process occurs each time they move from one database to another.
It may seem unusual to discuss user authentication in a section devoted to authorization but, very simply, if a user has a UID in a database, he has access. Whether or not he has permissions depends on several other criteria, but at the least he has the potential to access the data in the database. Conversely, removing a UID from the database denies all access to its data. This means that user accounts are a kind of authentication that occurs each time a user attempts to access data. Further, this process occurs even if a command references objects in databases other than the current one. Finally, because database access depends on the SUID from syslogins or the SID from sysxlogins, denying login privileges or removing a login altogether effectively eliminates access to all databases at once. This can be an effective way to seal security breaches quickly, without damaging the overall permissions structure in a database.
The discussion of database access is confused further by SQL Server 7.0’s addition of Windows NT groups as a means of gaining login access to the server. Because users can log in by virtue of their membership in a group, and not their own personal accounts, their personal SID does not need to be recorded anywhere in SQL Server so long as the SID corresponding to one of their groups is there. In this case, the users’ total database permissions will be those granted to their Windows NT groups that have access to the database. If that was not enough, even more confusion arises out of the fact that the Windows NT groups in the database do not have to be the same groups a user uses to log into the server.
Database Roles Like the default server roles, there are default database roles that have permissions that cannot be granted independently. Unlike server roles, some of the permissions in the sysusers table can be granted directly to a given user. Additionally, you can create your own roles and assign permissions to them if the built-in database roles do not have the proper combination of permissions. The only thing you cannot do is place a role within another role, much like a Windows NT local group cannot be a member of another local group.
The following is a list of all the built-in database roles:
- db_owner: This role is mostly self-explanatory. Members of this group gain all the rights and privileges of the database owner, which is to say just about complete control over everything in the database. Database object owners (dboo—yes, that is the acronym Microsoft uses) can deny database owners some types of access, but the owners can always take ownership of the object and grant whatever permissions they want. Other than that minor inconvenience, members of db_owner have no limitations on what they can do with the objects in the database.
public: This is the one role to which you need never grant membership, because all users automatically have membership just by being listed in sysusers. The main security concern is that anyone granted access to the database also automatically gains the permissions granted to public. As you will see later, you will end up granting to the public role only the permissions that everyone in the database should have. With careful planning, you can minimize the risk. Alternatively, you can create a user-defined role, granting the permissions you would normally grant to public and not granting or revoking any permissions to public itself. Besides this one concern, public can be a very useful role for decreasing the amount of work you must do to grant permissions in a database with many users.
db_accessadmin: The “access” part of db_accessadmin refers to database access, and members of this role can add and remove database user accounts.
- db_securityadmin: This role controls the management of user-defined roles in the database. Members can create and remove user-defined roles, as well as manage the users in those roles. Neither this role nor db_accessadmin can grant its members any permissions to the data itself, although db_securityadmin members can, of course, add themselves to any user-defined role and gain access through its permissions.
- db_ddladmin: This is the workhorse of the built-in roles. Members can execute the GRANT, DENY, and REVOKE statements as well as create foreign key references between tables. They can also perform some simple operations on objects, such as renaming them or changing their owners. Note that members of this role cannot actually add themselves to other roles; they can only grant permissions to those roles.
- db_backupoperator: This role has permissions that allow its members to perform database backups and the DBCC commands that check the integrity of the database before a backup starts. Interestingly, members of this role cannot restore a backup; that privilege is reserved for members of the sysadmin role. Members cannot view the data they are backing up either, so you can safely grant membership in this role to just about anyone who knows how to back up the data.
- db_datareader: This role has SELECT permissions on all tables in the database.
- db_datawriter: This has INSERT, UPDATE, and DELETE permissions on all tables.
- db_denydatareader and db_denydatawriter: These roles are the reverse of their counterparts and specifically deny SELECT or INSERT, UPDATE, and DELETE permissions, respectively, to their members. The primary benefit of these roles is that they allow you to restrict access to the data to users who might otherwise have access they should not have by virtue of their other role memberships.
Though not strictly a database role, the database owner account, named dbo, has special privileges in a database much like the sa account has in the server as a whole. Like sa and the sysadmin role, dbo exists separately from the db_owner role. Unlike sa, dbo does not gain all its permissions from membership in the db_owner role; it has its own permissions outside the database role structure. Accordingly, only dbo may add members to the db_owner role, and no member of that role may remove dbo from it or grant ownership of the database to another user.
This unique status in the database makes the dbo members second only to members of the sysadmin role in terms of what they can do within SQL Server. The logical conclusion is that dbo in a production environment should be someone who has a high degree of both SQL Server expertise and trust within the organization. In most cases, it should be one of the senior-level server administrators because of the amount of damage a mistake made out of ignorance can cause.
 This chapter is from SQL Server Security Distilled, by Morris Lewis (Apress, 2004, ISBN: 1590592190). Check it out at your favorite bookstore today. Buy this book now.
|
Next: Database Owner Rights in SQL Server 6.5 >>
More Windows Security Articles
More By Apress Publishing
/ 2 
