1

I am securing an ASP.NET/MVC web application running on top of MS SQL. As part of that I have different database connections to perform different things in the website.

EDIT - Of course it is up to the DBA and Ops people to decide how data is accessed and how the app is hosted, but they can't do much with an application that isn't granular enough to afford them the controls they need to lock things down.

The system uses only stored procedures with no dynamic SQL. Some stored procedures mutate data, so I call those using a conceptually different connection than the one used for other data access that only reads. No SQL user has rights to access any other database object than a stored procedure. Account authorization has a separate connection. Some parts of the web application shows billing information, other parts of the website direct billable work. There seems to be ways of putting bulkheads into place that separate conceptually different things.

I am looking for a way to integration test that I am not overly generous with the privileges chosen. I would like a mechanism that throws an exception on the build server if a dev tried to use the wrong access level in the wrong place in the code, even if the mistake is that the chosen account actually has too many privileges, which would give no error from the database.

I have been down the route of creating an implementation of IDbCommand that would act as a factory of IDbCommand instances that would basically issue the same command onto the backend using two different connections using different principals that have different access rights in SQL Server and then throw if the lower privileges would have been sufficient.

I have two problems with this approach

  1. It is hardly 'elegant' or obviously flawless. I have barely finished thinking about it and it is so much code already. I'd have to queue up all things happening to the lower connection and then replay that on the higher privileged connection which will not be realistic in how it works and may cause weird timing issues with the other code. I like to code, but I also like my tests to be trustworthy and easily understood and without introducing a new set of complexities that make the tests brittle in a different way than the code under test. Maybe if I fully duplicate all the work and all the data and run separate databases, but then the build server scripting will be a nightmare.
  2. It hardly qualifies as granular.

Surely this is a solved problem that only because of bad luck or bad google fu evaded my several hours of searching?

hrillo666
  • 99
  • 1
  • 7
  • This is really more a matter for the DBA than for an integration test. – John Saunders Feb 20 '15 at 17:12
  • I don't agree, I'd say you would like to lock down various parts of the application that are functionally separate, so that once p0wned you haven't given away your entire site. It is hard for a DBA to make that distinction. I must at least provide the DBA with what "levers to pull" to know where to apply what rights. If the entire system runs on one connection configuration the DBA has little opportunity to lock things down – hrillo666 Feb 20 '15 at 17:17
  • It is up to the DBA to ensure data integrity. In particular, it is up to the DBA to establish roles and permissions, and to tell _you_ which connection strings to use. You should work with the DBA to establish which parts of your application need which access to which database resources. – John Saunders Feb 20 '15 at 19:11
  • How does the web app interact with the database? Does it pass dynamic or static query strings to SQL Server? Does it use stored procs for everything? Or maybe a combination of both? What are the "different things" in the website? Could the database objects for each of those "things" be tightly correlated with a separate database schema (for each thing)? BTW, I frequently encounter apps where no effort was made to use appropriate authorization. Most just set up a SQL login with membership in SYSADMIN and do nothing else. Kudos for pursuing a more secure model. – Dave Mason Feb 20 '15 at 19:14
  • I edited to add some answers to the questions raised here – hrillo666 Feb 20 '15 at 19:29
  • 1
    Since the web app only uses stored procs to interact with the database, you're in an excellent position. Regardless of how many different logins you use, the associated database user would only need the `EXECUTE` permission, right? Even if that db user had permission to execute every stored proc, it's still pretty limited. No other permission would be needed (`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `ALTER`, etc.) You could go more granular if you like: create db roles with `EXECUTE` permission to "logical" groups of sp's, then assign db users to those roles, etc. Does any of this help? – Dave Mason Feb 20 '15 at 20:22
  • I have thought about it. I know what the sprocs do, they only do one thing each. There never is any need for overlap, as in one sproc called from different contexts. As in, yes, this is all I need – hrillo666 Feb 22 '15 at 16:34
  • So feel free to add your comments as an answer so I can approve them? – hrillo666 Feb 23 '15 at 09:15

0 Answers0