Author: Troy Frericks

— In MariaDB or MySQL, here is a demonstration of how to easily write 
— JSON to a table, and providing an easy way for normal SQL to reference 
— that JSON. Storing JSON can be a definition of a NoSQL database
— Note, this is intended for you to copy/paste into your SQL tool, and play. 
— It is self explanatory!


CREATE DATABASE tst;
USE tst;


— DROP TABLE tab;
CREATE TABLE tab (row_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  document_store JSON);


— Insert two JSON objects into the column ‘document_store’ of type JSON.
INSERT INTO tab (document_store) VALUES (‘{“fname”:”jim”,
     “lname”:”jackson”,
     “stuff”:”nothing here”}’);
INSERT INTO tab (document_store) VALUES (‘{“fname”:”troy”,
     “lname”:”Frericks”,
     “stuff”:”A bunch of stuff”}’);
SELECT * FROM tab;


— select data from the JSON typed column
SELECT JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’)),
 JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.lname’)),
 JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.stuff’))
FROM tab
WHERE JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’)) = ‘troy’;


— create virtual/stored columns that ‘expose’ the data in the JSON object
ALTER TABLE tab ADD json_fname VARCHAR(32) AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’))) VIRTUAL;


ALTER TABLE tab ADD json_lname VARCHAR(32) GENERATED ALWAYS AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.lname’))) VIRTUAL;


ALTER TABLE tab ADD json_stuff VARCHAR(32) GENERATED ALWAYS AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.stuff’))) STORED;


SELECT * FROM tab;


— prove that virtual or stored columns can be indexed
CREATE INDEX idx_tab_fname ON tab (json_fname);
CREATE INDEX idx_tab_sname ON tab (json_lname);
CREATE INDEX idx_tab_stuff ON tab (json_stuff);






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

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