Overview of SQL Server Database Security Concepts


This chapter provides a conceptual introduction to SQL Server security topics. It also touches on how SQL Server security interacts with the functionality available from Access projects, such as connecting to a SQL Server database, creating a new SQL Server database, or making a new table or other database object in a database. Subsequent sections in this chapter present code samples for selected content from this section.

Note  

SQL Server security is a broad topic that can easily consume a book all by itself. This chapter focuses on a subset of security features relating to database access and manipulation. Many security issues, including the Slammer worm outbreak in late January 2003, are best addressed by server administration policies and practices. As of this writing, the definitive Microsoft statement on SQL Server security tools for DBAs is available at http://www.microsoft.com /downloads/details.aspx?familyid=9552D43B-04EB-4AF9-9E24-6CDE4D933600&displaylang=en . By the way, it was the Slammer worm that taught many organizations that an operator of a computer running MSDE 2000 with a Web connection is a DBA.

Authentication

Authentication is the process by which SQL Server recognizes users trying to gain access to a SQL Server instance. SQL Server 2000 permits two authentication modes: Microsoft Windows-only authentication and mixed-mode authentication supporting both SQL Server and Windows logins. In either style, the SQL Server DBA must create a SQL Server login.

Windows-only authentication is the default mode after you initially install a SQL Server 2000 instance. With this mode, users can gain access to a SQL Server only after successfully logging into a Windows operating system, such as Windows NT, Windows 2000, Windows XP, or Windows 2003. This approach simplifies connecting to a SQL Server database because a user does not require an additional login and password for SQL Server. A user can present a Windows login credential, and SQL Server trusts Windows to authenticate the user. With this authentication mode, users can gain access to a SQL Server instance via their individual Windows login account or via a Windows group to which they belong. The DBA must explicitly recognize a Windows user account or a Windows group account in order for the Windows account to gain access to a SQL Server instance. The DBA recognizes a Windows account by assigning a SQL Server login to represent the Windows account.

SQL Server or mixed-mode authentication assigns the DBA more responsibility for managing SQL Server security. This is because the SQL Server DBA uniquely controls the logins for a SQL Server instance. With SQL Server authentication, users can log in with the built-in sa login and other custom SQL Server login accounts created by a SQL Server DBA and connect to a SQL Server instance. Because the sa login has unrestricted permissions in a SQL Server instance, it is important to assign a password to the login so that you can protect your SQL Server instance.

Enabling SQL Server authentication is valid in circumstances where it is convenient for SQL Server DBAs to have exclusive control of logins due to the sensitive nature of database contents or administrative obstacles to coordinating Windows logins with SQL Server logins. There are at least three circumstances when SQL Server authentication is the only option available to a SQL Server DBA. They are:

  • SQL Server is running on a Windows 98 computer

  • Your application is running on a Windows workstation in standalone mode from a network server

  • You do not have a Windows server managing logins to your network

Login and User Accounts

Login and user accounts apply to users as opposed to SQL Server instances. If you think of authentication as the lock for a SQL Server instance and its databases, then login and user accounts are the keys to the authentication locks. SQL Server documentation frequently shortens the name for login and user accounts to logins and users. A login grants access to a SQL Server instance. Therefore, this kind of security object belongs to a SQL Server database server. A user account grants access to a particular database on a SQL Server instance. Any one login can have multiple user accounts associated with it. Generally , a login will have one user account for each database to which it has access. In fact, when you grant database access to a login account, you automatically create a user in the database for that login. User objects therefore belong to databases and not to SQL Server instances.

Login accounts can apply to a SQL Server login without a connection to a Windows login or a SQL Server login granted to a Windows user or a Windows group. The Windows server administrator will create Windows user and Windows group accounts. A Windows group account represents a collection of Windows user accounts. A SQL Server DBA creates a SQL Server login by managing the account name and setting a password and a database password. The DBA also controls the permissions that a SQL Server login can have. Although a SQL Server DBA does not create a Windows user or a Windows group, the DBA must create the SQL Server login account that corresponds to a Windows user or a Windows group. Without this, a Windows user or members of the Windows group have no ability to connect to a SQL Server instance or any database on a SQL Server instance.

