CODEWITHSUNDEEP
sqlsql-servert-sql
Thierry Prost
Thierry Prost

Reputation: 1025

SQL Cross-Schema stored procedure permission issues

Given a simplified stored procedure

CREATE OR ALTER PROCEDURE [FooSchema].[Foo]
AS
SELECT
    B.*,
    FROM [BarSchema].[Bar] AS B
    WHERE [...]

After granting EXEC to a user on the FooSchema, but not on the BarSchema, this stored procedure will fail with the message

The SELECT permission was denied on the object 'Bar',

Is there a way to make the user access [FooSchema].[Foo] without exposing [BarSchema].[Bar]'s entire data set as the stored procedure already filters out data relevant to this user.

Upvotes: 1

Views: 974

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46425

If both schemas are owned by the same user, normal ownership chaining will apply and users with EXECUTE permissions on the proc don't need permissions on the underlying objects. Users will be restricted to the data returned by the stored procedure and won't be able to perform ad-hoc queries on the tables unless you specifically grant them permissions to do so.

For example:

CREATE USER SchemaOwner WITHOUT LOGIN;
ALTER AUTHORIZATION ON SCHEMA::FooSchema TO SchemaOwner;
ALTER AUTHORIZATION ON SCHEMA::BarSchema TO SchemaOwner;

Another method to provide additional permissions only within the scope of the module is with module signing.

CREATE CERTIFICATE FooUserCertificate
   ENCRYPTION BY PASSWORD = '0bfuscatedPassword'
   WITH SUBJECT = 'Allow access to bar table';

CREATE USER FooUser FROM CERTIFICATE FooUserCertificate;

GRANT SELECT ON BarSchema.Bar TO FooUser;

ADD SIGNATURE TO FooSchema.Foo BY CERTIFICATE FooUserCertificate
    WITH PASSWORD = '0bfuscatedPassword';

ALTER CERTIFICATE FooUserCertificate REMOVE PRIVATE KEY;
GO

Albeit more complex, the advantage of module signing over EXECUTE AS is that the original caller's identity is preserved during execution and not sandboxed in the current database. This is useful for auditing and cross-database access. See this article for more information.

Upvotes: 3

Greg
Greg

Reputation: 4055

Easiest option is to use [WITH EXECUTE AS] statement, where the username specified has the exact permissions needed for the objects in question.

CREATE OR ALTER PROCEDURE [FooSchema].[Foo]
WITH EXECUTE AS '<username>'
AS
SELECT
    B.*,
    FROM [BarSchema].[Bar] AS B
    WHERE [...]

Upvotes: 5

Related Questions