Безопасность на уровне строк в сценарии клиентской базы данных

Вся тема безопасности на уровне строк довольно противоречива. Я лично считаю, что вы лаете не на то дерево, пытаясь реализовать это на уровне ACL базы данных. Я знаю, что Oracle поддерживает это, но мне кажется, это была действительно плохая идея с самого начала и вызвала гораздо больше разочарований, чем пользы. Я знаю, что вы испытываете искушение повторно использовать существующие функции контроля доступа, просто чтобы сэкономить на строках кода, но я сам не осмелился бы пойти по этому пути только потому, что вы можете оказаться в тупике из-за ожиданий и реальности того, как ACL реализовано в зависимости от того, как вы хотите, чтобы он работал.

Я сделал это в Oracle и SQL Server на уровне базы данных, а также через веб-сервер с предустановленными элементами управления авторизацией (запрос не в свободной форме), а также через синтаксический анализатор SQL, который позволяет выполнять запросы в произвольной форме. Мое мнение:

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, 

В целом, я рекомендую использовать подход 1, если у вас очень небольшой проект, и это сэкономит вам время на использование ODBC (например, я сделал это для пилотного / тестового проекта, в котором мы создали приложение в Microsoft Access в 2 недели), а в противном случае, если гибкость действительно не является приоритетом номер 1, а производительность не важна, перейти к подходу 2 с использованием структурированного интерфейса, который позволяет приложению контролировать доступ, а также предоставляет вам больший контроль над производительностью.

Я работаю над таким прокси здесь https://github.com/jbaliuka/sql-analytic Это изначально был разработан для отчетов / аналитических целей, но я планирую реализовать приложение-шлюз, чтобы я мог перемещаться по БД и выполнять SQL с DML через JavaScript из браузера. Это может быть полезно в качестве плагина Olingo для публикации базы данных как службы OData.