Blog

Limiting User Queries in SQL Server

Why Limit User Queries?

We often run into customers wondering how to limit user queries in SQL Server. The motivation for needing to limit queries is typically related to performance, security, and/or compliance.

From a performance perspective, administrators would like to limit queries so that a user is not able to write an ad hoc query that could exhaust the resources of the SQL Server. This could impact other user queries as well as application responsiveness.

From a security and compliance perspective, administrators would like to limit the types of data that users are allowed to view. This can be done by limiting access to data or masking data for users that are not allowed to access it. This can be to adhere to various compliance and regulatory requirements.

SQL Server’s Native Solution Falls Short

SQL Server Enterprise does provide a way to limit user queries through its Resource Governor feature. Note that this feature is only available in the Enterprise version, so not all SQL Server instances may have this capability.

The Resource Governor is focused on perserving performance by implementing resource pools and workload groups to help restrict the resources available to queries. While this does address the performance concern, it doesn’t help with security and compliance issues as it doesn’t provide a means of controlling who can accessing what data.

Another potential problem with using the Resource Governor is the upkeep. As part of the setup and continued maintenance, you need to be able to properly identify sessions to be able to place them in the proper resource pools and workload groups. With ever changing demands from business units and the data itself, the upkeep of the classification into the correct pools and groups could become tedious long term.

Our Proposed Solution

In this article, we’ll look at how Cyral can address the security and compliance gaps while also addressing the performance issues.

The System Setup

It’s useful to understand what all of the moving parts are for this so that my scenarios make more sense and can relate to more real world implementations.

The Data

For the purposes of this post, I’m going to be using the Wide World Importers sample database provided by Microsoft. Since this is a known sample database provided by Microsoft, I won’t go into too much detail on its structure as that is already documented on Microsoft’s site.

For the purposes of my different scenarios, I’ll focus on the Application.People table. This table Contains user names, contact information, for all who use the application, and for the people that the Wide World Importers deal with at customer organizations. This table includes employees, customers, suppliers, and any other contacts. For people who have been granted permission to use the system or website, the information includes login details.

The Database Users

In order to make management easier on the DBA, we’re going to only need a handful of users created in the database. We’ll be able to leverage Cyral to help map users to these database accounts. These users and their assigned database roles are below.

Database User	Database Role
admin	db_owner
readwrite	db_datawriter, db_datareader
readonly	db_datareader
app_user	db_owner

The End Users and their reasons for accessing the database

Let’s assume that we have the following users and roles with the following reasons for accessing this table:

User	Role	Reason
Ted	Human Resources	Ted needs to be able to access the table to be able to generate reports regarding how many employees have created accounts in the online application.
Bill	Marketing	In order to be able to send marketing emails and promotions to customers, Bill needs to be able to access all of the customer Email addresses.
Rufus	Support	Rufus needs to be able to generate reports on application users that have been locked out of the system.
app_user	N/A	This is the user used by the frontend web application. This user needs to be able to register new users and make updates to existing users. It will also need to be able to access user data to populate various screens within the application.

The Cyral Setup

At this point, we have successfully deployed Cyral within our organization. Cyral is sitting in front of our SQL Server. We have configured network access controls to deny all traffic directly to the database unless the requests come through the sidecar. I have set up the User authentication and authorization for this repo by registering the database accounts listed above. I have also set up the proper the following access rules for the groups specified.

SSO Group	Database Account
Human Resources	readonly
Marketing	readonly
Support	readonly

Implementing Policies to Limit User Queries in SQL Server

With Cyral in place, we are able to walk through how to limit user queries in SQL Server based upon their required level of access. These examples will make use of Cyral’s Global Policies policy structure.

Cyral’s platform is flexible enough to allow for policy creation in a number of different ways such as the:

I’m going to create these examples using Cyral’s Terraform provider. I’m assuming that you are familiar with Terraform so I will focus on just the template itself. Once everything is built using Terraform, I’ll also show what the policies look like in the Cyral Control Plane UI.

Creating the Label and Data Map

The first step would be to create the data label we want to use for the data within our application table using the cyral_datalabel resource.

resource "cyral_datalabel" "MSSQL_APP_PERSON" {
  name = "MSSQL_APP_PERSON"
}

With the data label ready, we can add on the data map. This data map is simple in that we’re going to tag all of the columns in the database with the same tag. We could decide to create multiple labels and label each column individually.

resource "cyral_datalabel" "MSSQL_APP_PERSON" {
  name = "MSSQL_APP_PERSON"
}

