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 <monika.kachhad...@privacera.com> AuthorDate: Mon Oct 16 07:23:20 2023 -0700 RANGER-4394: database schema upgrade patches to support GDS Signed-off-by: Madhan Neethiraj <mad...@apache.org> --- .../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;