A Model-Driven Approach for Enforcing Fine-Grained Access Control for SQL Queries

Abstract

In this paper we propose a novel, model-driven approach for enforcing fine-grained access control (FGAC) policies when executing SQL queries. More concretely, we define a function $\mathrm{SecQuery}()$ that, given a FGAC policy $\mathcal{S}$ and a SQL select-statement q, generates a SQL stored-procedure, such that: if a user is authorized, according to $\mathcal{S}$, to execute q, then calling this stored-procedure returns the same result that executing q; otherwise, if a user is not authorized, according to $\mathcal{S}$, to execute q, then calling this stored-procedure signals an error. We have implemented our approach in an open-source project, called SQL Security Injector (SQLSI).

Appendices

A Data Models, Object Models, and Security Models

In this section we first define our notion of data models and object models. Data models specify the resources to be protected. Object models (also called scenarios) are instances of data models. Next, we briefly introduce the Object Constraint Language (OCL)  [13], which is a language for specifying queries and constraints on models. Then, we define our notion of security models. Security models specify fine-grained access control policies for executing actions on protected resources. The actions that we consider here are read-actions on data specified in data models. Finally, we formalize the semantics of our security models by defining a predicate $\mathrm{Auth}()$ that declares when a user, using a specific role, is authorized to execute a read-action upon an object in a scenario.

Definition 1

Let $\mathcal{T}$ be a set of predefined types. A data model $\mathcal {D}$ is a tuple $\langle C , AT , AS \rangle$, where:

• C is a set of classes c.

• $AT$ is a set of attributes ${ at}$, ${ at} = \langle ati$, c, $t\rangle$, where ${ ati}$ is the attribute’s identifier, c is the class of the attribute, and t is the type of the values of the attribute, with $t\in \mathcal{T}$ or $t\in C$.

• $AS$ is a set of associations ${ as}$, ${ as} = \langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$, where ${ asi}$ is the association’s identifier, ${ ase}_\mathrm{l}$ and ${ ase}_\mathrm{r}$ are the association’s ends, and ${c}_\mathrm{l}$ and ${c}_\mathrm{r}$ are the classes of the objects at the corresponding association’s ends.

Definition 2

Let ${\mathcal {D}}$ = $\langle C , AT , AS \rangle$ be a data model. An object model $\mathcal {O}$ of $\mathcal {D}$ (also called an instance of $\mathcal{D}$) is a tuple $\langle OC$, $OAT$, $OAS \rangle$ where:

• $OC$ is set of objects o, $o = \langle { oi}, c\rangle$, where ${ oi}$ is the object’s identifier and c is the class of the object, where $c\in C$.

• ${ OAT}$ is a set of attribute values ${ atv}$, ${ atv}$ = $\langle \langle ati$, c, $t\rangle$, $\langle oi$, $c\rangle$, $vl \rangle$, where $\langle ati , c, t\rangle \in { AT}$, $\langle oi, c\rangle \in { OC}$, and ${ vl}$ is a value of the type t. The attribute value ${ atv}$ denotes the value ${ vl}$ of the attribute $\langle ati , c, t\rangle$ of the object $\langle oi$, $c\rangle$.

• $OAS$ is a set of association links ${ asl}$, ${ asl}= \langle \langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$, $\langle oi _\mathrm{l}$, $c_\mathrm{l}\rangle$, $\langle oi _\mathrm{r}$, $c_\mathrm{r}\rangle \rangle$, where $\langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle \in { AS}$, $\langle oi_\mathrm{l}, c_\mathrm{l}\rangle \in { OC}$, and $\langle oi_\mathrm{r}, c_\mathrm{r}\rangle \in { OC}$. The association link ${ asl}$ denotes that there is a link of the association $\langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$ between the objects $\langle oi_\mathrm{l}, c_\mathrm{l}\rangle$ and $\langle oi_\mathrm{r}, c_\mathrm{r}\rangle$, where the later stands at the end $ase _\mathrm{r}$ and the former stands at the end $ase _\mathrm{l}$.

Without loss of generality, we assume that every object has a unique identifier.

Object Constraint Language (OCL). OCL  [13] is a language for specifying constraints and queries using a textual notation. Every OCL expression is written in the context of a model (called the contextual model). OCL is strongly typed. Expressions either have a primitive type, a class type, a tuple type, or a collection type. OCL provides standard operators on primitive types, tuples, and collections. For example, the operator includes checks whether an element is inside a collection. OCL also provides a dot-operator to access the value of an attribute of an object, or the collect the objects linked with an object at the end of an association. For example, suppose that the contextual model includes a class c with an attribute ${ at}$ and an association-end ${ ase}$. Then, if o is an object of the class c, the expression o.${ at}$ refers to the value of the attribute ${ at}$ of the object o, and ${ o}$.${ ase}$ refers to the objects linked to the object o at the association-end ${ ase}$. OCL provides operators to iterate over collections, such as forAll, exists, select, reject, and collect. Collections can be sets, bags, ordered sets and sequences, and can be parameterized by any type, including other collection types. Finally, to represent undefinedness, OCL provides two constants, namely, null and invalid. Intuitively, null represents an unknown or undefined value, whereas invalid represents an error or an exception.

