Restricting to SELECT only

  • Thread starter Thread starter John Downs
  • Start date Start date
J

John Downs

Hi
I'm trying to make a query tool that will let users access a SQL Server
database directly, over an ASP.NET page, but I want to make it so they can
only query the database, and not make any changes. The best way I can think
of doing this is somehow restricting them to using SELECT queries only. Is
there any way I can do this?

Thanks very much

John Downs
 
The easy way, not necessarily the one I recommend is to load an array with
all of the DDL/DML commands other than "SELECT". Iterate through the array
and check for their existence and cancel the query if they are present. A
better way would be to use a Regex validator. You could also (which I
recommend) is give them a list of Stored Procedures and give them the
ability to pass in params for GroupBy Fields and OrderBy etc. Grant a
permission to the proc but don't let them query your DB outside of those
procs. It's more work for sure, but a lot less work than explaining how
some a-hole took down your db


HTH,

Bill
 
If you are allowing users to type in ad-hoc queries then the best thing to
do is use SQL Server restrictions and lock down the database for the user
your ASP.Net process is running as.

Colin
 
Yes,

Use stored procs where you can to select, update and insert data. Give the
sql server user permissions on the stored procs and views but not on the
database tables.

This is the way we do it and we can give great flexibility to the user (e.g.
querying the database through MS Access) whereby you link views into access
as tables.

Ive
 
Back
Top