Databases Reference
In-Depth Information
Because validation is based on the login, two-tier systems typically do not
associate passwords with users. Common features of users typically include:
▲
User name
▲
Associated login
▲
Database access permissions
▲
Object-level permissions
It's common to have several data users associated with a single login, one
user for each database to which you want to give the login access. Be careful to
create only those logins and users you actually need in order to help minimize
the possibility of unauthorized access.
In a single-tier system, you have one account acting as the security principal
for both server and database access. In this type of system, the account will have
an associated password, server-level permissions, and database-level permissions,
including permissions assigned at the database object level.
Because of the differences you will find between the various manufactur-
ers' solutions to security requirements, we will focus on SQL Server 2005 as
a representative example of one manufacturer's choices. Throughout this dis-
cussion, keep in mind that SQL Server security is based on both logins and
users, and that logins and users are distinctly different security objects with
different security management roles.
11.2.1 Controlling Server Access
One of the decisions you make during SQL Server installation is the authenti-
cation type, choosing either
Windows authentication
(with Windows user
accounts used for authentication) or
mixed authentication
(supporting SQL
Server authentication and Windows authentication).
SQL Server authentication
is based on logins and passwords stored with the database server and authenti-
cated by the database server. What you are choosing is the type of logins that
your server will support. You can change your selection at any time through the
server properties, as shown in Figure 11-7.
Windows authentication support is not unique to SQL Server. Windows
authentication integrates database server security (or other server application
security) with Windows and Active Directory domain security.
Active Directory
is a Microsoft directory service that supports a full-featured network security envi-
ronment organized around security boundaries known as
domains.
Login to the
server is granted to Windows and domain user accounts and groups. Authoriza-
tion is based on the user account you used when you logged in at your computer.
In order to log in at the database server, either your Windows user account must
be authorized as a login account or the account must belong to a Windows group
that is authorized as a login account. When creating the account, you specify the
