SECURITY

SQL Server 2005 Security : SQL Server 2005 Security Overview

2/27/2013 8:27:10 PM

1. Four Themes of the Security Framework

SQL Server 2005 security is organized around four themes: Secure by Design, Secure by Default, Secure by Deployment, and Communications.

Secure by Design

Security has been a design consideration in all previous versions of SQL Server. With SQL Server 2005, the product development group made sure that everyone was on the same page when it came to security. The entire product team went through mandatory security training, and threat models were written and reviewed for all components of all the features within the product. In addition, a massive effort was carried out to review code with respect to security for the entire product. Microsoft takes security very seriously, and designers of features within SQL Server have made security a top consideration in the final design.

Secure by Default

The Secure by Default effort is one of the most notable areas of the Security Framework that SQL Server users will experience. You can experience it by simply installing SQL Server with the default options. Users of previous SQL Server versions will notice that services such as SQL Server Agent are off by default. In addition, certain features such as xp_cmdshell and OPENROWSET queries are disabled. This “off by default” effort attempts to minimize the surface area for attack, and the effects of this can be seen throughout the product.

Secure by Deployment

Perhaps one of the most challenging issues with SQL Server is effective deployment in a production environment. With so many different configurations and features, it can be difficult for administrators to keep on top of the latest patches and best practices. SQL Server 2005 is now a part of Microsoft Update to help alleviate the pain of determining the latest patch to apply.

Communications

Even before SQL Server 2005 was released to the public, a plethora of technical information was already available in various forms. White papers, Webcasts, and active newsgroups educated and assisted beta customers with the product. Today, most of these Webcasts and white papers have been updated and provide rich educational content.

All editions of SQL Server 2005 include security features that help users protect their data.

  • Overview of security, including authentication and authorization

  • User-schema separation

  • Encrypting data within the database and while in transit

  • Protecting SQL Server 2005


2. SQL Server 2005 Security Overview

If you are already familiar with the concepts of users and roles and SQL Server logins. But for those who aren’t, we’ll provide a quick explanation. The concepts of users and roles exist both in the Microsoft Windows world and in SQL Server. In an abstract sense, they are referred to as principals. Principals are entities that can request resources from the application or an operating system. In the case of Windows, principals are entities such as Domain Users and Groups, and Local Users and Groups. In SQL Server, these entities are logins and server roles. Within a database, these entities are database users, database roles, and application roles, to name a few.

So what can we do with entities? Chances are, you have an object such as a file or a database table that you want to allow access to. These objects, or securables, are the resources to which the authorization system regulates access. Some securables can be contained within other securables, creating nested hierarchies called scopes that can themselves be secured. The securable scopes in the SQL Server Database Engine are server, database, and schema. Every securable in SQL Server has an associated permission that can be granted to a principal. Figure 1 shows a graphical representation of the principal-securable-permission model.

Figure 1. Principal-securable-permission model

SQL Server Logins

Now that we have discussed the big picture of SQL Server security and how it relates to Windows security, it is time to get into SQL Server–specific areas. To connect to SQL Server, you need a login. This login can be a combination of a custom user name (such as Login1) and a complex password. Alternatively, you can add an existing Windows account as a login to SQL Server and forgo the creation of a separate username and password. Thus, SQL Server supports two kinds of logins: Windows logins and SQL Server logins, respectively.

Logins themselves have no access to any specific database within SQL Server; they basically allow for connection to SQL Server. In addition, logins are the entities that can be granted server-wide permissions to perform certain actions. These actions are bundled into server roles such as sysadmin, diskadmin, and dbcreator (to name a few). Table 1 shows the list of server roles and their corresponding functions.

Table 1. SQL Server 2005 Fixed Server Roles
Server RoleDescription
sysadminMembers can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role. The SQL Server service account is also a member of this role.
dbcreatorMembers can create, alter, drop, and restore any database.
diskadminThis role is used for managing disk files. Most of the capabilities relate to adding and removing backup devices.
ProcessadminMembers can terminate processes that are running in an instance of SQL Server. This role is useful if you want to give someone the ability to kill a long-running query or an orphaned connection.
securityadminMembers can manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions as well as database-level permissions. They can also reset passwords for SQL Server logins. This role has no rights to assign database permissions. If you want securityadmin members to be able to do this, you must make their logins part of the db_accessadmin fixed database role for the specific database.
BulkadminMembers can run the BULK INSERT statement. Membership in this role still requires that non-sysadmins have access to the object being updated.
serveradminMembers can change server-wide configuration options and shut down the server.
setupadminMembers can add and remove linked servers and also execute some system stored procedures.
publicAll valid SQL Server logins are members of the public role.

Database Users

Logins can be created using SQL Server Management Studio or via the T-SQL statement CREATE LOGIN. After you create a login, the natural desire is to then grant it access to a particular database. Databases themselves have their own set of roles that define specific access and actions that users of these roles can take. Before we can go ahead and give a login access to a database, we must create a database User for this login. Again, you can create a database user by using SQL Server Management Studio or via the T-SQL statement CREATE USER.

Once you create a database user, you can optionally include it in one of the database roles. Table 2 lists the roles that all databases have.

Table 2. SQL Server 2005 Fixed Database Roles
Database RoleDescription
db_accessadminMembers can add or remove access for Windows logins, Windows groups, and SQL Server logins.
db_backupoperatorMembers can back up the database.
db_datareaderMembers can read all data from all user tables.
db_datawriterMembers can add, delete, or change data in all user tables.
db_ddladminMembers can run any Data Definition Language (DDL) command in a database.
db_denydatareaderMembers cannot read any data in the user tables within a database.
db_denydatawriterMembers cannot add, modify, or delete any data in the user tables within a database.
db_ownerMembers can perform all configuration and maintenance activities on the database, including dropping the database.
db_securityadminMembers can modify role membership and manage permissions.

There is a special role that sysadmins cannot explicitly give users permissions to; it is known as the public role. All database users that are created are included in the public role. This role captures all default permissions for users in a particular database. It cannot have users, groups, or roles assigned to it because everyone belongs to this role by default. This role cannot be dropped. Thus, to protect against unauthorized data access, you should minimize the permissions granted to the public role. Instead, grant permissions to other database roles and to user accounts associated with logins.

The Guest User Account

On the topic of unauthorized data access, it is important to note a special user account available in SQL Server called guest. This account is created by default on new user-defined databases but does exist in master and tempdb. However, the guest account is disabled by default, which means it does not have any access within the database. The guest account allows a login without a user account to access a database. A login assumes the identity of the guest user when all of the following conditions are met:

  • The login has access to an instance of SQL Server but does not have access to the database through his or her own user account or via a Windows group membership.

  • The database contains a guest user account.

  • The guest account is enabled in the database.

You can apply permissions to the guest user account just as you can any other user account. But you should avoid using the guest account because all logins without their own database permissions obtain the database permissions granted to this account. If you must use the guest account, grant minimum permissions.

Other  
 
Top 10
Review : Sigma 24mm f/1.4 DG HSM Art
Review : Canon EF11-24mm f/4L USM
Review : Creative Sound Blaster Roar 2
Review : Philips Fidelio M2L
Review : Alienware 17 - Dell's Alienware laptops
Review Smartwatch : Wellograph
Review : Xiaomi Redmi 2
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8