TWO FACTOR AUTHENTICATION, MULTI FACTOR AUTHENTICATION, TWO STEP AUTHENTICATION

TWO FACTOR AUTHENTICATION, MULTI FACTOR AUTHENTICATION, TWO STEP AUTHENTICATION

What are the following types of authentication?

  • multi-factor
  • two-factor (2FA)
  • two-step

Two-factor authentication is a subset of multi-factor authentication (ie, almost no one does three-factor authentication).


Multi-factor authentication has a rigid definition. It is using more than one of the following authentication factors…

  1. something you and only you know (ie, password) 
  2. something you and only you have (ie, a token)
  3. something you and only you are (ie, a fingerprint)

SMS or an App doesn’t count as something “you and only you have” (reference, reference 2 added 22-Mar-2019). You can’t touch (have) SMS, and cell site maintenance personnel can easily intercept an SMS message. You also can’t touch an App, and phones can be cloned so others can have an app that functions exactly like yours. Use of one of this type of methods of secondary authentication methods that “you and only you” can’t “have” are considered two-step authentication. Note, the second step is frequently an out-of-band verification.

An interesting option that may greatly simplify authentication is a “single-step” shared secret out-of-band verification called SQRL. Something to keep an eye on.

Keywords: identity, verification, authorization, 2fa, 2factor, 2step, two factor, two step, multi factor

Troy Frericks.
blog 17-Jan-2018
=
Copyright 2015-2018 by Troy Frericks, http://dba.frericks.us/.
#

MARIADB VIRTUAL PRIVATE DATABASE (VPD)

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

Troy Frericks.
blog 31-Aug-2017
=
Copyright 2015-2017 by Troy Frericks, http://dba.frericks.us/.
#