resource "cyral_repository_datamap" "sqlserver_datamap" {
  repository_id = abc123xyz789

  mapping {
    label = cyral_datalabel.MSSQL_APP_PERSON.name
    attributes = [
      "Application.People.FullName",
      "Application.People.PersonID",
      "Application.People.PreferredName",
      "Application.People.SearchName",
      "Application.People.IsPermittedToLogon",
      "Application.People.LogonName",
      "Application.People.IsExternalLogonProvider",
      "Application.People.HashedPassword",
      "Application.People.IsSystemUser",
      "Application.People.IsEmployee",
      "Application.People.IsSalesPerson",
      "Application.People.UserPreferences",
      "Application.People.PhoneNumber",
      "Application.People.FaxNumber",
      "Application.People.EmailAddress",
      "Application.People.Photo",
      "Application.People.CustomFields",
      "Application.People.OtherLanguages",
      "Application.People.LastEditedBy",
      "Application.People.ValidFrom",
      "Application.People.ValidTo"
    ]
  }

}

Creating the Policy

With the label and data map in place, I’m starting the policy using the cyral_policy resource.

resource "cyral_datalabel" "MSSQL_APP_PERSON" {
  name = "MSSQL_APP_PERSON"
}

resource "cyral_repository_datamap" "sqlserver_datamap" {
  repository_id = abc123xyz789

  mapping {
    label = cyral_datalabel.MSSQL_APP_PERSON.name
    attributes = [
      "Application.People.FullName",
      "Application.People.PersonID",
      "Application.People.PreferredName",
      "Application.People.SearchName",
      "Application.People.IsPermittedToLogon",
      "Application.People.LogonName",
      "Application.People.IsExternalLogonProvider",
      "Application.People.HashedPassword",
      "Application.People.IsSystemUser",
      "Application.People.IsEmployee",
      "Application.People.IsSalesPerson",
      "Application.People.UserPreferences",
      "Application.People.PhoneNumber",
      "Application.People.FaxNumber",
      "Application.People.EmailAddress",
      "Application.People.Photo",
      "Application.People.CustomFields",
      "Application.People.OtherLanguages",
      "Application.People.LastEditedBy",
      "Application.People.ValidFrom",
      "Application.People.ValidTo"
    ]
  }

}

resource "cyral_policy" "sqlserver_app_access" {
  data= [
    "MSSQL_APP_PERSON"
  ]
  description= "Policy to control access to the Person table in our Application database"
  enabled= true
  name= "SQLServer Application Access"
  tags= []
}

The policy resource is used to decide which data label will trigger this policy. In our case, this is the MSSQL_APP_PERSON label created above. The next step is to create each rule within our defined policy.

Creating the Policy Rules

With the policy in place, we now need to create each rule to handle the different use cases outlined above.

Creating the Policy for the Support Team

We’ll start with a policy that applies to anyone in the Support group in our IDP Integration. This is done by adding Support to the identities.group list. This policy does not have an updates or deletes section so an implicit deny will be in place for these functions. The policy will only explicitly allow reads with certain conditions applied to those reads.

As we are limiting user queries in SQL Server, we’re going to enforce two sets of limits (a row limits and rate limit) in the policy:

rows : 10
rate_limit : 100

We don’t anticipate the support team handling more than 10 users that have been locked out at a time. In addition, there should be no reason for support staff to query for more than 100 rows in an hour. This resulting limit on user queries will ensure that the support group can only query for 10 rows at a time. In addition, they will not be able to query for more than 100 rows in an hour.

resource "cyral_policy_rule" "sqlserver_app_access_rule_support" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Support"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = 10
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select PersonID, FullName, PreferredName, SearchName, IsPermittedToLogon, LogonName, IsEmployee, PhoneNumber, EmailAddress, ValidFrom, ValidTo from WideWorldImporters.Application.People where EmailAddress is NOT NULL and IsPermittedToLogon =0"
      parameters = []
    }
  rate_limit = 100
  }
}

In order to further help limit user queries on this data, we’ve implemented a Dataset Rewriting rule as well. This rewrite will first make sure the query is constrained to records that have some kind of EmailAddress present (EmailAddress is NOT NULL). In addition, this query will also only query for accounts that should be logging into the system (IsPermittedToLogon =0). We’re returning only a subset of fields that might be needed by the Support team with our query rewrite that will help reduce the load of a SELECT * (FullTableScan) query.

The rewrite will prevent a query like SELECT * FROM WideWorldImporters.Application.People from being executed. The resulting query that is sent to the database would be written similar to the one below

SELECT * FROM (select PersonID, FullName, PreferredName, SearchName, IsPermittedToLogon, LogonName, IsEmployee, PhoneNumber, EmailAddress, ValidFrom, ValidTo from WideWorldImporters.Application.People where EmailAddress is NOT NULL and IsPermittedToLogon =0)

Creating the Policy for the Human Resources Team

The logic for this rule is very similar to what we’ve created for the Support team. The primary difference for this rule is that it makes sure the data accessed is for employees only (isEmployee=1). This way people within the Human Resources team are only viewing employee related information.

