Foreign Keys

There are many situations in database development where Row Level Security (RLS) becomes a security concern. The fks block is introduced to solve the problem.

{
  "fks" : {
    "member" : ["purchase_id", "purchase_sha1", "item_id", "item_sha1"],
    "vendor" : [ ... ]
     ...
   }
}

Here is a simple SQL data model for a shopping cart website:

• table member for registered members, with the primary key (PK) member_id
• table product for products with PK production_id
• table purchase for members’ purchase history with PK purchase_id
• table item for the product list in each purchase with PK item_id

The SQL statements to create the tables in MySQL are:

create table member (
  member_id int not null auto_increment primary key,
  email varchar(32) not null,
  passwd varchar(32) not null,
  member_name varchar(255) not null,
  unique key (email)
);

create table product (
 product_id not null auto_increment primary key,
 product_name varchar(255),
 unit_price double
);
 
create table purchase (
  purchase_id int not null auto_increment primary key,
  member_id int not null,
  purchase_time datetime not null,
  total_price double not null,
  index (member_id)
);

create table item (
  item_id int not null auto_increment primary key,
  purchase_id int not null,
  product_id int not null,
  number_product int default 1,
  index (product_id),
  index (purchase_id)
);

They are mapped to 4 components Member, Product, Purchase, Item in your Genelet project.

 

Problem

Now, a member logs in. She is looking for her purchase history. This is the RESTful action topics on Purchase which is granted to the member role in ACL. To make sure that she only views her own purchase history, not other members, you assign member_id as a constraint in the search. Since member_id is decrypted from the login cookie, its integrity is guaranteed.

Up to now, the authentication, ACL and the constraint work well. Next, she’d like to look for the list of products in a specific purchase. Normally, this is implemented by the RESTful topics on Item. Client sends you purchase_id in URL, which you could use as a constraint in the Item search. The SQL would look like: SELECT * FROM item WHERE purchase_id=?

Now problem arises. A malicious user could send any purchase_id. It she sends one belong to somebody self, she would see that person’s purchase history! This is a typical case for RLS, discussion by Oracle here.

There are two solutions. The first one is to add member_id to the Item table, so you can use both it and purchase_id as constraints. Because member_id is retrieved from the login cookie, the searched results are guaranteed to belong to her. However, because it requires the redundant member_id, the table design is against the Normalization rule of RDMS.

The second solution is to join table Purchase. So you can use Purchase‘s member_id and Item‘s purchase_id as the constraints. While the solution looks to be trivial in the current example, for a large database it could become tedious and easily mistaken because of more deeper relationships.

 

Genelet’s Solution

It starts with a table containing the user id. In the above example, this is Purchase.

For every record searched from topics on the table, you digitally signed the PK with user id and a secret word (and optionally, a time stamp). In the example, this is to calculate SHA1 of purchase_id with  member_id.

When the user navigates to the next down-level table that has no user id, e.g. Item, you verify if the searched key has the correctly signed signature. You only let is continue if the signature is correct. In the example, you check if the SHA1 of purchase_id is correct.

Genelet make the whole flow automatically from up levels to down levels. To use it, define the fks (Foreign Keys):

"fks" : {
  "ROLE" : ["in_id", "in_sha1", "out_id", "out_sha1"],
  ...
}

where ROLE is a login role name; in_id is key to use as constraint, usually a FK in the current table. in_sha1 is its digital signature, convoluted with user id, secret word and time stamp. The client sends both in_id and in_sha1 in request. out_id is usually the PK of the current table.

After the search is finished, Genelet internally scans out_id in the output data, and calculate the signature out_sha1. So out_id and out_sha1 can be used new incoming contraint parameters for the next down-level pages.

The signature process could be repeated all the way down to the last bottom-level table.

Note that while in_id and out_id are table’s field names which you can’t change, you are free to choose names for in_sha1 and  out_sha1.

 

Special Cases

If user id is used as constraint, you don’t need its signature because it is already proofed in the login cookie. So fks will look like:

"ROLE" : ["role_id", false, "out_id", "out_sha1"]

If you don’t need out_id, use:

"ROLE" : ["in_id", "in_sha1"]

If you only want to generate out_id:

"ROLE" : [false, false, "out_id", "out_sha1"]