This is an automated email from the ASF dual-hosted git repository.
madhan pushed a commit to branch RANGER-3923
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/RANGER-3923 by this push:
new f8ae1838c RANGER-4394: database schema upgrade patches to support GDS
f8ae1838c is described below
commit f8ae1838c41e018bdb99e7127264a6e70cff3b3c
Author: Monika Kachhadiya <[email protected]>
AuthorDate: Mon Oct 16 07:23:20 2023 -0700
RANGER-4394: database schema upgrade patches to support GDS
Signed-off-by: Madhan Neethiraj <[email protected]>
---
.../optimized/current/ranger_core_db_mysql.sql | 4 +
.../db/mysql/patches/067-create-gds-tables.sql | 219 +++++++++++++++++++
.../db/mysql/patches/068-create-view-principal.sql | 18 ++
.../patches/069-add-gz_json_x_security_zone.sql | 31 +++
.../db/mysql/patches/070-add-gds-perm.sql | 57 +++++
.../optimized/current/ranger_core_db_postgres.sql | 4 +
.../db/postgres/patches/067-create-gds-tables.sql | 243 +++++++++++++++++++++
.../postgres/patches/068-create-view-principal.sql | 17 ++
.../patches/069-add-gz_json_x_security_zone.sql | 39 ++++
.../db/postgres/patches/070-add-gds-perm.sql | 52 +++++
10 files changed, 684 insertions(+)
diff --git a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
index 3cbca48ef..19ecc1fc8 100644
--- a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
+++ b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
@@ -2041,6 +2041,10 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('065',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('066',UTC_TIMESTAMP(),'Ranger 3.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('067',UTC_TIMESTAMP(),'Ranger 3.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('068',UTC_TIMESTAMP(),'Ranger 3.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('069',UTC_TIMESTAMP(),'Ranger 3.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('070',UTC_TIMESTAMP(),'Ranger 3.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('J10001',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
diff --git a/security-admin/db/mysql/patches/067-create-gds-tables.sql
b/security-admin/db/mysql/patches/067-create-gds-tables.sql
new file mode 100644
index 000000000..ed74da8ae
--- /dev/null
+++ b/security-admin/db/mysql/patches/067-create-gds-tables.sql
@@ -0,0 +1,219 @@
+-- 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 TABLE IF EXISTS `x_gds_dataset_policy_map`;
+DROP TABLE IF EXISTS `x_gds_project_policy_map`;
+DROP TABLE IF EXISTS `x_gds_dataset_in_project`;
+DROP TABLE IF EXISTS `x_gds_data_share_in_dataset`;
+DROP TABLE IF EXISTS `x_gds_shared_resource`;
+DROP TABLE IF EXISTS `x_gds_data_share`;
+DROP TABLE IF EXISTS `x_gds_dataset`;
+DROP TABLE IF EXISTS `x_gds_project`;
+
+
+CREATE TABLE `x_gds_dataset` (
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `name` VARCHAR(512) NOT NULL
+ , `description` TEXT NULL DEFAULT NULL
+ , `acl` TEXT NULL DEFAULT NULL
+ , `terms_of_use` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_dataset_UK_name`(`name`)
+ , CONSTRAINT `x_gds_dataset_FK_added_by_id` FOREIGN KEY(`added_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_dataset_FK_upd_by_id` FOREIGN KEY(`upd_by_id`)
REFERENCES `x_portal_user`(`id`)
+);
+CREATE INDEX `x_gds_dataset_guid` ON `x_gds_dataset`(`guid`);
+
+CREATE TABLE `x_gds_project` (
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `name` VARCHAR(512) NOT NULL
+ , `description` TEXT NULL DEFAULT NULL
+ , `acl` TEXT NULL DEFAULT NULL
+ , `terms_of_use` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_project_UK_name`(`name`)
+ , CONSTRAINT `x_gds_project_FK_added_by_id` FOREIGN KEY(`added_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_project_FK_upd_by_id` FOREIGN KEY(`upd_by_id`)
REFERENCES `x_portal_user`(`id`)
+);
+CREATE INDEX `x_gds_project_guid` ON `x_gds_project`(`guid`);
+
+CREATE TABLE `x_gds_data_share`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `name` VARCHAR(512) NOT NULL
+ , `description` TEXT NULL DEFAULT NULL
+ , `acl` TEXT NOT NULL
+ , `service_id` BIGINT(20) NOT NULL
+ , `zone_id` BIGINT(20) NOT NULL
+ , `condition_expr` TEXT NULL
+ , `default_access_types` TEXT NULL
+ , `default_tag_masks` TEXT NULL
+ , `terms_of_use` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_data_share_UK_name`(`service_id`, `zone_id`, `name`)
+ , CONSTRAINT `x_gds_data_share_FK_added_by_id` FOREIGN KEY(`added_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_data_share_FK_upd_by_id` FOREIGN KEY(`upd_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_data_share_FK_service_id` FOREIGN KEY(`service_id`)
REFERENCES `x_service`(`id`)
+ , CONSTRAINT `x_gds_data_share_FK_zone_id` FOREIGN KEY(`zone_id`)
REFERENCES `x_security_zone`(`id`)
+);
+CREATE INDEX `x_gds_data_share_guid` ON `x_gds_data_share`(`guid`);
+CREATE INDEX `x_gds_data_share_service_id` ON `x_gds_data_share`(`service_id`);
+CREATE INDEX `x_gds_data_share_zone_id` ON `x_gds_data_share`(`zone_id`);
+
+CREATE TABLE `x_gds_shared_resource`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `name` VARCHAR(512) NOT NULL
+ , `description` TEXT NULL DEFAULT NULL
+ , `data_share_id` BIGINT(20) NOT NULL
+ , `resource` TEXT NOT NULL
+ , `resource_signature` VARCHAR(128) NOT NULL
+ , `sub_resource` TEXT NULL DEFAULT NULL
+ , `sub_resource_type` TEXT NULL DEFAULT NULL
+ , `condition_expr` TEXT NULL DEFAULT NULL
+ , `access_types` TEXT NULL DEFAULT NULL
+ , `row_filter` TEXT NULL DEFAULT NULL
+ , `sub_resource_masks` TEXT NULL DEFAULT NULL
+ , `profiles` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_shared_resource_UK_name`(`data_share_id`, `name`)
+ , UNIQUE KEY `x_gds_shared_resource_UK_resource_signature`(`data_share_id`,
`resource_signature`)
+ , CONSTRAINT `x_gds_shared_resource_FK_added_by_id` FOREIGN
KEY(`added_by_id`) REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_shared_resource_FK_upd_by_id` FOREIGN
KEY(`upd_by_id`) REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_shared_resource_FK_data_share_id` FOREIGN
KEY(`data_share_id`) REFERENCES `x_gds_data_share`(`id`)
+);
+CREATE INDEX `x_gds_shared_resource_guid` ON
`x_gds_shared_resource`(`guid`);
+CREATE INDEX `x_gds_shared_resource_data_share_id` ON
`x_gds_shared_resource`(`data_share_id`);
+
+CREATE TABLE `x_gds_data_share_in_dataset`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `description` TEXT NULL DEFAULT NULL
+ , `data_share_id` BIGINT(20) NOT NULL
+ , `dataset_id` BIGINT(20) NOT NULL
+ , `status` SMALLINT NOT NULL
+ , `validity_period` TEXT NULL DEFAULT NULL
+ , `profiles` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , `approver_id` BIGINT(20) NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_dshid_UK_data_share_id_dataset_id` (`data_share_id`,
`dataset_id`)
+ , CONSTRAINT `x_gds_dshid_FK_added_by_id` FOREIGN KEY(`added_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_dshid_FK_upd_by_id` FOREIGN KEY(`upd_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_dshid_FK_data_share_id` FOREIGN KEY(`data_share_id`)
REFERENCES `x_gds_data_share`(`id`)
+ , CONSTRAINT `x_gds_dshid_FK_dataset_id` FOREIGN KEY(`dataset_id`)
REFERENCES `x_gds_dataset`(`id`)
+ , CONSTRAINT `x_gds_dshid_FK_approver_id` FOREIGN KEY(`approver_id`)
REFERENCES `x_portal_user`(`id`)
+);
+CREATE INDEX `x_gds_dshid_guid` ON
`x_gds_data_share_in_dataset`(`guid`);
+CREATE INDEX `x_gds_dshid_data_share_id` ON
`x_gds_data_share_in_dataset`(`data_share_id`);
+CREATE INDEX `x_gds_dshid_dataset_id` ON
`x_gds_data_share_in_dataset`(`dataset_id`);
+CREATE INDEX `x_gds_dshid_data_share_id_dataset_id` ON
`x_gds_data_share_in_dataset`(`data_share_id`, `dataset_id`);
+
+CREATE TABLE `x_gds_dataset_in_project`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `guid` VARCHAR(64) NOT NULL
+ , `create_time` TIMESTAMP NULL DEFAULT NULL
+ , `update_time` TIMESTAMP NULL DEFAULT NULL
+ , `added_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `upd_by_id` BIGINT(20) NULL DEFAULT NULL
+ , `version` BIGINT(20) NOT NULL DEFAULT 1
+ , `is_enabled` TINYINT(1) NOT NULL DEFAULT '1'
+ , `description` TEXT NULL DEFAULT NULL
+ , `dataset_id` BIGINT(20) NOT NULL
+ , `project_id` BIGINT(20) NOT NULL
+ , `status` SMALLINT NOT NULL
+ , `validity_period` TEXT NULL DEFAULT NULL
+ , `profiles` TEXT NULL DEFAULT NULL
+ , `options` TEXT NULL DEFAULT NULL
+ , `additional_info` TEXT NULL DEFAULT NULL
+ , `approver_id` BIGINT(20) NULL DEFAULT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_dip_UK_data_share_id_dataset_id`(`dataset_id`,
`project_id`)
+ , CONSTRAINT `x_gds_dip_FK_added_by_id` FOREIGN KEY(`added_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_dip_FK_upd_by_id` FOREIGN KEY(`upd_by_id`)
REFERENCES `x_portal_user`(`id`)
+ , CONSTRAINT `x_gds_dip_FK_dataset_id` FOREIGN KEY(`dataset_id`)
REFERENCES `x_gds_dataset`(`id`)
+ , CONSTRAINT `x_gds_dip_FK_project_id` FOREIGN KEY(`project_id`)
REFERENCES `x_gds_project`(`id`)
+ , CONSTRAINT `x_gds_dip_FK_approver_id` FOREIGN KEY(`approver_id`)
REFERENCES `x_portal_user`(`id`)
+);
+CREATE INDEX `x_gds_dip_guid` ON `x_gds_dataset_in_project`(`guid`);
+CREATE INDEX `x_gds_dip_dataset_id` ON
`x_gds_dataset_in_project`(`dataset_id`);
+CREATE INDEX `x_gds_dip_project_id` ON
`x_gds_dataset_in_project`(`project_id`);
+
+CREATE TABLE `x_gds_dataset_policy_map`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `dataset_id` BIGINT(20) NOT NULL
+ , `policy_id` BIGINT(20) NOT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_dpm_UK_dataset_id_policy_id`(`dataset_id`, `policy_id`)
+ , CONSTRAINT `x_gds_dpm_FK_dataset_id` FOREIGN KEY(`dataset_id`) REFERENCES
`x_gds_dataset`(`id`)
+ , CONSTRAINT `x_gds_dpm_FK_policy_id` FOREIGN KEY(`policy_id`) REFERENCES
`x_policy`(`id`)
+);
+CREATE INDEX `x_gds_dpm_dataset_id` ON
`x_gds_dataset_policy_map`(`dataset_id`);
+CREATE INDEX `x_gds_dpm_policy_id` ON `x_gds_dataset_policy_map`(`policy_id`);
+
+CREATE TABLE `x_gds_project_policy_map`(
+ `id` BIGINT(20) NOT NULL AUTO_INCREMENT
+ , `project_id` BIGINT(20) NOT NULL
+ , `policy_id` BIGINT(20) NOT NULL
+ , PRIMARY KEY(`id`)
+ , UNIQUE KEY `x_gds_ppm_UK_project_id_policy_id`(`project_id`, `policy_id`)
+ , CONSTRAINT `x_gds_ppm_FK_project_id` FOREIGN KEY(`project_id`) REFERENCES
`x_gds_project`(`id`)
+ , CONSTRAINT `x_gds_ppm_FK_policy_id` FOREIGN KEY(`policy_id`) REFERENCES
`x_policy`(`id`)
+);
+CREATE INDEX `x_gds_ppm_project_id` ON
`x_gds_project_policy_map`(`project_id`);
+CREATE INDEX `x_gds_ppm_policy_id` ON `x_gds_project_policy_map`(`policy_id`);
+
diff --git a/security-admin/db/mysql/patches/068-create-view-principal.sql
b/security-admin/db/mysql/patches/068-create-view-principal.sql
new file mode 100644
index 000000000..687c9ffc3
--- /dev/null
+++ b/security-admin/db/mysql/patches/068-create-view-principal.sql
@@ -0,0 +1,18 @@
+-- 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 VIEW IF EXISTS `vx_principal`;
+
+CREATE VIEW vx_principal as (SELECT u.user_name AS principal_name, 0 AS
principal_type, u.status status, u.is_visible is_visible, u.other_attributes
other_attributes, u.create_time create_time, u.update_time update_time,
u.added_by_id added_by_id, u.upd_by_id upd_by_id FROM x_user u) UNION (SELECT
g.group_name principal_name, 1 AS principal_type, g.status status, g.is_visible
is_visible, g.other_attributes other_attributes, g.create_time create_time,
g.update_time update_time, g.added_by [...]
\ No newline at end of file
diff --git
a/security-admin/db/mysql/patches/069-add-gz_json_x_security_zone.sql
b/security-admin/db/mysql/patches/069-add-gz_json_x_security_zone.sql
new file mode 100644
index 000000000..52ae48eca
--- /dev/null
+++ b/security-admin/db/mysql/patches/069-add-gz_json_x_security_zone.sql
@@ -0,0 +1,31 @@
+-- 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 add_gz_jsonData_x_security_zone;
+
+delimiter ;;
+create procedure add_gz_jsonData_x_security_zone() begin
+
+if not exists (select * from information_schema.columns where
table_schema=database() and table_name = 'x_security_zone' and
column_name='gz_jsonData') then
+ ALTER TABLE x_security_zone ADD gz_jsonData LONGBLOB NULL DEFAULT NULL;
+end if;
+
+end;;
+
+delimiter ;
+
+call add_gz_jsonData_x_security_zone();
+
+drop procedure if exists add_gz_jsonData_x_security_zone;
\ No newline at end of file
diff --git a/security-admin/db/mysql/patches/070-add-gds-perm.sql
b/security-admin/db/mysql/patches/070-add-gds-perm.sql
new file mode 100644
index 000000000..2d1894cae
--- /dev/null
+++ b/security-admin/db/mysql/patches/070-add-gds-perm.sql
@@ -0,0 +1,57 @@
+-- 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.
+
+
+DELIMITER $$
+DROP PROCEDURE if exists getXportalUIdByLoginId$$
+CREATE PROCEDURE `getXportalUIdByLoginId`(IN input_val VARCHAR(100), OUT myid
BIGINT)
+BEGIN
+SET myid = 0;
+SELECT x_portal_user.id into myid FROM x_portal_user WHERE
x_portal_user.login_id = input_val;
+END $$
+
+DELIMITER ;
+
+DELIMITER $$
+DROP PROCEDURE if exists getModulesIdByName$$
+CREATE PROCEDURE `getModulesIdByName`(IN input_val VARCHAR(100), OUT myid
BIGINT)
+BEGIN
+SET myid = 0;
+SELECT x_modules_master.id into myid FROM x_modules_master WHERE
x_modules_master.module = input_val;
+END $$
+
+DELIMITER ;
+
+
+DELIMITER $$
+DROP PROCEDURE if exists insertRangerPrerequisiteGDSEntries $$
+CREATE PROCEDURE `insertRangerPrerequisiteGDSEntries`()
+BEGIN
+DECLARE adminID bigint;
+DECLARE moduleIdGovernedDataSharing bigint;
+
+call getXportalUIdByLoginId('admin', adminID);
+
+if not exists (select * from x_modules_master where module='Governed Data
Sharing') then
+ INSERT INTO `x_modules_master`
(`create_time`,`update_time`,`added_by_id`,`upd_by_id`,`module`,`url`) VALUES
(UTC_TIMESTAMP(),UTC_TIMESTAMP(),adminID,adminID,'Governed Data Sharing','');
+end if;
+call getModulesIdByName('Governed Data Sharing', moduleIdGovernedDataSharing);
+if not exists (select * from x_user_module_perm where user_id=adminID and
module_id=moduleIdGovernedDataSharing) then
+ INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(adminID,moduleIdGovernedDataSharing,UTC_TIMESTAMP(),UTC_TIMESTAMP(),adminID,adminID,1);
+end if;
+
+END $$
+DELIMITER ;
+call insertRangerPrerequisiteGDSEntries();
diff --git
a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
index 4cdf7e27d..ea98736a1 100644
--- a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
+++ b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
@@ -2144,6 +2144,10 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('060',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('065',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('066',current_timestamp,'Ranger 3.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('067',current_timestamp,'Ranger 3.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('068',current_timestamp,'Ranger 3.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('069',current_timestamp,'Ranger 3.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('070',current_timestamp,'Ranger 3.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',current_timestamp,'Ranger
1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
diff --git a/security-admin/db/postgres/patches/067-create-gds-tables.sql
b/security-admin/db/postgres/patches/067-create-gds-tables.sql
new file mode 100644
index 000000000..688173527
--- /dev/null
+++ b/security-admin/db/postgres/patches/067-create-gds-tables.sql
@@ -0,0 +1,243 @@
+-- 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 TABLE IF EXISTS x_gds_dataset_policy_map;
+DROP TABLE IF EXISTS x_gds_project_policy_map;
+DROP TABLE IF EXISTS x_gds_dataset CASCADE;
+DROP TABLE IF EXISTS x_gds_project CASCADE;
+DROP TABLE IF EXISTS x_gds_data_share CASCADE;
+DROP TABLE IF EXISTS x_gds_shared_resource CASCADE;
+DROP TABLE IF EXISTS x_gds_data_share_in_dataset CASCADE;
+DROP TABLE IF EXISTS x_gds_dataset_in_project CASCADE;
+
+DROP SEQUENCE IF EXISTS x_gds_project_policy_map_seq;
+DROP SEQUENCE IF EXISTS x_gds_dataset_policy_map_seq;
+DROP SEQUENCE IF EXISTS X_GDS_DATASET_SEQ;
+DROP SEQUENCE IF EXISTS X_GDS_PROJECT_SEQ;
+DROP SEQUENCE IF EXISTS X_GDS_DATA_SHARE_SEQ;
+DROP SEQUENCE IF EXISTS X_GDS_SHARED_RESOURCE_SEQ;
+DROP SEQUENCE IF EXISTS X_GDS_DATA_SHARE_IN_DATASET_SEQ;
+DROP SEQUENCE IF EXISTS X_GDS_DATASET_IN_PROJECT_SEQ;
+
+
+CREATE SEQUENCE X_GDS_DATASET_SEQ;
+CREATE TABLE x_gds_dataset (
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_DATASET_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , name VARCHAR(512) NOT NULL
+ , description TEXT NULL DEFAULT NULL
+ , acl TEXT NULL DEFAULT NULL
+ , terms_of_use TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_dataset_UK_name UNIQUE(name)
+ , CONSTRAINT x_gds_dataset_FK_added_by_id FOREIGN KEY(added_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_dataset_FK_upd_by_id FOREIGN KEY(upd_by_id)
REFERENCES x_portal_user(id)
+);
+CREATE INDEX x_gds_dataset_guid ON x_gds_dataset(guid);
+commit;
+
+CREATE SEQUENCE X_GDS_PROJECT_SEQ;
+CREATE TABLE x_gds_project (
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_PROJECT_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , name VARCHAR(512) NOT NULL
+ , description TEXT NULL DEFAULT NULL
+ , acl TEXT NULL DEFAULT NULL
+ , terms_of_use TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_project_UK_name UNIQUE(name)
+ , CONSTRAINT x_gds_project_FK_added_by_id FOREIGN KEY(added_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_project_FK_upd_by_id FOREIGN KEY(upd_by_id)
REFERENCES x_portal_user(id)
+);
+CREATE INDEX x_gds_project_guid ON x_gds_project(guid);
+commit;
+
+CREATE SEQUENCE X_GDS_DATA_SHARE_SEQ;
+CREATE TABLE x_gds_data_share(
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_DATA_SHARE_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , name VARCHAR(512) NOT NULL
+ , description TEXT NULL DEFAULT NULL
+ , acl TEXT NOT NULL
+ , service_id BIGINT NOT NULL
+ , zone_id BIGINT NOT NULL
+ , condition_expr TEXT NULL
+ , default_access_types TEXT NULL
+ , default_tag_masks TEXT NULL
+ , terms_of_use TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_data_share_UK_name UNIQUE(service_id, zone_id, name)
+ , CONSTRAINT x_gds_data_share_FK_added_by_id FOREIGN KEY(added_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_data_share_FK_upd_by_id FOREIGN KEY(upd_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_data_share_FK_service_id FOREIGN KEY(service_id)
REFERENCES x_service(id)
+ , CONSTRAINT x_gds_data_share_FK_zone_id FOREIGN KEY(zone_id)
REFERENCES x_security_zone(id)
+);
+CREATE INDEX x_gds_data_share_guid ON x_gds_data_share(guid);
+CREATE INDEX x_gds_data_share_service_id ON x_gds_data_share(service_id);
+CREATE INDEX x_gds_data_share_zone_id ON x_gds_data_share(zone_id);
+commit;
+
+CREATE SEQUENCE X_GDS_SHARED_RESOURCE_SEQ;
+CREATE TABLE x_gds_shared_resource(
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_SHARED_RESOURCE_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , name VARCHAR(512) NOT NULL
+ , description TEXT NULL DEFAULT NULL
+ , data_share_id BIGINT NOT NULL
+ , resource TEXT NOT NULL
+ , resource_signature VARCHAR(128) NOT NULL
+ , sub_resource TEXT NULL DEFAULT NULL
+ , sub_resource_type TEXT NULL DEFAULT NULL
+ , condition_expr TEXT NULL DEFAULT NULL
+ , access_types TEXT NULL DEFAULT NULL
+ , row_filter TEXT NULL DEFAULT NULL
+ , sub_resource_masks TEXT NULL DEFAULT NULL
+ , profiles TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_shared_resource_UK_name UNIQUE(data_share_id, name)
+ , CONSTRAINT x_gds_shared_resource_UK_resource_signature
UNIQUE(data_share_id, resource_signature)
+ , CONSTRAINT x_gds_shared_resource_FK_added_by_id FOREIGN KEY(added_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_shared_resource_FK_upd_by_id FOREIGN KEY(upd_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_shared_resource_FK_data_share_id FOREIGN
KEY(data_share_id) REFERENCES x_gds_data_share(id)
+);
+CREATE INDEX x_gds_shared_resource_guid ON
x_gds_shared_resource(guid);
+CREATE INDEX x_gds_shared_resource_data_share_id ON
x_gds_shared_resource(data_share_id);
+commit;
+
+CREATE SEQUENCE X_GDS_DATA_SHARE_IN_DATASET_SEQ;
+CREATE TABLE x_gds_data_share_in_dataset(
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_SHARED_RESOURCE_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , description TEXT NULL DEFAULT NULL
+ , data_share_id BIGINT NOT NULL
+ , dataset_id BIGINT NOT NULL
+ , status SMALLINT NOT NULL
+ , validity_period TEXT NULL DEFAULT NULL
+ , profiles TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , approver_id BIGINT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_dshid_FK_added_by_id FOREIGN KEY(added_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_dshid_FK_upd_by_id FOREIGN KEY(upd_by_id)
REFERENCES x_portal_user(id)
+ , CONSTRAINT x_gds_dshid_FK_data_share_id FOREIGN KEY(data_share_id)
REFERENCES x_gds_data_share(id)
+ , CONSTRAINT x_gds_dshid_FK_dataset_id FOREIGN KEY(dataset_id)
REFERENCES x_gds_dataset(id)
+ , CONSTRAINT x_gds_dshid_UK_data_share_id_dataset_id UNIQUE(data_share_id,
dataset_id)
+ , CONSTRAINT x_gds_dshid_FK_approver_id FOREIGN KEY(approver_id)
REFERENCES x_portal_user(id)
+);
+CREATE INDEX x_gds_dshid_guid ON
x_gds_data_share_in_dataset(guid);
+CREATE INDEX x_gds_dshid_data_share_id ON
x_gds_data_share_in_dataset(data_share_id);
+CREATE INDEX x_gds_dshid_dataset_id ON
x_gds_data_share_in_dataset(dataset_id);
+CREATE INDEX x_gds_dshid_data_share_id_dataset_id ON
x_gds_data_share_in_dataset(data_share_id, dataset_id);
+commit;
+
+CREATE SEQUENCE X_GDS_DATASET_IN_PROJECT_SEQ;
+CREATE TABLE x_gds_dataset_in_project(
+ id BIGINT NOT NULL DEFAULT
nextval('X_GDS_DATASET_IN_PROJECT_SEQ'::regclass)
+ , guid VARCHAR(64) NOT NULL
+ , create_time TIMESTAMP NULL DEFAULT NULL
+ , update_time TIMESTAMP NULL DEFAULT NULL
+ , added_by_id BIGINT NULL DEFAULT NULL
+ , upd_by_id BIGINT NULL DEFAULT NULL
+ , version BIGINT NOT NULL DEFAULT 1
+ , is_enabled BOOLEAN NOT NULL DEFAULT '1'
+ , description TEXT NULL DEFAULT NULL
+ , dataset_id BIGINT NOT NULL
+ , project_id BIGINT NOT NULL
+ , status SMALLINT NOT NULL
+ , validity_period TEXT NULL DEFAULT NULL
+ , profiles TEXT NULL DEFAULT NULL
+ , options TEXT NULL DEFAULT NULL
+ , additional_info TEXT NULL DEFAULT NULL
+ , approver_id BIGINT NULL DEFAULT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_dip_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES
x_portal_user(id)
+ , CONSTRAINT x_gds_dip_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES
x_portal_user(id)
+ , CONSTRAINT x_gds_dip_FK_dataset_id FOREIGN KEY(dataset_id) REFERENCES
x_gds_dataset(id)
+ , CONSTRAINT x_gds_dip_FK_project_id FOREIGN KEY(project_id) REFERENCES
x_gds_project(id)
+ , CONSTRAINT x_gds_dip_UK_data_share_id_dataset_id UNIQUE(dataset_id,
project_id)
+ , CONSTRAINT x_gds_dip_FK_approver_id FOREIGN KEY(approver_id) REFERENCES
x_portal_user(id)
+);
+CREATE INDEX x_gds_dip_guid ON x_gds_dataset_in_project(guid);
+CREATE INDEX x_gds_dip_dataset_id ON x_gds_dataset_in_project(dataset_id);
+CREATE INDEX x_gds_dip_project_id ON x_gds_dataset_in_project(project_id);
+commit;
+
+CREATE SEQUENCE x_gds_dataset_policy_map_seq;
+CREATE TABLE x_gds_dataset_policy_map(
+ id BIGINT NOT NULL DEFAULT
nextval('x_gds_dataset_policy_map_seq'::regclass)
+ , dataset_id BIGINT NOT NULL
+ , policy_id BIGINT NOT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_dpm_FK_dataset_id FOREIGN KEY(dataset_id) REFERENCES
x_gds_dataset(id)
+ , CONSTRAINT x_gds_dpm_FK_policy_id FOREIGN KEY(policy_id) REFERENCES
x_policy(id)
+ , CONSTRAINT x_gds_dpm_UK_dataset_id_policy_id UNIQUE(dataset_id, policy_id)
+);
+CREATE INDEX x_gds_dpm_dataset_id ON x_gds_dataset_policy_map(dataset_id);
+CREATE INDEX x_gds_dpm_policy_id ON x_gds_dataset_policy_map(policy_id);
+commit;
+
+CREATE SEQUENCE x_gds_project_policy_map_seq;
+CREATE TABLE x_gds_project_policy_map(
+ id BIGINT NOT NULL DEFAULT
nextval('x_gds_project_policy_map_seq'::regclass)
+ , project_id BIGINT NOT NULL
+ , policy_id BIGINT NOT NULL
+ , PRIMARY KEY(id)
+ , CONSTRAINT x_gds_ppm_FK_project_id FOREIGN KEY(project_id) REFERENCES
x_gds_project(id)
+ , CONSTRAINT x_gds_ppm_FK_policy_id FOREIGN KEY(policy_id) REFERENCES
x_policy(id)
+ , CONSTRAINT x_gds_ppm_UK_project_id_policy_id UNIQUE(project_id, policy_id)
+);
+CREATE INDEX x_gds_ppm_project_id ON x_gds_project_policy_map(project_id);
+CREATE INDEX x_gds_ppm_policy_id ON x_gds_project_policy_map(policy_id);
+commit;
diff --git a/security-admin/db/postgres/patches/068-create-view-principal.sql
b/security-admin/db/postgres/patches/068-create-view-principal.sql
new file mode 100644
index 000000000..801de1096
--- /dev/null
+++ b/security-admin/db/postgres/patches/068-create-view-principal.sql
@@ -0,0 +1,17 @@
+-- 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 VIEW IF EXISTS vx_principal;
+CREATE VIEW vx_principal as (SELECT u.user_name AS principal_name, 0 AS
principal_type, u.status status, u.is_visible is_visible, u.other_attributes
other_attributes, u.create_time create_time, u.update_time update_time,
u.added_by_id added_by_id, u.upd_by_id upd_by_id FROM x_user u) UNION (SELECT
g.group_name principal_name, 1 AS principal_type, g.status status, g.is_visible
is_visible, g.other_attributes other_attributes, g.create_time create_time,
g.update_time update_time, g.added_by [...]
\ No newline at end of file
diff --git
a/security-admin/db/postgres/patches/069-add-gz_json_x_security_zone.sql
b/security-admin/db/postgres/patches/069-add-gz_json_x_security_zone.sql
new file mode 100644
index 000000000..b78c41549
--- /dev/null
+++ b/security-admin/db/postgres/patches/069-add-gz_json_x_security_zone.sql
@@ -0,0 +1,39 @@
+-- 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.
+
+
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION add_gz_jsonData_x_security_zone()
+RETURNS void AS $$
+DECLARE
+ v_column_exists integer := 0;
+BEGIN
+ select count(*) into v_column_exists from pg_attribute where attrelid
in(select oid from pg_class where relname='x_security_zone') and
attname='gz_jsonData';
+ IF v_column_exists = 0 THEN
+ ALTER TABLE x_security_zone ADD COLUMN gz_jsonData BYTEA NULL DEFAULT NULL;
+ END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select add_gz_jsonData_x_security_zone();
+select 'delimiter end';
+commit;
+
+
+
+
diff --git a/security-admin/db/postgres/patches/070-add-gds-perm.sql
b/security-admin/db/postgres/patches/070-add-gds-perm.sql
new file mode 100644
index 000000000..15f0243b0
--- /dev/null
+++ b/security-admin/db/postgres/patches/070-add-gds-perm.sql
@@ -0,0 +1,52 @@
+-- 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.
+
+
+
+CREATE OR REPLACE FUNCTION getXportalUIdByLoginId(input_val varchar(100))
+RETURNS bigint LANGUAGE SQL AS $$ SELECT x_portal_user.id FROM x_portal_user
+WHERE x_portal_user.login_id = $1; $$;
+
+CREATE OR REPLACE FUNCTION getModulesIdByName(input_val varchar(100))
+RETURNS bigint LANGUAGE SQL AS $$ SELECT x_modules_master.id FROM
x_modules_master
+WHERE x_modules_master.module = $1; $$;
+
+
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION add_gds_permissions()
+RETURNS void AS $$
+DECLARE
+ v_column_exists integer := 0;
+BEGIN
+ select count(*) into v_column_exists from x_modules_master where
module='Governed Data Sharing';
+ IF v_column_exists = 0 THEN
+ INSERT INTO
x_modules_master(create_time,update_time,added_by_id,upd_by_id,module,url)
VALUES(current_timestamp,current_timestamp,getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),'Governed
Data Sharing','');
+ END IF;
+
+ v_column_exists:=0;
+ select count(*) into v_column_exists from x_user_module_perm where
user_id=getXportalUIdByLoginId('admin') and
module_id=getModulesIdByName('Governed Data Sharing');
+ IF v_column_exists = 0 THEN
+ INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (getXportalUIdByLoginId('admin'),getModulesIdByName('Governed Data
Sharing'),current_timestamp,current_timestamp,getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
+ END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select add_gds_permissions();
+select 'delimiter end';
+
+commit;
