1

We want to allow to an administrator (only) to execute an SQL SELECT statement via Web UI. Please note that the administrator is allowed to view all information in a database.

What are security risks with this approach? Is it possible to change somehow a database by SQL SELECT statement (any record or table)?

added

RDBMS is PostgreSQL

Michael
  • 10,063
  • 18
  • 65
  • 104
  • For further read: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Thanos Markou May 20 '14 at 08:30
  • I do know what is SQL injection. My question is different: Is it possible to change somehow a database by SQL SELECT statement (any record or table)? – Michael May 20 '14 at 08:32
  • Which RDBMS are you talking about? If there is a way, it's most likely RDBMS specific. – Joachim Isaksson May 20 '14 at 08:34
  • Yes, every SQL statement that takes parameters is subject to SQL Injection. – Thanos Markou May 20 '14 at 08:34
  • 2
    `SELECT INTO newTable FROM oldTable `... this create tables. – Mudassir Hasan May 20 '14 at 08:35
  • You can always use the full qualified identifier `database.table`. – Gumbo May 20 '14 at 08:41
  • 2
    did you mean the `SELECT` privilege? http://www.postgresql.org/docs/current/static/sql-grant.html -- or you will parse the query (provided on this ui), and only execute `SELECT` ones? – pozs May 20 '14 at 09:24
  • Execution of the select command. Unfortunately (thanks to mmhasannn) it is possible to change values using `SELECT INTO newTable FROM oldTable`: http://www.postgresql.org/docs/9.1/static/sql-selectinto.html – Michael May 20 '14 at 09:45
  • @Michael it depends. if you use the `SELECT` privilege only for that role, I'm (almost) sure it won't allow `SELECT INTO` statements (however the docs doesn't clarify this explicitly) – pozs May 20 '14 at 09:47

1 Answers1

3

We want to allow to an administrator (only) to execute an SQL SELECT statement via Web UI. Please note that the administrator is allowed to view all information in a database.

What are security risks with this approach? Is it possible to change somehow a database by SQL SELECT statement (any record or table)?

Well, how do you propose to let them execute SELECT statements? PostgreSQL doesn't have user-level statement privileges, it has privileges on individual database objects.

In theory you can absolutely modify the database with SELECT. Just look at PostgreSQL's function list. Not to mention the user-defined functions. The most obvious case is of course nextval, which advances a sequence, or setval which sets a sequence's position.

However, everything requires rights on the associated database objects. If you only GRANT the SELECT right on a table (or some subset of the column's tables) then the user won't be able to modify the data in that table directly. Nor does PostgreSQL provide functions to bypass that restriction built-in. So simply creating a user with only SELECT rights to some tables and USAGE rights on the schemas they're in should be fine.

There are some important caveats though. SECURITY DEFINER user defined functions can do anything the defining user can do so if you have SECURITY DEFINER user defined functions that weren't carefully written you might have opened security holes. The same is true of user-defined functions written in C - so consider your extensions carefully. Finally, you need to be extremely careful about any functions written in "untrusted" languages like plpython, plperl, etc (as opposed to plperlu which is sandboxed) as these must be implemented carefully to make sure the calling user can't trick them into doing things they weren't supposed to do.

If you plan to give a user direct access to write raw SQL you have to be more careful about securing your DB. Don't GRANT rights to public, and REVOKE them where they're granted by default. Check security definer functions to make sure they have a SET search_path option defined. Etc. It's generally safe, but only if you're careful.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778