"Safe" SQL for trusted staff - blacklist, whitelist, none of the above?

Note, this is purely hypothetical; the very idea gives me the heebie-jeebies. I'd far rather have a database management UI that covers all eventualities. But I got to thinking about whether it was possible to do this safely, and ended up tying myself in all kind of knots. Hence the question.

Anyway: Suppose I wanted to allow certain users of my website to search part of the database by entering "raw" SQL:

• trusted (and logged-in, obviously) staff only
• read-only access to the relevant tables
• no access whatsoever to the other tables
• no possiblity of running unsafe/disallowed SQL, accidental or otherwise

All the tables are prefixed with the area to which they relate, e.g., videos_, photos_, forum_. I'd want to grant these users read access to certain sections, but not allow them to modify the data directly by entering SQL, even if I trust them to make changes through the UI.

I'd want to lock even these trusted users out of, say, all forum_ tables - otherwise they could read all users' private messages, admin forum posts, etc.!

The "trusted user" requirement is taken care of, they would have to be in a certain usergroup to see or use this. It's more the "safe SQL" part.

Is it as simple as connecting with a different DB user who has only read privileges and only on the right tables?

Or would I blacklist (and reject the SQL if it contains) forum_, DROP, ALTER, UPDATE, CREATE, INSERT, etc.?

Or whitelist SELECT, FROM, WHERE, the relevant table 开发者_JAVA技巧names and field names? That sounds safer, until I think about the need for WHERE field_name="Arbitrary text here".

Or some combination? (Say, logging use of "blacklisted" SQL, even though it's known to be castrated.)

Or run away screaming?

How would you tackle it? Is it actually possible to do this safely?

If your target end users are comfortable with SQL wouldn't you save yourself a lot of time by just giving them access to client access tools and limiting what they can do via their login permissions to said database.

Please don't try to manually filter bad SQL (on words like insert, delete etc...). That sounds like a horrible plan!

Your database should provide you with tested functionality to grant or revoke read / write / update / delete ... permissions for specific users / groups of users on specific schema's / tables, etc...

Many RDMS systems implement "schemas", which (for SQL Server) are essentially containers within databases containing database objects (such as tables -- "dbo" is the default schema if none is specified). Once set up, you could assign access rights to logins to the schemas and all objecgs contained therin--so if a table gets added, they'd automatically get (or be denied) access to it by means of the schema.

The downside is that you really have to plan ahead for it. To move all your existing tables into schemas now would require renaming everything, and updating all your existing code accordingly. Still, if you do it, it would make setting up and managing black and/or white lists much simpler.

You should (depending on your DBMS) be able to limit what users can gain access to using permissions down to the object level - even column level if you need that.

I'd suggest going for the whitelist rather than blacklist approach as whenever you add a new object (table, view etc.) you'll be safe by default - rather than getting into grief if you forget to set the object to blacklisted if required.

Whitelist approach is a way to go.
You can create views over your tables, deny all permissions on the base tables and grant them on views only. This way you have control who, what and where can do (ie. deny insert there, allow updates, but not inserts here, hide this tables completely, etc.)
This approach has one very significant advantage when we're talking about security - it completely hides database schema from external users, reducing possibility of later exploits of you applications (yes, it does happen this way).