# database_create - Create Database # # Description: # This script creates the tables required for the KASP database. # Matches kaspimport.pl at 12/03/2009 # Get rid of tables in reverse order drop table if exists parameters_policies; drop table if exists serialmodes; drop table if exists parameters; drop table if exists categories; drop table if exists dnsseckeys; drop table if exists zones; drop table if exists keypairs; drop table if exists securitymodules; drop table if exists policies; # now create the tables # security modules - store information about all the sms used create table securitymodules ( id tinyint not null auto_increment, # id for sm name varchar(30) not null, # name of the sm capacity mediumint not null, requirebackup tinyint default 1, constraint primary key (id) )ENGINE=InnoDB; # categories - stores the possible categories (or uses) of parameters create table categories ( id tinyint not null auto_increment, # id for category_id name varchar(30) not null, # name of the category_id constraint primary key (id) )ENGINE=InnoDB; # parameters - stores the types of parameters available create table parameters ( id mediumint not null auto_increment, # id for parameters name varchar(30) not null, # name of the parameter description varchar(255), # description of the paramter category_id tinyint not null, # category_id of the parameter constraint primary key (id), constraint unique (name, category_id), constraint foreign key (category_id) references categories (id) )ENGINE=InnoDB; create table serialmodes ( id tinyint auto_increment, # id for serial mode name varchar(30), # name of the serial mode description varchar(255), # description of the serial mode constraint primary key (id) )ENGINE=InnoDB; # policies - create table policies ( id mediumint not null auto_increment, # id name varchar(30) not null, # name of the policy description varchar(255), # description of the salt varchar(512), # value of the salt salt_stamp timestamp, # when the salt was generated audit varchar(1024), # contents of constraint primary key (id), constraint unique (name) )ENGINE=InnoDB; # zones - stores the zones create table zones( id mediumint not null auto_increment, # id name varchar(30) not null , # name of the parameter policy_id mediumint not null, constraint primary key (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # stores the private key info create table keypairs( id smallint not null auto_increment, HSMkey_id varchar(255) not null, algorithm tinyint not null, # algorithm code size smallint, securitymodule_id tinyint, # where the key is stored state tinyint, # state of the key (defines valid fields) generate timestamp null default null, # time key inserted into database publish timestamp null default null, # time when key published into the zone ready timestamp null default null, # time when the key is ready for use active timestamp null default null, # time when the key was made active retire timestamp null default null, # time when the key retires dead timestamp null default null, # time when key is slated for removal policy_id mediumint, compromisedflag tinyint, publickey varchar(1024), # public key data backup timestamp null default null, # time when backup was performed fixedDate tinyint default 0, # Set to 1 to stop dates from being set according to the policy timings constraint primary key (id), constraint foreign key (securitymodule_id) references securitymodules (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # stores meta data about keys (actual keys are in a (soft)hsm) create table dnsseckeys ( id int not null auto_increment, # unique id of the key keypair_id smallint, zone_id mediumint, keytype smallint not null, # zsk or ksk (use code in dnskey record) constraint primary key (id), constraint foreign key (zone_id) references zones (id), constraint foreign key (keypair_id) references keypairs (id) )ENGINE=InnoDB; # parameters_policies - join table to hold the values of parameters create table parameters_policies ( id mediumint auto_increment, # id parameter_id mediumint not null, policy_id mediumint not null, value int, # integer value of this key constraint primary key (id), constraint foreign key (parameter_id) references parameters (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # The VIEWS create or replace view PARAMETER_VIEW as select p.name, c.name as category, pp.parameter_id, pp.value, pp.policy_id from parameters_policies pp, parameters p, categories c where pp.parameter_id = p.id and p.category_id = c.id; create or replace view PARAMETER_LIST as select p.name, c.name as category, p.id as parameter_id from parameters p, categories c where p.category_id = c.id; create or replace view KEYDATA_VIEW as select k.id, k.state, k.generate, k.publish, k.ready, k.active, k.retire, k.dead, d.keytype, k.algorithm, k.HSMkey_id as location, d.zone_id, k.policy_id, k.securitymodule_id, k.size, k.compromisedflag, k.fixedDate from keypairs k left outer join dnsseckeys d on k.id = d.keypair_id; # insert default data # default categories insert into categories (name) values ("signature"); insert into categories (name) values ("denial"); insert into categories (name) values ("ksk"); insert into categories (name) values ("zsk"); insert into categories (name) values ("keys"); insert into categories (name) values ("enforcer"); insert into categories (name) values ("zone"); insert into categories (name) values ("parent"); insert into categories (name) values ("audit"); # default serial number modes insert into serialmodes (name, description) values ("unixtime", "seconds since 1 Jan 1970"); insert into serialmodes (name, description) values ("counter", "add one everytime updated"); insert into serialmodes (name, description) values ("datecounter", "YYYYMMDDXX"); insert into serialmodes (name, description) values ("keep", "Signer should not change the serial"); # default parameters insert into parameters (name, description, category_id) select "resign", "re-signing interval", id from categories where name="signature"; insert into parameters (name, description, category_id) select "refresh", "how old a signature may become before it needs to be re-signed",id from categories where name="signature"; insert into parameters (name, description, category_id) select "jitter", "jitter to use in signature inception and expiration times", id from categories where name="signature"; insert into parameters (name, description, category_id) select "clockskew", "estimated max clockskew expected in clients", id from categories where name="signature"; insert into parameters (name, description, category_id) select "ttl", "ttl for RRSIGS", id from categories where name="signature"; insert into parameters (name, description, category_id) select "valdefault", "signature validity period", id from categories where name="signature"; insert into parameters (name, description, category_id) select "valdenial", "nsec(3) validity period", id from categories where name="signature"; insert into parameters (name, description, category_id) select "ttl", "ttl for nsec(3) rrs", id from categories where name="denial"; insert into parameters (name, description, category_id) select "version", "nsec version (0 or 3)", id from categories where name="denial"; insert into parameters (name, description, category_id) select "optout", "opt out flag for nsec3", id from categories where name="denial"; insert into parameters (name, description, category_id) select "resalt", "re-salting interval", id from categories where name="denial"; insert into parameters (name, description, category_id) select "algorithm", "nsec3 algorithm", id from categories where name="denial"; insert into parameters (name, description, category_id) select "iterations", "nsec3 iterations", id from categories where name="denial"; insert into parameters (name, description, category_id) select "saltlength", "nsec3 salt length", id from categories where name="denial"; insert into parameters (name, description, category_id) select "ttl", "ttl for ksk rrs", id from categories where name="keys"; insert into parameters (name, description, category_id) select "retiresafety", "ksk retirement safety factor", id from categories where name="keys"; insert into parameters (name, description, category_id) select "publishsafety", "ksk publish safety factor", id from categories where name="keys"; insert into parameters (name, description, category_id) select "algorithm", "ksk algorithm", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "bits", "ksk key size", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "lifetime", "ksk lifetime", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "emergency", "number of ksks is use at any one time", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "repository", "default ksk sm (for newly generated keys)", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "rfc5011", "are we doing rfc5011?", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "algorithm", "zsk algorithm", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "bits", "zsk key size", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "lifetime", "zsk lifetime", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "emergency", "number of zsks is use at any one time", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "repository", "default zsk sm (for newly generated keys)", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="zone"; insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="zone"; insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="zone"; insert into parameters (name, description, category_id) select "serial", "how serial no are changed", id from categories where name="zone"; insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="parent"; insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="parent"; insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="parent"; insert into parameters (name, description, category_id) select "ttlds", "ttl of the ds", id from categories where name="parent"; #insert into parameters (name, description, category_id) select "keycreate", "policy for key creation 0=fill the hsm, 1=only generate minimum needed", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "interval", "run interval", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "keygeninterval", "interval between key generation runs", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "backupdelay", "how old must a new key be before it can be assumed to have been backed up", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "zones_share_keys", "do all zones on this policy share the same keys", id from categories where name="keys"; insert into parameters (name, description, category_id) select "registrationdelay", "Dr", id from categories where name="parent"; insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll ksks when their time is up", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll zsks when their time is up", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "audit", "placeholder for audit tag", id from categories where name="audit"; COMMIT;