Disclosure Analysis of SQL Workflows

Abstract

In the context of business process management, the implementation of data minimization requirements requires that analysts are able to assert what private data each worker is able to access, not only directly via the inputs of the tasks they perform in a business process, but also indirectly via the chain of tasks that lead to the production of these inputs. In this setting, this paper presents a technique which, given a workflow that transforms a set of input tables into a set of output tables via a set of inter-related SQL statements, determines what information from each input table is disclosed by each output table, and under what conditions this disclosure occurs. The result of this disclosure analysis is a summary representation of the possible computations leading from the inputs of the workflow to a given output thereof.

A Translating SQL Workflows to Internal Representation

The translation of a query Q to a summary dependency graph (SDG) proceeds by first translating the database schema, then performing the syntax-directed translation of the actual query Q, followed by the addition of output nodes. We call the intermediate graphs Partial Summary Dependency Graphs (PDSG), where the partiality indicates the lack of output nodes.

Let G be a PSDG and consider a relation schema r with attributes $a_1,\ldots ,a_n$. A representation of r in G is a mapping $R:\{{\varvec{\exists }},a_1,\ldots ,a_n\}\rightarrow V(G)$, such that $\mathsf {dim}({R({\varvec{\exists }})})=\mathsf {dim}({R(a_1)})=\cdots =\mathsf {dim}({R(a_n)})$, the output type of each $R(a_i)$ matches with the type of $a_i$, and the output type of $R({\varvec{\exists }})$ is boolean. We write $\mathsf {dim}({R})$ for $\mathsf {dim}({R({{\varvec{\exists }}})})$. A representation of a database schema $dbs$ in G is a mapping from the contained relations into their representations in G.

Translating a Database Schema. The translation of a database schema $dbs$ returns a PSDG $G_{ dbs }$, as well as a representation $R_{ dbs }$ of $dbs$ in it. These are the following:

• Let t : r be a table declaration in $dbs$, where r is the relation schema $r(a_1:D_1,\ldots ,a_n:D_n;\mathsf {index}_{r})$, with certain attributes belonging to the index. W.l.o.g. let $a_1,\ldots ,a_h$ be the index attributes. The graph G will contain nodes $v^t_{{\varvec{\exists }}}$ and $v^t_i$ for $1\le i\le n$. The input dimension and the dimension of all nodes is $\mathcal {I}=\prod _{i=1}^h D_i$. All nodes are input nodes. During the execution, the instance $(x_1,\ldots ,x_h)$ of the node $v^t_i$ is supposed to carry the value of the attribute $a_i$ in the row of the table t that corresponds to the index value $(a_1=x_1,\ldots ,a_h=x_h)$. The instance $(x_1,\ldots ,x_h)$ of the node $v^t_{{\varvec{\exists }}}$ carries the value $\mathsf {true}$ iff the table t has a row with index value $(a_1=x_1,\ldots ,a_h=x_h)$.

• The representation $R_{ dbs }$ maps each table t to the mapping $\{{\varvec{\exists }}\mapsto v^t_{{\varvec{\exists }}}\}\cup \{a_i\mapsto v^t_i\,|\, 1\le i\le |t|\}$.

Translating the Query. The translation $\mathcal {G}$ of a query Q against a database with schema $dbs$ takes as input a PSDG $G_\circ$ and a representation $R_{ dbs }$ of $dbs$ in it. It returns a new PSDG $G_\bullet$ (which is obtained from $G_\circ$ by adding zero or more nodes to it) and a representation of $\mathbf {attr}({Q})$ in $G_\bullet$, where $\mathbf {attr}({Q})$ is the schema of the output relation of Q.

The translation $\mathcal {G}$ may call the translation $\mathcal {E}$ for expressions e. It takes as input a PSDG $G_\circ$ and a representation R of a relation schema in $G_\circ$. This relation schema must contain all attributes used by e. The translation $\mathcal {E}$ returns a new PSDG $G_\bullet$ and a node $v_e\in V(G_\bullet )$. The translation $\mathcal {E}$ works as follows.

• $\mathcal {E}\llbracket {a}\rrbracket (G_\circ ,R)$ returns $G_\circ$ and R(a).