resource "cyral_policy_rule" "sqlserver_app_access_rule_hr" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Human Resources"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = -1
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=1"
      parameters = []
    }
  }
}

Creating the Policy for the Marketing Team

This is simply the inverse of the Human Resources rule. This rule allows access to non-employee information only. Again, we are limiting the fields that they can access down to only what is needed.

resource "cyral_policy_rule" "sqlserver_app_access_rule_marketing" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Marketing"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = -1
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=0"
      parameters = []
    }
  }
}

Creating the Policy for the app_user

The policy rule I created for the app_user does not contain any rewrites because we want the application to have access to all of the fields so as to not limit its capabilities. We know the application does not do any bulk functions so we’re limiting it to only reading and updating single entries in the database at a time. This is done with the rows = 1 in the reads and updates sections of the rule.

This application should never delete data either so I’ve not listed any delete permissions in the rule. This will help ensure that the application doesn’t delete data. We maintain data deletions through other means.

resource "cyral_policy_rule" "sqlserver_app_access_rule_app_user" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    users = ["app_user"]
  }
  hosts = [“10.1.1.1”,”10.1.1.2”]
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = 1
    severity = "high"
  }
  updates {
    data = ["MSSQL_APP_PERSON"]
    rows = 1
    severity = "high"
  }
}

resource "cyral_repository_network_access_policy" "sqlserver_repo_policy" {
  repository_id = abc123xyz789
  network_access_rule {
    name = "app_user rule"
    description = "Rule to restrict access to only our application server pool"
    db_accounts = ["app_user"]
    source_ips = ["1.2.3.4", "4.3.2.1"]
  }

  network_access_rule {
    name = "any access rule"
    db_accounts = ["admin","readonly","readwrite"]
    description = "Rule to allow access from anywhere"
  }
}

If you look at the above block, there’s an additional configuration that is using the cyral_repository_network_access_policy which is leveraging the Network Shield feature from Cyral. Since the app_user has some additional privileges, we don’t want anyone to be able to use that account from outside of the application. This additional configuration block is only allowing the app_user to login from the application servers of 1.2.3.4 and 4.3.2.1. Any attempts to use the app_user from a different client will be denied.

It is important to note that once you enable Network Shield, the default behavior is to implicitly deny access. This is the reason that I created the second rule named any access rule. This rule explicitly allows access from any IP address for the admin, readonly, and readwrite accounts.

Putting It All Together

The Terraform Template

Here is what our full Terraform configuration looks like:

resource "cyral_datalabel" "MSSQL_APP_PERSON" {
  name = "MSSQL_APP_PERSON"
}

resource "cyral_repository_datamap" "sqlserver_datamap" {
  repository_id = abc123xyz789

  mapping {
    label = cyral_datalabel.MSSQL_APP_PERSON.name
    attributes = [
      "Application.People.FullName",
      "Application.People.PersonID",
      "Application.People.PreferredName",
      "Application.People.SearchName",
      "Application.People.IsPermittedToLogon",
      "Application.People.LogonName",
      "Application.People.IsExternalLogonProvider",
      "Application.People.HashedPassword",
      "Application.People.IsSystemUser",
      "Application.People.IsEmployee",
      "Application.People.IsSalesPerson",
      "Application.People.UserPreferences",
      "Application.People.PhoneNumber",
      "Application.People.FaxNumber",
      "Application.People.EmailAddress",
      "Application.People.Photo",
      "Application.People.CustomFields",
      "Application.People.OtherLanguages",
      "Application.People.LastEditedBy",
      "Application.People.ValidFrom",
      "Application.People.ValidTo"
    ]
  }

}

resource "cyral_policy" "sqlserver_app_access" {
  data= [
    "MSSQL_APP_PERSON"
  ]
  description= "Policy to control access to the Person table in our Application database"
  enabled= true
  name= "SQLServer Application Access"
  tags= []
}

resource "cyral_policy_rule" "sqlserver_app_access_rule_support" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Support"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = 10
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select PersonID, FullName, PreferredName, SearchName, IsPermittedToLogon, LogonName, IsEmployee, PhoneNumber, EmailAddress, ValidFrom, ValidTo from WideWorldImporters.Application.People where EmailAddress is NOT NULL and IsPermittedToLogon =0"
      parameters = []
    }
  rate_limit = 100
  }
}

resource "cyral_policy_rule" "sqlserver_app_access_rule_hr" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Human Resources"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = -1
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=1"
      parameters = []
    }
  }
}

resource "cyral_policy_rule" "sqlserver_app_access_rule_marketing" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    groups = ["Marketing"]
  }
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = -1
    severity = "low"
    dataset_rewrites {
      dataset = "Application.People"
      repo = “SQLServer”
      substitution = "select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=0"
      parameters = []
    }
  }
}

resource "cyral_policy_rule" "sqlserver_app_access_rule_app_user" {
  policy_id = cyral_policy.sqlserver_app_access.id
  identities {
    users = ["app_user"]
  }
  hosts = [“10.1.1.1”,”10.1.1.2”]
  reads {
    data = ["MSSQL_APP_PERSON"]
    rows = 1
    severity = "high"
  }
  updates {
    data = ["MSSQL_APP_PERSON"]
    rows = 1
    severity = "high"
  }
}

resource "cyral_repository_network_access_policy" "sqlserver_repo_policy" {
  repository_id = abc123xyz789
  network_access_rule {
    name = "app_user rule"
    description = "Rule to restrict access to only our application server pool"
    db_accounts = ["app_user"]
    source_ips = ["1.2.3.4", "4.3.2.1"]
  }
  
  network_access_rule {
    name = "any access rule"
    db_accounts = ["admin","sa","readonly","readwrite"]
    description = "Rule to allow access from anywhere"
  }
}

The Policy in the UI

The policy is expressed in YAML format in the UI as shown below

data:
  - MSSQL_APP_PERSON
rules:
  - identities:
      groups:
        - Marketing
    reads:
      - data:
          - MSSQL_APP_PERSON
        rows: any
        severity: low
        datasetRewrites:
          - repo: DO-K8-SQLServer
            dataset: Application.People
            substitution: select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=0
  - identities:
      groups:
        - Support
    reads:
      - data:
          - MSSQL_APP_PERSON
        rows: 10
        severity: low
        rateLimit: 100
        datasetRewrites:
          - repo: DO-K8-SQLServer
            dataset: Application.People
            substitution: select PersonID, FullName, PreferredName, SearchName,
IsPermittedToLogon, LogonName, IsEmployee, PhoneNumber,EmailAddress, ValidFrom, ValidTo from WideWorldImporters.Application.People where EmailAddress is NOT NULL and IsPermittedToLogon =0
  - identities:
      groups:
        - Human Resources
    reads:
      - data:
          - MSSQL_APP_PERSON
        rows: any
        severity: low
        datasetRewrites:
          - repo: DO-K8-SQLServer
            dataset: Application.People
            substitution: select FullName, PreferredName, PhoneNumber, EmailAddress from WideWorldImporters.Application.People where isEmployee=1
  - identities:
      users:
        - app_user
    reads:
      - data:
          - MSSQL_APP_PERSON
        rows: 1
        severity: high
    updates:
      - data:
          - MSSQL_APP_PERSON
        rows: 1
        severity: high

Testing Out the Policies

With our policy disabled, we can run a simple query (select * from application.people) using our Marketing account, Bill. The result of the query is below:

There are a number of fields returned in the query and we also get over 1100 rows returned. If we enable the policy and run that same query, the results returned are quite different:

The results are reduced to only the fields relevant to the user and we only have 1092 records returned. We can also confirm this is the case by reviewing the logs.

…
"identity": {
  "endUser": "bill_preston@turtleshell.net",
  "endUserEmail": "bill_preston@turtleshell.net",
  "group": "Marketing",
  "userGroups": [
    "CyralUsers",
    "Everyone",
    "Marketing"
  ],
  "repoUser": "readonly",
  "dbRole": "readonly"
…
},
"request": {
  "statement": "SELECT * FROM application.people",
  "rewrittenStatement": "SELECT * FROM (SELECT fullname, preferredname, phonenumber, emailaddress FROM wideworldimporters.application.people WHERE isemployee = ${cyral_redact}) AS people_6096063639036176601",
  "statementType": "SELECT",
  "isSensitive": true,
  "datasetsAccessed": [
    {
      "dataset": "application.people",
      "accessType": "read"
    }
  ],
  "fieldsAccessed": [
…

We can also confirm that the network access rule is working as expected by attempting to login with our app_user from a client IP address that isn’t specifically allowed. This gives the following error message:

Final Thoughts

These are some simple cases of leveraging some of the features Cyral has to offer. There are many other options available to help limit user queries in not just SQL Server but other databases as well with similar policies.

While this article focused on just our Global Policies, you can leverage some of our prebuilt Repo-level policies. These are prebuilt policy templates that account for common controls that customers implement across their databases. These templates accept a number of different parameters to help configure it to meet your needs.

For those that want even more granularity in their policies, we’ve also exposed our policy logic even more with our rego policies. These policies allow customers to build their own templates and parameters using the Rego language.

Subscribe to our Blog

Get stories about data security delivered directly to your inbox

Try Cyral

Get Started in Minutes with our Free Trial

Free Trial

Data Security Governance

Commitment to Security

Get Started

Featured Content

Platform

Technology

Spotlight Features