I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'.

I'm working on a security system and I want client apps to only access a handful of top-level functions. For example:

  • CanUserEditObject(@personId, @objectId) -- called to see if an edit is possible
  • EditableBy(@personId) -- returns a list of editable object ids

These form the API I want client apps to use.

To implement that, I need some other functions which I don't want client apps to call;

  • GetLocationsAdministeredBy(@personId)
  • IsUserSysadmin(@personId)

Client apps have no need to call them, and I don't promise to support them going forward -- they're just an implementation detail. So I'd like to hide them. Ideally, any attempt to call it by a client application will fail. Ideally ideally, I'd be able to call them in SQL Server Management Studio to develop them.

Basically, this is inspired by the OO principle of information hiding and language constructs like public and private access modifiers in C++/Java/C#/etc.

I don't need it to be a guaranteed, hard-security kind of solution. I basically trust my clients here, but I just don't want some plucky developer to spot a function I've written and use it, then complain when I rewrite it later.

This is mainly for SQL Server 2014, but I'm interested if there are any 'standard' SQL techniques.

Also, we have a team where some of us are better at SQL than others. The SQL people would add their own objects, but they wouldn't use my 'private' functions. The other devs might discover existing functions rather than add them. I'm looking for a way to differentiate so that no-one accidentally relies on an unsupported function. Something as simple as a naming convention would do but I am interested how other people muddle through with an issue that most non-SQL languages have good support for.

  • In this case, SQL Server 2014, but interested if there are any 'standard' SQL techniques, too. – Steve Cooper Jul 22 '16 at 12:43
  • We have a team where some of us are better at SQL than others. The SQL people would add their own objects, but they wouldn't use my 'private' functions. The other devs might discover existing functions rather than add them. I'm looking for a way to differentiate so that no-one accidentally relies on an unsupported function. Something as simple as a naming convention would do but I am interested how other people muddle through with an issue that most non-SQL languages have good support for. – Steve Cooper Jul 22 '16 at 15:17
  • So you are saying: "yes, some people would be creating new DB objects, but I still don't want them to have direct execute access to my private functions and stored procedures". Is that correct? – Solomon Rutzky Jul 22 '16 at 15:19
  • Ok, I have now finished. Hopefully it all makes sense :-). I have also deleted my previous comment to help clean this up. I will add the clarifications you provided above into the question and then delete my first two comments. I suggest you then delete all of your comments above. Thanks. – Solomon Rutzky Jul 22 '16 at 17:35
up vote 2 down vote accepted

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).

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.