1#!/bin/sh 2 3# Copyright (C) 2019-2021 Internet Systems Consortium, Inc. ("ISC") 4# 5# This Source Code Form is subject to the terms of the Mozilla Public 6# License, v. 2.0. If a copy of the MPL was not distributed with this 7# file, You can obtain one at http://mozilla.org/MPL/2.0/. 8 9# shellcheck disable=SC1091 10# SC1091: Not following: ... was not specified as input (see shellcheck -x). 11 12# shellcheck disable=SC2039 13# SC2039: In POSIX sh, 'local' is undefined. 14 15# Exit with error if commands exit with non-zero and if undefined variables are 16# used. 17set -eu 18 19# shellcheck disable=SC2034 20# SC2034: ... appears unused. Verify use (or export if used externally). 21prefix="@prefix@" 22 23# Include utilities. Use installed version if available and 24# use build version if it isn't. 25if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then 26 . "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh" 27else 28 . "@abs_top_builddir@/src/bin/admin/admin-utils.sh" 29fi 30 31VERSION=$(mysql_version "$@") 32 33if [ "$VERSION" != "8.1" ]; then 34 printf 'This script upgrades 8.1 to 8.2. ' 35 printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" 36 exit 0 37fi 38 39# Get the schema name from database argument. We need this to 40# query information_schema for the right database. 41for arg in "${@}" 42do 43 if ! printf '%s' "${arg}" | grep -Eq '^\-\-' 44 then 45 schema="$arg" 46 break 47 fi 48done 49 50# Make sure we can id the schema 51if [ -z "$schema" ] 52then 53 printf "Could not find database schema name in cmd line args: %s\n" "${*}" 54 exit 255 55fi 56 57# Save the command line args, as we use these later change_column function. 58 59# Function to rename a column in a table. 60change_column() { 61 local schema="${1-}"; shift 62 local table="${1-}"; shift 63 local ocolumn="${1-}"; shift 64 local ncolumn="${1-}"; shift 65 66 # First let's find out if the column name in the table actually needs updating. 67 sql="select count(column_name) from information_schema.columns where table_schema='$schema' and table_name = '$table' and column_name = '$ocolumn'" 68 if ! count=$(mysql -N -B "${@}" -e "${sql}") 69 then 70 printf 'change_column: schema query failed [%s]\n' "${sql}" 71 exit 255 72 fi 73 74 # If we found a match record, the column needs to be renamed 75 if [ "$count" -eq 1 ] 76 then 77 sql="ALTER TABLE $table CHANGE COLUMN $ocolumn $ncolumn" 78 if ! mysql -N -B "${@}" -e "${sql}" 79 then 80 printf 'change_column: alter query failed [%s]\n' "${sql}" 81 exit 255 82 fi 83 else 84 printf '%s column is already correct\n' "${table}" 85 fi 86} 87 88mysql "$@" <<EOF 89 90# Drop existing trigger on the dhcp4_shared_network table. 91DROP TRIGGER dhcp4_shared_network_ADEL; 92 93# Create new trigger which will delete options associated with the shared 94# network. 95DELIMITER $$ 96CREATE TRIGGER dhcp4_shared_network_BDEL BEFORE DELETE ON dhcp4_shared_network 97 FOR EACH ROW 98 BEGIN 99 CALL createAuditEntryDHCP4('dhcp4_shared_network', OLD.id, "delete"); 100 DELETE FROM dhcp4_options WHERE shared_network_name = OLD.name; 101 END $$ 102DELIMITER ; 103 104# Drop existing trigger on the dhcp4_subnet table. 105DROP TRIGGER dhcp4_subnet_ADEL; 106 107# Create new trigger which will delete pools associated with the subnet and 108# the options associated with the subnet. 109DELIMITER $$ 110CREATE TRIGGER dhcp4_subnet_BDEL BEFORE DELETE ON dhcp4_subnet 111 FOR EACH ROW 112 BEGIN 113 CALL createAuditEntryDHCP4('dhcp4_subnet', OLD.subnet_id, "delete"); 114 DELETE FROM dhcp4_pool WHERE subnet_id = OLD.subnet_id; 115 DELETE FROM dhcp4_options WHERE dhcp4_subnet_id = OLD.subnet_id; 116 END $$ 117DELIMITER ; 118 119# Do not perform cascade deletion of the data in the dhcp4_pool because 120# the cascade deletion does not execute triggers associated with the table. 121# Instead we are going to use triggers on the dhcp4_subnet table. 122ALTER TABLE dhcp4_pool 123 DROP FOREIGN KEY fk_dhcp4_pool_subnet_id; 124 125ALTER TABLE dhcp4_pool 126 ADD CONSTRAINT fk_dhcp4_pool_subnet_id FOREIGN KEY (subnet_id) 127 REFERENCES dhcp4_subnet (subnet_id) 128 ON DELETE NO ACTION ON UPDATE CASCADE; 129 130# Drop existing trigger on the dhcp6_shared_network table. 131DROP TRIGGER dhcp6_shared_network_ADEL; 132 133# Create new trigger which will delete options associated with the shared 134# network. 135DELIMITER $$ 136CREATE TRIGGER dhcp6_shared_network_BDEL BEFORE DELETE ON dhcp6_shared_network 137 FOR EACH ROW 138 BEGIN 139 CALL createAuditEntryDHCP6('dhcp6_shared_network', OLD.id, "delete"); 140 DELETE FROM dhcp6_options WHERE shared_network_name = OLD.name; 141 END $$ 142DELIMITER ; 143 144# Drop existing trigger on the dhcp6_subnet table. 145DROP TRIGGER dhcp6_subnet_ADEL; 146 147# Create new trigger which will delete pools associated with the subnet and 148# the options associated with the subnet. 149DELIMITER $$ 150CREATE TRIGGER dhcp6_subnet_BDEL BEFORE DELETE ON dhcp6_subnet 151 FOR EACH ROW 152 BEGIN 153 CALL createAuditEntryDHCP6('dhcp6_subnet', OLD.subnet_id, "delete"); 154 DELETE FROM dhcp6_pool WHERE subnet_id = OLD.subnet_id; 155 DELETE FROM dhcp6_pd_pool WHERE subnet_id = OLD.subnet_id; 156 DELETE FROM dhcp6_options WHERE dhcp6_subnet_id = OLD.subnet_id; 157 END $$ 158DELIMITER ; 159 160# Do not perform cascade deletion of the data in the dhcp6_pool and dhcp6_pd_pool 161# because the cascaded deletion does not execute triggers associated with the table. 162# Instead we are going to use triggers on the dhcp6_subnet table. 163ALTER TABLE dhcp6_pool 164 DROP FOREIGN KEY fk_dhcp6_pool_subnet_id; 165 166ALTER TABLE dhcp6_pd_pool 167 DROP FOREIGN KEY fk_dhcp6_pd_pool_subnet_id; 168 169ALTER TABLE dhcp6_pool 170 ADD CONSTRAINT fk_dhcp6_pool_subnet_id FOREIGN KEY (subnet_id) 171 REFERENCES dhcp6_subnet (subnet_id) 172 ON DELETE NO ACTION ON UPDATE CASCADE; 173 174ALTER TABLE dhcp6_pd_pool 175 ADD CONSTRAINT fk_dhcp6_pd_pool_subnet_id FOREIGN KEY (subnet_id) 176 REFERENCES dhcp6_subnet (subnet_id) 177 ON DELETE NO ACTION ON UPDATE CASCADE; 178 179# Create trigger which removes pool specific options upon removal of 180# the pool. 181DELIMITER $$ 182CREATE TRIGGER dhcp6_pd_pool_BDEL BEFORE DELETE ON dhcp6_pd_pool FOR EACH ROW 183BEGIN 184DELETE FROM dhcp6_options WHERE scope_id = 6 AND pd_pool_id = OLD.id; 185END 186$$ 187DELIMITER ; 188 189# Add missing columns in pools. 190ALTER TABLE dhcp4_pool 191 ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, 192 ADD COLUMN require_client_classes LONGTEXT, 193 ADD COLUMN user_context LONGTEXT; 194 195ALTER TABLE dhcp6_pd_pool 196 ADD COLUMN excluded_prefix VARCHAR(45) DEFAULT NULL, 197 ADD COLUMN excluded_prefix_length TINYINT(3) NOT NULL, 198 ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, 199 ADD COLUMN require_client_classes LONGTEXT, 200 ADD COLUMN user_context LONGTEXT; 201 202ALTER TABLE dhcp6_pool 203 ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, 204 ADD COLUMN require_client_classes LONGTEXT, 205 ADD COLUMN user_context LONGTEXT; 206 207-- ----------------------------------------------------- 208-- 209-- New version of the createOptionAuditDHCP4 stored 210-- procedure which updates modification timestamp of 211-- a parent object when an option is modified. 212-- 213-- The following parameters are passed to the procedure: 214-- - modification_type: "create", "update" or "delete" 215-- - scope_id: identifier of the option scope, e.g. 216-- global, subnet specific etc. See dhcp_option_scope 217-- for specific values. 218-- - option_id: identifier of the option. 219-- - subnet_id: identifier of the subnet if the option 220-- belongs to the subnet. 221-- - host_id: identifier of the host if the option 222-- - belongs to the host. 223-- - network_name: shared network name if the option 224-- belongs to the shared network. 225-- - pool_id: identifier of the pool if the option 226-- belongs to the pool. 227-- - modification_ts: modification timestamp of the 228-- option. 229-- ----------------------------------------------------- 230DROP PROCEDURE IF EXISTS createOptionAuditDHCP4; 231DELIMITER $$ 232CREATE PROCEDURE createOptionAuditDHCP4(IN modification_type VARCHAR(32), 233 IN scope_id TINYINT(3) UNSIGNED, 234 IN option_id BIGINT(20) UNSIGNED, 235 IN subnet_id INT(10) UNSIGNED, 236 IN host_id INT(10) UNSIGNED, 237 IN network_name VARCHAR(128), 238 IN pool_id BIGINT(20), 239 IN modification_ts TIMESTAMP) 240BEGIN 241 # These variables will hold shared network id and subnet id that 242 # we will select. 243 DECLARE snid VARCHAR(128); 244 DECLARE sid INT(10) UNSIGNED; 245 246 # Cascade transaction flag is set to 1 to prevent creation of 247 # the audit entries for the options when the options are 248 # created as part of the parent object creation or update. 249 # For example: when the option is added as part of the subnet 250 # addition, the cascade transaction flag is equal to 1. If 251 # the option is added into the existing subnet the cascade 252 # transaction is equal to 0. Note that depending on the option 253 # scope the audit entry will contain the object_type value 254 # of the parent object to cause the server to replace the 255 # entire subnet. The only case when the object_type will be 256 # set to 'dhcp4_options' is when a global option is added. 257 # Global options do not have the owner. 258 IF @cascade_transaction IS NULL OR @cascade_transaction = 0 THEN 259 # todo: host manager hasn't been updated to use audit 260 # mechanisms so ignore host specific options for now. 261 IF scope_id = 0 THEN 262 # If a global option is added or modified, create audit 263 # entry for the 'dhcp4_options' table. 264 CALL createAuditEntryDHCP4('dhcp4_options', option_id, modification_type); 265 ELSEIF scope_id = 1 THEN 266 # If subnet specific option is added or modified, update 267 # the modification timestamp of this subnet to allow the 268 # servers to refresh the subnet information. This will 269 # also result in creating an audit entry for this subnet. 270 UPDATE dhcp4_subnet AS s SET s.modification_ts = modification_ts 271 WHERE s.subnet_id = subnet_id; 272 ELSEIF scope_id = 4 THEN 273 # If shared network specific option is added or modified, 274 # update the modification timestamp of this shared network 275 # to allow the servers to refresh the shared network 276 # information. This will also result in creating an 277 # audit entry for this shared network. 278 SELECT id INTO snid FROM dhcp4_shared_network WHERE name = network_name LIMIT 1; 279 UPDATE dhcp4_shared_network AS n SET n.modification_ts = modification_ts 280 WHERE n.id = snid; 281 ELSEIF scope_id = 5 THEN 282 # If pool specific option is added or modified, update 283 # the modification timestamp of the owning subnet. 284 SELECT dhcp4_pool.subnet_id INTO sid FROM dhcp4_pool WHERE id = pool_id; 285 UPDATE dhcp4_subnet AS s SET s.modification_ts = modification_ts 286 WHERE s.subnet_id = sid; 287 END IF; 288 END IF; 289END $$ 290DELIMITER ; 291 292# Recreate dhcp4_options_AINS trigger to pass timestamp to the updated 293# version of the createOptionAuditDHCP4. 294DROP TRIGGER IF EXISTS dhcp4_options_AINS; 295 296# This trigger is executed after inserting a DHCPv4 option into the 297# database. It creates appropriate audit entry for this option or 298# a parent object owning this option. 299DELIMITER $$ 300CREATE TRIGGER dhcp4_options_AINS AFTER INSERT ON dhcp4_options 301 FOR EACH ROW 302 BEGIN 303 CALL createOptionAuditDHCP4("create", NEW.scope_id, NEW.option_id, NEW.dhcp4_subnet_id, 304 NEW.host_id, NEW.shared_network_name, NEW.pool_id, 305 NEW.modification_ts); 306 END $$ 307DELIMITER ; 308 309# Recreate dhcp4_options_AUPD trigger to pass timestamp to the updated 310# version of the createOptionAuditDHCP4. 311DROP TRIGGER IF EXISTS dhcp4_options_AUPD; 312 313# This trigger is executed after updating a DHCPv4 option in the 314# database. It creates appropriate audit entry for this option or 315# a parent object owning this option. 316DELIMITER $$ 317CREATE TRIGGER dhcp4_options_AUPD AFTER UPDATE ON dhcp4_options 318 FOR EACH ROW 319 BEGIN 320 CALL createOptionAuditDHCP4("update", NEW.scope_id, NEW.option_id, NEW.dhcp4_subnet_id, 321 NEW.host_id, NEW.shared_network_name, NEW.pool_id, 322 NEW.modification_ts); 323 END $$ 324DELIMITER ; 325 326# Recreate dhcp4_options_ADEL trigger to pass timestamp to the updated 327# version of the createOptionAuditDHCP4. 328DROP TRIGGER IF EXISTS dhcp4_options_ADEL; 329 330# This trigger is executed after deleting a DHCPv4 option in the 331# database. It creates appropriate audit entry for this option or 332# a parent object owning this option. 333DELIMITER $$ 334CREATE TRIGGER dhcp4_options_ADEL AFTER DELETE ON dhcp4_options 335 FOR EACH ROW 336 BEGIN 337 CALL createOptionAuditDHCP4("delete", OLD.scope_id, OLD.option_id, OLD.dhcp4_subnet_id, 338 OLD.host_id, OLD.shared_network_name, OLD.pool_id, 339 NOW()); 340 END $$ 341DELIMITER ; 342 343 344-- ----------------------------------------------------- 345-- 346-- New version of the createOptionAuditDHCP4 stored 347-- procedure which updates modification timestamp of 348-- a parent object when an option is modified. 349-- 350-- The following parameters are passed to the procedure: 351-- - modification_type: "create", "update" or "delete" 352-- - scope_id: identifier of the option scope, e.g. 353-- global, subnet specific etc. See dhcp_option_scope 354-- for specific values. 355-- - option_id: identifier of the option. 356-- - subnet_id: identifier of the subnet if the option 357-- belongs to the subnet. 358-- - host_id: identifier of the host if the option 359-- - belongs to the host. 360-- - network_name: shared network name if the option 361-- belongs to the shared network. 362-- - pool_id: identifier of the pool if the option 363-- belongs to the pool. 364-- - pd_pool_id: identifier of the pool if the option 365-- belongs to the pd pool. 366-- - modification_ts: modification timestamp of the 367-- option. 368-- ----------------------------------------------------- 369DROP PROCEDURE IF EXISTS createOptionAuditDHCP6; 370DELIMITER $$ 371CREATE PROCEDURE createOptionAuditDHCP6(IN modification_type VARCHAR(32), 372 IN scope_id TINYINT(3) UNSIGNED, 373 IN option_id BIGINT(20) UNSIGNED, 374 IN subnet_id INT(10) UNSIGNED, 375 IN host_id INT(10) UNSIGNED, 376 IN network_name VARCHAR(128), 377 IN pool_id BIGINT(20), 378 IN pd_pool_id BIGINT(20), 379 IN modification_ts TIMESTAMP) 380BEGIN 381 # These variables will hold shared network id and subnet id that 382 # we will select. 383 DECLARE snid VARCHAR(128); 384 DECLARE sid INT(10) UNSIGNED; 385 386 # Cascade transaction flag is set to 1 to prevent creation of 387 # the audit entries for the options when the options are 388 # created as part of the parent object creation or update. 389 # For example: when the option is added as part of the subnet 390 # addition, the cascade transaction flag is equal to 1. If 391 # the option is added into the existing subnet the cascade 392 # transaction is equal to 0. Note that depending on the option 393 # scope the audit entry will contain the object_type value 394 # of the parent object to cause the server to replace the 395 # entire subnet. The only case when the object_type will be 396 # set to 'dhcp6_options' is when a global option is added. 397 # Global options do not have the owner. 398 IF @cascade_transaction IS NULL OR @cascade_transaction = 0 THEN 399 # todo: host manager hasn't been updated to use audit 400 # mechanisms so ignore host specific options for now. 401 IF scope_id = 0 THEN 402 # If a global option is added or modified, create audit 403 # entry for the 'dhcp6_options' table. 404 CALL createAuditEntryDHCP6('dhcp6_options', option_id, modification_type); 405 ELSEIF scope_id = 1 THEN 406 # If subnet specific option is added or modified, update 407 # the modification timestamp of this subnet to allow the 408 # servers to refresh the subnet information. This will 409 # also result in creating an audit entry for this subnet. 410 UPDATE dhcp6_subnet AS s SET s.modification_ts = modification_ts 411 WHERE s.subnet_id = subnet_id; 412 ELSEIF scope_id = 4 THEN 413 # If shared network specific option is added or modified, 414 # update the modification timestamp of this shared network 415 # to allow the servers to refresh the shared network 416 # information. This will also result in creating an 417 # audit entry for this shared network. 418 SELECT id INTO snid FROM dhcp6_shared_network WHERE name = network_name LIMIT 1; 419 UPDATE dhcp6_shared_network AS n SET n.modification_ts = modification_ts 420 WHERE n.id = snid; 421 ELSEIF scope_id = 5 THEN 422 # If pool specific option is added or modified, update 423 # the modification timestamp of the owning subnet. 424 SELECT dhcp6_pool.subnet_id INTO sid FROM dhcp6_pool WHERE id = pool_id; 425 UPDATE dhcp6_subnet AS s SET s.modification_ts = modification_ts 426 WHERE s.subnet_id = sid; 427 ELSEIF scope_id = 6 THEN 428 # If pd pool specific option is added or modified, create 429 # audit entry for the subnet which this pool belongs to. 430 SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pd_pool_id; 431 UPDATE dhcp6_subnet AS s SET s.modification_ts = modification_ts 432 WHERE s.subnet_id = sid; 433 END IF; 434 END IF; 435END $$ 436DELIMITER ; 437 438# Recreate dhcp6_options_AINS trigger to pass timestamp to the updated 439# version of the createOptionAuditDHCP6. 440DROP TRIGGER IF EXISTS dhcp6_options_AINS; 441 442# This trigger is executed after inserting a DHCPv6 option into the 443# database. It creates appropriate audit entry for this option or 444# a parent object owning this option. 445DELIMITER $$ 446CREATE TRIGGER dhcp6_options_AINS AFTER INSERT ON dhcp6_options 447 FOR EACH ROW 448 BEGIN 449 CALL createOptionAuditDHCP6("create", NEW.scope_id, NEW.option_id, NEW.dhcp6_subnet_id, 450 NEW.host_id, NEW.shared_network_name, NEW.pool_id, 451 NEW.pd_pool_id, NEW.modification_ts); 452 END $$ 453DELIMITER ; 454 455# Recreate dhcp6_options_AUPD trigger to pass timestamp to the updated 456# version of the createOptionAuditDHCP6. 457DROP TRIGGER IF EXISTS dhcp6_options_AUPD; 458 459# This trigger is executed after updating a DHCPv6 option in the 460# database. It creates appropriate audit entry for this option or 461# a parent object owning this option. 462DELIMITER $$ 463CREATE TRIGGER dhcp6_options_AUPD AFTER UPDATE ON dhcp6_options 464 FOR EACH ROW 465 BEGIN 466 CALL createOptionAuditDHCP6("update", NEW.scope_id, NEW.option_id, NEW.dhcp6_subnet_id, 467 NEW.host_id, NEW.shared_network_name, NEW.pool_id, 468 NEW.pd_pool_id, NEW.modification_ts); 469 END $$ 470DELIMITER ; 471 472# Recreate dhcp6_options_ADEL trigger to pass timestamp to the updated 473# version of the createOptionAuditDHCP6. 474DROP TRIGGER IF EXISTS dhcp6_options_ADEL; 475 476# This trigger is executed after deleting a DHCPv6 option in the 477# database. It creates appropriate audit entry for this option or 478# a parent object owning this option. 479DELIMITER $$ 480CREATE TRIGGER dhcp6_options_ADEL AFTER DELETE ON dhcp6_options 481 FOR EACH ROW 482 BEGIN 483 CALL createOptionAuditDHCP6("delete", OLD.scope_id, OLD.option_id, OLD.dhcp6_subnet_id, 484 OLD.host_id, OLD.shared_network_name, OLD.pool_id, 485 OLD.pd_pool_id, NOW()); 486 END $$ 487DELIMITER ; 488 489# Update the schema version number 490UPDATE schema_version 491SET version = '8', minor = '2'; 492 493# This line concludes database upgrade to version 8.2. 494 495EOF 496 497# We need to rename the columns in the option def tables because "array" is 498# a MySQL keyword as of 8.0.17 499change_column "${schema}" dhcp4_option_def array "is_array TINYINT(1) NOT NULL" "${@}" 500change_column "${schema}" dhcp6_option_def array "is_array TINYINT(1) NOT NULL" "${@}" 501