CODEWITHSUNDEEP
c#sql-server
Chronicide
Chronicide

Reputation: 1152

How can I ensure that my C# application can only read from the SQL Server databases?

I'm building a C# application that runs/exports batches of Crystal Reports to PDF files. Some tests that I've done have shown that pushing the data to the report is much more efficient than letting the ReportDocument refresh it's own data.

I've found a way to extract the SQL from the Crystal Reports, and now I just need to safely execute the sql and bind the results back to the Crystal Report. My problem is that Crystal Reports have absolutely no built in checks to ensure that users aren't creating harmful Crystal Reports. You can create a Crystal Report, add a SQL Command Table, and provide the following as the "query":

DELETE FROM tbl_Test; SELECT * FROM tbl_Test

Crystal Reports won't see any problem with the above SQL. When you refresh the report, you'll lose all of the data in tbl_Test. The only limitation is the permissions of the sql user who is creating/running the report.

So, I need to make sure that my program isn't pulling/executing anything harmful. The best that I can come up with is to create a new user account on the SQL Server with read-only access to all databases and using this account to establish the connections within my application.

The problem with this is that I'm going to have my users log in with their existing SQL Server user accounts which each have their own existing permissions regarding which databases they have access to. I still need to limit what each user can view based upon their personal permissions, but some of these users have write-permissions to a number of databases, and I also need to make sure that any connection to any database from my application is 100% read-only.

Is there any way at all to establish a read-only connection to an SQL Server using the SQL Authentication of a user account that has permissions to insert/update/delete/etc.?

P.S. I am aware that any user with sufficient permissions/know-how/motivation could use Crystal Reports (...or any number of third-part applications, for that matter) to directly harm our databases. Right now I am more concerned about ensuring that my application can't be made into an unwitting accomplice. =P

Upvotes: 1

Views: 1391

Answers (1)

Coding Flow
Coding Flow

Reputation: 21881

THere is nothing forcing you to use the same connection for everything within the application. Why don't you use a specific connection for this particular task. That way you can leave all the existing user permissions the same but for this particular task create a new connection with a sql user that has only read only access to the required tables. That way no dodgy SQL in the reports is going to do anything bad.

Upvotes: 5

Related Questions