Reputation: 1025
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
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
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