After installing SQL Server 2000, you will have several built-in accounts, including the Administrator, sa, and BUILTIN\Administrators login accounts. If you accept the default installation choices, the startup account for the SQL Server instance will be the Windows Administrator account on the computer running SQL Server. The default installation choices map this Windows account to the SQL Server Administrator login. In addition, the default installation choices turn on the Windows-only authentication mode.

The Administrator account belongs to a SQL Server role that grants the login unlimited permissions on a SQL Server instance. The sa login is another login account set up during SQL Server installation. The sa login name stands for Systems Administrator. This login is disabled with the default installation choices, but it can function if you enable mixed-mode authentication.

The BUILTIN\Administrators login account is the third account that is created automatically. Both the Administrator and sa login accounts are standard SQL Server logins, while the BUILTIN\Administrators login account is a Windows group account. This account grants SQL Server access to members in the Windows Administrators group on the computer running SQL Server. As with the Administrator and sa logins, the default installation choices set up the BUILTIN\Administrators login with unlimited permissions on a SQL Server. Since the BUILTIN\Administrators login is for a Windows group, it functions with either Windows or mixed-mode authentication.

You should create logins other than the three automatically created accounts to enable users to perform the tasks they need with a SQL Server instance or one of its databases. You'll learn how to create logins later in this chapter in the "Programming Login and User Accounts" section. All the automatically created accounts can be dropped or have their permissions altered , except for the sa login. These automatically created logins might expose a SQL Server to attack by a hacker. Since you cannot drop the sa login, you should always assign a password to it during installation.

You should use the startup account when you log into SQL Server after installation. If you accept all the default choices during installation, the SQL Server instance grants a login to the Windows Administrator account. This is the startup login. Also, the SQL Server instance has Windows-only authentication. Therefore, you should log into your computer as the Administrator, and choose Use Windows NT Integrated security on the Data Link Properties dialog box. If an installation enabled mixed-mode authentication, then you can also use the sa login. To do this, select the Use A Specific User Name And Password option on the Data Link Properties dialog box. Then enter the sa login as the user name with the password assigned to the sa login during installation (see Figure 13-1). If no password was assigned during installation, then leave the Password text box empty. After opening an Access project by clicking OK, you should immediately assign a password to the sa login. Choose the Tools, Security, Set Login Password command to open the Change Password dialog box. Click the Help button for instructions on using the dialog to assign a password to a login.

click to expand
Figure 13.1: The Data Link Properties dialog box for an Access project ready to connect to the Northwind database with an sa login that has a password.

Notice in Figure 13-1 that sa is the user name. Login names serve as user names in the Data Link Properties dialog box. The sa login has automatic access to any database on a SQL Server instance. User accounts are for logins that do not belong to the sysadmin fixed server role. The sa and other logins automatically created during installation belong to the sysadmin fixed server role, but other user-defined logins may or may not belong to the sysadmin role. These other login accounts are typically created after the installation for those in an organization who require access to one or more databases on a SQL Server instance. One organizational member may require access to just one database, another may need access to two databases, and a third may need access to one database, but one that is different from that of the first user. A login account grants access to a SQL Server instance, and a user account grants access to a specific database on a SQL Server instance.

Roles

Roles and permissions are the two mechanisms for enabling login accounts to perform actions within a SQL Server instance and the databases managed by a SQL Server instance. You can think of a role as a cluster of individual permissions. There are four types of roles. Two of the four are fixed roles specified by SQL Server. Within each of these two roles are individual roles that convey permissions to logins and users that belong to them. Fixed server roles convey different clusters of permissions for a SQL Server instance to logins (not users). Fixed database roles convey different clusters of permissions within a database to users (not logins). Assigning logins and users to these fixed roles is a convenient way to manage which permissions logins and users have, so long as the permissions you need to manage are specified by one of the fixed server or database roles. When this is not the case, then you can create custom roles, which are the third kind of role, and assign specific permissions that meet the requirements of your application. Then you can assign users to these custom roles just as with the fixed database roles.

