{"id":88723,"date":"2020-10-19T15:18:22","date_gmt":"2020-10-19T15:18:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=88723"},"modified":"2026-04-21T13:24:36","modified_gmt":"2026-04-21T13:24:36","slug":"exploring-errors-to-reveal-unauthorized-information","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exploring-errors-to-reveal-unauthorized-information\/","title":{"rendered":"SQL Server View-Based Security Flaw: How Error Messages Expose Hidden Data"},"content":{"rendered":"\n

A common pre-SQL Server 2016 pattern for row-level access control used views: create a view that filters rows based on the current user, grant users access to the view but not the underlying table. This article demonstrates a specific information disclosure vulnerability in this approach: even if a user cannot SELECT a row from the view, they can infer the row exists by attempting to INSERT, UPDATE, or DELETE against the view and reading the resulting error message. <\/strong><\/p>\n\n\n\n

SQL Server’s error messages for constraint violations, duplicate key errors, and other integrity errors can reveal information about rows the view is supposed to hide. The fix is SQL Server’s native Row Level Security feature, introduced in SQL Server 2016.<\/strong><\/p>\n\n\n\n

Maintaining a secure environment is very hard. There are so many threats that can be exploited that it demands a specialized security team to continuously evaluate, monitor, and audit the many known and unknown threats. SQL Server is just another process that can be exploited and needs to be monitored. Still, since the database\u2019s nature is to store information, including sensitive information, it is one of the main targets chosen by attackers.<\/p>\n\n\n\n

In this article: how to reveal information that a user is not supposed to see (and how to protect it)<\/h4>\n\n\n\n

In this article, I would like to show you a technique that can be used to reveal information that a user is not supposed to see and how to protect it. The technique is very simple, and it relies on SQL Server error messages to reveal information that could be stolen by adversaries. This technique has been used for several years in SQL Injection attacks, and many DBAs still overlook it.<\/p>\n\n\n\n

The idea is simple; a non-privileged user can write a query referencing a SQL Server view that causes an exception, such as invalid conversion<\/em><\/strong> to be thrown during query processing if certain row values exist in the underlying tables. Depending on the query plan, these exceptions may bypass SQL Server permission validations and are thrown even if existing data cannot be retrieved through the view.<\/p>\n\n\n\n

Before I move forward with recommendations, let\u2019s understand the problem starting by looking at a simple example.<\/p>\n\n\n\n

Note: I\u2019m running all tests on Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) – 14.0.3294.2 (X64) . Other versions may have different results.<\/p>\n\n\n\n

A simple view-based row-level security<\/h2>\n\n\n\n

Before SQL Server 2016 and row-level-security<\/a>, it was very difficult to implement restrictions on data at the row level. One of the most common solutions for this requirement is to use a view with a predicate filter used to reveal only the information a user has access.<\/p>\n\n\n\n

For instance, consider the following scenario:<\/p>\n\n\n\n

Create two user accounts that will demonstrate different access capabilities.<\/p>\n\n\n\n

