Reasonably secure way to allow table name access on client side

Question

We have a need coming up in an application where the following is true:

• A web page uses AJAX to request data from a server.
• The specification of the data (e. g. table name) requested from the server will not be known until run-time.
• The configuration of the data view is itself data-driven, and configurable by an administrator.
• Data updates and inserts must be supported, not just views.

Prototyping this was very easy - we could pass in the appropriate information (table name, changeset, whatever) to a generic data service that just did what it was told (using JSON as the data storage mechanism). The data service could do basic validation on the parameters to ensure the current user can perform the requested operation (read the data, insert a row, read the row).

The issue we have now that we are looking to doing this is a secure production manner, and the idea of passing table names and column names is frightening. Everything we think of to deal with this devolves into trusting the client in some significant way, or seems to involve substantial bookkeeping on the server. For example:

1. User requests a viewing page.
2. The server notes the table name and saves it server side with a request ID
3. The server notes the column names and saves them, replacing them with "col1, col2", etc., and stores the mapping with the request ID data.
4. The client page sends the request ID to the service, which looks up the server storage by ID
5. The service returns col1, col2, etc.

This would work, we think, but feels very messy.

Does anyone have experience with this kind of problem and can offer a solution?

Answer 1

If I had to expose back-end information to end customers, I'd probably hide the actual physical representation using meta-data that would remap table names and columns to more user-friendly text, that would also enable me to provide views on the tables that are a bit more advanced than plain table / column names... As properly modeling associations between tables and so on...

Answer 2

Do you need to give them access to raw tables? Perhaps you can go meta, and make a meta-table that stores the tabular data in a secure manner (ie, only the system knows the table/schema, but the user's concept of schema/table are just abstractions that all map back to the same schema/table)...

Again, more information is needed as to what can be abstracted. Allowing DDL operations by the end-users is asking for trouble, as you rightfully assessed, and I would just abstract that so that "DDL" becomes DML.

However, mapping actual SQL that is written against this data would be much more difficult to abstract, if that is a requirement.