The fourth kind of role is an application role. This role is different from the other three in that it does not have logins or users associated with it. Instead, an application role has a name, password, and permissions for a specific application. An application role is a gateway into which any user can enter (so long as the user knows the name and password for the application role). This kind of role does not depend on SQL Server user accounts. However, application roles are best demonstrated with the Query Analyzer, a SQL Server client tool.

The fixed server roles target permissions to perform tasks within a SQL Server instance, such as creating a database. There are eight fixed server roles. Because these roles especially target permissions for administering a SQL Server instance, many of them have only passing interest to developers. Nevertheless, it is important to know about them generally, and a few of them can be vitally important. When you are in an environment where you serve as database developer and DBA, then it is useful to learn the full array of fixed server roles. A code sample in the T-SQL Help for Roles section offers a way for learning more about fixed server roles.

The sysadmin, dbcreator, and securityadmin roles are three of the eight fixed server roles that are likely to have special value to database developers. Logins belonging to the sysadmin role have special value to developers serving a dual developer/DBA function within an organization. Members of the sysadmin role can perform any function on a SQL Server instance. This is especially important for managing DBA tasks, but it is convenient for many developer tasks. For example, you cannot create a new SQL Server database from an Access project unless your login belongs to either the sysadmin or dbcreator role. In general, it is good practice to assign the lowest level permission appropriate for a task. Therefore, if a login does not need the more general permissions of the sysadmin role, assign the dbcreator role instead. The securityadmin role is convenient for logins that must manage logins. Logins in the securityadmin role can create new logins, assign logins membership in roles, and assign individual permissions to other logins.

There are nine fixed database roles. These roles convey permissions to perform tasks in or for a database. You assign membership in these roles to users. Users can have membership in multiple roles within a single database. In addition, a single login can have membership in different sets of fixed database roles for two or more different databases. Just as members of the sysadmin roles have unlimited authority within a SQL Server instance so does the db_owner role have unlimited permissions within a database. The db_datareader role grants permission to read all tables and views within a database. The db_datawriter role enables permission to insert, update, and delete rows for any table or view in a database. The db_denydatawriter denies permission to insert, update, or delete from any table or view in a database.

Fixed database roles are likely to affect the ability of those using Access projects to perform typical tasks. For example, you cannot open a table unless you have SELECT permission for the table. One way to gain this permission is through membership in the db_datareader role. Update queries will not work from an Access project unless the login for the Access project has an associated user account with update permission. One way to get this permission is for the user account to have membership in the db_datawriter role.

SQL Server security allows a single user account to belong to multiple roles that can have conflicting permissions. A denied permission always trumps a granted permission. For example, if a single user belongs to both the db_datawriter and db_denydatawriter roles, then the user cannot insert, update, or delete from any table or view in a database. This situation can exist if a user has a login for an individual Windows account, and the individual also belongs to a Windows group with a SQL Server login. This kind of conflict can additionally occur with the granting and denying of individual permissions.

You can learn more about roles from many topics in Books Online. The Roles topic is my favorite topic in Books Online for beginning to grasp security via fixed server and fixed database roles. Do not limit your study of roles to the Roles topic, however. For example, the public Role topic in Books Online introduces the operation of the default role in a database. All users in a database belong to a public role. You can assign permissions to a public role just as you can to any custom or fixed database role. However, since all users belong to the public role, it is good practice to strip it of permissions so that the only way users can receive permissions is through membership in fixed database roles or custom roles that you create for the assignment of permissions.

Note  

Recall from Chapter 11 that Books Online is the official source for Microsoft SQL Server documentation and help. It offers definitive presentations of conceptual issues, code samples for T-SQL and SQL-DMO, as well as coverage of SQL Server administration topics, such as database security.

There are several special user accounts you



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net