Need help locking my code for SQL Server

Question

We want to build into our software the capability to build SQL queries freehand (currently cannot do so with our software), but need to be able to lock it down so that users cannot make any changes, only select data from certain tables. Are there any good guides for helping me to lock this down appropriately (ie, least permissions needed, suggested forbidden keywords, etc)? Or do I just need to continue browsing the web and absorbing all of the SQL Server security tutorials (aimed at the system itself, rather than programming) I find? Thanks.

Answer 1

The easiest way to implement this is to create a service account that you will use in your system explicitly for this dynamic type of querying. The idea is that you use all that sql security goodness you've been absorbing and make a very restricted, low level service account on the server and use that to connect to the database.

I would suggest just giving them SELECT access for now and then releasing control gradually if it is requested by your users and is within reason.

This link will tell you how to give very specific account security to a logon using the GRANT keyword.

So all you need to do is create a new account in sql server, using sql server based security, not Windows. After that simply add the specific permissions that you want them to have to that account using the information in the above link. Then switch the connection string in your application to the new account for that particular piece of functionality.

Any other programming based security around this is an added bonus, but a programming solution alone will not be as bulletproof as a database level access restriction.

Answer 2

I'd go for belt and braces here.

• Make sure your query tool is proof against sql injection and unwanted queries.
• Don't talk directly to the database, use a server process to do that.
• Create a SQL Server user that has read only access to the tables required.
• Be paranoid about security, if you aren't, hire someone who is.

HTH

Answer 3

In addition to creating a least privilege account specifically for these kinds of operations (I prefer all my user accounts to only have EXEC on certain SPs and SELECT on certain utility views - that's usually it for starters), you can also use views to make the data as simple as possible and to perform certain common joins. You can use stored procedures or inline table-valued functions in cases where the user absolutely MUST provide some kind of filter, like a date range where you don't want them doing SELECT * FROM view.

Answer 4

create all objects (views, tables, procedures, functions) for SQL Server with tag "with encryption". For example: create procedure BBBB with encryption as begin ... end

after this, users can't view source code and cannot modify it.