Month: August 2017

MARIADB VIRTUAL PRIVATE DATABASE (VPD)

SUMMARY

This is an implementation of a Virtual Private Database (VPD) in MariaDB. I’ll refer to the concept here as a Pseudo Private Database (PPD) to distinguish it from Oracle’s VPD which is implemented in the database engine, and does not rely on a view and a couple triggers for each table.

This VPD uses the same database and the same tables for multiple users. Each user will have their own view of the data that they “own” in the tables. They will not be able to affect data “owned” by another user. When an administrator alters a table, it’s altered for all users. Wikipedia defines a VPD like this: Wikipedia.

DETAIL

The MariaDB PPD works as follows. Given a set of tables…

• Tables are renamed.
• Tables are altered to contain an additional id column.
• Views are created with the old table name that reference the new table name. Those views query the table by adding the id column to the WHERE criteria.
• INSERT & UPDATE triggers are created on the tables to populate the id column.
• PPD users are not allowed to SELECT, INSERT, UPDATE, DELETE, DROP (etc) with reference to tables. Only views may be referenced by the user.
• There is allowance for sharing table data among all users.

Some caveats, the PPD…

• supports row isolation of tables
• does not isolate stored procedures, functions, triggers, and events
• does not support DDL such as TRUNCATE or ALTER

EXAMPLE

Here is a PPD demonstration for MariaDB 10.1.19. It supports SELECT, INSERT, UPDATE, DELETE, and REPLACE (DELETE + INSERT)

• SELECT & DELETE are handled by a view for the table
• INSERT, UPDATE is handled by two triggers on the table
• REPLACE is handled by the view and a trigger on the table

Data is filtered by the current user. Each table will have a ‘ppd_id VARCHAR(256)’ column. To see under which user you are logged into, issue this command…

• SELECT SUBSTRING_INDEX(USER(),’@’,1); /* userID portion of user@’%’ formatted user */

To set up our test, create the sample database (dbname) and have that be our current database…

• CREATE DATABASE dbname;
• USE dbname;

Outside the scope of this example is working with lots of existing tables. If you wish to implement this example on a current example, you may find this select statement useful.h

• SELECT `name` FROM information_schema.innodb_sys_tables WHERE NAME LIKE ‘dbname/%’;

The example begins by creating a new table to simulate an existing table.

• CREATE TABLE tabname (col1 VARCHAR(32), col2 VARCHAR(32));

Rename & alter the table to a new name to demonstrate what is involved in converting an existing set of tables

• RENAME TABLE tabname TO ppd_tabname;
• ALTER TABLE ppd_tabname ADD (ppd_id VARCHAR(256));

Create the INSERT trigger, which is used to add the ppd_id to each INSERT

• CREATE OR REPLACE TRIGGER ppd_tabname_insert BEFORE INSERT ON ppd_tabname FOR EACH ROW SET NEW.ppd_id = SUBSTRING_INDEX(USER(),’@’,1);

Create the UPDATE trigger, which is used to add the ppd_id to each UPDATE. (note, this should not need to be done as the ppd_id has already been filled in, but I wanted to account for the case when someone attempted to change the ppd_id. In that specific case, the UPDATE updates zero rows, rather than updating as normal with the exception of the ppd_id. I did not diagnose the issue further as I was happy with such a subversion attempt failing)

• CREATE OR REPLACE TRIGGER ppd_tabname_update BEFORE UPDATE ON ppd_tabname FOR EACH ROW SET NEW.ppd_id = OLD.ppd_id;

Create the view used to query the tables.

• CREATE OR REPLACE VIEW tabname AS (SELECT * FROM ppd_tabname WHERE ppd_tabname.ppd_id=SUBSTRING_INDEX(USER(),’@’,1) OR ppd_tabname.ppd_id=’SHARED’ );

Work from two sessions connected to the same instance, but with the connection using a different userID.

— session 1

• USE dbname;
• INSERT INTO tabname (col1,col2) VALUES (‘aaa’, ‘aaa’);
• INSERT INTO tabname (col1,col2) VALUES (‘bbb’, ‘bbb’);

— session 2

• USE dbname;
• INSERT INTO tabname (col1,col2) VALUES (‘ccc’, ‘ccc’);
• INSERT INTO tabname (col1,col2) VALUES (‘ddd’, ‘ddd’);

— session 1

• SELECT * FROM tabname; — two rows (see session 1 inserts above)
• SELECT * FROM tabname WHERE col1=’aaa’; — 1 row
• SELECT * FROM tabname WHERE col1=’ccc’; — 0 rows
• UPDATE tabname SET col2=’ABC’ WHERE col1=’aaa’; — 1 row
• UPDATE tabname SET col2=’ABC’ WHERE col1=’ccc’; — 0 rows
• DELETE FROM tabname WHERE col1=’aaa’; — 1 row
• DELETE FROM tabname WHERE col1=’ccc’; — 0 row

— session 2

• SELECT * FROM tabname; — two rows (see session 2 inserts above)
• SELECT * FROM tabname WHERE col1=’aaa’; — 0 row
• SELECT * FROM tabname WHERE col1=’ccc’; — 1 rows
• UPDATE tabname SET col2=’ABC’ WHERE col1=’aaa’; — 0 row
• UPDATE tabname SET col2=’ABC’ WHERE col1=’ccc’; — 1 rows
• DELETE FROM tabname WHERE col1=’aaa’; — 0 row
• DELETE FROM tabname WHERE col1=’ccc’; — 1 row

PRIVILEGES

Try this on both sessions.

• SELECT * FROM ppd_tabname

Privileges can be adjusted to force the use of the views (rather than allowing the user to directly query the tables as in the above SELECT statement). Note, you may drop the database and start the example again, and adjust the privs just prior to inserting data. The example will work the same way.

• REVOKE ALL ON dbname.* from user1@’%’;
• GRANT select, insert, update, delete, trigger ON dbname.tabname TO user1@’%’;
• — no grants to the table itself
• FLUSH PRIVILEGES;
• SHOW GRANTS FOR user1@’%’;

(If you use SQLyog, be sure to turn off Preferences/Tools/Preferences/PowerTools/EnableQueryProfiler)

OPTIONAL OPTIMIZATION
Rather than storing the ppd_id in a VARCHAR(256), use ppd_id of TINYINT UNSIGNED. Create a translation table of USER() -> TINYINT, and use a join in the view.

Keywords: virtual private database, vpd