This is an automated email from the ASF dual-hosted git repository. pradeep pushed a commit to branch ranger-2.4 in repository https://gitbox.apache.org/repos/asf/ranger.git
commit 02e976602f9b827d888213f3077c980acc17765c Author: Pradeep AgrawaL <[email protected]> AuthorDate: Tue Feb 28 12:45:41 2023 +0530 RANGER-4109: Add unique constraint on resource_signature column of x_rms_service_resource table --- .../optimized/current/ranger_core_db_mysql.sql | 3 +- ...n-x_rms_service_resource-resource_signature.sql | 48 ++++++++++++++++++ .../optimized/current/ranger_core_db_oracle.sql | 3 +- ...n-x_rms_service_resource-resource_signature.sql | 50 +++++++++++++++++++ .../optimized/current/ranger_core_db_postgres.sql | 3 +- ...n-x_rms_service_resource-resource_signature.sql | 58 ++++++++++++++++++++++ .../current/ranger_core_db_sqlanywhere.sql | 4 +- ...n-x_rms_service_resource-resource_signature.sql | 27 ++++++++++ .../optimized/current/ranger_core_db_sqlserver.sql | 12 ++--- ...n-x_rms_service_resource-resource_signature.sql | 55 ++++++++++++++++++++ 10 files changed, 252 insertions(+), 11 deletions(-) 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 f36f7c02b..8d9db7849 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 @@ -1625,10 +1625,10 @@ CREATE TABLE `x_rms_service_resource` ( `service_resource_elements_text` text, PRIMARY KEY (`id`), UNIQUE KEY `x_rms_service_res_UK_guid` (`guid`), + UNIQUE KEY `x_rms_service_resource_UK_resource_signature` (`resource_signature`), CONSTRAINT `x_rms_service_res_FK_service_id` FOREIGN KEY (`service_id`) REFERENCES `x_service` (`id`) ); CREATE INDEX x_rms_service_resource_IDX_service_id ON x_rms_service_resource(service_id); -CREATE INDEX x_rms_service_resource_IDX_resource_signature ON x_rms_service_resource(resource_signature); CREATE TABLE `x_rms_notification` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, @@ -1813,6 +1813,7 @@ 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 ('058',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 ('059',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 ('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 ('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/065-add-uk-on-x_rms_service_resource-resource_signature.sql b/security-admin/db/mysql/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql new file mode 100644 index 000000000..303de0b86 --- /dev/null +++ b/security-admin/db/mysql/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql @@ -0,0 +1,48 @@ +-- 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 truncate_rms_tables; +delimiter ;; +create procedure truncate_rms_tables() begin + SET FOREIGN_KEY_CHECKS = 0; + truncate table x_rms_mapping_provider; + truncate table x_rms_resource_mapping; + truncate table x_rms_notification; + truncate table x_rms_service_resource; + SET FOREIGN_KEY_CHECKS = 1; +end;; + +delimiter ; +call truncate_rms_tables(); + +commit; + +drop procedure if exists create_index_for_x_rms_service_resource; +drop procedure if exists create_uniqueindex_for_x_rms_service_resource; + +delimiter $$ +create procedure create_uniqueindex_for_x_rms_service_resource() begin + if exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_rms_service_resource' AND index_name='x_rms_service_resource_IDX_resource_signature') then + DROP INDEX x_rms_service_resource_IDX_resource_signature on x_rms_service_resource; + commit; + end if; + if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_rms_service_resource' AND index_name='x_rms_service_resource_UK_resource_signature') then + ALTER TABLE x_rms_service_resource ADD UNIQUE INDEX x_rms_service_resource_UK_resource_signature(resource_signature); + commit; + end if; +end $$ + +delimiter ; +call create_uniqueindex_for_x_rms_service_resource(); diff --git a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql index d85a260da..8c794ecf4 100644 --- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql +++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql @@ -1841,11 +1841,11 @@ is_enabled NUMBER(1) DEFAULT '1' NOT NULL, service_resource_elements_text CLOB DEFAULT NULL NULL, primary key (id), CONSTRAINT x_rms_service_res_UK_guid UNIQUE (guid), +CONSTRAINT x_rms_svc_res_UK_res_sign UNIQUE (resource_signature), CONSTRAINT x_rms_svc_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id) ); CREATE INDEX x_rms_svc_res_IDX_service_id ON x_rms_service_resource(service_id); -CREATE INDEX x_rms_svc_res_IDX_res_sign ON x_rms_service_resource(resource_signature); CREATE TABLE x_rms_notification ( id NUMBER(20) NOT NULL, @@ -1973,6 +1973,7 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '058',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '059',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '060',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '065',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,getXportalUIdByLoginId('admin'),getModulesIdByName('Reports'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1); diff --git a/security-admin/db/oracle/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql b/security-admin/db/oracle/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql new file mode 100644 index 000000000..f91dfef06 --- /dev/null +++ b/security-admin/db/oracle/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql @@ -0,0 +1,50 @@ +-- 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. +-- sync_source_info CLOB NOT NULL, + +DECLARE + v_index_exists number:=0; + v_count number:=0; + +BEGIN + execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping DISABLE CONSTRAINT x_rms_res_map_FK_hl_res_id'; + execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping DISABLE CONSTRAINT x_rms_res_map_FK_ll_res_id'; + execute IMMEDIATE 'truncate table x_rms_mapping_provider'; + execute IMMEDIATE 'truncate table x_rms_resource_mapping'; + execute IMMEDIATE 'truncate table x_rms_notification'; + execute IMMEDIATE 'truncate table x_rms_service_resource'; + execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping ENABLE CONSTRAINT x_rms_res_map_FK_hl_res_id'; + execute IMMEDIATE 'ALTER TABLE x_rms_resource_mapping ENABLE CONSTRAINT x_rms_res_map_FK_ll_res_id'; + commit; + SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_rms_svc_res_IDX_res_sgn') AND TABLE_NAME= upper('x_rms_service_resource'); + IF (v_index_exists > 0) THEN + EXECUTE IMMEDIATE 'DROP INDEX x_rms_svc_res_IDX_res_sgn ON x_rms_service_resource(resource_signature)'; + commit; + END IF; + + select count(*) into v_count from user_tab_cols where table_name=upper('x_rms_service_resource') and column_name IN('RESOURCE_SIGNATURE'); + if (v_count = 1) then + v_count:=0; + select count(*) into v_count from user_constraints where table_name=upper('x_rms_service_resource') and constraint_name=upper('x_rms_svc_res_UK_res_sgn') and constraint_type='U'; + if (v_count = 0) then + v_count:=0; + select count(*) into v_count from user_ind_columns WHERE table_name=upper('x_rms_service_resource') and column_name IN('RESOURCE_SIGNATURE') and index_name=upper('x_rms_svc_res_UK_res_sgn'); + if (v_count = 0) THEN + EXECUTE IMMEDIATE 'ALTER TABLE x_rms_service_resource ADD CONSTRAINT x_rms_svc_res_UK_res_sgn UNIQUE (RESOURCE_SIGNATURE)'; + commit; + end if; + end if; + end if; +END;/ 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 d7e2fd5e4..2b3072dbf 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 @@ -1604,6 +1604,7 @@ is_enabled BOOLEAN DEFAULT '1' NOT NULL, service_resource_elements_text TEXT DEFAULT NULL NULL, primary key (id), CONSTRAINT x_rms_service_res_UK_guid UNIQUE (guid), +CONSTRAINT x_rms_service_resource_UK_resource_signature UNIQUE (resource_signature), CONSTRAINT x_rms_service_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id) ); commit; @@ -1809,7 +1810,6 @@ CREATE INDEX x_ugsync_audit_info_uname ON x_ugsync_audit_info(user_name); CREATE INDEX x_data_hist_idx_objid_objclstype ON x_data_hist(obj_id,obj_class_type); CREATE INDEX x_rms_service_resource_IDX_service_id ON x_rms_service_resource(service_id); -CREATE INDEX x_rms_service_resource_IDX_resource_signature ON x_rms_service_resource(resource_signature); CREATE INDEX x_rms_notification_IDX_notification_id ON x_rms_notification(notification_id); CREATE INDEX x_rms_notification_IDX_hms_name_notification_id ON x_rms_notification(hms_name, notification_id); CREATE INDEX x_rms_notification_IDX_hl_service_id ON x_rms_notification(hl_service_id); @@ -1896,6 +1896,7 @@ 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 ('058',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 ('059',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 ('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 ('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/065-add-uk-on-x_rms_service_resource-resource_signature.sql b/security-admin/db/postgres/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql new file mode 100644 index 000000000..cfb3c7703 --- /dev/null +++ b/security-admin/db/postgres/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql @@ -0,0 +1,58 @@ +-- 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 truncate_rms_tables() +RETURNS void AS $$ +BEGIN + truncate table x_rms_mapping_provider; + truncate table x_rms_resource_mapping; + truncate table x_rms_notification; + truncate table x_rms_service_resource CASCADE; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +select truncate_rms_tables(); +select 'delimiter end'; + +select 'delimiter start'; +CREATE OR REPLACE FUNCTION create_unique_index_for_x_rms_service_resource() +RETURNS void AS $$ +DECLARE + v_attnum1 integer := 0; +BEGIN + select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_rms_service_resource') and attname in('resource_signature'); + IF v_attnum1 > 0 THEN + IF exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_rms_service_resource') and indkey[0]=v_attnum1) THEN + DROP INDEX IF EXISTS x_rms_service_resource_IDX_resource_signature; + END IF; + END IF; + select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_rms_service_resource') and attname in('resource_signature'); + IF v_attnum1 > 0 THEN + IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_rms_service_resource') and conname='x_rms_service_resource_UK_resource_signature' and contype='u') THEN + IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_rms_service_resource') and indkey[0]=v_attnum1) THEN + ALTER TABLE x_rms_service_resource ADD CONSTRAINT x_rms_service_resource_UK_resource_signature UNIQUE(resource_signature); + END IF; + END IF; + END IF; +END; +$$ LANGUAGE plpgsql; +select 'delimiter end'; + +select create_unique_index_for_x_rms_service_resource(); +select 'delimiter end'; + + diff --git a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql index 1cb77ecfb..ce413be2e 100644 --- a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql +++ b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql @@ -2084,11 +2084,11 @@ resource_signature VARCHAR(128) DEFAULT NULL NULL, is_enabled tinyint DEFAULT 1 NOT NULL, service_resource_elements_text TEXT DEFAULT NULL NULL, primary key (id), +CONSTRAINT x_rms_service_resource_IDX_resource_signature UNIQUE(resource_signature), CONSTRAINT x_rms_notification_FK_hl_service_id FOREIGN KEY(hl_service_id) REFERENCES x_service(id), CONSTRAINT x_rms_notification_FK_ll_service_id FOREIGN KEY(ll_service_id) REFERENCES x_service(id) ); CREATE INDEX x_rms_service_resource_IDX_service_id ON x_rms_service_resource(service_id); -CREATE INDEX x_rms_service_resource_IDX_resource_signature ON x_rms_service_resource(resource_signature); GO CREATE TABLE dbo.x_rms_notification ( @@ -2267,6 +2267,8 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active GO 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'); GO +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'); +GO 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'); GO INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); diff --git a/security-admin/db/sqlanywhere/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql b/security-admin/db/sqlanywhere/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql new file mode 100644 index 000000000..1aff33d13 --- /dev/null +++ b/security-admin/db/sqlanywhere/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql @@ -0,0 +1,27 @@ +-- 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. + +BEGIN + TRUNCATE TABLE dbo.x_rms_mapping_provider; + TRUNCATE TABLE dbo.x_rms_resource_mapping; + TRUNCATE TABLE dbo.x_rms_notification; + TRUNCATE TABLE dbo.x_rms_service_resource; +END +GO +DROP INDEX IF EXISTS x_rms_service_resource_IDX_resource_signature; +GO +CREATE UNIQUE INDEX IF NOT EXISTS x_rms_service_resource_UK_resource_signature ON x_rms_service_resource(resource_signature); +GO +EXIT diff --git a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql index 86fed2eda..dc354c48f 100644 --- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql +++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql @@ -3923,6 +3923,10 @@ CREATE TABLE [dbo].[x_rms_service_resource]( CONSTRAINT [x_rms_service_resource$x_service_res_UK_guid] UNIQUE NONCLUSTERED ( [guid] ASC +)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], +CONSTRAINT [x_rms_service_resource_UK_resource_signature] UNIQUE NONCLUSTERED +( + [resource_signature] ASC )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO @@ -3934,13 +3938,6 @@ CREATE NONCLUSTERED INDEX [x_rms_service_resource_IDX_service_id] ON [x_rms_serv ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] GO -CREATE NONCLUSTERED INDEX [x_rms_service_resource_IDX_resource_signature] ON [x_rms_service_resource] -( - [resource_signature] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -GO - SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON @@ -4115,6 +4112,7 @@ 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 ('058',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 ('059',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 ('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 ('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 (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource Based Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); diff --git a/security-admin/db/sqlserver/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql b/security-admin/db/sqlserver/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql new file mode 100644 index 000000000..c824fa9b6 --- /dev/null +++ b/security-admin/db/sqlserver/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql @@ -0,0 +1,55 @@ +-- 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. + +IF (OBJECT_ID('x_rms_res_map_FK_hl_res_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_rms_resource_mapping] DROP CONSTRAINT x_rms_res_map_FK_hl_res_id +END +GO +IF (OBJECT_ID('x_rms_res_map_FK_ll_res_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_rms_resource_mapping] DROP CONSTRAINT x_rms_res_map_FK_ll_res_id +END +GO + +BEGIN + TRUNCATE TABLE [dbo].[x_rms_mapping_provider]; + TRUNCATE TABLE [dbo].[x_rms_resource_mapping]; + TRUNCATE TABLE [dbo].[x_rms_notification]; + TRUNCATE TABLE [dbo].[x_rms_service_resource]; + ALTER TABLE [dbo].[x_rms_resource_mapping] WITH CHECK ADD CONSTRAINT [x_rms_res_map_FK_hl_res_id] FOREIGN KEY([hl_resource_id]) + REFERENCES [dbo].[x_rms_service_resource] ([id]); + ALTER TABLE [dbo].[x_rms_resource_mapping] WITH CHECK ADD CONSTRAINT [x_rms_res_map_FK_ll_res_id] FOREIGN KEY([ll_resource_id]) + REFERENCES [dbo].[x_rms_service_resource] ([id]); +END +GO + + +IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_rms_service_resource' and column_name = 'resource_signature') +BEGIN + IF EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_rms_service_resource' and column_name='resource_signature' and constraint_name = 'x_rms_service_resource_IDX_resource_signature') + BEGIN + DROP INDEX [x_rms_service_resource_IDX_resource_signature] ON [x_rms_service_resource]; + END + IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_rms_service_resource' and column_name='resource_signature' and constraint_name = 'x_rms_service_resource_UK_resource_signature') + BEGIN + IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_rms_service_resource' and constraint_name = 'x_rms_service_resource_UK_resource_signature' and CONSTRAINT_TYPE='UNIQUE') + BEGIN + ALTER TABLE [dbo].[x_rms_service_resource] ADD CONSTRAINT [x_rms_service_resource_UK_resource_signature] UNIQUE ([resource_signature]); + END + END +END +GO +exit \ No newline at end of file
