This article explains data security for accessing sensitive data and restricts access in application using SQL Views and stored procedures.
We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users?
Overview
In this tip, we’ll look at a few techniques that we can use to restrict permissions on data. Even though we are not looking at personally identifiable information, we can apply some of these techniques along with encryption when (or if) we have PII data stored. I will caution developers that data doesn’t have to belong to PII data to restrict access; for an example, in many jurisdictions around the world, salary and bonus information are not considered personal data, yet allowing everyone the company access to this data could turn into a legal disaster. With all data, always lean on the side of providing the least amount of data access to everyone.
Working with SQL views and procedures
As a first step, we want to create our hierarchy to prevent some level of employees from accessing some data. For a contrived example throughout this tip, we’ll separate staff into three groups – group one of executive staff, who will have more permissions when viewing data and group two of other staff, who will have fewer permissions or no permissions when viewing data. The final group will be the contrived group of admins, who will have sole access to the audit information. Admin staff would be the CEO, CFO and a few others who may need to review audit data for access. The below image shows our contrived hierarchy:
While this step involves organization, we’ll use this as a map for how we set permissions. I recommend that you always do this step prior to giving permissions.
We’ll also be using two service accounts in this example for executive and other staff, though we could use AD accounts as well and apply the same level of permissions used in this tip. We’ll start off by creating two service account users – one for the executive group and the other for the other group. The administrators in this example will have windows authentication access through their AD account and we’ll be using this windows authentication to create the objects (this will be your default logged in user already). These service accounts will also have a role each – other for other staff and executive for executive staff.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE master GO CREATE LOGIN [exampleExecutiveStaff] WITH PASSWORD = 'this1passwordisONLYanexample!' CREATE USER [exampleExecutiveStaff] FROM LOGIN [exampleExecutiveStaff] CREATE LOGIN [exampleOtherStaff] WITH PASSWORD = 'this2passwordisONLYanexample!' CREATE USER [exampleOtherStaff] FROM LOGIN [exampleOtherStaff] ---- Example database USE GenExaAll GO CREATE USER [exampleExecutiveStaff] FROM LOGIN [exampleExecutiveStaff] CREATE USER [exampleOtherStaff] FROM LOGIN [exampleOtherStaff] CREATE ROLE [executive] CREATE ROLE [other] ALTER ROLE [executive] ADD MEMBER [exampleExecutiveStaff] ALTER ROLE [other] ADD MEMBER [exampleOtherStaff] |
In our first example, we will create two SQL views: one SQL view for the executive staff and the other SQL view for the other staff. The other staff will only see part of the table, while the execute staff will be able to view the full table from the view (create in separate batches):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--Create SQL View CREATE VIEW viSDRHoldingsExecutive AS SELECT Members,SDRHoldings,SDRAllocations FROM tbSDRHoldings CREATE VIEW viSDRHoldingsOther AS SELECT Members,SDRAllocations FROM tbSDRHoldings GRANT SELECT ON viSDRHoldingsExecutive TO [executive] GRANT SELECT ON viSDRHoldingsOther TO [other] |
What we’re using here is a SQL view on top of a table, granting an account access to a role, and granting the role read access to the view. Relative to what the SQL view allows, the user will have the permission. The user will not have further permissions on the table or the other view. As a test, we will log in as the exampleOtherStaff user and try to read from the tbSDRHoldings table and the viSDRHoldingsExecutive view directly and get an error, though we will be able to read from viSDRHoldingsOther without an error:
|
1 2 3 4 5 6 7 8 |
---- logged in as exampleOtherStaff --Get data from SQL View USE GenExaAll GO SELECT * FROM viSDRHoldingsExecutive SELECT * FROM tbSDRHoldings SELECT * FROM viSDRHoldingsOther |
A view is one way we can restrict access. We can also restrict access using stored procedures. In our next example, we’ll create two stored procedures with the same select statements we see in the views and demarcate them by the group. We’ll then grant execute permissions to the stpSDRHoldingsOther to the other role:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE stpSDRHoldingsExecutive AS BEGIN SELECT Members,SDRHoldings,SDRAllocations FROM tbSDRHoldings END CREATE PROCEDURE stpSDRHoldingsOther AS BEGIN SELECT Members,SDRAllocations FROM tbSDRHoldings END GRANT EXECUTE ON stpSDRHoldingsOther TO [other] |
If we try to execute both stored procedure when logged in as the exampleOtherStaff user, we only get the result for the stored procedure granted execute permission:
|
1 2 3 4 5 6 |
---- logged in as exampleOtherStaff USE GenExaAll GO EXEC stpSDRHoldingsExecutive EXEC stpSDRHoldingsOther |
Even if a user does not have direct access to a table, such as inserting, updating, deleting or even selecting from a table, we can still use grants on stored procedures to allow data operations. In our next example using the same stored procedure stpSDRHoldingsOther, we’ll add an ending insert to a logging table to the final part of the procedure so that we retain a record of when the user calls this stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
---- Audit table: CREATE TABLE tbAuditLog ( LogUser VARCHAR(100), LogDate DATETIME DEFAULT GETDATE() ) ---- Alter our procedure to add an insert to audit table: ALTER PROCEDURE stpSDRHoldingsOther AS BEGIN SELECT Members,SDRAllocations FROM tbSDRHoldings INSERT INTO tbAuditLog (LogUser) VALUES (SUSER_NAME()) END |
If we switch over to our window where the exampleOtherStaff is logged in and call the procedure now, we’ll notice in the messages we see that the insert took place:
|
1 2 3 4 5 |
---- logged in as exampleOtherStaff USE GenExaAll GO EXEC stpSDRHoldingsOther |
If we try to read from the audit table as the exampleOtherStaff, we get an error, as this user does not have permission to this table outside of the insert within the stored procedure:
|
1 2 3 4 5 6 |
---- logged in as exampleOtherStaff USE GenExaAll GO SELECT * FROM tbAuditLog |
When we use our admin user that we’re creating these objects with and we look at the table, we see relevant information about the user that executed the procedure and the time:
|
1 2 |
SELECT * FROM tbAuditLog |
While we have other ways of tracking who executed stored procedures, such as using traces and other audits, we can use stored procedures in this manner even without giving the user access to the logging tables. This means that we can design for allowing some lower level users to add or change data through restrictions in stored procedures without access to the underlying objects.
Review
After we map out the levels of access, from the highest to the lowest level while restricting each level as much as possible, we can create an object such as views or procedures on top of other objects, such as tables. Using roles or even adding permissions directly to the user, we can grant the roles or users access to the higher-level objects, while keeping the underlying objects restricted. Finally, we can add logging to stored procedures, if we choose to go this route, as procedures can allow operations for roles or users without conceding permissions to the underlying objects.
Remember that this only covers the database layer; on the application side, we would want to restrict user input as much as possible and eliminate all data or information that gives a user more insight into what exists underneath (like seeing a report name they don’t have access to).
He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.
In his free time, he is a contributor to the decentralized financial industry.
View all posts by Timothy Smith
- Data Masking or Altering Behavioral Information - June 26, 2020
- Security Testing with extreme data volume ranges - June 19, 2020
- SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020