security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql

-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

drop procedure if exists update_TagDefAccessTypes_for_atlas;

delimiter ;;
create procedure update_TagDefAccessTypes_for_atlas() begin
DECLARE new_atlas_def_name varchar(100);
if exists (select version from x_db_version_h where version = 'J10013') then
	if exists (select name from x_service_def where name like 'atlas.%') then
		set new_atlas_def_name=(select name from x_service_def where name like 'atlas.%');
		if exists(select * from x_access_type_def where def_id in(select id from x_service_def where name='tag') and name in('atlas:read','atlas:create','atlas:update','atlas:delete','atlas:all')) then
			update x_access_type_def set name=concat(new_atlas_def_name,':read') where def_id=100 and name='atlas:read';
			update x_access_type_def set name=concat(new_atlas_def_name,':create') where def_id=100 and name='atlas:create';
			update x_access_type_def set name=concat(new_atlas_def_name,':update') where def_id=100 and name='atlas:update';
			update x_access_type_def set name=concat(new_atlas_def_name,':delete') where def_id=100 and name='atlas:delete';
			update x_access_type_def set name=concat(new_atlas_def_name,':all') where def_id=100 and name='atlas:all';
		end if;
		if exists(select * from x_access_type_def_grants where atd_id in (select id from x_access_type_def where def_id in (select id from x_service_def where name='tag') and name like 'atlas%') and implied_grant in ('atlas:read','atlas:create','atlas:update','atlas:delete','atlas:all')) then
			update x_access_type_def_grants set implied_grant=concat(new_atlas_def_name,':read') where implied_grant='atlas:read';
			update x_access_type_def_grants set implied_grant=concat(new_atlas_def_name,':create') where implied_grant='atlas:create';
			update x_access_type_def_grants set implied_grant=concat(new_atlas_def_name,':update') where implied_grant='atlas:update';
			update x_access_type_def_grants set implied_grant=concat(new_atlas_def_name,':delete') where implied_grant='atlas:delete';
			update x_access_type_def_grants set implied_grant=concat(new_atlas_def_name,':all') where implied_grant='atlas:all';
		end if;
	end if;
end if;
end;;

delimiter ;
call update_TagDefAccessTypes_for_atlas();

drop procedure if exists update_TagDefAccessTypes_for_atlas;


drop procedure if exists alter_table_x_policy;

delimiter ;;
create procedure alter_table_x_policy() begin

if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy') then
  if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name = 'policy_text') then
    ALTER TABLE `x_policy` ADD `policy_text` MEDIUMTEXT DEFAULT NULL;
  end if;
 end if; 
end;;

delimiter ;
call alter_table_x_policy();

drop procedure if exists alter_table_x_policy;

DROP PROCEDURE IF EXISTS removeConstraints;
DELIMITER ;;
CREATE PROCEDURE removeConstraints(vTableName varchar(128))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE cName VARCHAR(64);
  DECLARE cur CURSOR FOR
          SELECT DISTINCT CONSTRAINT_NAME
          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
          WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = vTableName
          AND REFERENCED_TABLE_NAME IS NOT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET AUTOCOMMIT=0;
  SET FOREIGN_KEY_CHECKS=0;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO cName;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('ALTER TABLE ',vTableName,' DROP FOREIGN KEY ',cName,';');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  SET AUTOCOMMIT=1;
END ;;
DELIMITER ;

call removeConstraints('x_policy_item');
call removeConstraints('x_policy_item_access');
call removeConstraints('x_policy_item_condition');
call removeConstraints('x_policy_item_datamask');
call removeConstraints('x_policy_item_group_perm');
call removeConstraints('x_policy_item_user_perm');
call removeConstraints('x_policy_item_rowfilter');
call removeConstraints('x_policy_resource');
call removeConstraints('x_policy_resource_map');

DROP PROCEDURE removeConstraints;
DROP TABLE IF EXISTS `x_policy_ref_group`;
DROP TABLE IF EXISTS `x_policy_ref_user`;
DROP TABLE IF EXISTS `x_policy_ref_datamask_type`;
DROP TABLE IF EXISTS `x_policy_ref_condition`;
DROP TABLE IF EXISTS `x_policy_ref_access_type`;
DROP TABLE IF EXISTS `x_policy_ref_resource`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_resource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `resource_def_id` bigint(20) NOT NULL,
  `resource_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_res_UK_polId_resDefId`(`policy_id`, `resource_def_id`),
  CONSTRAINT `x_policy_ref_res_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_res_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_res_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_res_FK_resource_def_id` FOREIGN KEY (`resource_def_id`) REFERENCES `x_resource_def` (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `x_policy_ref_access_type` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `access_def_id` bigint(20) NOT NULL,
  `access_type_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_access_UK_polId_accessDefId`(`policy_id`, `access_def_id`),
  CONSTRAINT `x_policy_ref_access_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_access_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_access_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_access_FK_access_def_id` FOREIGN KEY (`access_def_id`) REFERENCES `x_access_type_def` (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `x_policy_ref_condition` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `condition_def_id` bigint(20) NOT NULL,
  `condition_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_condition_UK_polId_condDefId`(`policy_id`, `condition_def_id`),
  CONSTRAINT `x_policy_ref_condition_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_condition_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_condition_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_condition_FK_condition_def_id` FOREIGN KEY (`condition_def_id`) REFERENCES `x_policy_condition_def` (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `x_policy_ref_datamask_type` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `datamask_def_id` bigint(20) NOT NULL,
  `datamask_type_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_datamask_UK_polId_dmaskDefId`(`policy_id`, `datamask_def_id`),
  CONSTRAINT `x_policy_ref_datamask_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_datamask_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_datamask_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_datamask_FK_datamask_def_id` FOREIGN KEY (`datamask_def_id`) REFERENCES `x_datamask_type_def` (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `x_policy_ref_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_user_UK_polId_userId`(`policy_id`, `user_id`),
  CONSTRAINT `x_policy_ref_user_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_user_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_user_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_user_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_user` (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `x_policy_ref_group` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `guid` varchar(1024) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `added_by_id` bigint(20) DEFAULT NULL,
  `upd_by_id` bigint(20) DEFAULT NULL,
  `policy_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `group_name` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `x_policy_ref_group_UK_polId_groupId`(`policy_id`, `group_id`),
  CONSTRAINT `x_policy_ref_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_group_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
  CONSTRAINT `x_policy_ref_group_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
  CONSTRAINT `x_policy_ref_group_FK_group_id` FOREIGN KEY (`group_id`) REFERENCES `x_group` (`id`)
) ROW_FORMAT=DYNAMIC;