controlling security through stored procedures -- 2005 behaviour

Hi!

I'm trying to control security through sps -- meaning execute permissions are granted on stored procedures, and no users have read/write permissions on tables, etc directly.

Which works fine as long as all objects referenced are in the same db as the procedure.

An issue arises when a stored procedure accesses a table in another database:

Getting a : Msg 229 SELECT permission denied on object 'blah' Even though the procedure is created by sysadmin.

Has this changed since 2000? I'm pretty sure in 2000 it would've worked as the sp would be executed in sp owner's security context.

Moreover, when I try to use EXECUTE AS in the sp as a workaround, I am getting the following, no matter what account I try to impersonate:

Msg 916, Level 14, State 1, Procedure vvv, Line 4
The server principal % is not able to access the database "blah" under the current security context.

any ideas?
Thanks!

Most likely this scenario worked on Windows 2000 with cross-database ownership chaining enabled. Turning on this feature is not recommended, as it may lead to an elevation of privileges (i.e. the DB administrators of the source database may escalate their privileges to become DB administrators on the target DB).

The reason why your stored procedure marked with “execute as” is not working is because the impersonated context is (by default) scoped only to the surrent (source) database, and stripped down from it's server-scoped permissions and privileges. If you wish to use this impersonated context outside the source DB, you need to establish a trust relationship on the target DB.

To solve this problem, you can probably use digital signatures to solve your problem; by signing the stored procedure with a certificate you have a way to ensure that the code has not been tampered with. If at run time the signature matches the code, the certificate can be used in two ways:

* As a secondary identity for the execution context. This means that if there is a user mapped to the signing certificate, the permissions on that user will be used to calculate the permissions on the object.

* When the module (SP) is marked with execute as, the signature will work as an authenticator, that means the signature will be used to vouch for the impersonated context in the stored procedure

Note that for the secondary identity approach, the signature will be added to the current context therefore, if the current context is not a valid one on the server scope (i.e. the caller is an approle), the certificate as secondary identity cannot be used on cross database scenario.

The second approach on the other hand establishes a whole new context on top of the calling context, and it is the signature the one vouching for this new context on the target database.

I am posting a small demo at the end taht I hope will help you.

Thanks a lot for your comments and feedback.

-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.

-

/*******************************************************************

*

* This posting is provided "AS IS" with no warranties, and
* confers no rights.

*

* Author: Raulga

* Date: 08/24/2005

* Description:

* This demo shows how to use digital signatures to access
* resources on a different database by using digitaly signed stored
* procedures to control the access rather than using cross database
* ownership chaining.

*

* The first SP will be using the siganture as a secondary identity
* on top of the calling context. This means that only a context with
* a server-presence will succeed on this call (i.e. approles will not
* be able to accsss the resources on the target database as they

* don't have a server presence).

*

* The second approach will be by specifying a context switch
* (EXECUTE AS) on the stored procedure and using the signature as an
* authenticator; this means that the signature can vouch for the
* impersonated context (specifid on the module). This mechanism will
* allow to access the resources regardless of the original calling
* context because a new context (vouched by the signature) is placed
* on top of the orginal one, but requires more managment.

*

* (c) 2005 Microsoft Corporation. All rights reserved.

*

***********************************************************************************************/

CREATE DATABASE db_Source

go

CREATE DATABASE db_Target

go

CREATE LOGIN dbo_db_Source WITH PASSWORD = 'My S0uRc3 D8 p@.55W0rD!'

CREATE LOGIN dbo_db_Target WITH PASSWORD = 'My +@.r637 D8 p@.55W0rD!'

go

-- Change the ownership for the source and the target databases

ALTER AUTHORIZATION ON DATABASE::db_Source to dbo_db_Source

ALTER AUTHORIZATION ON DATABASE::db_Target to dbo_db_Target

go

-- This principal will be the data owner, he can access the data on

-- the target database, and he controls the stored procedures on the

-- source database

CREATE LOGIN data_owner WITH PASSWORD = 'd@.+4 0wn3R'

-- This principal should only have access to the data via the stored

-- procedures

CREATE LOGIN someuser WITH PASSWORD = 's0m3 p@.55w0Rd'

go

use db_Target

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

CREATE TABLE data_owner.MyTable( data nvarchar(100) )

go

INSERT INTO data_owner.MyTable values ( N'My data' )

go

use db_Source

go

CREATE USER someuser

CREATE USER data_owner WITH DEFAULT_SCHEMA = data_owner

go

CREATE SCHEMA data_owner AUTHORIZATION data_owner

go

-- ALlow someuser to execute any module on the schema called data_owner

GRANT EXECUTE ON SCHEMA::data_owner TO someuser

go

-- Create a stored procedure that uses the default execution context