• $\mathcal {E}\llbracket {\otimes (e_1,\ldots ,e_k)}\rrbracket (G_\circ ,R)$ calls $\mathcal {E}\llbracket {e_1}\rrbracket ,\ldots ,\mathcal {E}\llbracket {e_k}\rrbracket$ one after another. Let the output of $\mathcal {E}\llbracket {e_i}\rrbracket$ be $G_i$ and $v_i$. Then the inputs to $\mathcal {E}\llbracket {e_i}\rrbracket$ are $G_{i-1}$ (with $G_0\equiv G_\circ$) and R. After obtaining $G_k$, add a new node v to the graph. Its label is $\otimes$, and its dimension and input dimension are both $\mathsf {dim}({R})$. Also add arcs $\alpha _1,\ldots ,\alpha _k$ to the graph, going from nodes $v_1,\ldots ,v_k$ to the node v. For all i, the mapping $\overline{\delta }({\alpha _i})$ is equal to the identity map on $\mathsf {dim}({R})$. Return the modified graph $G_k$ and the vertex v.

The translation $\mathcal {G}$ works as follows.

• $\mathcal {G}\llbracket {t}\rrbracket (G_\circ ,R_{ dbs })$ returns $G_\circ$ and $R_{ dbs }(t)$.

• $\mathcal {G}\llbracket {Q_1\times \cdots \times Q_k}\rrbracket (G_\circ ,R_{ dbs })$ calls $\mathcal {G}\llbracket {Q_1}\rrbracket ,\ldots ,\mathcal {G}\llbracket {Q_k}\rrbracket$ one after another. Let the output of $\mathcal {G}\llbracket {Q_i}\rrbracket$ be $G_i$ and $R^Q_i$. Then the inputs to $\mathcal {G}\llbracket {Q_i}\rrbracket$ are $G_{i-1}$ (with $G_0\equiv G_\circ$) and $R_{ dbs }$. After obtaining $G_k$ and $R^Q_1,\ldots ,R^Q_k$, we add the following nodes and arcs to $G_k$:

  • Let $\mathcal {I}=\prod _{i=1}^k\mathsf {dim}({R^Q_i})$.

  • Add a node $v_{{\varvec{\exists }}}$. The label of this node is “&” (boolean conjunction). Its dimension and input dimension are both $\mathcal {I}$.

  • For each $i\in \{1,\ldots ,k\}$ add an arc $\alpha _{{\varvec{\exists }},i}$ from the node $R^Q_i({\varvec{\exists }})$ to $v_{{\varvec{\exists }}}$. The mapping $\overline{\delta }({\alpha _{{\varvec{\exists }},i}})$ is the canonical projection from $\mathcal {I}$ to its i-th component $\mathsf {dim}({R^Q_i})$.

  • For each $i\in \{1,\ldots ,k\}$ and each attribute $a_j\in \mathbf {attr}({Q_i})$ add a node $v_{i,j}$. The label of this node is “ID” (the identity mapping). Its dimension and input dimension are both $\mathcal {I}$.

  • Also, add an arc $\alpha _{i,j}$ from $R^Q_i(a_j)$ to $v_{i,j}$. The mapping $\overline{\delta }({\alpha _{i,j}})$ is the canonical projection from $\mathcal {I}$ to its i-th component $\mathsf {dim}({R^Q_i})$.

  Let the output PSDG $G_\bullet$ be the modified graph $G_k$. The output representation R maps ${\varvec{\exists }}$ to $v_{{\varvec{\exists }}}$ and the attribute $a_j$ in $\mathbf {attr}({Q_i})$ to $v_{i,j}$.

• $\mathcal {G}\llbracket {[{Q}]_{{a}\rightarrow {a'}}}\rrbracket (G_\circ ,R_{ dbs })$ runs $(G_\bullet ,R) = \mathcal {G}\llbracket {Q}\rrbracket (G_\circ ,R_{ dbs })$. It returns $G_\bullet$ and $R[a'\mapsto R(a)]$.

• $\mathcal {G}\llbracket {\sigma (Q;e)}\rrbracket (G_\circ ,R_{ dbs })$ runs $(G',R) = \mathcal {G}\llbracket {Q}\rrbracket (G_\circ ,R_{ dbs })$ and $(G'',v_{\varvec{?}})=\mathcal {E}\llbracket {e}\rrbracket (G',R)$. It adds a node $v_{{\varvec{\exists }}}$ to $G''$. The label of this node is “&” and both its dimension and input dimension are $\mathsf {dim}({R})$. The node $v_{{\varvec{\exists }}}$ has two inputs, from $R({\varvec{\exists }})$ and from $v_{\varvec{?}}$. The $\overline{\delta }({\cdot })$-mappings of both respective arcs are the identity mappings over $\mathsf {dim}({R})$. Let $G_\bullet$ be the modified graph $G''$. The translation returns $G_\bullet$ and $R[{\varvec{\exists }}\mapsto v_{{\varvec{\exists }}}]$.

• $\mathcal {G}\llbracket {\pi _{a_1,\ldots ,a_k}(Q)}\rrbracket (G_\circ ,R_{ dbs })$ runs $(G_\bullet ,R) = \mathcal {G}\llbracket {Q}\rrbracket (G_\circ ,R_{ dbs })$. It returns $G_\bullet$ and R restricted to $\{{\varvec{\exists }},a_1,\ldots ,a_k\}$.

• $\mathcal {G}\llbracket {\mathsf {col}_{{a}\leftarrow {e}}({Q})}\rrbracket (G_\circ ,R_{ dbs })$ runs $(G',R) = \mathcal {G}\llbracket {Q}\rrbracket (G_\circ ,R_{ dbs })$ and $(G_\bullet ,v_{e})=\mathcal {E}\llbracket {e}\rrbracket (G',R)$. It returns $G_\bullet$ and $R[a\mapsto v_e]$.

• $\mathcal {G}\llbracket {\mathsf {let}\ {t}={Q_1}\ \mathsf {in}\ {Q_2}}\rrbracket (G_\circ ,R_{ dbs })$ runs $(G',R_0) = \mathcal {G}\llbracket {Q_1}\rrbracket (G_\circ ,R_{ dbs })$, followed by $(G_\bullet ,R)=\mathcal {G}\llbracket {Q_2}\rrbracket (G', R_{ dbs }[t\mapsto R_0])$. It returns $G_\bullet$ and R.

• $\mathcal {G}\llbracket {Q_1\cup Q_2}\rrbracket (G_\circ ,R_{ dbs })$ runs

  $$\begin{aligned} (G',R')&=\mathcal {G}\llbracket {Q_1}\rrbracket (G_\circ ,R_{ dbs })\\ (G'',R'')&=\mathcal {G}\llbracket {Q_2}\rrbracket (G',R_{ dbs }). \end{aligned}$$

  For each attribute $a\in \mathbf {attr}({Q_1})=\mathbf {attr}({Q_2})$ it will then add a node $v_a$ to $G''$, with the operation “ID” and its dimension and input dimension both being equal to $\mathsf {dim}({R'})+\mathsf {dim}({R''})$. The mapping $\delta ({v_a})$ is the identity mapping. The node $v_a$ has a single incoming arc $\alpha _a$, which has two sources—$R'(a)$ and $R''(a)$. The mapping $\overline{\delta }({\alpha _a})$ is the identity mapping from $\overrightarrow{\mathsf {dim}}({v_a})$ to $\mathsf {dim}({R'(a)})+\mathsf {dim}({R''(a)})$.

  We also add a node $v_{{\varvec{\exists }}}$ to the graph $G''$ with the same dimension, input dimension and $\delta ({\cdot })$-mapping as described in the previous paragraph. The operation in this node is again “ID” (boolean disjunction), and it again has a single incoming arc $\alpha _{{\varvec{\exists }}}$ with two sources: $R'({\varvec{\exists }})$ and $R''({\varvec{\exists }})$, with the mapping $\overline{\delta }({(})\alpha _{{\varvec{\exists }}})$ again being the identity map.

  Let the output PDSG $G_\bullet$ be the graph $G''$ with the added nodes and arcs. The output representation R maps ${\varvec{\exists }}$ to $v_{{\varvec{\exists }}}$ and each attribute a to $v_a$.

• $\mathcal {G}\llbracket {Q_1\cap Q_2}\rrbracket (G_\circ ,R_{ dbs })$ runs

  $$ (G',R')=\mathcal {G}\llbracket {\sigma (Q_1\times [{Q_2}]_{{a:\mathbf {attr}({Q_2})}\rightarrow {a'}}; \bigwedge _{a\in \mathbf {attr}({Q_1})} a=a')}\rrbracket (G_\circ , R_{ dbs }) $$

  first, while also keeping the representation $R_1$ that was produced while $\mathcal {G}\llbracket {Q_1}\rrbracket (G_\circ ,R_{ dbs })$ was run as a subroutine. Here the write-up $[{Q_2}]_{{a:\mathbf {attr}({Q_2})}\rightarrow {a'}}$ denotes that we have renamed all attributes a of $Q_2$ into their primed versions.

  We add to $G'$ a node $v_{\varvec{\exists }}$ with the operation “$\bigvee$” (boolean disjunction). We let $\mathsf {dim}({v_{\varvec{\exists }}})=\mathsf {dim}({R_1})$ and $\overrightarrow{\mathsf {dim}}({v_{\varvec{\exists }}})=\mathsf {dim}({R'})$. Recall that $\mathsf {dim}({R'})$ is equal to the Cartesian product of $\mathsf {dim}({R_1})$ and the dimension of the nodes resulting from the translation of the query $Q_2$. The mapping $\delta ({v_{\varvec{\exists }}})$ is the natural projection to the first component of this product.

  As $\mathsf {dim}({v_{\varvec{\exists }}})\not =\overrightarrow{\mathsf {dim}}({v_{\varvec{\exists }}})$, this node may have a single incoming arc. This arc comes from the node $R'({\varvec{\exists }})$, its $\overline{\delta }({\cdot })$-mapping is the identity mapping.

  We return the graph $G'$ with the extra node and arc. As the output representation, we return $R_1[{\varvec{\exists }}\mapsto v_{\varvec{\exists }}]$.

• $\mathcal {G}\llbracket {{Q_1}\mathbin {\ltimes _{e}}{Q_2}}\rrbracket (G_\circ ,R_{ dbs })$ runs

  $$\begin{aligned} (G',R_2)&=\mathcal {G}\llbracket {Q_1\times Q_2}\rrbracket (G_\circ , R_{ dbs })\\ (G'',v_e)&=\mathcal {E}\llbracket {e}\rrbracket (G', R_2). \end{aligned}$$

  We also keep the representation $R_1$ that was produced when $\mathcal {G}\llbracket {Q_1}\rrbracket (G_\circ ,R_{ dbs })$ was run as a subroutine. After that, we add the following nodes and arcs to $G''$.

  • Node $v_1$, operation “&”, with dimension and input dimension equal to $\mathsf {dim}({R_2})$. Its inputs are $v_e$ and $R_2(\exists )$.

  • Node $v_2$, operation “$\bigvee$”. Its dimension is equal to $\mathsf {dim}({R_1})$ and its input dimension to $\mathsf {dim}({R_2})$. The mapping $\delta ({v_2})$ is the natural projection from the second to the first. The input to $v_2$ is the node $v_1$.

  • Node $v_3$, operation “NOT”. Its dimension and input dimension are equal to $\mathsf {dim}({R_1})$. Its input is the node $v_2$.

  • Node $v_4$, operation “&”. Its inputs are $v_3$ and $R_1({\varvec{\exists }})$.

  For all arcs described above, their $\overline{\delta }({\cdot })$-mapping is the identity mapping. The translation returns the PSDG $G''$ together with added nodes and arcs. As the output representation, it returns $R_1[{\varvec{\exists }}\mapsto v_4]$.

• $\mathcal {G}\llbracket {\mathsf {group}^{a_1,\ldots ,a_k}_{(a'_1\bigotimes _1),\ldots ,(a'_l,\bigotimes _l)}({Q})}\rrbracket (G_\circ ,R_{ dbs })$ first runs $(G',R')=\mathcal {G}\llbracket {Q}\rrbracket (G_\circ ,R_{ dbs })$. It will determine the types $D_1,\ldots ,D_k$ of the attributes $a_1,\ldots ,a_k$ of Q. These types must be elements of $\mathcal {S}$. The following nodes and arcs are then added to $G'$:

  • Nodes $v^\mathrm {TD}_1,\ldots ,v^\mathrm {TD}_k$. These are input nodes of the SDG. The dimension of $v^\mathrm {TD}_i$ is $D_i$. In the infinite dependency graph, a node v corresponding to the value $x\in D_i$ and the node $v^\mathrm {TD}_i$, is expected to carry the value x. Let $\mathcal {I}=D_1\times \cdots \times D_k$.

  • Nodes $v^=_1,\ldots ,v^=_k$. The operation of these nodes is “$=$” (equality check). The dimension and input dimension of these nodes is $\mathsf {dim}({R'})\times \mathcal {I}$. The node $v^=_i$ has two inputs: $v^\mathrm {TD}_i$ and $R'(a_i)$. The $\overline{\delta }({\cdot })$-mappings for the arcs connecting these nodes are the natural projections.

  • Node $v^=$. The operation of this node is “&”. Its dimension and input dimension are both $\mathsf {dim}({R'})\times \mathcal {I}$. Its inputs are the nodes $v^=_1,\ldots ,v^=_k$.

  • Node $v_{\varvec{\exists }}$. The operation of this node is “$\bigvee$”. Its dimension is $\mathcal {I}$ and its input dimension is $\mathsf {dim}({R'})\times \mathcal {I}$. The mapping $\delta ({w_{\varvec{\exists }}})$ is the natural projection. Node $v_{\varvec{\exists }}$ receives its input from $v^=$.

  • Nodes $v^f_1,\ldots ,v^f_l$. The operation of these nodes is “Output”; this operation takes two arguments and returns the first one only if the second one is true. Their dimension and input dimension are $\mathsf {dim}({R'})\times \mathcal {I}$. The inputs of the node $v^f_j$ are $v^=$ (for the first,“conditioning” argument) and $R'(a'_j)$ (for the second, “value” argument). The $\overline{\delta }({\cdot })$-mapping for the arc connecting to the first input is the identity mapping, while for the arc connecting to the second input is the natural projection from $\mathsf {dim}({R'})\times \mathcal {I}$ to $\mathsf {dim}({R'})$.

  • Nodes $v^\otimes _1,\ldots ,v^\otimes _l$. The operation of the node $v^\otimes _j$ is “$\bigotimes _j$”. The dimension of $v^\otimes _j$ is $\mathcal {I}$, while its input dimension is $\mathsf {dim}({R'})\times \mathcal {I}$. The mapping $\delta ({v^\otimes _j})$ is the natural projection. The input to the node $v^\otimes _j$ is the node $v^f_j$.

  We see that the expansions of the nodes $v^\otimes _j$ in the infinite dependency graph perform the actual aggregations of the values of the dataset resulting from the query Q. We have implicitly assumed that the NULL-values among the inputs of the operations $\bigotimes _j$ do not change their output value. The translation returns the graph $G'$ together with the added nodes and arcs. The output representation R is the following:

  • $R({\varvec{\exists }})=w_{\varvec{\exists }}$;

  • $R(a_i)=v^\mathrm {TD}_i$ for the attributes $a_1,\ldots ,a_k$;

  • $R(a'_j)=v^\otimes _j$ for the attributes $a'_1,\ldots ,a'_l$.

Adding Output Nodes. Let the query Q be translated by calling $\mathcal {G}\llbracket {Q}\rrbracket$ on the translation of the database schema. The result of $\mathcal {G}\llbracket {Q}\rrbracket$ is a PSDG G and a representation R of $\mathbf {attr}({Q})$ in G. We add the following nodes and arcs to G:

• For each $a_i\in \mathbf {attr}({Q})$, add nodes $v_i$ and $v^O_i$. For both of them, their dimension and input dimension are equal to $\mathsf {dim}({R})$. Node $v_i$ is an internal node, while $v^O_i$ is an output node. There is an arc from $v_i$ to $v^O_i$; its $\overline{\delta }({\cdot })$-mapping is the identity mapping on $\mathsf {dim}({R})$. There are two arcs into $v_i$, first from $R({\varvec{\exists }})$ and second from $R(a_i)$. Their $\overline{\delta }({\cdot })$-mappings are also the identity mappings on $\mathsf {dim}({R})$. The operation of $v_i$ is named “Output”. The semantics of an“Output” operation is to return the second argument, if the first argument is true, and to return NULL otherwise.