Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2005 : Privilege and Authorization - Ownership Chaining

7/24/2011 4:53:05 PM
The most common method of securing SQL Server resources is to deny database users any direct access to SQL Server resources and provide access only via stored procedures or views. If a database user has access to execute a stored procedure, and the stored procedure is owned by the same database user that owns a resource being referenced within the stored procedure, the user executing the stored procedure will be given access to the resource, via the stored procedure. This is called an ownership chain.

To illustrate, start by creating and switching to a test database:

CREATE DATABASE OwnershipChain
GO

USE OwnershipChain
GO

Now, create two database users, Louis and Hugo:

CREATE USER Louis
WITHOUT LOGIN
GO

CREATE USER Hugo
WITHOUT LOGIN
GO


Note that both of these users are created using the WITHOUT LOGIN option, meaning that although these users exist in the database, they are not tied to a SQL Server login and therefore no one can authenticate as one of them by logging in to the server. This option is one way of creating the kind of proxy users mentioned previously.

Once the users have been created, create a table owned by Louis:

CREATE TABLE SensitiveData
(
IntegerData INT
)
GO

ALTER AUTHORIZATION ON SensitiveData TO Louis
GO

At this point, Hugo has no access to the table. To create an access path without granting direct permissions to the table, a stored procedure could be created, also owned by Louis:

CREATE PROCEDURE SelectSensitiveData
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM dbo.SensitiveData
END
GO

ALTER AUTHORIZATION ON SelectSensitiveData TO Louis
GO

Hugo still has no permissions on the table at this point; the user needs to be given permission to execute the stored procedure:

GRANT EXECUTE ON SelectSensitiveData TO Hugo

At this point Hugo can execute the stored procedure, thereby selecting from the table. However, this only works because Louis owns both tables, and both are in the same database; if either of those conditions were not true, the ownership chain would break, and Hugo would have to be authorized another way to select from the table. The ownership chain would also fail if the execution context changed within the stored procedure. For example, ownership chaining will not work with dynamic SQL.

In the case of a stored procedure in one database requesting access to an object in another database, it is possible to maintain an ownership chain, but it gets quite a bit more complex, and security is much more difficult to maintain. To set up cross-database ownership chaining, the user that owns the stored procedure and the referenced table(s) must be associated with a server-level login, and each database must have the DB_CHAINING property set using the ALTER DATABASE command. That property tells SQL Server that either database can participate in a cross-database ownership chain, either as source or target—but there is no way to control the direction of the chain, so setting the option could open up security holes inadvertently.

I recommend that you avoid cross-database ownership chaining whenever possible, and instead call stored procedures in the remote database. Doing so will result in a more secure, more flexible solution. For example, moving databases to separate servers is much easier if they do not depend on one another for authentication. In addition, with the inclusion of schemas in SQL Server 2005, splitting objects into multiple databases is no longer as important as it once was. Consider avoiding multiple databases altogether, if at all possible.
Other -----------------
- SQL Server 2005 : Privilege and Authorization - Basic Impersonation Using EXECUTE AS
- Configuring Standard Permissions for Exchange Server 2010 (part 2) - Understanding & Assigning Advanced Exchange Server Permissions
- Configuring Standard Permissions for Exchange Server 2010 (part 1)
- Feature Overview of Microsoft Lync Server 2010 : Dial-In Conferencing & Enterprise Voice
- Feature Overview of Microsoft Lync Server 2010 : Instant Messaging & Web Conferencing
- Feature Overview of Microsoft Lync Server 2010 : Presence
- Installing Windows Small Business Server 2011
- Business Server 2011 : Planning Fault Tolerance and Avoidance - Disk Arrays
- Microsoft Dynamics GP 2010 : Improving financial reporting clarity by splitting purchasing accounts & Speeding up lookups with Advanced Lookups
- Microsoft Dynamics GP 2010 : Remembering processes with an Ad hoc workflow
- Microsoft Dynamics GP 2010 : Gaining additional reporting control with Account Rollups
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 2) - FAST Search Server 2010 for SharePoint
- SharePoint 2010 Search : Replacing the SharePoint Search Engine (part 1) - Google Search Appliance
- Microsoft Dynamics NAV : Backing up and restoring with SQL Server
- Microsoft Dynamics NAV : Using HotCopy backup & Testing the database
- Microsoft Dynamics NAV : Creating and restoring backups using a Dynamics NAV client
- Microsoft SQL Server 2008 Analysis Services : Building Basic Dimensions and Cubes - Setting up a new Analysis Services project
- Windows Server 2008 Server Core : Managing IIS - Working with the ApplicationHost.CONFIG File
- Microsoft Dynamics CRM 2011 : Creating a Dynamic Marketing List
- Microsoft Dynamics CRM 2011 : Evaluating Members Included in a List by Using Advanced Find & Removing Selected Members from a List
 
 
Most view of day
- Integrating BizTalk Server 2010 and Microsoft Dynamics CRM : Communicating from Dynamics CRM to BizTalk Server (part 1) - Setup
- SQL Server 2012 : Running SQL Server in A Virtual Environment - EXTENDED FEATURES OF VIRTUALIZATION
- Administering an Exchange Server 2013 Environment (part 2) - Exchange Administration Center - Overview of the Exchange Administration Center
- Windows Phone 7 : Running Silverlight Projects in the Browser (part 2)
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 1)
- Microsoft Exchange Server 2010 : Managing Message Pickup, Replay, Throttling, and Back Pressure (part 1) - Configuring and Moving the Pickup and Replay Directories
- Windows Server 2003 on HP ProLiant Servers : Security Planning and Design (part 1)
- Microsoft Project 2010 : Setting Up a Project Budget - Assigning Accounting Codes
- Windows Server 2012 : Enhanced security and compliance (part 1) - Dynamic Access Control
- Microsoft Project 2010 : Accounting for Project Costs - Accounting for Overtime Spent on Project Tasks
Top 10
- Windows Server 2012 : Configuring IPsec (part 7) - Configuring connection security rules - Monitoring IPsec
- Windows Server 2012 : Configuring IPsec (part 6) - Configuring connection security rules - Creating a custom rule, Configuring authenticated bypass
- Windows Server 2012 : Configuring IPsec (part 5) - Configuring connection security rules - Creating an authentication exemption rule, Creating a server-to-server rule, Creating a tunnel rule
- Windows Server 2012 : Configuring IPsec (part 4) - Configuring connection security rules - Types of connection security rules, Creating an isolation rule
- Windows Server 2012 : Configuring IPsec (part 3) - Configuring IPsec settings - Customizing IPsec tunnel authorizations, Configuring IPsec settings using Windows PowerShell
- Windows Server 2012 : Configuring IPsec (part 2) - Configuring IPsec settings - Customizing IPsec defaults
- Windows Server 2012 : Configuring IPsec (part 1) - Understanding connection security
- Microsoft Project 2010 : Linking Tasks (part 8) - Auditing Task Links,Using the Task Inspector
- Microsoft Project 2010 : Linking Tasks (part 7) - Creating Links by Using the Mouse,Working with Automatic Linking Options
- Microsoft Project 2010 : Linking Tasks (part 6) - Creating Links by Using the Entry Table
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro