Database specific security in SQL Server

Users:
Logins and users are sometimes used interchangeably, but within SQL Server, there is a clear distinction. A login is used to authenticate on the server side and a user is used for access on the database side. In each database a user can be associated with one server login. Logins might not be associated with users in all databases, and it is possible for a user to exist without any association to a login.

Something to take into account is that sometimes database users get orphaned. This happens when a database user is based on a login, but this login no longer exists. Here is a guide on how you can troubleshoot this. You can give the user either the same or a different name compared to the login used for association. There are 13 types of users to create. How you will utilize these all depends on what u actually want to do.

Schemas:
Every database starts with its defaults schemas. Dbo , guest, sys, INFORMATION_SCHEMA. In a lot of cases dbo is used, but we can create new schemas. With this, we have a way to make a separate container for database objects. This has a couple of advantages like being able to assign narrower rights/access on the database, more easily transfer objects between two different schemas, reuse of same table names because of the prefix that you need to include while using a different schema (example: intern.users and extern.users). For more details, I found this post extremely useful.

Database roles:
As with schemas, when you create a database there will be several default database-level roles. As before, we can create our own and do a lot with those. We can grant/deny/revoke very specific access through these roles. Like, if we created a new schema and we have a user that only needs read access on all objects of this schema we can create a new db_role as follows:

• CREATE ROLE intern_reader
• GRANT SELECT ON SCHEMA :: intern TO intern_reader;

This is just the tip of the iceberg. Here you can find more about granting schema permissions but it does not stop there. You can also grant rights on specific objects. For example, a role that needs read-write access to all user tables regardless of schema. Or if we want the intern_reader role to be able to read from all tables but one we can do DENY SELECT ON intern.paycheck for example. Thus in short with database roles we can manage who has what access on the database and which objects/schemas.

Ownership Chaining:
When you create a new schema, it will get an owner assigned. Unless specified otherwise this will be the dbo user. Each new object created under this schema will have the same owner unless specified otherwise. To specify an owner as follows;

ALTER AUTHORIZATION ON SCHEMA/OBJECT :: SCHEMANAME/OBJECTNAME to OWNER

This can be used as one more way to narrow down who can see with in your database. With this technique you can give a user permission to select from the view but not directly from the connected tables, or execute stored procedures while giving rights the underlying tables if the objects are owned by the same owner.

Here you can find a blogpost that can give you a more in depth understanding of this.

Row Level security:

This feature is configured on table level and is used to determine what users can view which rows. Because it is configured in the database, there is less need to manage security on the application layer.

RLS works for example like this. Imagine there is a company with a CEO, several team managers and the employees divided over several teams. You want to have everybody be able to select on the salary table, but not everybody should be able to see everybody’s salaries. The employees should be able to only view their own salary, the team leads their own salary plus their team members and the CEO to be able to view all. With RLS you can achieve this. You will need to create user or specific database_roles for access management. When using roles, IS_MEMBER() shall need to be used int the function. An inline table-valued function, used for the filtering predicate for the table. And the security policy on the table in which we reference the inline table-valued function created previously.

Here you can find a great blog with examples about this technology. If however you are still working on an older version of SQL, this way can also be used to implement RLS.

Dynamic Data Masking:

DDM and RLS may seem similar at first view. Both features are intended to make the viewing of sensitive data by unauthorised users impossible and both are on the database level. But how it works is very different. With DDM you will be able to select all the data, but the values of this data will be masked while with RLS you will only see the relevant data.

You configure this data masking in the column definition of the table. After your datatype definition you will have to add  “MASKED WITH (FUNCTION = “ and then your NULL, not null or other definitions. Be warned, there are some limitations and also a security flaw. The flaw works like this: while the function does mask the data for the user, it still holds its true values. So if a user with access to the salary table, does a select * from salary where salary > their salary then they can still know who is earning more than them. If that user wants they can be more specific in that ‘where’ clause to get more specific details, all while the query results show them nothing.

Since SQL Server 2022 there is also a new feature added to this called DDM with granular permissions, this is also relevant for Azure DBs.

A second form called Static Data Masking was promised to be implemented as preview in SSMS 18, but Microsoft did not deliver. It should enable you to use a feature which will randomise data of your choice. This change would be permanent and would be great for example to alter data from Production you use to refresh in DEV. This should have been implemented into SSMS 18.5, but it was not even in SSMS 19. So let’s move on

Transparent Data Encryption (TDE):

This feature will be encrypting your data when it is at rest. This means your page file when it gets written to disk will be encrypted and when it gets read into memory it will be decrypted. TDE will also encrypt your backups. This means if the scenario plays out that your physical files of the DB or the backup gets stolen, the thief will not be able to simply open it without the needed keys and passwords. Once you have done the setup for one of your user DBs your Tempdb will also be encrypted in the same way. Other system DBs cannot be encrypted this way. This feature does have a little impact on performance so be aware. It does not have impact on the database size.

Always Encrypted:

While TDE protects the data at rest on the server side, it doesn’t protect data in transit or from unauthorized access within the company. For this, Microsoft introduced Always Encrypted (AE) in SQL Server 2016. AE does both, it allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. It allows you to encrypt only certain columns, rather than the entire database. This way sensitive data only gets to be viewed by those who use the data. Those who manage the data like us DBAs will not be able to view the data.

I found this and this to be very helpful for an in depth explanation.

In conclusion, there are many versatile methods to secure data both inside and outside the database. However, it’s crucial to adhere to the Principle of Least Privilege, ensuring that users have only the necessary permissions.

The next blog in this series will be about SQL Ledgers.  Until soon!