SQLSI is a Java application that rewrites SQL queries into security-aware ones, i.e. "enforcing" predefined Fine-Grained Access Control (FGAC) policy.

(Jul, 2023) This branch (models23) is preserved for submitting working artifacts for the ACM/IEEE 26th International Conference on Model-Driven Engineering Languages and Systems (MODELS) 2023.

This open-source project is intended for readers of our papers:

• An extended model-based characterization of fine-grained access control for SQL queries. paper
• For architecture design, see here.

SQLSI takes three inputs:

• a data model,
• a security model,
• and a SQL-select statement.

SQLSI returns three outputs, namely,

• the generated SQL database schema (corresponding to the given data model),
• the generated SQL-authorization functions (corresponding to the given security model) and,
• the generated SQL secure stored procedure (corresponding to the given SQL-select statement).

Interested readers can clone our project here

git clone https://github.com/MoDELSVGU/SQLSI.git
cd SQLSI
git checkout models23

Users can either call it as a standalone Java application or extend the implementation.

The following snippet demonstrates the usage of SQLSI:

SqlSI myExec = new SqlSI(); // Initialize SQLSI component
myExec.setDataModel(<datamodel_url>); // Setting (parsing) DataModel context
myExec.setSecurityModel(<securitymodel_url>); // Setting (parsing) SecurityModel context

/* (1). To generate MySQL database schema: */
final String schemaURL = "<schema_destination_url>"; 
myExec.SqlSIGenDatabase(schemaURL); 

/* 2). To generate MySQL authorization checks: */
final String authFuncURL = "<policy_destination_url>";
myExec.SqlSIGenAuthFunc(authFuncURL);

/* (3). To generate MySQL secured stored-procedure */
final String queryProcURL = "<stored_procedure_url>";
final String statement = "<an_sql_select_statement>";
myExec.SqlSIGenSecQuery(queryProcURL, statement);

• (required) Maven 3 and Java 1.8 (or higher).

Go to the main directory then execute the following:

mvn clean install

When it is done, the executable jar (i.e., sqlsi-1.0.3-ASC.jar) is stored in target subdirectory along with the libraries on which it depends.

Copy the datamodel and securitymodel into this target directory.

To execute it, simply invoke the following command:

java -jar sqlsi-1.0.3-ASC.jar <datamodel_url> <securitymodel_url> <SQLquery>

in which:

• <datamodel_url> refers to the url of the datamodel, e.g., voting_dm
• <securitymodel_url> refers to the url of the securitymodel, e.g., voting_sm
• refers to the SQL query, e.g., SELECT value FROM Vote

Note parameters must be double quoted, if they contain spaces. For example:

java -jar sqlsi-1.0.3-ASC.jar "voting_dm" "voting_sm" "SELECT value FROM Vote"

The Voting Management System is a basic application designed for managing sensitive information related to elections. It handles data regarding voters (such as name and social security number), elections (including descriptions) and votes (consisting of value, timestamp) cast by voters in elections.

For detailed information about the data model, please refer to the manuscript.

The datamodel primarily consists of three classes: two classes (Election and Voter) and one association class (Vote).

• Voter class contains fields for name and ssn while Election has a field for description.
• Vote class represents the association between Voter and Election, and includes an additional field value for storing the vote result.

Example of the aforementioned datamodel can be found here.

For detailed information about the security model, please refer to the manuscript.

The security model defines the following rules:

• There is a single role in this application, namely, User.
• A User can read (i) the description of an election, (ii) the name of the voter, and (iii) the value of the vote.
• A User can only read their own social security number.
• For each election, every User can know the voters who participated in the election.
• For each election, every User can know the votes that were cast in the election.
• For each vote, only the voter who cast the vote can know who cast it.

Example of the aforementioned securitymodel can be found here.

Consider the following queries:

SELECT value FROM Vote

SELECT value FROM Vote WHERE voters = 'Alice'

SELECT elections FROM Vote WHERE voters = 'Bob'

SELECT value FROM Vote WHERE elections = 'Spain2023'

SELECT value FROM Vote JOIN (SELECT Voter_id FROM Voter WHERE name = 'Bob') AS TEMP ON voters = TEMP.Voter_id

Voter
+----------+---------+------------+
| Voter_id | name    | ssn        |
+----------+---------+------------+
| Alice    | Alice   | Alicessn   |
| Bob      | Bob     | Bobssn     |
| Charlie  | Charlie | Charliessn |
+----------+---------+------------+

Election
+-------------+----------------------+
| Election_id | description          |
+-------------+----------------------+
| Spain2023   | Spain2023            |
| Swiss2023-1 | Swiss vote quarter 1 |
+-------------+----------------------+

Vote
+---------+-------------+--------+-------+
| Vote_id | elections   | voters | value |
+---------+-------------+--------+-------+
| 1       | Spain2023   | Alice  |     3 |
| 2       | Swiss2023-1 | Alice  |     4 |
| 3       | Swiss2023-1 | Bob    |     1 |
+---------+-------------+--------+-------+

To replicate this scenario, please follow these instructions:

• Clone the project (see Section III).
• Build JAR file (see Section III.B.).
• Copy all files in resources\scenario1 into the target folder.
• Stay in target and execute JAR file

java -jar sqlsi-1.0.3-ASC.jar "voting_dm" "voting_sm" <query>

in which <query> can be chosen from the queries above (see IV.C.).

• Source the SQL artifacts into the MySQL databases in the following order:
  • mydb.sql
  • myfunc.sql
  • myquery.sql
  • scenario1.sql
• Run the stored-procedure in MySQL server and observe the result:

call secquery(<user>, <role>);

in which:

• <user> can be 'Alice', 'Bob', or 'Charlie'
• <role> can be 'Voter'.