0

I have a web application which takes sql queries and produces the output in the form of a report. I don't want the user to MODIFY the existing database any way. To do this I decided to block all the CREATE,ALTER,DELETE,DROP commands at the web application level by just looking at the first word of the supplied query. This would stop the user from altering the existing schema or the instance of the database.

Recently I discovered that Microsoft SQL Server has the command SELECT * INTO NEW_TABLE FROM OLD_TABLE which allows us to create a copy of the the existing table. Are there any more commands of this kind which would allow us to modify the schema or instance of the existing DB by passing the web application filter ?

Any better ways to block the commands according to my requirements are also welcomed but I do not wish to take away the freedom of creating report using SQL queries at the cost of security.

Why Cannot I use Grant

I see that grant is one good option that I see from the comment as well as the answers but I will not be able to use them because the user supplies the DB details which I use to create the report along with the username and password. It is for the DB's table the user points to that I create the report

vikkyhacks
  • 3,190
  • 9
  • 32
  • 47
  • 1
    Learn [`GRANT`](http://msdn.microsoft.com/ru-ru/library/ms187965.aspx) – Alexander Mar 04 '14 at 05:24
  • 1
    Blocking the CREATE command will also restrict your user from creating temporary table that are often required while creating complex reports. – Riz Mar 04 '14 at 05:25
  • http://stackoverflow.com/questions/18003737/sql-server-prevent-updates-to-a-table-via-user-permissions – vhadalgi Mar 04 '14 at 05:28
  • Better to disallow your web application users from executing any arbitrary SQL statements at all. – Jeffrey Kemp Mar 04 '14 at 05:55
  • @JeffreyKemp Even I understand the risk, but is there any better alternative you propose ? – vikkyhacks Mar 04 '14 at 06:40
  • Um, just don't allow users to enter SQL statements at all. I don't know how things are done with SQL Server, but in my Oracle web applications, all the SQL is written by me, user data is always bound so SQL injection is impossible. – Jeffrey Kemp Mar 04 '14 at 06:42
  • @JeffreyKemp I get the idea of stopping users from entering sql commands, but my question is how can I make them get the report from the table they desire after I take away the freedom of allowing them enter their own queries. I don generate the report, the user generates it for himself – vikkyhacks Mar 04 '14 at 06:48
  • I wouldn't offer them that freedom. If my users needed to specify adhoc queries, I'd offer them a menu of possibilities (e.g. tables, columns, predicates, sorting, grouping, etc) which they can choose from. That way I'm in total control of the SQL that is being issued to the database. If I have some power users who want to be able to issue arbitrary SQL to my database, they can use one of the many tools that are available for that purpose; and I'd give them a database account tailored for their use, with limited grants. – Jeffrey Kemp Mar 04 '14 at 06:52
  • If you believe that scanning query test is the right approach (it generally isn't) do white-listing, not black-listing. Decide which queries you *will* allow and block everything else. – Damien_The_Unbeliever Mar 04 '14 at 07:12
  • @Damien_The_Unbeliever Well that is what is currently done now, but `select * into new_table from other_table` bypasses that filter, so I am curious if other such queries exist. @Jeffrey Your comment makes sense, you have a point there. I'll keep that in mind. – vikkyhacks Mar 04 '14 at 07:37
  • No, it's not what's done now. a `SELECT` clause that ends with `columnname into new_table` doesn't resemble anything valid in a normal `SELECT` clause. So you're *not* white-listing - you're using some form of black-listing. – Damien_The_Unbeliever Mar 04 '14 at 07:41
  • You should really block queries containing the keywords `CREATE,ALTER,DELETE,DROP,INTO,LOAD` at *any* position – symcbean Mar 04 '14 at 14:24
  • If you're using Oracle, execute `SET TRANSACTION READ ONLY...` before the user supplied SQL statement. If you're not using Oracle, then please remove the `oracle` tag, this is an engine-specific question. – Daniel Vérité Mar 04 '14 at 15:00

2 Answers2

2

You can generate the reports from results of a query performed by a user with only read permissions. This implies management of the database connection to allow other parts of the application to manipulates the data ( you will need to connect as different users). 

CREATE USER 'foouser'@'localhost' IDENTIFIED BY 'barpass';  
GRANT SELECT ON db.table TO 'foouser'@'localhost';
Ярослав Рахматуллин
  • 7,599
  • 1
  • 41
  • 60
0

Even if you use "SELECT * INTO NEW_TABLE FROM OLD_TABLE" you have to create the new_table first using create statement then you can use that statement.

user3324373
  • 13
  • 1
  • 2