There are many situations where RLS (Row Level Security) becomes a security concern.
Here is a simple data model of shopping website. There is a member table, a product table for products, a purchase table for members’ purchase history, and an item table that shows which products are in each purchase. Their PKs are member_id, product_id, purchase_id and item_id respectively. The 4 tables have been programmed as 4 components in Genelet: Member, Product, Purchase and Item.
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 nul, 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, num_product int default 1, index (product_id), index (purchase_id) );
Now, a member logs in and would like to see her purchase history. This is the RESTful action topics on Purchase, using member_id as a constraint. Since member_id is de-crypted from her login cookie, which can’t be spoofed, she would be restricted to view only her own purchases. Up to now, the authentication and ACL work well in protecting topics, i.e. to guarantee that whatever she tries, she can’t steal other member’s purchase records.
Next, she’d like to see the product list of a specific purchase. This is the RESTful topics on Item, using purchase_id as a constraint. Now, a problem arises. If she sends a purchase_id owned by somebody else, surprisingly, she can see that person’s purchase history! Because purchase_id is passed in URL or POST form in plain text, she could maliciously pickup any id for topics.
This is a typical case of RLS, as discussion by Oracle here.
You could solve the problem by (1) add member_id to the item table, and use it as additional constraint; (2) INNER JOIN Item and Purchase, so you can use member_id in the purchase table as the constraint. However, solution 1 would result in redundant member_id. Solution 2 is less practical. For a large database, many tables could be separated from the member table by 2 or more relations. To write JOIN SQLs for every RESTful action is tedious and easily mistaken.
Genelet’s solution to RLS is a two-step procedure. First, it digitally signs upline’s PK with role’s id and an optional time stamp. In the above example, it signs SHA1 of purchase_id and member_id using a secret word. Second, it checks if the signature of the key, which usually appears as a FK in the current table, is validate. If verified, the key is guaranteed to belong to the member. Just randomly passing an arbitrary key by malicious hacker can’t make the correct signature.
The fks is thus defined to
fks => {
ROLE => [in_id, in_sha1, out_id, out_sha1],
...
}
where ROLE is a login role like member; in_id is a key to be constrained, usually a FK in the current table. You should pass the value of in_id and its digital signature in_sha1 as two incoming variables, so Genelet can verify that in_id is indeed owned by member_id by inspecting in_sha1.
By default, in_id will always be used as a constraint in the model.
out_id is a different column in the table, usually the PK. You need to pass it to downlines with signature, named out_sha1. Genelet internally will scan out_id in $self->{LISTS} so every out_id will have an associated out_sha1, which are ready to use for next clicks.
Note that in_id and out_id are field name in the table, which are fixed, but you are free to choose whatever names for in_sha1 and our_sha1.
If role’s id is used as a constraint, you don’t need its signature because it is already proofed in the login cookie. So fks will look like:
fks => {
role => [role_id, undef, out_id, out_sha1],
...
}
If you don’t need out_id:
fks => {
role => [in_id, in_sha1],
...
}
If you only want out_id:
fks => {
role => [unde, undef, out_id, out_sha1],
...
}
Finally note that in the Genelet life circle, RLS checking is made at Phase 8, followed by presest(), and outgoing RLS signature at Phase 15, followed by after().