SQL Server – Cross Database Permission through Database Chaining

Background

Working on creating a new account and making sure that it has the minimal privileges to do it work.

Scenario

Let us prepare our scenario:

We will have two databases, DBLAB and DBLAB_v2.

Logins & Users

We will only have a single login.

Let us create a SQL Login, LabAccount, and grant it access to our Originating DB.


use master
go

create login [LabAccount]
	with password = 'mygoodpasswd2015'
	, check_expiration = off
	, check_policy = off
go

use [DBLab]
go

create user [LabAccount]
	from LOGIN [LabAccount]
go	

 

Database Object – Table & programmable Objects

Here are the objects in each database

  1. DBLAB_v2
    • Table
      • HRDB.employee
    • Stored Procedure
      • HRDB.usp_ExposeEmployeeList
  2. DBLAB
    • Stored Procedure
      • HRDBExt.usp_getEmployeeList

 

Target Database (DBLab_v2)

Table

HRDB.employee

set noexec off
go

use [DBLab_v2]
go

if schema_id('HRDB') is null
begin

	exec('create schema [HRDB] authorization dbo')

end
go

if object_id('[HRDB].[employee]') is not null
begin

	set noexec on

end
go

create table [HRDB].[employee]
(
	  [employeeID] bigint not null
	, [firstname]  varchar(60)
	, [lastname]   varchar(60)

	, constraint PK_HRDB_EMPLOYEE
		primary key
			(
				[employeeID]
			)
)
go

set noexec off
go

 

Stored Procedure

HRDB.usp_exposeEmployeeList

use [DBLab_v2]
go

if schema_id('HRDB') is null
begin

	exec('create schema [HRDB] authorization dbo')

end
go

if object_id('[HRDB].[usp_exposeEmployeeList]') is null
begin

	exec('create procedure [HRDB].[usp_exposeEmployeeList] as select 1/0 as [shell] ')

end
go

alter procedure [HRDB].[usp_exposeEmployeeList]
as
begin

	select 
		  [employeeID]
		, [firstname]
		, [lastname]

	from [hrdb].[employee]

end
go


 

Originating Database (DBLab)

Stored Procedure

HRDBExt.usp_getEmployeeList

use [DBLab]
go

if schema_id('HRDBExt') is null
begin

	exec('create schema [HRDBExt] authorization dbo')

end
go

if object_id('[HRDBExt].[usp_getEmployeeList]') is null
begin

	exec('create procedure [HRDBExt].[usp_getEmployeeList] as select 1/0 as [shell] ')

end
go

alter procedure [HRDBExt].[usp_getEmployeeList]
as
begin

	exec [DBLab_v2].[HRDB].[usp_ExposeEmployeeList]

end
go


 

Test

We will attempt a basic test and that will be for our test account, LabAccount, to invoke the HRDBExt.usp_getEmployeeList SP.

Let us quickly point out that [HRDB].[usp_ExposeEmployeeList] references a Stored Procedure, HRDB.usp_ExposeEmployeeList, in our target DB, DBLab_v2.


set nocount on;
go

use [DBLab]
go

setuser 'LabAccount'

	select
		username = user_name()

	exec [HRDBExt].[usp_getEmployeeList]

setuser

Execute Permission on Stored Procedure in Originating DB

The first error we encountered is missing execute permission on Stored Procedure in originating DB.

Textual:


Msg 229, Level 14, State 5, Procedure usp_getEmployeeList, Line 1
The EXECUTE permission was denied on the 
object 'usp_getEmployeeList', database 'DBLAB', schema 'HRDBExt'.

Image:

userlacksAccessToPrimarySP

 

Remediation:


grant execute on [HRDBExt].[usp_getEmployeeList] to [LabAccount];

DB Access Permission on Target DB

The next error we encountered is access privileged to target database

Textual:


Msg 916, Level 14, State 1, Procedure usp_getEmployeeList, Line 6
The server principal "LabAccount" is not able to access the database "DBLAB_v2" under the current security context.

Image:

DBAccessMissingOnTargetDB

 

Correct:


use [DBLab_v2]
go

create user [LabAccount]
from LOGIN [LabAccount];
go

DB Access Permission on Targeted Schema\Objects

Error

Next we encountered errors on the targeted DB Object.

Textual:


Msg 229, Level 14, State 5, Procedure usp_exposeEmployeeList, Line 1
The EXECUTE permission was denied on the object 'usp_exposeEmployeeList', database 'DBLAB_v2', schema 'HRDB'.

Image:
ExecutePermissionDeniedOnTargetSP_v2

Correct

Grant Execute on Specific Schemas


use [DBLab_v2]
go

GRANT EXECUTE ON SCHEMA :: HRDB TO [LabAccount];
go

 

Grant Execute on Targeted Objects


use [DBLab_v2]
go

grant execute on [HRDB].[usp_exposeEmployeeList] to [LabAccount]
go

 

DB Chaining

It is possible that we can get down the snail trail of having to grant secondary and tertiary Object permissions.

And, so I will suggest that you enable DB Chaining.

This is similar to what one will do in the case of Domain Networks – Mutual Authentication.

Enable DB Chaining

Code:


use [master]
go

ALTER DATABASE [DBLAB_v2] SET DB_CHAINING OFF
ALTER DATABASE [DBLAB] SET DB_CHAINING OFF

Review DB Chaining

Code:


select 
	  [database]
		=tblSD.name

	, tblSD.is_db_chaining_on

from   master.sys.databases tbLSD

where  tblSD.is_db_chaining_on = 1

Output:
listDBThatHaveDBChainingOn

 

Pre-Requisites

For DB Chaining to work well, the databases and the database Objects have to share the same owner.

Review Database Owner


select 
	 [database]
	   =tblSD.name

	, [owner]
	  = suser_sname(owner_sid)

from   sys.databases tbLSD

where  tblSD.is_db_chaining_on = 1

Review Database Object Owner

Here is a very illuminating script shared by K. Brian Kelly



select

	  [schema] = tblSS.[name]
	, [object] = tblSO.[name]
	, [type] = tblSO.type_desc

	, [objectOwnerID]
		= tblSO.principal_id

	, [objectOwnerName]
		= user_name(tblSO.principal_id)

	, [schemaOwnerID]	
		= tblSS.principal_id

	, [schemaOwnerName]
		= user_name(tblSS.principal_id)

	, [loginName]
		= tblMSP.[name]

from   sys.objects tblSO

	   inner join sys.schemas tblSS 

		on tblSO.schema_id = tblSS.[schema_id]

	  left join [sys].[database_principals] tblSDP
 
		on coalesce(
				  tblSO.principal_id
				, tblSS.principal_id
	 		  )

			= tblSDP.[principal_id]

	left join [master].[sys].[server_principals] tblMSP

		on tblSDP.[sid]	= tblMSP.[sid]

where  tblSO.type in ('U', 'V', 'P')

order by
		  1  
		, 2
		, 3


Output:
listDBObjectAndSchemaOwner

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s