Before we dive into the
concepts of authentication and authorization, it is important to discuss
a new feature in SQL Server 2005 called Endpoints. In previous versions
of SQL Server, clients could connect via TCP, Named Pipes, Shared
Memory, and VIA. As long as one of these protocols was enabled on the
server and the user had a valid login, the connection was accepted. SQL
Server 2005 introduces a separation of this behavior via Endpoints.
Endpoints can be considered a point of entry into
SQL Server. Administrators can create an endpoint for not only TCP,
Named Pipes, Shared Memory, and VIA, but also for HTTP. Once an endpoint
is created, you can restrict access so users can connect only via a
certain endpoint type. For example, you might create a login called
Login1 and grant access to the HTTP endpoint and deny access to all
other endpoints (TCP, Named Pipes, Shared Memory, and VIA). In this
state, Login1 can access SQL Server only via the HTTP endpoint; it
cannot directly connect to SQL Server via TCP or any other protocol. To
see how this endpoint verification affects authentication, let’s
consider the process of making a client connection.
How Clients Establish a Connection
If a TCP client wants to make a connection to
SQL Server, it must first know which port to connect to. In previous
versions of SQL Server, there was always a thread waiting on UDP port
1434. Its purpose was to give back details on all of the instances of
SQL Server that were running, as well as the port numbers of those
instances. All a client had to do was make a connection to this port and
determine which port it wanted to connect to, given a specific instance
of SQL Server. This process generally worked until hackers found a way
to launch a Denial of Service (DoS) attack on SQL Server by continuously
sending packets to this port requesting the enumeration. Because this
process of enumerating was part of the SQL Server service, the “SQL
Slammer” worm virus created some headaches for SQL Server users. In SQL
Server 2005, this functionality has been pulled out into a separate
service called the SQL Server Browser service. Now you can turn this
functionality on and off without touching the SQL Server service itself.
Note
You can mitigate the DoS
issue with the browsing service if you block the 1434 port on your
firewall. However, if you are concerned about intranet attacks, you
might want to consider not running this service and instead explicitly
passing the port numbers on the connections strings. |
After
the network connection request and pre-login handshake has been made,
SQL Server must authenticate the user to make sure he has access to
begin with. Figure 1 depicts the authentication process.
At this point, the service accepts the login
credentials supplied by the user and attempts to validate them. If
successful, the login is authorized against the endpoint corresponding
to the type of connection made to SQL Server. In this example, the login
is checked to see if it has been granted CONNECT permissions to the TCP
endpoint. If this is true, the authentication process proceeds;
otherwise, the connection fails at this point.
Note
By default, new logins are automatically granted CONNECT permissions to the TCP endpoint. |
Once the login has passed the endpoint check,
SQL Server switches to a database context (the default database
specified for the login or specified in the connection string). It then
attempts to authenticate the login as a user of that database. If this
is successful, the connection succeeds; otherwise, it fails. Now that a
database context is established and the login has been authenticated,
the user can perform his work against the server.
Password Policies
In the Windows world, administrators can set
login expirations and enforce password policies (for example, requiring
passwords to be a certain length and contain a mixture of special
characters). Traditionally in SQL Server, SQL logins never respected
these global policy settings. In SQL Server 2005, both
Windows-authenticated and SQL logins obey the group policy settings
defined in the Windows domain.
Note
Password policy enforcement is available only in SQL Server running on Windows Server 2003 and later. |
To help illustrate password policies, let’s
define a minimum password length using the Local Security Settings
applet located in the Administrative Tools folder of the Control Panel.
This tool is shown in Figure 2.
Using this tool, we can change a variety of
parameters. For this example, we will change the minimum password length
to eight characters. Once this change has been made, we can proceed
with creating our new login.
Instead of using sp_addlogin, as previous versions of SQL Server did, SQL Server 2005 has a new DDL for creating logins: CREATE LOGIN.
CREATE LOGIN foo
WITH PASSWORD='123',
CHECK_POLICY=ON,
CHECK_EXPIRATION=ON
When this command is executed, it will fail because our password has only three characters and we specified a minimum of eight.
Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet
policy requirements because it is too short.
The new system view sys.sql_logins returns
information about the login such as whether the policy and expiration
are set, but if you want additional information regarding the login,
such as how many bad passwords for this login were submitted, you can
use the LOGINPROPERTY built-in function, as shown here.
DECLARE @name varchar(20)
SET @name='foo'
SELECT LOGINPROPERTY( @name, 'PasswordLastSetTime' ) AS PasswordLastSetTime,
LOGINPROPERTY( @name, 'IsExpired' ) AS IsExpiried,
LOGINPROPERTY( @name, 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( @name, 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( @name, 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( @name, 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( @name, 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( @name, 'HistoryLength' ) AS HistoryLength,
LOGINPROPERTY( @name, 'PasswordHash' ) AS PasswordHash
User-Schema Separation
Imagine
an employee in an organization who is a heavy SQL Server user. She has
created many tables and stored procedures, and then suddenly one day she
is let go by the company. As a database administrator, your task is to
reassign ownership of all the objects that she created and owned. In
previous versions of SQL Server, this was a tedious task. In SQL Server
2005, the task is made a lot easier because of the schema feature.
SQL Server 2005 introduces a different concept
of schemas than in previous versions of SQL Server. In SQL Server 2000,
database users and schemas are equivalent. Every database user is the
owner of a schema that has the same name as the user. An owner of an
object is effectively identical to the owner of the schema that contains
the object. This one-to-one mapping behavior is what makes ownership
reassignment so tedious.
SQL Server 2005 separates users and schemas, and
schemas are independent buckets that can contain zero or more objects.
Users can own one or more schemas and can be assigned a default schema.
If a default schema is not specified, the user is defaulted to the dbo
schema of the database. This default schema is used to resolve the names
of securables that are referred to without using their fully qualified
name. In SQL Server 2000, the location that is checked first is the
schema owned by the calling database user, followed by the schema owned
by dbo. In SQL Server 2005, each user has a default schema, so when SQL
Server resolves the names of securables, it checks the default schema.
You can thus transfer ownership of many
securables, and a schema can contain securables owned by a database user
other than the owner of the schema. This is important to note because
if you leverage the old system views, such as sysobjects, the results returned in queries will not show up correctly. You must use a catalog view, such sys.objects, instead.
The CREATE SCHEMA DDL is used to create a new schema. It can also be used to create tables and views within the new schema, and to set GRANT, DENY, or REVOKE permissions on those objects, as shown in the following example:
CREATE SCHEMA Sales
AUTHORIZATION Rob
CREATE TABLE Leads (id int, name varchar(max),phone nvarchar(20))
GRANT SELECT ON Sales.Leads TO Tammie
DENY SELECT ON Sales.Leads TO Vince;
GO
This example creates the Sales schema and
assigns it to Rob. A Leads table is created within the Sales schema, and
Tammie is granted select access to the table while Vince is denied
select access.
For Tammie to access the table in SQL 2000, she must use Rob for the schema name, as shown here:
SELECT * FROM Production.SalesReps.Rob.Leads
In SQL Server 2005, she can use the Sales schema name instead:
SELECT * FROM Production.SalesReps.Sales.Leads
If Rob ever quits or gets fired, all the
sysadmin has to do to transfer ownership of the Sales schema to Tammie
is the following:
ALTER AUTHORIZATION ON SCHEMA::Sales TO Tammie;
GO
The abstraction of database users from schemas provides many benefits to developers and administrators, including the following:
Multiple users can own a single schema
through membership in roles or Windows groups. This extends the familiar
functionality and allows roles and groups to own objects.
Dropping database users is greatly simplified.
Dropping
a database user does not require renaming objects that are contained by
the schema of that user. Therefore, you do not have to revise and test
applications that refer explicitly to schema-contained securables after
dropping the user who created them.
Multiple users can share one default schema for uniform name resolution.
Shared
default schemas allow developers to store shared objects in a schema
that was created specifically for a specific application, instead of in
the dbo schema.
Permissions on schemas and schema-contained securables can be managed with a higher degree of detail than in earlier releases.
Fully qualified object names have four parts: server.database.schema.object.
Execution Context
Granting
and managing permissions to non-sysadmin users has always been an
interesting challenge, especially when it comes to users who own stored
procedures that use tables and other objects they do not own. In a
typical example, suppose there are three users: User1, User2, and User3.
User3 owns a table, CustomerInformation. User2 has written a stored
procedure that enumerates only the customer name from this table. User1
needs to execute User2’s stored procedure. With previous versions of SQL
Server, User1 not only needs EXECUTE
permission on User2’s stored procedure, but she also needs access to the
underlying CustomerInformation table owned by User3. Now multiply this
requirement across an enterprise, and you can see the permission
management nightmare that unfolds. With SQL Server 2005, User2 can
change the execution context of the stored procedure to any of the
following:
Execute as Caller Executes under the credentials of the caller. This is the same default behavior as in previous versions of SQL Server.
Execute as Self Executes under the credentials of the user who last modified the stored procedure.
Execute as <insert name of login>
Executes under the credentials of the login specified. For this to
work, the user creating or modifying the stored procedure must have IMPERSONATE permission for the login specified.
Note
EXECUTE AS
also takes a database user name. If you use a login, the scope for the
impersonation is server level. If you use a database user, the scope is
for the database only. |
Execute as Owner
Executes under the credentials of the login who owns the stored
procedure. If the owner changes after the object was created, the
execution context is automatically mapped to the new owner.
Referring back to our example, let us write our original problem (Listing 1).
Listing 1. Execution context example
--Create our users
create login User1 with password='^*ahfn2@^(K'
go
create login User2 with password='*HABa7s7aas'
go
create login User3 with password='zxd837&^gqF'
go
create database ExampleDB
go
use ExampleDB
go
--User 3 will own a table
create user User3 with default_schema=User3
go
create schema User3 authorization User3
go
--User 2 will have SELECT access and write a proc to access
create user User2 with default_schema=User2
go
create schema User2 authorization User2
go
--User 1 will have the right to exec the proc
create user User1 with default_schema=User1
go
create schema User1 authorization User1
go
grant create table to User3
go
grant create proc to User2
go
execute as login='User3'
go
create table User3.CustomerInformation
(CustomerName nvarchar(50))
go
insert into CustomerInformation values ('Bryan''s Bowling Alley')
insert into CustomerInformation values ('Tammie''s Tavern')
insert into CustomerInformation values ('Frank''s Fresh Produce')
go
grant select on CustomerInformation to User2
go
revert
go
execute as login='User2'
--create a stored proc that will return the rows in our table
create proc ViewCustomerNames
AS
BEGIN
select * from User3.CustomerInformation
END
go
grant execute on ViewCustomerNames to User1
go
revert
go
|
Now
that all of the users and objects are created, let’s log in as User1
and try to directly access the table and stored procedure.
execute as login='User1'
--Notice User1 cannot access table directly
select * from User3.CustomerInformation
--User1 can execute the procedure but does not have permissions on the underlying table
exec User2.ViewCustomerNames
go
revert
go
At this point, in
previous versions of SQL Server we would have to give User1 some type of
access against User3’s table. In SQL Server 2005, User2 can change the
execution context of the stored procedure. In our example, User2 will
change the execution context to EXECUTE AS OWNER. This will cause the stored procedure to be executed as User2 because he is the owner of the stored procedure.
execute as login='User2'
go
ALTER PROCEDURE ViewCustomerNames
WITH EXECUTE AS OWNER
AS
BEGIN
select * from User3.CustomerInformation
END
go
revert
go
Now when User1 executes User2’s stored
procedure, the stored procedure will be executing under the credentials
of User2 and thus have access to User3’s table only within the context
of the stored procedure. This allows User1 to execute User2’s stored
procedure without having explicit access to the underlying table.
execute as login='User1'
--User1 still cannot access table directly
select * from User3.CustomerInformation
--User1 can execute a procedure that uses the CustomerInformation table
exec User2.ViewCustomerNames
go
revert
go
Another form of execution context switching is
used within the previous example. In SQL Server 2005 it is possible to
change the execution context of the current connection without having to
close and reopen the connection. The user must either be a sysadmin or
have IMPERSONATE permission on the
login for this to work. Alternatively, you can use this same statement
to switch context for a database user. When used for switching context
on a database, the scope of impersonation is restricted to the current
database. In a context switch to a database user, the server-level
permissions of that user are not inherited. In addition, a user must
either be a sysadmin or have IMPERSONATE permission on the user of the database.
Executing
context switching is a powerful and efficient way to reduce the number
of permissions to manage. For developers and sysadmins, it provides an
easy way to test scripts and debug without having to log out and
reconnect to SQL Server.
