Enable custom statements

You need custom statements (pass-through) if you want to send statements to the database unchecked—such as Access with pass-through queries that contain schema changes, temporary tables, or several statements.

For joins, groupings over several tables, functions, and set-based changes over linked tables, no enabling is needed: the service checks these statements against the caller's rules and runs them, as long as the alias allows checked statements (default), see Checked statements.

You enable the route in two places: on the data source (switch Durchreiche (pass-through) or AllowPassThrough) and for the caller. What a statement may do is decided by the login it runs under:

Rationale: The one exception: the pass-through route.

With an administration store (administration app):

1. Open Datenquellen (Data sources), select the data source, turn on Durchreiche in the form, and switch back to the list.
2. For callers in the first row above: open Regeln (Rules), select the caller's rule, choose Merkmal hinzufügen … (Add attribute), and add the attribute resource with the value sql; the rule must not be read-only. See “Create a rule” (documentation of the administration app).
3. Check: a caller with this rule sends POST /api/v1/cf/<alias>/sql.json with {"sql": "SELECT SUSER_SNAME() AS anmeldung"} and gets 200 with the name of the login the statement ran under. This test proves the unchecked route: in a checked statement, the service refuses functions that describe the login, while a SELECT 1 would also pass as a checked statement. For callers in the second row, the same test applies with their own login; the response then names that login. A caller without a matching rule gets 403.

Without an administration store (configuration file): add both entries and restart the service:

{
  "Gateway": {
    "Databases": [
      { "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
        "AllowPassThrough": true }
    ],
    "Security": {
      "Clients": [
        { "Id": "auswertung", "Databases": [ "fachdaten" ],
          "Resources": [ "views", "sql" ], "Verbs": [ "GET", "POST" ], "ReadOnly": false }
      ]
    }
  }
}

A rule that names sql and is read-only at the same time prevents startup.

Turning it off. Turn off the data source's Durchreiche switch (or remove AllowPassThrough); this ends the route for all callers, including those in the second row—with an administration store on the next request, with the configuration file after a restart. To take the route away from individual callers only, remove sql from their rule—this works only for the first row of the table; callers in the second row lose it only through the switch or a read-only rule.

• Choose the data source carefully. Whatever the login of its connection string may do, every pass-through statement may do—reading, writing, schema changes. A data source whose login owns the schema or has far-reaching permissions shouldn't get the switch.

• If the statement runs as the person, SQL Server is the only barrier. For the second row of the table above, a rule that isn't read-only is enough; it doesn't have to name the resource sql. Such a caller may then send any SQL—everything the person's login may do in SQL Server, no more and no less. The service's rules then only decide whether they may send statements, not which ones. If you want writing to go exclusively through procedures, give the people (or a database role they're members of) this permission profile:

  Permission	Purpose
  GRANT SELECT on the views that are read	reading through views, including row-filtered ones (see Row-level restriction)
  GRANT EXECUTE on the procedures that write	writing only through the procedure's checks
  no INSERT, UPDATE, DELETE on the tables	otherwise a pass-through statement writes past the procedures
  Views, procedures, and tables with the same owner	then SQL Server doesn't check the permissions on the tables again (ownership chaining); view and procedure reach the table without a permission of the person's own
  no CONTROL or ALTER on the schema, no IMPERSONATE	whoever may change the schema or switch identity cancels out every row above

  Also check what the database's public role may already do: every permission there applies to every person. And impersonation doesn't protect against permissions granted to groups: permissions from Windows groups apply under impersonation too. A group with db_datawriter gives every member back the ability to write to all tables.

• The statement text is in the audit stream; the values aren't.
• A service that offers the address for custom statements names it to authorized callers in its schema document; the ODBC driver then finds it by itself.

• HTTP: custom statements (pass-through)
• “Connect a data source” (in the “Administrator guide for WKL Database Gateway Manager”)
• Documentation of the ODBC driver: “Set up pass-through queries”