Author: jfthomps
Date: Fri May 26 18:56:19 2017
New Revision: 1796326
URL: http://svn.apache.org/viewvc?rev=1796326&view=rev
Log:
VCL-1045 - Method of encrypting sensitive database entries
vcl.sql:
-updated addomain: set username and password to not allow NULL; added secretid
and key for secretid
-added cryptkey
-added cryptsecret
-updated vmprofile: added secretid and key for secretid
-added constraints for addomain
-added constraints for cryptsecret
-added constraint for vmprofile.secretid
update-vcl.sql:
-updated addomain: set username and password to not allow NULL; added secretid
and key for secretid
-added cryptkey
-added cryptsecret
-added call to AddColumnIfNotExists to add vmprofile.secretid
-added call to AddIndexIfNotExists to add key for vmprofile.secretid
-added constraints for addomain
-added constraints for cryptsecret
-added constraint for vmprofile.secretid
Modified:
vcl/trunk/mysql/update-vcl.sql
vcl/trunk/mysql/vcl.sql
Modified: vcl/trunk/mysql/update-vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1796326&r1=1796325&r2=1796326&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Fri May 26 18:56:19 2017
@@ -874,10 +874,12 @@ CREATE TABLE IF NOT EXISTS `addomain` (
`name` varchar(30) NOT NULL default '',
`domainDNSName` varchar(70) NOT NULL default '',
`dnsServers` varchar(512) default NULL,
- `username` varchar(64) default NULL,
- `password` varchar(256) default NULL,
+ `username` varchar(64) NOT NULL default '',
+ `password` varchar(256) NOT NULL default '',
+ `secretid` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
- UNIQUE KEY `domainDNSName` (`domainDNSName`)
+ UNIQUE KEY `domainDNSName` (`domainDNSName`),
+ KEY `secretid` (`secretid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -967,6 +969,37 @@ CALL AddIndexIfNotExists('continuations'
-- --------------------------------------------------------
--
+-- Table structure for table `cryptkey`
+--
+
+CREATE TABLE IF NOT EXISTS `cryptkey` (
+ `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
+ `hostid` smallint(6) unsigned NOT NULL,
+ `hosttype` enum('managementnode','web') NOT NULL DEFAULT 'managementnode',
+ `pubkey` varchar(1000) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `hostid` (`hostid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `cryptsecret`
+--
+
+CREATE TABLE IF NOT EXISTS `cryptsecret` (
+ `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
+ `cryptkeyid` smallint(5) unsigned NOT NULL,
+ `secretid` smallint(5) unsigned NOT NULL,
+ `cryptsecret` varchar(1000) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `cryptkeyid` (`cryptkeyid`,`secretid`),
+ KEY `secretid` (`secretid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
-- Table structure for table `image`
--
@@ -1546,6 +1579,7 @@ CALL AddColumnIfNotExists('vmprofile', '
CALL AddColumnIfNotExists('vmprofile', 'rsakey', "varchar(256) NULL default
NULL AFTER `rsapub`");
CALL AddColumnIfNotExists('vmprofile', 'encryptedpasswd', "text NULL default
NULL AFTER `rsakey`");
CALL AddColumnIfNotExists('vmprofile', 'folderpath', "varchar(256) default
NULL AFTER resourcepath");
+CALL AddColumnIfNotExists('vmprofile', 'secretid', "smallint(5) unsigned NULL
default NULL AFTER password");
CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth0_generated',
'eth0generated', "tinyint(1) unsigned NOT NULL default '0'");
CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth1_generated',
'eth1generated', "tinyint(1) unsigned NOT NULL default '0'");
@@ -1555,6 +1589,7 @@ CALL AlterVMDiskValues();
CALL AddUniqueIndex('vmprofile', 'profilename');
CALL AddIndexIfNotExists('vmprofile', 'repositoryimagetypeid');
CALL AddIndexIfNotExists('vmprofile', 'datastoreimagetypeid');
+CALL AddIndexIfNotExists('vmprofile', 'secretid');
-- --------------------------------------------------------
@@ -2056,6 +2091,16 @@ UPDATE vmprofile SET vmprofile.datastore
-- --------------------------------------------------------
--
+-- Constraints for table `addomain`
+--
+
+CALL DropExistingConstraints('addomain', 'secretid');
+
+CALL AddConstraintIfNotExists('addomain', 'secretid', 'cryptsecret',
'secretid', 'none', '');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `blockComputers`
--
@@ -2227,6 +2272,14 @@ CALL AddConstraintIfNotExists('continuat
-- --------------------------------------------------------
--
+-- Constraints for table `cryptsecret`
+--
+
+CALL AddConstraintIfNotExists('cryptsecret', 'cryptkeyid', 'cryptkey', 'id',
'delete', 'CASCADE');
+
+-- --------------------------------------------------------
+
+--
-- Constraints for table `image`
--
@@ -2644,6 +2697,7 @@ CALL DropExistingConstraints('vmprofile'
CALL AddConstraintIfNotExists('vmprofile', 'imageid', 'image', 'id', 'none',
'');
CALL AddConstraintIfNotExists('vmprofile', 'repositoryimagetypeid',
'imagetype', 'id', 'update', 'CASCADE');
CALL AddConstraintIfNotExists('vmprofile', 'datastoreimagetypeid',
'imagetype', 'id', 'update', 'CASCADE');
+CALL AddConstraintIfNotExists('vmprofile', 'secretid', 'cryptsecret',
'secretid', 'none', '');
-- --------------------------------------------------------
Modified: vcl/trunk/mysql/vcl.sql
URL:
http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1796326&r1=1796325&r2=1796326&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Fri May 26 18:56:19 2017
@@ -35,10 +35,12 @@ CREATE TABLE IF NOT EXISTS `addomain` (
`name` varchar(30) NOT NULL default '',
`domainDNSName` varchar(70) NOT NULL default '',
`dnsServers` varchar(512) default NULL,
- `username` varchar(64) default NULL,
- `password` varchar(256) default NULL,
+ `username` varchar(64) NOT NULL default '',
+ `password` varchar(256) NOT NULL default '',
+ `secretid` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
- UNIQUE KEY `domainDNSName` (`domainDNSName`)
+ UNIQUE KEY `domainDNSName` (`domainDNSName`),
+ KEY `secretid` (`secretid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -414,6 +416,36 @@ CREATE TABLE IF NOT EXISTS `continuation
KEY `deletefromid` (`deletefromid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `cryptkey`
+--
+
+CREATE TABLE IF NOT EXISTS `cryptkey` (
+ `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
+ `hostid` smallint(6) unsigned NOT NULL,
+ `hosttype` enum('managementnode','web') NOT NULL DEFAULT 'managementnode',
+ `pubkey` varchar(1000) NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `hostid` (`hostid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `cryptsecret`
+--
+
+CREATE TABLE IF NOT EXISTS `cryptsecret` (
+ `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
+ `cryptkeyid` smallint(5) unsigned NOT NULL,
+ `secretid` smallint(5) unsigned NOT NULL,
+ `cryptsecret` varchar(1000) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `cryptkeyid` (`cryptkeyid`,`secretid`),
+ KEY `secretid` (`secretid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -1488,6 +1520,7 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
`vmdisk` enum('dedicated','shared') NOT NULL default 'dedicated',
`username` varchar(80) NULL default NULL,
`password` varchar(256) NULL default NULL,
+ `secretid` smallint(5) unsigned NULL default NULL,
`eth0generated` tinyint(1) unsigned NOT NULL default '0',
`eth1generated` tinyint(1) unsigned NOT NULL default '0',
`rsapub` text NULL default NULL,
@@ -1497,7 +1530,8 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
UNIQUE KEY `profilename` (`profilename`),
KEY `imageid` (`imageid`),
KEY `repositoryimagetypeid` (`repositoryimagetypeid`),
- KEY `datastoreimagetypeid` (`datastoreimagetypeid`)
+ KEY `datastoreimagetypeid` (`datastoreimagetypeid`),
+ KEY `secretid` (`secretid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
@@ -2272,6 +2306,11 @@ INSERT IGNORE INTO `vmtype` (`id`, `name
--
--
+-- Constraints for table `addomain`
+--
+ALTER TABLE `addomain` ADD CONSTRAINT FOREIGN KEY (`secretid`) REFERENCES
`cryptsecret` (`secretid`);
+
+--
-- Constraints for table `blockComputers`
--
ALTER TABLE `blockComputers` ADD CONSTRAINT FOREIGN KEY (`blockTimeid`)
REFERENCES `blockTimes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
@@ -2371,6 +2410,11 @@ ALTER TABLE `continuations` ADD CONSTRAI
ALTER TABLE `continuations` ADD CONSTRAINT FOREIGN KEY (`parentid`) REFERENCES
`continuations` (`id`) ON DELETE CASCADE;
--
+-- Constraints for table `cryptsecret`
+--
+ALTER TABLE `cryptsecret` ADD CONSTRAINT FOREIGN KEY (`cryptkeyid`) REFERENCES
`cryptkey` (`id`) ON DELETE CASCADE;
+
+--
-- Constraints for table `image`
--
ALTER TABLE `image` ADD CONSTRAINT FOREIGN KEY (`ownerid`) REFERENCES `user`
(`id`) ON UPDATE CASCADE;
@@ -2651,6 +2695,7 @@ ALTER TABLE `vmhost` ADD CONSTRAINT FORE
ALTER TABLE `vmprofile` ADD CONSTRAINT FOREIGN KEY (`imageid`) REFERENCES
`image` (`id`);
ALTER TABLE `vmprofile` ADD CONSTRAINT FOREIGN KEY (`repositoryimagetypeid`)
REFERENCES `imagetype` (`id`) ON UPDATE CASCADE;
ALTER TABLE `vmprofile` ADD CONSTRAINT FOREIGN KEY (`datastoreimagetypeid`)
REFERENCES `imagetype` (`id`) ON UPDATE CASCADE;
+ALTER TABLE `vmprofile` ADD CONSTRAINT FOREIGN KEY (`secretid`) REFERENCES
`cryptsecret` (`secretid`);
--
-- Constraints for table `winKMS`