-- (the caller's context) at runtime

CREATE PROC data_owner.sp_GetMyData01

AS

select * from db_Target.data_owner.MyTable

go

-- Create a stored procedure similar to teh previous one, but this time we will explicitly

-- use the data_owner context via EXECUTE AS

CREATE PROC data_owner.sp_GetMyData02

WITH EXECUTE AS 'data_owner'

AS

select * from db_Target.data_owner.MyTable

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- Will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will fail due to the lack of permissions on the target database

EXEC data_owner.sp_GetMyData01

-- will fail as the impersonated context is not trusted on the target
-- database

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Signing the stored procedures

--

-- Create 2 certificates one to sign each SP.

-- Note that I am using passwords to protect the private keys.

-- It is also possible to use a DB master key to protect private the
-- keys, please refer to BOL for more information on the key
-- hierarchy

CREATE CERTIFICATE cert_GetMyData01

ENCRYPTION BY PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData01'

go

CREATE CERTIFICATE cert_GetMyData02

ENCRYPTION BY PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

WITH SUBJECT = 'Certificate to sign sp_GetMyData02'

go

-- Now sign the stored procedures, as the cert's

-- private keys are protected by passwords, we have to use the
-- passwords to sign

ADD SIGNATURE TO data_owner.sp_GetMyData01 BY CERTIFICATE cert_GetMyData01

WITH PASSWORD = 'GetMyData01 c3r+ p@.55w0Rd'

go

ADD SIGNATURE TO data_owner.sp_GetMyData02 BY CERTIFICATE cert_GetMyData02

WITH PASSWORD = 'GetMyData02 c3r+ P455W0Rd'

go

-- Let's take a quick look to the metadata for the signed modules

SELECT schema_name( c.schema_id ) as schema_name, c.name,

b.name, a.crypt_property as 'module siganture' FROM

sys.crypt_properties a,

sys.certificates b,

sys.objects c

WHERE a.thumbprint = b.thumbprint AND a.class = 1
AND a.major_id = c.object_id

go

-- Depending on your application and environment, sometimes you may
-- not want to leave the private keys on the database, and either
-- destroy the private keys (this way, they can never be used to
-- sign anything else), or back up a copy of the private keys and
-- store them in a safe place. For this demo I will just destoy the
-- private keys as we don't need them anymore

ALTER CERTIFICATE cert_GetMyData01 REMOVE PRIVATE KEY

ALTER CERTIFICATE cert_GetMyData02 REMOVE PRIVATE KEY

go

-- Now, we need to create a backup for the certificate public data.

-- We will need to import it back on teh target database.

BACKUP CERTIFICATE cert_GetMyData01 TO FILE = 'cert_GetMyData01.cer'

BACKUP CERTIFICATE cert_GetMyData02 TO FILE = 'cert_GetMyData02.cer'

go

use db_Target

go

-- Import the certificates on the target database, note that we don't
-- need the private keys

CREATE CERTIFICATE cert_GetMyData01
FROM FILE = 'cert_GetMyData01.cer'

go

CREATE CERTIFICATE cert_GetMyData02
FROM FILE = 'cert_GetMyData02.cer'

go

-- Now let's create users mapped to each one of the certificates.

-- As permissions can only be granted to principals and not directly

-- to a certificate, we need to map the certificate to a user.

-- Note: The cert-mapped user SID is derived from teh certificate
-- thumbprint

-- therefore any 2+ principals (login or user in any database)
-- mapped to the

-- same certificate will have the same SID and will refer to the same

-- principal for practical purposes.

CREATE USER cert_GetMyData01 FOR CERTIFICATE cert_GetMyData01

go

CREATE USER cert_GetMyData02 FOR CERTIFICATE cert_GetMyData02

go

-- For the first SP, grant the permissions to the cert-mapped
-- user directly

GRANT SELECT ON data_owner.MyTable TO cert_GetMyData01

go

-- For the second SP, we want only AUTHENTICATE permissiion, this
-- will allow teh certificate to vouch for the context only on this
-- database.

-- Note: As the trust is only accross database and not accross the
-- instance, the new context is only valid for database operations,
-- and will not honor any server-scoped permissions.

GRANT AUTHENTICATE TO cert_GetMyData02

go

USE db_Source

go

-

-- Let's see what is the behavior without any signatures

--

-- You can either start new connections or just use the

-- EXECUTE AS LOGIN & REVERT statements I show here for testing

-- Execute as the data owner

-

EXECUTE AS LOGIN = 'data_owner'

go

-- will succeed

EXEC data_owner.sp_GetMyData01

go

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- Execute as someuser

-

EXECUTE AS LOGIN = 'someuser'

go

-- will succed as the certificate will be granting the required
-- permission to select the data from the table

-- Note that someuser is a valid context accross the server at
-- this point

EXEC data_owner.sp_GetMyData01

-- will succeed as the module is executing as "data_owner"

-- (the module is specifying the context itself), and the

-- signature is vouching for this context

EXEC data_owner.sp_GetMyData02

go

REVERT

go

-

-- cleanup

USE master

go

DROP DATABASE db_Source

go

DROP DATABASE db_Target

go

DROP LOGIN dbo_db_Source

DROP LOGIN dbo_db_Target

DROP LOGIN data_owner

DROP LOGIN someuser

go

|||

Raul -- thanks a lot for taking the time to do this. Excellent explanation and demo!