Is it possible to enforce Read Only behaviour with SqlCommand?

Question

Is it possible to enforce read only permissions using the System.Data.SqlClient code accessing a Sql Server database?

I want to allow trusted users to write their own SELECT statements, in a web site.

NO Im not trolling here! Obvious solutions are to create a readonly user in the database, and use those credentials in the connection string, and surely only an idiot accepts a SQL statement in a webpage. This is a user deployment issue, I don't trust someone else to set that up correctly and don't want to write code to check that the readonly connection string is readonly.

One solution would be to parse the SQL and verify that it is a readonly command, or to do something similar. What I want to do is to do something like;

SqlConnection conn = new SqlConnection(myConnectionString, Flags.Readonly)

update Given a connection string with SA priviledges, "create user blah with password=xxx" "use my-db" "create login blah" "grant select on mytable to blah". Then make a new connection string.

Answer 1

You could use a transaction and always rollback? (but make sure the executed sql doesn't commit)

Answer 2

Create a new login in SQL Server and only give that login the permissions you want on the tables. Then in the connection string have the application use that login. You mention this as an obvious solution in your post but I don't see why you wouldn't want to do it this way.

Answer 3

No, there is no built-in facility for ensuring that end-user actions don't have side effects. While it may be simple in your scenario, a general-purpose implementation of this would be incredibly complex, if not impossible. What if the select statement uses a UDF that has side effects?

Answer 4

create another database, possibly restore the nightly backup, so it a day old. only allow the users to access this database. Then users can't slow down production with their awful queries or really hurt anything if your security fails and an change is made.