1

I support a number of applications some of which have sql databases. There's a number of scripts (mostly powershell/bash) to check the state of an application to see if it's considered healthy. Some of these scripts make calls out to the sql databases.

I'm fairly certain I could just change the script in place to do something malicious and either run it myself or wait for someone else in my team to run it

Is there any way of preventing or at least mitigating some of the potential damage that a PU could do with a script that hooks into a sql db?

bain2236
  • 47
  • 5

2 Answers2

0

In reality, it isn't even a matter of adjusting the script. Presumably there are SQL credentials in the script somewhere to allow it access, and you could take those credentials, connect directly to the server, and do whatever you want.

It's worth emphasizing that this is a very valid concern. "Insiders" are responsible for roughly half of all data breaches, with roughly half of those being intentional and the other half accidental. As a result it's actually very important to limit and monitor all access to production databases that contain anything sensitive. You have two main options:

The Principle of Least Privilege (wiki)

The principle of least privileges is a key aspect of information security. The idea is that people/things should have access only to exactly the things they need to do their "job" - nothing more. In your case what this means is that the database access credentials being used by the script shouldn't have full and unfettered access to the database. They should have read-only privileges to only the tables/columns necessary to get the data they need.

This means that you'll have to create a new user account in SQL server that has substantially limited access to the database. Most people don't bother with such a step because it can take more effort to manage. For instance, if your needs change and your script needs to check some more tables, someone will have to update the SQL user to increase their privileges. However, based on real-world experiences of many companies, giving large numbers of developers unrestricted access to the production database is usually a bad idea for a lot of reasons.

So, if your current setup with your script is convenient, then update the SQL access credentials to give the associated user as little access as possible.

Remove SQL access all together

Personally though I'd probably go another route (although this is still just a variation on the "Principle of least privileges"). As a general rule of thumb I don't like giving out SQL access at all, other than to mitigate the bus factor. Most likely you can come up with another solution all together that won't require giving anyone direct SQL access.

This could come down to generating some API calls or a web portal to accomplish the same tasks. You would give someone access to the API/portal rather than to your server and scripts (which really just gives them direct database access). You can still limit database access for the user that the API logs in as, just in case the web system ends up with its own vulnerabilities for people to exploit.

The advantage of an API/portal is that you have many more options for tracking user activity, auditing, and access controls. The disadvantage is that it is another system you have to build, with its own potential security concerns. However, based on the fact that you already have a number of applications, scripts, and users, I think it is only a matter of time until this is the best solution for you.

Summary

Your first step would be to limit database access for the user that your scripts are operating as. However, in cases like this it's easy to get stuck in the habit of just writing up a new script everytime some new administrative task is needed. This can quickly lead to the kinds of disorganization, easy access, and thrown-together-coding that lend themselves to a major data leak. Designing and building an administrative tool meant to enforce access controls, auditing, and the principle of least privilege puts you in a place where you can default to good security.

Granted, that suggestion may be overkill for you right now. However, if you continue to grow, there will come a point where your current solution borders on outright negligence.

Conor Mancone
  • 29,899
  • 13
  • 91
  • 96
  • Thanks it was roughly along the lines I was thinking but formulated it more concisely than I would have – bain2236 Aug 01 '19 at 14:26
0

Depending on what the scripts do it is possible you could restrict the access to reading the database internal state only.
In Microsoft SQL Server this is done by granting the VIEW DATABASE STATE as described in the documentation here.

This sounds like a monitoring account and if it is only reading database state the SQL server permission above will do the job. If it is reading things like free disk space then the Windows permissions can be limited as well.

kevinskio
  • 161
  • 5