Timothy Smith

Restricting and monitoring SQL Server data access with views and procedures

May 17, 2018 by

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

Our example hierarchy used in our example.

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.

In our first example, we will create two views: one view for the executive staff and the other 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):

What we’re using here is a 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 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:



Direct access to the table and executive view fail, while the view for other staff (since that is the logged in user for our example) passes.

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:

If we try to execute both stored procedure when logged in as the exampleOtherStaff user, we only get the result for the procedure granted execute permission:



The user exampleOtherStaff is able to execute only the procedure granted to it.

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

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:


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:


The stored procedure that we altered allows us to add records to the table with the user, but the user can’t do this directly or even read from the table since it hasn’t been granted those permissions.

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:


The audit table records the date and user with the final insert statement.

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

References

See more

To audit SQL database and security activities, consider ApexSQL Audit, an enterprise level SQL Server auditing tool.


Timothy Smith

Timothy Smith

Tim manages hundreds of SQL Server and MongoDB instances, and focuses primarily on designing the appropriate architecture for the business model.

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
Timothy Smith
Auditing

About Timothy Smith

Tim manages hundreds of SQL Server and MongoDB instances, and focuses primarily on designing the appropriate architecture for the business model. 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

654 Views