0

In our .net application, we have a tool that allows you to type SQL in a browser and submit it, for testing. In this context, though, I need to be able to prevent testers from writing to specific tables. So, based on the parameter passed from the controller (InSupportTool = true, or something), I need to know if SQL Server is allowed to make updates or inserts to, say, an accounts table.

Things I've tried so far:

  1. I have tried looking into triggers, but there is no before trigger available, and I've heard people don't recommend using them if you can help it.

  2. Parsing the passed SQL string to look for references to inserting or updating on that table. This is even more fragile and has countless ways, I'm sure, of getting around it if someone wanted to.

  3. Check constraint, which is the closest I feel I've gotten but I can't quite put it together.

For check constraints, I have this:

ALTER TABLE Accounts WITH NOCHECK 
    ADD CONSTRAINT chk_read_only_accounts CHECK(*somehow this needs to be dynamic based on parameters passed from C# controller*)

The above works to prevent updates to that table, but only if I put a check like 1 = 0. I've seen a post where people said you could use a function as the check, and pass parameters that way, but I'm at the limit of my familiarity with SQL/.net.

Given what I'm looking to do, does anyone have experience with something like this? Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan Bowman
  • 1,606
  • 1
  • 12
  • 17
  • Are you seeking an opinion, or is there any issue with the code? – Sonal Borkar Jan 19 '19 at 14:39
  • 3
    Why not just use a different login/user for your tool and deny update on the table in question? There's a rich permission model in SQL which can help you do permission enforcement – Conor Cunningham MSFT Jan 19 '19 at 14:39
  • SonalBorkar: I'm looking for opinions from people who have encountered something like this before, or if I'm close with one of my listed options I was hoping to get feedback and a nudge in the right direction. @ConorCunninghamMSFT So, I could have the sql that was passed be run as a different user than the one who logged in? – Jonathan Bowman Jan 19 '19 at 14:44
  • @JonathanBowman, so your app is using an application account instead of the end-user's account? Add the version-specific SQL Server tag to your question. – Dan Guzman Jan 19 '19 at 14:50
  • @DanGuzman added the version tag, and this is an internal-only application that I've just begun working on, so I'm unsure right now if the application is using its own account to execute the sql, or if it's using the permissions of the user who logged in. I'm going through that right now to see. UPDATE it looks like the connection string it uses to connect before running is using a different user than the logged in user, so maybe I can have a restricted Application level user for this specific tool? – Jonathan Bowman Jan 19 '19 at 14:57
  • Using SQL Server's built-in security features will work. Properly set up to deny everything and then grant limited access as needed will prevent mistakes like access being granted by default to new tables. Parsing ought to be reliable, but more complicated. (You don't let testers create synonyms, do you?) It also helps you weed out DDL from DML, `output` clauses writing to forbidden tables, ... . Depending on the changes you want to allow testers to make to data, you could wrap the statements in a transaction that you rollback when it completes. – HABO Jan 19 '19 at 15:09

1 Answers1

0

Since the application is running under a different account than the end user, you could specify your application name in the connection string (e.g. Application Name=SupportTool) and check that in an after trigger, rolling back the transaction as needed:

CREATE TABLE dbo.example(
    col1 int
);
GO

CREATE TRIGGER tr_example
ON dbo.example
AFTER INSERT, UPDATE, DELETE
AS
IF APP_NAME() = N'SupportTool'
BEGIN
    ROLLBACK;
    THROW 50000, 'This update is not allowed using the support tool', 1;
END;
GO

INSERT INTO dbo.example VALUES(1);
GO
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Oh, I didn’t know you could pass arbitrary parameters on the connection string like that! I’ll give that a shot and see if it gets it there and report back. Thank you! – Jonathan Bowman Jan 19 '19 at 15:11
  • Since this is specifically denying, rather than granting, access it suffers from the problem of new tables not being protected by default. Using [`sp_settriggerorder`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-settriggerorder-transact-sql?view=sql-server-2017) to set the trigger to fire first may help reduce side effects, e.g. from triggers that write to history logs. – HABO Jan 19 '19 at 15:14
  • @JonathanBowman, the `Application Name` connection string keyword is not arbitrary. It is a formal [`SqlClient` connection string keyword](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.7.2). The reason I asked about the version is that SQL 2016 introduced [`session_context`](https://stackoverflow.com/questions/25581002/how-to-add-custom-attributes-to-sql-connection-string), which does allow ad-hoc key/value pairs, whouch you could have used as an alternative. – Dan Guzman Jan 19 '19 at 15:16