URGENT: "Feature Restrictions" actually increases chances of SQL Injection and must be removed immediately (before RTM)
The new "Feature Restrictions" feature that was introduced in CTP 3.2 of SQL Server 2019 is highly problematic. It is an ill-conceived feature that does _not_ prevent SQL Injection in any way. It doesn't mask user error messages (only system errors), and it prevents WAITFOR but not a WHILE loop that accomplishes the same delay. So this feature is absolutely useless.
Actually, it's worse than that because people will assume that this feature actually does prevent these things, which will give them a false sense of security.
This feature is not fixable and needs to be removed entirely. This needs to be done _before_ SQL Server 2019 goes RTM so that it is not subject to the backwards-compatibility guarantee.
The main problems are:
1) EXEUTE AS 'dbo' or Login is member of sysadmin Fixed Server Role will prevent restriction.
2) Hiding error messages helps for scenarios where the error message is displayed, but you can still infer by testing conditions and forcing an error, just to see if the resulting behavior is different.
3) The "error messages" with masked internal information are only system generated error message. The output of RAISERROR and THROW is not masked!
4) WAITFOR is not the only means of causing a delay. Someone could just as easily use a simple WHILE loop.
For full details and example code that proves what I am saying, please see:
Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted Opportunity ( https://sqlquantumleap.com/2019/08/05/feature-restrictions-in-sql-server-2019-are-worse-than-useless-a-false-sense-of-security-and-wasted-opportunity/ )
Thank you for the valuable feedback.
We are looking closely into your comments and into various ways to improve this feature going forward. We are also going to make changes to the current documentation to address some of the concerns you raised.
Please continue to follow the evolution of Feature Restriction.
Solomon Rutzky commented
I updated my post (linked in the main content above) to add the following to the "Conclusion", to provide more explanation of why I feel that spending any more time trying to fix / expand this feature is futile, and provide some suggestions that I feel would do a better job of accomplishing this goal (which I do agree with):
This feature is intended to improve security when using Dynamic SQL. When using Dynamic SQL, it is common to run into permissions problems resulting from broken Ownership Chaining. While it is best to use Module Signing ( https://ModuleSigning.Info/ ) in order to resolve the permissions errors, many people still use the `WITH EXECUTE AS` clause with either `'dbo'` or `OWNER` as the User to "Execute As" (and if the Schema is owned by `dbo`, then that is the same as specifying `'dbo'`). If "Feature Restrictions" does not work with the `dbo` User, and yet it is common for stored procedures using Dynamic SQL to be executing as `dbo`, then how often will "Feature Restrictions" actually be restricting anything?
If anything is to be restricted, why not expand the existing permissions hierarchy by making more commands their own named permissions, commands such as `WAITFOR`, `RAISERROR`, `TRUNCATE TABLE`, `PRINT`, etc. Doing this fits naturally into the `GRANT` / `DENY` / `REVOKE` system that people are familiar with and would allow for `DENY`ing something such as `WAITFOR`, which can be a Database-level permission (along with `PRINT` and `RAISERROR`, while `TRUNCATE TABLE` can be Schema-level and/or object-level).
If the desire is to make Dynamic SQL more secure _without_ requiring code changes (or at least a lot of them), then a far better approach would be to attempt preventing end-user manipulation of the query, rather than what the query can do once it has been manipulated. There are only a few methods that someone can use to inject their own SQL, but if they can do even one of them, then there is a lot that they can do. Rather than focus on cleaning up a mess, focus on _preventing_ the mess (i.e. "an ounce of prevention is worth a pound of cure"). If the problems all begin with User input, provide one or more mechanisms to filter one or more input parameters. For example, implementing the following suggestion would not only work for this scenario, but it also handles other scenarios, thereby increasing the ROI for the time / energy put into developing it: "Intercept query batch from client to rewrite it or cancel it" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896183-intercept-query-batch-from-client-to-rewrite-it-or ).
Solomon Rutzky commented
I found two more problems:
1. If the `EXECUTE AS` clause specifies a User that is associated with Login that is “sysadmin”, _and_ the DB is set to `TRUSTWORTHY ON`, _and_ the DB is owned by a Login that has a certain level of permissions, that will prevent the restrictions from working as expected.
2. No permissions are required to `SELECT` from `sys.sql_feature_restrictions` system catalog view, even though it should require at least having the `CONTROL` DB-level permission.
I have updated my blog post (linked at the bottom of the main ticket body above) to include these new issues.