Home

	Expand Search

What's New in SYBASE SQL Server Release 10.0?

Chapter 1: New Features in SQL Server Release 10.0

New Security Features

New Security Features

SQL Server Release 10.0 provides features targeted at the C2 level of trust. Many of the security features provide new flexibility for SQL Server users who do not need all of the C2 functionality. The major features are:

• New system administration roles

• New user identification and authentication features, including password encryption

• A complete system for auditing activity on SQL Server

System Administration Roles

In previous releases, SQL Server allowed only one login, "sa", to perform most system administration tasks. SQL Server Release 10.0 recognizes the following security-related operational and administrative roles:

• System Administrator: required for tasks such as manipulating disk space and memory usage, granting and revoking permission to execute create database statements, running diagnostic and repair functions that read data pages or recover data or indexes in a controlled manner, granting and revoking the System Administrator role, and modifying, dropping, and locking server login accounts.

• System Security Officer: required for security-related tasks such as creating server login accounts, changing passwords, granting and revoking roles, configuring the password expiration interval, and managing the audit system.

• Operator: used for backing up and restoring databases.

These roles can be granted to and revoked from individual login accounts in SQL Server, and a login account may possess more than one role. Roles can be granted permissions on objects and commands, similar to groups. Details on roles can be found in Chapter 2, "Roles in SQL Server" , in the System Administration Guide .

When Release 10.0 of SQL Server is installed, it still has the default "sa" account, which has System Administrator, System Security Officer, and Operator roles enabled. For greater accountability for the highly privileged users in your system, it is recommended that you create individual login accounts for users who are to be granted these privileges, grant them their roles, and then lock the "sa" account. If you have automated scripts or programs that log into SQL Server as "sa", see "Using Roles in SQL Server" for more information.

User Identification

There are now a variety of mechanisms SQL Server uses to positively identify an individual user and to enforce accountability and security, such as:

• Login account locking makes it possible to lock a user's account without dropping the user and the user's objects from all databases.

• A minimum password length of 6 bytes makes passwords more secure. (This does not affect existing user passwords, but all new or changed passwords must be at least 6 bytes.).

• Passwords are stored in master..syslogins in encrypted form.

• You can enable optional system-wide password expiration.

• You can enable optional client-side password encryption.

• Recovery mechanisms are provided in the case of lost or expired passwords.

New sections in Volume 1 of the SQL Server Reference Manual called "Roles" and "Login Management" provide an overview of the system procedures and other commands used to manage these features.

Auditing

SQL Server can be configured to provide an audit trail for events such as:

• Server logins and logouts

• Use of any commands that require a special role

• Use of commands that reference a specified object or database

• Deletion of objects

• Execution of stored procedures and triggers

• Any actions performed by a specified user

The audit system includes a new database, sybsecurity , which contains the audit trail in a system table called sysaudits . Auditing is described in Chapter 16, "Auditing" , in the System Administration Guide .