Sql-server – Lockdown database access on certain tables

logonSecuritysql serverstored-procedures

I have a dev team who have a number of SQL databases used as a back end for mostly excel front end.

This originally came from an excel spreadsheet until it got made into SQL databases.

At first, they were not too concerned about security and decided to give access to a domain group logon that contains basically all the organisation's users. Over time they have put more sensitive information into the databases and have realised that anyone in the organisation can view the information if they were to use another tool, power BI or SSMS due to the domain logon.

They say that they still need to use the domain logon as everyone needs access but wants to secure the sensitive information on certain tables but still be accessible to their queries from excel.

They have suggested Signing Stored Procedures with a Certificate but I am concerned they will want to try this with all their code placed in SPs, removing the domain logon, and this would create numerous amounts of admin to maintain all of them.

How am I able to implement this in SQL Server in another way?

Best Answer

There's no need to bother with certificate signing of stored procedures.

If I'm reading your question correctly, your requirements are essentially this:

  • All data is in one SQL Server Database
  • All authentication is done via a domain group, which most individuals are part of
  • End users primarily access data through Excel, but do use other tools
  • For most data, it is OK for users to freely query the tables directly
  • For some data, users should not be able to read the raw data directly, but should instead only use approved queries

The last requirement is the change you're looking to implement, for which you are considering cert-signed stored procedures.

The answer is in stored procedures, but there should be no need to bother with cert-signing.

Ownership chaining!

SQL Server allows for ownership chaining, which essentially means if you GRANT EXECUTE on a stored procedure, the procedure will be able to access the data it needs, and return an accurate result, even if the user doesn't have direct access to the data.

An example

First, lets set up a database, a SQL Login (though, in your case, you'd just use your domain group), and some data.

--Create a database to test with
CREATE DATABASE TestingPermissions;
GO

--Create a login to test with
CREATE LOGIN ReadOnlyUser WITH PASSWORD = 'SuperDuperSecure!';
GO

--And create a corresponding user
USE TestingPermissions
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;

--And a couple tables
CREATE TABLE dbo.PublicData (
    PublicId int,
    PublicText varchar(100)
    );

CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );

--And some data
INSERT INTO dbo.PublicData (PublicId, PublicText)
SELECT object_id, name
FROM sys.objects;

INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT object_id, column_id, name
FROM sys.columns;

Now, rather than adding the user to the db_datareader fixed role, we'll grant data explicitly to the table(s) with data they can access directly:

-- And only grant permissions to the one public table!
GRANT SELECT ON dbo.PublicData TO ReadOnlyUser;

And when we query that data as the login we created. We'll get results when we query the public table that we granted SELECT on, and we'll get an error for the private table we have no permissions on.

--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
--This will error
    -- Msg 229, Level 14, State 5, Line 43
    -- The SELECT permission was denied on the object 'PrivateData', database 'TestingPermissions', schema 'dbo'.
SELECT TOP 1 
       pub.PublicText, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Now, we'll create a stored procedure which does that second query within the procedure, and we'll grant EXECUTE on that procedure to the limited access login.

--We create a stored procedure & Grant execute to the read-only user
CREATE OR ALTER PROCEDURE dbo.GetMyData
AS
SELECT TOP 1 
       pub.PublicText, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyData TO ReadOnlyUser;
GO

Now, ReadOnlyUser has EXECUTE permission on the procedure that access dbo.PrivateData, but doesn't have access to query dbo.PrivateData directly.

--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyData;
--This will error with the same error as before
SELECT TOP 1 
       pub.PublicText, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Cross-Database problems

Ownership chaining won't cross database boundaries, so in the above example, if dbo.PrivateData were in a separate database, that EXEC dbo.GetMyData would actually return a permissions error. Cross-database ownership chaining can be enabled, but brings with it some security risks, but we can still avoid it.

A more complicated example

Continuing where the last example left off, lets move dbo.PrivateData into a different database

--Put all the private data in one database
USE master
CREATE DATABASE RestrictedAccess;
GO
--And create a corresponding user
USE RestrictedAccess
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;


--And move dbo.PrivateData into this database
CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );
INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT PublicId, PrivateId, PrivateText
FROM TestingPermissions.dbo.PrivateData;

--Now that its moved, Drop the table in the other DB
DROP TABLE TestingPermissions.dbo.PrivateData;
--And drop the procedure that we broke
DROP PROCEDURE IF EXISTS dbo.getMyData;

And we can create a stored procedure that gives access to just that aggregated data, without accessing the raw data directly. We grant execute to this procedure. In real life, this might be a bit more complicated, and probably would accept some parameters to limit the data.

CREATE OR ALTER PROCEDURE dbo.GetPrivateData
AS
SELECT priv.PublicID, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetPrivateData TO ReadOnlyUser;
GO

And we can alter the original GetMyData procedure to call this GetPrivateData procedure, rather than grabbing the data directly. Now, inside GetPrivateData, I'm doing an INSERT..EXEC... to put the data into a temp table, and then join to the temp table.

USE TestingPermissions;
GO
CREATE OR ALTER PROCEDURE dbo.GetMyDataWithInsertExec
AS

CREATE TABLE #PrivateData (
    PublicID int,
    PrivateCount int
    );
INSERT INTO #PrivateData (PublicID, PrivateCount)
EXECUTE RestrictedAccess.dbo.GetPrivateData;

SELECT TOP 1 
       pub.PublicText, 
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN #PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyDataWithInsertExec TO ReadOnlyUser;
GO

And when we test it as our user, we'll see that the procedure gives access, and querying the data directly gives an error.

USE TestingPermissions;
GO
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyDataWithInsertExec;
--This will error with the same error as before
SELECT TOP 1 
       pub.PublicText, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Views work too.

Ownership chaining can work with views, too. In all the places where I used a procedure to "cover" direct data access, I could have used a view similarly. There are differences & trade-offs. In general, views give users more access to go after the data in new & interesting ways, where stored procedures give you more control. This also means that stored procedures can give more protection to ensure data is only accessed in "approved" ways. Depending on what your controls are, and how you need to balance those things, you might find that the right thing for you could be a view, or a stored procedure.

Here's an alternate example using a view:

--Create a view to get Private data
USE RestrictedAccess
GO
CREATE OR ALTER VIEW dbo.SummaryPrivateData
AS
SELECT priv.PublicID, 
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT SELECT ON dbo.SummaryPrivateData TO ReadOnlyUser;
GO

--Then just query that view from the public database
USE TestingPermissions;
GO
SELECT TOP 1 
       pub.PublicText, 
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.SummaryPrivateData AS priv
    ON pub.PublicId = priv.PublicId

No cert signing necessary

It's just a matter of making your grants granular enough that you specifically grant access to the things people should have access to, and don't grant access directly if the user shouldn't have direct access. Stored procedures, views, and functions can be used as the abstraction layer, to give access indirectly to data.

Related Question