Notation. Let $\mathcal{D}$ be a data model. We denote by $\mathrm{Exp}(\mathcal{D})$ the set of OCL expressions whose contextual model is $\mathcal{D}$. Now, let $\mathcal{O}$ be an instance of $\mathcal{D}$, and let ${ e}$ be an OCL expression in $\mathrm{Exp}(\mathcal{D})$. Then, we denote by $\mathrm{Eval}(\mathcal{O}, { e})$ the result of evaluating ${ e}$ in $\mathcal{O}$ according to the semantics of OCL.

Definition 3

Let $\mathcal{D}$ be a data model $\mathcal{D} = \langle C, { AT}, { AS}\rangle$. Then, we denote by $\mathrm{Act}(\mathcal{D})$ the following set of read-actions:

• For every attribute ${ at}\in { AT}$, $\mathrm{read}({ at})\in \mathrm{Act}(\mathcal{D})$.

• For every association ${ as}\in { AS}$, $\mathrm{read}({ as})\in \mathrm{Act}(\mathcal{D})$.

Definition 4

Let ${\mathcal {D}} = \langle C , AT , AS \rangle$ be a data model. Let $\mathcal {O}$ = $\langle OC$, $OAT$, $OAS \rangle$ be an instance of $\mathcal {D}$. Then, we denote by $\mathrm{Act}(\mathcal {O})$ the following set of instance read-actions:

• For every attribute ${ at} = \langle ati , c, t\rangle$, ${ at}\in AT$, and every object $o = \langle { oi}, c\rangle$, $o\in { OC}$, the action $\mathrm{read}( at , o)$ of reading the value of the attribute $at$ in o.

• For every association ${ as} = \langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$, ${ as} \in AS$, and every pair of objects $o_\mathrm{l} = \langle { oi}_\mathrm{l}, c_\mathrm{l}\rangle$, $o_\mathrm{r} = \langle { oi}_\mathrm{r}, c_\mathrm{r}\rangle$, such that $o_\mathrm{l}, o_\mathrm{r}\in { OC}$, the action $\mathrm{read}({ as}, o_\mathrm{l}, o_\mathrm{r})$ of reading if there is a link of the association ${ as}$ between $o_l$ and $o_r$.

Definition 5

Let $\mathcal{D}$ be a data model. Then, a security model $\mathcal{S}$ for $\mathcal{D}$ is a tuple $\mathcal{S} = (R, \mathrm{auth})$, where R is a set of roles, and $\mathrm{auth}\!: R \times \mathrm{Act}(\mathrm{D})\longrightarrow \mathrm{Exp}(\mathcal{D})$ is a function that assigns to each role $r\in R$ and each action $a\in \mathrm{Act}(\mathcal{D})$ an authorization constraint ${ e} \in \mathrm{Exp}(\mathcal{D})$.

Definition 6

Let ${\mathcal {D}}$ be a data model. Let $\mathcal {S} =\langle R, \mathrm{auth}\rangle$ be a security model for $\mathcal {D}$. Let r be a role in R. Let $\mathcal {O}$ = $\langle OC$, $OAT$, $OAS \rangle$ be an object model of $\mathcal {D}$. Let u be an object in ${ OC}$. Then, we define the predicate Auth as follows:

• For any action $\mathrm{read}({ at}, o)\in \mathrm{Act}(\mathcal{O})$,

  

• For any action $\mathrm{read}({ as}, { o}_\mathrm{l}, { o}_\mathrm{r})\in \mathrm{Act}(\mathcal{O})$,

  

B Mapping Data and Object Models to Databases

In this section we define the specific mapping from data models (and object models) to SQL that we use in our solution for enforcing FGAC policies when executing SQL queries. Notice that other mappings from data models to SQL are also possible  [5]. As expected, if a different mapping from data models to SQL is chosen, then our enforcement of FGAC policies for SQL queries should be changed accordingly.

Definition 7

Let ${\mathcal {D}} = \langle C, AT , AS \rangle$ be a data model. Our mapping of $\mathcal {D}$ to SQL, denoted by $\overline{\mathcal {D}}$, is defined as follows:

• For every $c\in C$,

  

