/* connected as DBA */

CREATE USER 'appuser'@'localhost' IDENTIFIED BY '8374@#$%GRfDgfPTfg';
CREATE USER 'trusteduser'@'localhost' IDENTIFIED BY '*&^*(9879879DDSdse';
drop schema IF EXISTS secdemo;
create schema secdemo;

GRANT ALL ON secdemo.* TO 'trusteduser'@'localhost';

CREATE TABLE `secdemo`.`secretsdata` (
`id` INT NOT NULL,
`secret` VARBINARY(3000) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `secdemo`.`secretskey` (
`id` INT NOT NULL,
`secretkey` VARBINARY(3000) NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `secdemo`.`apppublickey` (
`appid` INT NOT NULL,
`publickey` VARBINARY(3000) NOT NULL,
PRIMARY KEY (`appid`));

CREATE TABLE `secdemo`.`privuserprivatekey` (
`appid` INT NOT NULL,
`privatekey` VARBINARY(3000) NOT NULL,
PRIMARY KEY (`appid`));

/*GRANT TO APPUSER */
GRANT SELECT, DELETE, UPDATE ON secdemo.secretsdata TO 'appuser'@'localhost';
GRANT SELECT ON secdemo.apppublickey TO 'appuser'@'localhost';
GRANT INSERT ON `secdemo`.`secretskey` TO 'appuser'@'localhost';

/* Server – Generate Private Key */
/* Key length in bits; make larger for stronger keys */
SET @key_len = 2048;
SET @algo = 'RSA';

/* Create private key */
SET @priv = CREATE_ASYMMETRIC_PRIV_KEY(@algo, @key_len);
select @priv;
INSERT INTO `secdemo`.`privuserprivatekey` (`appid`,`privatekey`) VALUES (1, @priv);

/* Server – Generate Public Key */

/* Derive corresponding public key from private key, using same algorithm */
SET @pub = CREATE_ASYMMETRIC_PUB_KEY(@algo, @priv);
INSERT INTO `secdemo`.`apppublickey` (`appid`,`publickey`) VALUES (1, @pub);

/* connected as appuser */

/* Web Client – Get Public Key */
select publickey from `secdemo`.`apppublickey` where appid=1 INTO @webpubkey;
select 'web public key', @webpubkey;
/* Web Client – Generate passphrase */
/* SET @passphrase = keyring_key_generate('MyKey', 'AES', 32); */
SET @passphrase = SHA2(lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0), 224);
select 'Symmetric Passphrase', @passphrase;

/* Web Client – Encrypt Data with passphrase */
SET @keepsecret='Santa and his Elves location is just north east of Longyearbyen, Norway';
select @keepsecret;

SET @enc_keepsecret=AES_ENCRYPT(@keepsecret,@passphrase);
select @enc_keepsecret;
INSERT INTO `secdemo`.`secretsdata` (`id`,`secret`) VALUES (1, @enc_keepsecret);
SELECT 'Decrypt just to check', AES_DECRYPT(@enc_keepsecret, @passphrase);

/* Web Client – Encrypt passphrase with Public Key */
SET @key_len = 2048;
SET @algo = 'RSA';
Set @enc_pp = ASYMMETRIC_ENCRYPT(@algo, @passphrase, @webpubkey);
select 'Encrypted secret key', @enc_pp;

/* Server - Store AES Data and PK Encrypted Passphrase */
INSERT INTO `secdemo`.`secretskey` (`id`, `secretkey`) VALUES (1, @enc_pp);

/* Look at key (you can't no permission and even if could - its encrypted */
select * from `secdemo`.`secretskey`;

/* Look at data - its encrypted */
select * from `secdemo`.`secretsdata`;

/* connected as trusteduser */

/* Privileged Client – Read PK Encrypted Passphrase */
select secretkey from `secdemo`.`secretskey` where id=1 INTO @encpassphrase;

/* Priv App Client – Get Private Key */
select privatekey from `secdemo`.`privuserprivatekey` where appid=1 INTO @privappprivpkey;
select 'privappprivpkey', @privappprivpkey;

/* Privileged Client – Decrypt passphrase with Private Key */
Set @enc_pp = ASYMMETRIC_DECRYPT(@algo, @encpassphrase, @privappprivpkey);
select 'Original Symmetric Passphrase', @enc_pp;

/* Privileged Client – Read AES Encrypted Data */
select * from `secdemo`.`secretsdata`;
/* Privileged Client – See Secret Data */
/* Privileged Client – Decrypt data with Passphrase */
select AES_DECRYPT(secret, @enc_pp) from `secdemo`.`secretsdata` where id=1;