Lesson 4: Overview of SQL Server Security

[Previous] [Next]

SQL Server validates users at two levels: login authentication and permissions validation. Login authentication ensures that the user is a known, valid user of the SQL Server. Permissions validation checks that the user is authorized to use a particular statement or object in a database. This lesson describes these two levels of security.

After this lesson, you will be able to

  • Describe login authentication
  • Describe permission validation
  • Describe SQL Server database users and roles

Estimated lesson time: 30 minutes

Login Authentication

A user must have a login account to connect to SQL Server. SQL Server recognizes two login authentication mechanisms—SQL Server authentication and Windows NT authentication—both of which have a different type of login account (see Figure 1.11).

click to view at full size.

Figure 1.11 Two types of login authentication

SQL Server Authentication

When using SQL Server authentication, a SQL Server system administrator defines a SQL Server login account and password. Users must supply both the SQL Server login and the password when they connect to SQL Server.

Windows NT Authentication

When using Windows NT authentication, a Windows NT user or group account controls user access to SQL Server—a user does not provide a SQL Server login account when connecting. A SQL Server system administrator must grant either the Windows NT user account or the Windows NT group account access to SQL Server.

Authentication Modes

When SQL Server is running on Windows NT, a system administrator can specify that it run in one of two authentication modes: Windows NT Authentication Mode or Mixed Mode.

NOTE
Previous versions of SQL Server supported three authentication modes. In SQL Server 7, Standard Mode is no longer supported.

Windows NT Authentication Mode

When the SQL Server is using Windows NT Authentication Mode, only Windows NT authentication is allowed. Users cannot specify a SQL Server login account. This mode is not supported by a SQL Server installed on Windows 95 or Windows 98.

Mixed Mode

When the SQL Server is using Mixed Mode, users can connect to SQL Server with Windows NT authentication or SQL Server authentication.

NOTE
Clients connecting to a server running in Windows NT Authentication Mode must use a Windows NT_authenticated connection. Clients connecting to a server running in Mixed Mode can choose an authentication mechanism.

Permission Validation

SQL Server accepts Transact-SQL statements after a user's login has been successfully authenticated. Each time a user sends a statement, SQL Server checks that the user has permission to carry out the action requested by the statement. If the user has permission, the action is carried out; if not, an error is returned to the user. Figure 1.12 illustrates this process.

click to view at full size.

Figure 1.12 Permission validation in SQL Server

NOTE
In most cases, users will be interacting with an application user interface, unaware of the Transact-SQL statements that their actions are generating.

Database User Accounts and Roles

User accounts and roles, which identify a successfully logged-on user within a database, are used to control ownership of objects. Permissions to execute statements and to use objects in a database are granted to users and roles. Each user account is mapped to a SQL Server login (see Figure 1.13).

click to view at full size.

Figure 1.13 Database users and roles

Database User Accounts

The user accounts used to apply security permissions are mapped to Windows NT user or group accounts or to SQL Server login accounts. User accounts are specific to a database and cannot be used in other databases.

Database Roles

Roles enable you to assemble users into a single unit to which you can apply permissions. Both server-level and database-level roles exist. A user can be made a member of more than one role.

SQL Server provides predefined server and database roles for common administrative functions, so that you can easily assign a selection of administrative permissions to a particular user simply by making the user a member of the appropriate roles. You can also create your own user-defined database roles.

NOTE
Roles replace the SQL Server version 6.5 concepts of aliases and groups.

Fixed Server Roles

Fixed server roles provide groupings of administrative privileges at the server level. They are managed independently of databases. Examples of fixed server roles are roles for system administrators, database creators, and security administrators.

Fixed Database Roles

Fixed database roles provide groupings of administrative privileges at the database level. Examples of fixed database roles are roles for backing up and restoring a database, for security administrators, for reading data, and for modifying data.

User-Defined Roles

You also can create your own database rol s to represent work performed by a group of employees in your organization. You do not have to grant, revoke, or deny permissions for each person. If the function of a role changes, you can easily change the permissions for the role, and the changes apply automatically to all members of the role.

Lesson Summary

SQL Server accepts connections from users that it can authenticate or those that have already been authenticated by Windows NT. After user logins are authenticated, they are mapped to user accounts and roles in one or more databases. A login must be mapped to a user account in a database in order to use that database. A login cannot be mapped to more than one user in a database but can be a member of many roles. Permissions are granted to user accounts and roles in each database.



Microsoft Press - Microsoft SQL Server 7. 0 System Administration Training Kit
Microsoft SQL Server 7.0 System Administration Training Kit
ISBN: 1572318279
EAN: 2147483647
Year: 1999
Pages: 100
Authors: Microsoft Press
BUY ON AMAZON

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