Home

	Expand Search

Security Administration Guide

Chapter 1: Overview of Security Functions Provided by SQL Server

Chapter 1

SQL Server is a database management system that is targeted for evaluation at the Class C2 criteria. The requirements for the C2 criteria are given by the Department of Defense in DOD 52.00.28-STD, Department of Defense Trusted Computer System Evaluation Criteria (TCSEC), also known as the "Orange Book."

The TCSEC defines a Trusted Computing Base (TCB) to be the collection of computer system protection mechanisms that are responsible for enforcing a security policy. SQL Server's TCB is a composite computing base that includes the operating system platform as a component. The SQL Server's TCB includes the underlying operating system, the server's executable code, internal data such as system databases and catalogs, Backup Server^TM, and isql and bcp (when these utilities are used as administrative interfaces).

Users interact with the TCB via:

• isql, the interactive command line interface for executing Transact-SQL commands.
• bcp, the bulk copy utility, which is used to import and export data into and out of database tables.
• System procedures, which are used to perform many system administration and security tasks.
• Communication through the Tabular Data Stream (TDS) protocol from a client process to SQL Server, and from SQL Server to the client process.

The Major Security Functions Available with SQL Server

SQL Server provides a number of security features that help you to protect sensitive data from inappropriate access and unauthorized disclosure. The major features are summarized in Table 1-1.

Table 1-1: Major security functions available with SQL Server

Security Feature	What It Is-In Short
Discretionary access controls (DAC)	Provides access controls that give object owners the ability to restrict access to objects, usually with the grant and revoke commands. This type of control is dependent upon an object owner's discretion.
Identification and authentication controls	Ensures that only authorized users can log into the system.
Division of roles	Allows you to grant privileged roles to specified users so that only designated users can perform critical management tasks.
Auditing	Provides the capability to audit logins, logouts, server boot operations, remote procedure calls, role changes, privileged commands, system errors, access to databases, tables, views, and stored procedures, and all actions for a specific user.

The following sections provide an overview of each major function and refer you to the chapter where the function is covered in detail.

Discretionary Access Controls

The SQL commands grant and revoke provide discretionary protection to SQL commands and objects. Owners of objects can, at their discretion, grant access to the objects to other users. The object owners can also grant other users the ability to pass the access permission to other users. With SQL Server's discretionary access controls, you can give various kinds of permissions to users, groups, and roles with the grant command. The revoke command permits you to rescind these permissions. The grant and revoke commands give users permission to execute specified commands and to access specified tables, views, and columns.

Some commands can be used at any time by any user, with no permission required. Others can be used only by users of certain status (for example, only by a System Administrator) and are not transferable.

The ability to assign permissions for the commands that can be granted and revoked is determined by each user's status (as System Administrator, Database Owner, or database object owner), and by whether or not a particular user has been granted a permission with the option to grant that permission to other users.

Discretionary access controls are discussed fully in Chapter 6, "Managing User Permissions."

Identification and Authentication Controls

Every user in SQL Server is given a login account with a unique ID. All of that user's activity on the server can be attributed to his or her server user ID and audited.

SQL Server passwords must be six bytes or longer. They are stored in the master..syslogins table in encrypted form. In addition, when you log into SQL Server from a client, you can choose client-side password encryption to encrypt your password before sending it over the network.

Identification and authentication controls are discussed in Chapter 4, "Managing SQL Server Logins and Database Users" and, for remote servers, in Chapter 7, "Managing Remote Servers."

Division of Roles

An important feature in SQL Server is the division of roles. The roles supported by SQL Server enable you to enforce and maintain individual accountability. Various security-related, administrative, and operational tasks are grouped into the following roles:

• System Security Officer, who performs security-related tasks such as:
  • Creating server login accounts, which includes assigning initial passwords
  • Changing the password of any account
  • Granting and revoking the System Security Officer and Operator roles
  • Setting the password expiration interval
  • Managing the audit system
• System Administrator, whose tasks include:
  • Managing disk storage
  • Monitoring SQL Server's automatic recovery procedure
  • Fine-tuning SQL Server by changing the configurable system parameters
  • Diagnosing system problems and reporting them as appropriate
  • Backing up and loading databases
  • Granting and revoking the System Administrator role
  • Modifying and dropping server login accounts
  • Granting permissions to SQL Server users
  • Creating user databases and granting ownership of them
  • Setting up groups (which are convenient in granting and revoking permissions)
• Operator, a user who can back up and load databases on a server-wide basis. The Operator role allows a single user to use the dump database, dump transaction, load database, and load transaction commands to back up and restore all databases on a server without having to be the owner of each one.

These roles provide individual accountability for users performing administrative tasks. Their actions can be audited and attributed to them. Division of roles is discussed fully in Chapter 5, "Roles in SQL Server."

Auditing

A comprehensive audit system is provided with SQL Server. The audit system consists of a system database called sybsecurity and a set of system procedures that allow you to selectively set the audit options you need.

You can choose to audit the following:

• Within a server you can audit logins and logouts, server boots, RPC connections made from other servers, system errors, and execution of commands requiring special roles.
• At the database level, you can audit the use of the grant, revoke, truncate table, and drop commands within a database; the use of the drop and use commands on a database; and references to a specific database from within another database.
• At the user level, you can audit a specified user's attempts to access tables and views, and you can audit the text of commands submitted to the server by a user.
• At the object level, you can audit access to specified tables and views and execution of stored procedures and triggers.
• If you have the correct permissions, you can send ad hoc annotations to the audit trail.

Auditing functions are discussed fully in Chapter 8, "Auditing."