Sql-server – Creating a helper UDF or stored procedure and limiting external access

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects referenced within a module so long as as the referenced objects have the same "owner" as the "owner" of the module being executed, and those referenced objects are not being referenced within Dynamic SQL.

Ownership Chaining

The following example illustrates this behavior. The Test Login cannot directly access the Function because no EXECUTE permission has been granted on it. But when it is used in a Stored Procedure that the Test Login / User has been granted EXECUTE permission on, then it works because both are in the same Schema (i.e. dbo). However, the ownership chain is broken by Dynamic SQL, which is why the second Stored Procedure, PublicDoSomethingDS, gets an error.

USE [master];
CREATE LOGIN [Test] WITH PASSWORD = 'test';
GO

USE [tempdb];
CREATE USER [Test] FROM LOGIN [Test];
CREATE TABLE dbo.PrivateTest (Col1 INT);
INSERT INTO dbo.PrivateTest (Col1) VALUES (1), (4), (999);
GO

CREATE FUNCTION dbo.PrivateGetRowCount (@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomething
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT dbo.PrivateGetRowCount(@SomeInput);
GO

CREATE PROCEDURE dbo.PublicDoSomethingDS
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

EXEC sys.sp_executesql
 N'SELECT dbo.PrivateGetRowCount(@tmpSomeInput);',
 N'@tmpSomeInput INT',
 @tmpSomeInput = @SomeInput;
GO

GRANT EXECUTE ON dbo.PublicDoSomething TO [Test];
GRANT EXECUTE ON dbo.PublicDoSomethingDS TO [Test];
GO

SELECT SESSION_USER; -- dbo
SELECT dbo.PrivateGetRowCount(1); -- 1

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- 12
EXEC('EXEC dbo.PublicDoSomethingDS 5;'); -- 15

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT dbo.PrivateGetRowCount(1); -- error:
-- Msg 229, Level 14, State 5, Line 60
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- error:
-- Msg 229, Level 14, State 5, Line 71
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

BUT, if others can create their own objects -- Stored Procedures, Functions, Views, etc -- then Ownership Chaining does't get you anywhere since someone creating their own module in the dbo Schema would then have access to those "private" modules.

In that case, you should be able to accomplish this separation with a little more work by using signatures and module signing.

Module Signing

Module Signing is using an Asymmetric Key or Certificate to create a Login and/or User from, assign the desired permission(s) to that Asymmetric Key-based or Certificate-based Login and/or User, and then "sign" the modules that should be given those permissions using the ADD SIGNATURE command (introduced in SQL Server 2008).

This approach is a little more complicated and takes a little more time to set up, but allows for very granular control of permissions.

Assuming you have fully run the example code shown above, now run the following:

USE [tempdb];
GO

CREATE SCHEMA [Private] AUTHORIZATION [dbo];
GO

CREATE FUNCTION [Private].[GetRowCount](@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomethingPrvt
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT [Private].[GetRowCount](@SomeInput);
GO

GRANT EXECUTE ON dbo.PublicDoSomethingPrvt TO [Test];

SELECT SESSION_USER; -- dbo
SELECT [Private].[GetRowCount](1); -- 3

EXEC dbo.PublicDoSomethingPrvt 2; -- 6



EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT [Private].[GetRowCount](1); -- error:
-- Msg 229, Level 14, State 5, Line 41
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

REVERT;
SELECT SESSION_USER; -- dbo

We created a new Schema and put the new Function in it, yet the Test User was still able to execute PublicDoSomethingPrvt. Why? Because the "owner" of the Private Schema is dbo, which is the same "owner" of the dbo Schema. Hence Ownership Chaining is still allowing this to work.

But we can finally break that by running the following:

CREATE USER [MrX] WITHOUT LOGIN;

ALTER AUTHORIZATION ON SCHEMA::[Private] TO [MrX];

EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- still works for dbo


EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 71
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.


REVERT;
SELECT SESSION_USER; -- dbo

Now there is no more Ownership Chain. And in fact, even if you were to grant EXECUTE on the [Private].[GetRowCount] Function to the Test User, they would still get an error because there is no more automatic link between the Function and the Table that it selects from.

So now what? Well, now we create the Asymmetric Key, then the User from that Key, then grant that User the desired permission(s), and then sign the Stored Procedure with that same Asymmetric Key:

CREATE ASYMMETRIC KEY [PrvtKey] WITH ALGORITHM = RSA_4096 ENCRYPTION BY PASSWORD = 'silly';
-- RSA_2048 for SQL Server 2012 and older

CREATE USER [PrvtUser] FROM ASYMMETRIC KEY [PrvtKey];

GRANT EXECUTE ON [Private].[GetRowCount] TO [PrvtUser];

ADD SIGNATURE TO [dbo].[PublicDoSomethingPrvt]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';


EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 94
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

Almost there. The PublicDoSomethingPrvt Stored Procedure (not the Test User!) can now execute the Function, but that Function still has no inherent link to the Table, which is in another Schema that has a different owner. Now we just add that final permission to the Asymmetric Key-based User and then sign the Function:

GRANT SELECT ON [dbo].[PrivateTest] TO [PrvtUser];

ADD SIGNATURE TO [Private].[GetRowCount]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';



EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 12; -- 36
-- works now!!!!!!!!!!!!!!!!!!  :-)


SELECT * FROM [dbo].[PrivateTest];
-- Msg 229, Level 14, State 5, Line 110
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

SELECT [Private].[GetRowCount](12);
-- Msg 229, Level 14, State 5, Line 115
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

As you can see, the Test User only has the permissions explicitly granted to it, namely EXECUTE on the PublicDoSomethingPrvt Stored Procedure.

Notes:

1. You control this by being the only one to have that "password" (determined when creating the Asymmetric Key, and used when running ADD SIGNATURE).
2. For security reasons, Signatures are dropped from modules if the definition changes in any way, even the EXECUTE AS clause of the CREATE statement. This ensures that someone doesn't change a situation that you were fine with into something that you aren't ok with. So when definitions change, you need to re-run the ADD SIGNATURE command.
3. You don't need multiple Users (i.e. MrX and PrvtUser), that was just for the demo code. You could use the Asymmetric Key-based User to own the "Private" Schema.
4. You don't need to assign permissions to the Asymmetric Key-based User per each Module. Instead, you could grant Schema-wide permissions to simplify things (e.g. EXECUTE on the Private Schema, SELECT on the dbo Schema, etc).
5. You could further divide the permissions by creating one Asymmetric Key and associated User for allowing the "public" modules to execute stuff in the Private Schema. Then, create another Asymmetric Key and associated User to allow the "private" modules access to objects in the "public" Schema(s).