You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by pr...@apache.org on 2023/03/14 05:28:57 UTC

[ranger] branch master updated (be40c58f7 -> 85cf0c2da)

This is an automated email from the ASF dual-hosted git repository.

pradeep pushed a change to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git


    from be40c58f7 RANGER-4121: fix for NPE in service-zone update
     new d5ae8af36 RANGER-4109: Add unique constraint on resource_signature column of x_rms_service_resource table
     new 00f493479 RANGER-4123: No policy found for given version
     new 85cf0c2da RANGER-4127: Unable to delete the user if policy is created by same user and added in the policy item

The 3 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.


Summary of changes:
 .../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 +-
 ...-x_rms_service_resource-resource_signature.sql} | 15 +++---
 .../optimized/current/ranger_core_db_sqlserver.sql | 12 ++---
 ...n-x_rms_service_resource-resource_signature.sql | 55 ++++++++++++++++++++
 .../main/java/org/apache/ranger/biz/XUserMgr.java  |  4 +-
 .../patch/PatchForUpdatingPolicyJson_J10019.java   |  5 ++
 12 files changed, 240 insertions(+), 20 deletions(-)
 create mode 100644 security-admin/db/mysql/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql
 create mode 100644 security-admin/db/oracle/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql
 create mode 100644 security-admin/db/postgres/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql
 copy security-admin/db/sqlanywhere/patches/{050-create-index-for-resource-signature.sql => 065-add-uk-on-x_rms_service_resource-resource_signature.sql} (68%)
 create mode 100644 security-admin/db/sqlserver/patches/065-add-uk-on-x_rms_service_resource-resource_signature.sql


[ranger] 02/03: RANGER-4123: No policy found for given version

Posted by pr...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

pradeep pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git

commit 00f4934797e3481c739276cc0b7c4b70b7ec8584
Author: Pradeep AgrawaL <pr...@apache.org>
AuthorDate: Mon Mar 6 15:26:49 2023 +0530

    RANGER-4123: No policy found for given version
---
 .../org/apache/ranger/patch/PatchForUpdatingPolicyJson_J10019.java   | 5 +++++
 1 file changed, 5 insertions(+)

diff --git a/security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingPolicyJson_J10019.java b/security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingPolicyJson_J10019.java
index 6eb3315e7..9b99b942c 100644
--- a/security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingPolicyJson_J10019.java
+++ b/security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingPolicyJson_J10019.java
@@ -84,6 +84,7 @@ import org.apache.ranger.plugin.model.RangerValiditySchedule;
 import org.apache.ranger.plugin.policyevaluator.RangerPolicyItemEvaluator;
 import org.apache.ranger.plugin.util.RangerPerfTracer;
 import org.apache.ranger.plugin.util.SearchFilter;
+import org.apache.ranger.service.RangerDataHistService;
 import org.apache.ranger.service.RangerPolicyService;
 import org.apache.ranger.util.CLIUtil;
 import org.slf4j.Logger;
@@ -124,6 +125,9 @@ public class PatchForUpdatingPolicyJson_J10019 extends BaseLoader {
 	@Autowired
 	XUserMgr xUserMgr;
 
+	@Autowired
+	RangerDataHistService dataHistService;
+
 	private final Map<String, Long>              groupIdMap         = new HashMap<>();
 	private final Map<String, Long>              userIdMap          = new HashMap<>();
 	private final Map<String, Map<String, Long>> resourceNameIdMap  = new HashMap<>();
@@ -289,6 +293,7 @@ public class PatchForUpdatingPolicyJson_J10019 extends BaseLoader {
             		addAccessDefRef(serviceType, policy.getId(), accesses);
             		addPolicyConditionDefRef(serviceType, policy.getId(), conditions);
             		addDataMaskDefRef(serviceType, policy.getId(), dataMasks);
+			dataHistService.createObjectDataHistory(policy, RangerDataHistService.ACTION_UPDATE);
         	} catch (Exception e) {
 		    logger.error("portPoliry(id=" + policy.getId() +") failed!!");
 		    logger.error("Offending policy:" + policyText);


[ranger] 03/03: RANGER-4127: Unable to delete the user if policy is created by same user and added in the policy item

Posted by pr...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

pradeep pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git

commit 85cf0c2da119af379bc1f818ab6a47c2315a14a9
Author: Pradeep AgrawaL <pr...@apache.org>
AuthorDate: Thu Mar 9 14:35:17 2023 +0530

    RANGER-4127: Unable to delete the user if policy is created by same user and added in the policy item
---
 security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java b/security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java
index 49a74cd1e..b3aca3164 100755
--- a/security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java
+++ b/security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java
@@ -2256,7 +2256,6 @@ public class XUserMgr extends XUserMgrBase {
 		List<XXPortalUserRole> xXPortalUserRoles=xXPortalUserRoleDao.findByUserId(xXPortalUserId);
 
 		XXPolicyDao xXPolicyDao = daoManager.getXXPolicy();
-		List<XXPolicy> xXPolicyList=xXPolicyDao.findByUserId(id);
 		logger.warn("Deleting User : "+vXUser.getName());
 		if (force) {
 			//delete XXGroupUser mapping
@@ -2308,6 +2307,7 @@ public class XUserMgr extends XUserMgrBase {
 				}
 			}
 			//delete XXPolicyItemUserPerm records of user
+			List<XXPolicy> xXPolicyList=xXPolicyDao.findByUserId(id);
 			for(XXPolicy xXPolicy:xXPolicyList){
 				RangerPolicy rangerPolicy = policyService.getPopulatedViewObject(xXPolicy);
 				List<RangerPolicyItem> policyItems = rangerPolicy.getPolicyItems();
@@ -2357,7 +2357,7 @@ public class XUserMgr extends XUserMgrBase {
 			}
 		} else {
 			boolean hasReferences=false;
-
+			List<XXPolicy> xXPolicyList=xXPolicyDao.findByUserId(id);
 			if(vxGroupUserList!=null && vxGroupUserList.getListSize()>0){
 				hasReferences=true;
 			}


[ranger] 01/03: RANGER-4109: Add unique constraint on resource_signature column of x_rms_service_resource table

Posted by pr...@apache.org.
This is an automated email from the ASF dual-hosted git repository.

pradeep pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git

commit d5ae8af36d589c78dd4fd2d5336c0cc0fee36eab
Author: Pradeep AgrawaL <pr...@apache.org>
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 9a79fe8ad..66ae5060a 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 fd6cec9a7..bfa6dd572 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 4d5a8cedf..8dd90c1b8 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 3ed2a5b9c..3a614e44e 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 ca8f7da1f..bbef08859 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