I've done this in Oracle and SQL Server at the database level, as well as via a web server with preset authorization controls (non-free-form query), as well as via SQL parser that enables free-form query. My take:
1. Approach 1: Use database-level mechanisms, where user A is the database user
that creates / owns / fully controls all tables, views, and other
objects, and user B, C, D... are the end user accounts that utilize
the objects that A grants access to.
a. Pros
i. Might be easier to maintain; you may need fewer test cases to confirm that it
works properly
ii. Allows you to distribute an application that uses direct ODBC connections
(such as a Microsoft Access file) to multiple users, who can each have separate
row-level security
iii. Allows real-time updates to access control (either to individual permissions,
or to entire sets of permissions), via back-end database changes
iv. You don't have to worry about application security, because you are relying on
the database for all security (including the security of your admin account)
b. Cons:
i. Requires a separate database user account for each end user. This is generally
not desirable for, for example, tens of thousands of users
ii. By using ODBC, users are directly connecting to the database server, which could
be a security weakness under some circumstances (which depends on more factors than
are in scope for this question)
iii. Performance takes a significant hit. Another barrier to scalability
iv. For these and other reasons, this approach is generally not considered best
practice for production use
c. Implementation:
i. For Oracle, as you noted, there is built-in support
ii. For SQL Server, this can be implemented using views and instead-of triggers,
where the view or stored proc does SELECTs and triggers perform writes
in a controlled manner. This can get the job done,
but it is cumbersome, and requires a fair amount of code, much of which needs to
be changed whenever your authorization approach changes (such as changing what
fields in what ACL tables will authorize what actions based on what values in the
tables you want to secure). Furthermore, each set of code needs to be added to each
table you want to secure. Oracle, on the other hand, does something akin to
parsing the SQL statement and interjecting a where clause whenever the table you
are securing is involved. This is a far more flexible approach, but would be very
difficult to implement in SQL server unless you can write a SQL parser in T-SQL
iii. For postgreql and mysql, I believe you can implement the same approach as described
above for SQL Server, if this is the way you want to go. I suppose, in postgresql
you could write a SQL parser in C which performs the transformation to add the
necessary where clauses, make it available as a database function, pass your free-
form SQL to this function in your trigger or stored proc, and use the resulting
modified SQL as the query that gets run (or just have the C function run the query
and pass that back to the view). But that might be a lot of work for some added
flexibility for queries that you could not anticipate.
2. Approach 2: Use an application in the middle. So either your application uses User A to log
in and do its stuff (not recommended, but technically, works fine), or you can set up a
more restricted User B just for your application, which can do everything that any end user
can do (e.g. view / change data), but nothing more (e.g. drop table). You rely on the
application to control access.
a. Pros: this is how most web and similar client-server applications work, and you'll find
lots of resources available for doing this
b. Cons:
i. you can't use this approach if you want to provide end users with an ODBC connection
(or an application that uses ODBC)
ii. As you point out, usually this is implemented in a manner that does not allow for
free-form SQL. There are two ways to address this latter concern:
A. Create your own SQL parser (this is your "proxy" solution), which your application
will use to parse any free-form SQL request (this will end up being similar to
Oracle's implementation, except that your SQL monkeying occurs in your application,
whereas Oracles occurs in the database). For all elements of the request that your
parser identifies as a table, you will perform a lookup in your ACL table to determine
what the "WHERE" predicate is (if any) related to that table, that will be added to
the SQL request before it is sent to the server. If you are familiar with creating
your own programming language parsers, this approach shouldn't be too hard, but if not,
you might not want to try-- you may find that trying to solve even simple use cases
ends up being just as complicated as solving any use case, so you either build a proper
parser that is completely flexible, or you get mired in bug fixing forever. In
addition, this approach will hit your performance hard just as Approach 1 does.
B. Create a user-interface that provides the type of query functionality you want without
truly being free-form. You would have to ensure the interface can support every
conceivable query you want to accept. While this is not ideal based on what you asked,
you may find it to be a more cost-effective approach given the amount of work to get
your SQL parser correct, if you haven't done it before,
Overall, my recommendation is to go with Approach 1 if you have a very small-scale project and it will save you time to use ODBC (for example, I did this for a pilot/test project where we built the application in Microsoft Access in 2 weeks), and otherwise, unless flexibility is truly the number 1 priority and performance is not important, to go with Approach 2 using a structured interface that allows the application to control access, and also to provide you with greater control over performance.