USE tempdb\nGO\nDROP USER IF EXISTS Manager\nDROP USER IF EXISTS User1\nCREATE USER Manager WITHOUT LOGIN  \nCREATE USER User1 WITHOUT LOGIN  \nGO<\/pre>\n\n\n\n
Create a table to hold test data.<\/p>\n\n\n\n
DROP TABLE IF EXISTS TabSalary\nCREATE TABLE TabSalary  \n(  \n    EmpID      INT,\n    Employee   VARCHAR(10),  \n    Salary     NUMERIC(8,2),\n    HideSalary BIT\n);\nGO<\/pre>\n\n\n\n
Populate the table with four rows of data.<\/p>\n\n\n\n
INSERT INTO TabSalary VALUES(1, 'Bob', 1000, 0), \n                            (2, 'Jonh', 5000, 0),\n                            (3, 'Mark', 8000, 1),\n                            (4, 'Robert', 9500, 1)\nGO\n-- 4 rows...\nSELECT * FROM TabSalary\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
What is a row-level security view?<\/h4>\n\n\n\n
A row-level security view controls which rows each user can see. For instance, the Manager user will be able to see all rows, while any other user will only see rows where HideSalary is equal to 0.<\/p>\n\n\n\n
DROP VIEW IF EXISTS vw_LowSalary\nGO\nCREATE VIEW vw_LowSalary\nAS\n  SELECT EmpID, Employee, Salary FROM TabSalary\n   WHERE (HideSalary = 0 OR USER_NAME() = 'Manager')\nGO<\/pre>\n\n\n\n
Grant read access on the view to users.<\/p>\n\n\n\n
GRANT SELECT ON vw_LowSalary TO Manager \nGRANT SELECT ON vw_LowSalary TO User1 \nGO<\/pre>\n\n\n\n
Now, if User1 tries to access the data, it will return the following:<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM vw_LowSalary\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
User Manager will have access to all rows:<\/p>\n\n\n\n
EXECUTE AS USER = 'Manager'\nSELECT * FROM vw_LowSalary\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The problem with this approach is that any user with read access to the view can write a carefully crafted query that uses an expression executed in a specific order by the query optimizer. This causes an information leak through the use of the exception error message.<\/em><\/strong><\/p>\n\n\n\n
For instance, if a query that attempts to convert the Employee column to an Integer, it returns the following message:<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM vw_LowSalary\nWHERE CONVERT(INT, Employee) = 1\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Bob is a name that User1 could already access, so there is no \u201cleaked information\u201d here. But what about a query that ignores employees Bob and Jonh that User1 can already access?<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM vw_LowSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND CONVERT(INT, Employee) = 1\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
User1 shouldn\u2019t be able to see Mark\u2019s row, right? How can User1 see Marks salary information? Easy, just change the query to convert the salary column.<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM vw_LowSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND CONVERT(INT, CONVERT(VARCHAR, Salary)) = 0\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As you can see, even though there is a security predicate in place to prevent a malicious user from directly querying other people’s salary, User1 was able to determine the data by running a query that returns a SQL Server error message to leak the data.<\/p>\n\n\n\n
Important to note!<\/h4>\n\n\n\n
To leak the correct information, you need to make sure that the query processor is evaluating the expression in the correct order. Otherwise, it would first try to convert the data in the row the user already has access to and not leak the desired data. The query optimizer will have to create a plan that is pushing the predicate down to the table access. Look at the execution plan of the query to confirm the expression evaluation order. If necessary, you may need to force a short-circuit using a CASE<\/a> expression.<\/em><\/p>\n\n\n\n
Row-level security in SQL Server 2016 – does it save the day?<\/h4>\n\n\n\n
Row-level security<\/a> in SQL Server 2016 was introduced to address this problem. Well, kind of. Considering the same scenario, you would need to do the following:<\/p>\n\n\n\n
The first step is to create a function with the predicate that will be used in a security policy.<\/p>\n\n\n\n
DROP SECURITY POLICY IF EXISTS SalaryFilter\nGO\nDROP FUNCTION IF EXISTS dbo.fn_SecurityPredicate\nGO\nCREATE FUNCTION dbo.fn_SecurityPredicate(@HideSalary CHAR(1))  \nRETURNS TABLE  \nWITH SCHEMABINDING  \nAS  \n  RETURN SELECT 1 AS fn_securitypredicate_result\n          WHERE (@HideSalary = 0 OR USER_NAME() = 'Manager');  \nGO<\/pre>\n\n\n\n
Create a security policy on table TabSalary.<\/p>\n\n\n\n
CREATE SECURITY POLICY SalaryFilter\nADD FILTER PREDICATE dbo.fn_SecurityPredicate(HideSalary)\nON dbo.TabSalary\nWITH (STATE = ON); \nGO<\/pre>\n\n\n\n
Grant access to table TabSalary to users.<\/p>\n\n\n\n
GRANT SELECT ON TabSalary TO Manager;  \nGRANT SELECT ON TabSalary TO User1;  \nGO<\/pre>\n\n\n\n
Just like when using the view, User1 only has access to employees \u201cBob\u201d and \u201cJonh\u201d.<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM TabSalary\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The user Manager can see all rows.<\/p>\n\n\n\n
EXECUTE AS USER = 'Manager'\nSELECT * FROM TabSalary\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
What is the main difference between the view-based solution and the row-level security feature?<\/h4>\n\n\n\n
Here is the main difference between the view-based solution and the row-level security feature. If User1 tries to run the implicit conversion query, the query will return the following error message:<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nSELECT * FROM TabSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As you can see, the data is masked. Because the query failed, the attacker can still infer that there are more rows in the underlying data. They can even write a query to identify the salary value, but it will be more difficult, and the data will never be leaked to the user screen. A query to infer the salary would be something like:<\/p>\n\n\n\n
EXECUTE AS USER = 'User1'\nGO\nSELECT * FROM TabSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND Salary = 7999\n  AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0\nGO\nSELECT * FROM TabSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND Salary = 8000\n  AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0\nGO\nSELECT * FROM TabSalary\nWHERE Employee NOT IN ('Bob', 'Jonh')\n  AND Salary = 8001\n  AND CONVERT(INT, CONVERT(VARCHAR, Employee)) = 0\nGO\nREVERT\nGO<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As you can see, a user could write a query in a loop to test a range of values and infer that a value exists. It is limited information, but in some scenarios, this could be a security problem.<\/p>\n\n\n\n
How to minimize the risk<\/h2>\n\n\n
\n