• For every attribute $at \in AT$, $at = \langle ati , c, t\rangle$,

  

  where:

  • if t $=$ Integer, then $\mathrm{SqlType}(t)$ $=$ int.

  • if t $=$ String, then $\mathrm{SqlType}(t)$ $=$ varchar.

  • if t $\in$ C, then $\mathrm{SqlType}(t)$ $=$ varchar.

  Moreover, if t $\in$ C, then

  

• For every association $as$ $\in$ $AS$, $as$ $=$ $\langle asi , ase _\mathrm{l}, c_\mathrm{l}, ase _\mathrm{r}, c_\mathrm{r}\rangle$ $\in$ $AS$,

  

  Moreover,

  

Definition 8

Let ${\mathcal {D}}$ = $\langle C , AT , AS \rangle$ be a data model. Let $\mathcal {O}$ = $\langle OC$, $OAT$, $OAS \rangle$ be an object model of $\mathcal {D}$. Our mapping of $\mathcal {O}$ to SQL, denoted by $\overline{\mathcal {O}}$, is defined as follows:

• For every object $o\in { OC}$, $o= \langle { oi}, c\rangle$,

  

• For every attribute value ${ atv}\in { OAT}$, ${ atv} = \langle \langle ati , c, t\rangle , \langle oi , c\rangle , vl \rangle$,

  

• For every association link ${ asl} \in OAS$, ${ asl} = \langle \langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$, $\langle oi _\mathrm{l}$, $c_\mathrm{l}\rangle$, $\langle oi _\mathrm{r}$, $c_\mathrm{r}\rangle \rangle$,

  

C Secure SQL Queries

In this section we define the key component in our model-driven solution for enforcing FGAC policies when executing SQL queries. In particular, we define a function $\mathrm{SecQuery}()$ such that, given a FGAC policy $\mathcal{S}$ and a SQL select-statement q, it generates a SQL stored-procedure satisfying the following: if a user is authorized, according to $\mathcal{S}$, to execute q, then calling this stored-procedure returns the same result that executing q; otherwise, if a user is not authorized, according to $\mathcal{S}$, to execute q, then calling this stored-procedure signals an error. By convention, we denote by $\ulcorner \mathrm{SecQuery}(\mathcal{S}, q)\urcorner$ the name of the stored-procedure generated by $\mathrm{SecQuery}$, for a FGAC policy $\mathcal{S}$ and a query q.

The function $\mathrm{SecQuery}()$ uses a number of auxiliary functions to generate the body of the aforementioned stored-procedure: namely, $\mathrm{AuthFunc}()$, AuthFuncRole(), $\mathrm{SecExp}()$, and $\mathrm{SecExpList}()$. Both $\mathrm{AuthFunc}()$ and $\mathrm{AuthFuncRole}()$ generate SQL functions, which we denote using the same convention as for the SQL stored-procedures generated by the function $\mathrm{SecQuery}()$. As expected, before we introduce the definition of $\mathrm{SecQuery}()$, we provide the definition of the aforementioned auxiliary functions. However, we leave “open” the concrete implementation in SQL of the authorization constraints specified in a given FGAC policy. More precisely, given an authorization constraint ${ auth}$ in OCL, we assume that there exists a function $\mathrm{map}()$ that generates the corresponding query $\mathrm{map}({ auth})$ in SQL. We also assume that this function returns a table with a column res containing the result of executing $\mathrm{map}({ auth})$. Our mapping OCL2PSQL  [11] can certainly be used as such function $\mathrm{map}()$. However, our current experiments suggest that, for non-trivial authorization constraints, manually-written implementations significantly outperforms those automatically generated by OCL2PSQL, when checking FGAC authorization in large databases.

Definition 9

Let $\mathcal {D} = \langle C, { AT}, { AS} \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$. Let ${ r}$ be a role in R. Let ${ at} = \langle { ati}, { c}, { t}\rangle$ be an attribute in ${ AT}$. Then, $\mathrm{AuthFuncRole}(\mathcal{S}, { at}, { r})$ generates the following SQL function:

Similarly, let $as$ $=$ $\langle asi , ase _\mathrm{l}, c_\mathrm{l}, ase _\mathrm{r}, c_\mathrm{r}\rangle$ $\in$ $AS$, be an association in ${ AS}$. Then, $\mathrm{AuthFuncRole}(\mathcal{S}, { as}, { r})$ generates the following SQL function:

Definition 10

Let $\mathcal {D} = \langle C, { AT}, { AS} \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$, with $R = \{r_{1}, r_{2},\ldots , r_n\}$. Let ${ at}$ be an attribute in ${ AT}$. Then, $\mathrm{AuthFunc}(\mathcal{S}, { at})$ generates the following SQL function:

Similarly, let as be an association in ${ AS}$. Then $\mathrm{AuthFunc}(\mathcal{S}, { as})$ generates the following SQL function:

Definition 11

Let $\mathcal {D} = \langle C, AT, AS \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$. Let ${ exp}$ be a SQL expression in $\overline{\mathcal{D}}$. We denote by $\mathrm{SecExp}(\mathcal{S}, { exp})$ the SQL expression in $\overline{\mathcal{D}}$ that results from replacing:

• each attribute ${ at} = \langle { ati}, { c}, { t}\rangle$ in ${ exp}$ by the following case-expression

  

• each association-end ${ ase}_{[\mathrm{l}|\mathrm{r}]}$ in ${ exp}$, with ${ ase}_{[\mathrm{l}|\mathrm{r}]}$ in $as = \langle { asi}, ase _\mathrm{l}$, $c_\mathrm{l}$, $ase _\mathrm{r}$, $c_\mathrm{r}\rangle$, by the following case-expression:

  

where the function throw_error() is defined as followed:

Definition 12

Let $\mathcal {D} = \langle C, AT, AS \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$. Let ${ selitems} = { exp}_{1}$, $\ldots$,${ exp}_{n}$ be a list of SQL expressions in $\overline{\mathcal{D}}$. We denote by $\mathrm{SecExpList}(\mathcal{S}, { selitems})$ the following SQL expression list: $\mathrm{SecExp}(\mathcal{S}, { exp}_{1})$, $\ldots$, $\mathrm{SecExp}(\mathcal{S}, { exp}_{n})$.

Notation. Let $\mathcal {D} = \langle C, AT, AS \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$. Let ${ exp}$ be a SQL expression in $\overline{\mathcal{D}}$. We denote by $\mathrm{RepExp}({ exp}, { as})$ the SQL expression in $\overline{\mathcal{D}}$ that results from replacing each association-end ${ ase}$ in ${ exp}$ by its corresponding class identifier.

Definition 13

Let $\mathcal {D} = \langle C, AT, AS \rangle$ be a data model. Let $\mathcal{S} = (R, \mathrm{auth})$ be a security model for $\mathcal {D}$. Let q be a SQL query in $\overline{\mathcal{D}}$. Then, $\mathrm{SecQuery}(\mathcal{S}, q)$ generates the following stored-procedure:

Case q = SELECT ${ selitems}$ FROM c WHERE ${ exp}$. Then, $\mathrm{SecQueryAux}()$ returns the following create-statements:

Case $q =$ SELECT ${ selitems}$ FROM ${ as}$ WHERE ${ exp}$. Then, $\mathrm{SecQueryAux}()$ returns the following create-statements:

Case $q =$ SELECT ${ selitems}$ FROM ${ subselect}$ WHERE ${ exp}$. Then, $\mathrm{SecQueryAux}()$ returns the following create-statements:

Case $q =$ SELECT ${ selitems}$ FROM c JOIN ${ as}$ ON ${ exp}$ WHERE ${ exp'}$. Then, $\mathrm{SecQueryAux}()$ returns the following create-statements:

Case $q =$ SELECT ${ selitems}$ FROM c JOIN ${ subselect}$ ON ${ exp}$ WHERE ${ exp'}$. Then, $\mathrm{SecQuery}(\mathcal{S}, q)$ returns the following create-statements:

Case $q =$ SELECT ${ selitems}$ FROM ${ as}$ JOIN ${ subselect}$ ON ${ exp}$ WHERE ${ exp'}$.

We must consider three cases. First, the case when ${ ase}_\mathrm{l}$ appears in ${ exp}$, but ${ ase}_\mathrm{r}$ does not appear in ${ exp}$. Let ${ col}$ be the column in ${ subselect}$ that ${ ase}_\mathrm{l}$ is related to in ${ exp}$. Then, $\mathrm{SecQueryAux}(\mathcal{S}, q)$ returns the following create-statements:

Secondly, the case when ${ ase}_\mathrm{r}$ appears in ${ exp}$, but ${ ase}_\mathrm{l}$ does not appear in ${ exp}$. This case is resolved analogously to the previous case. Thirdly, the case when both ${ ase}_\mathrm{l}$ and ${ ase}_\mathrm{r}$ appear in ${ exp}$. Then $\mathrm{SecQueryAux}(\mathcal{S}, q)$ returns the following SQL create-statements:

Case $q =$ SELECT ${ selitems}$ FROM ${ subselect}_{1}$ JOIN ${ subselect}_{2}$ ON ${ exp}$ WHERE ${ exp'}$. Then, $\mathrm{SecQuery}(\mathcal{S}, q)$ returns the following create-statements: