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 2021/12/02 08:38:12 UTC
[ranger] 02/02: RANGER-3512 : Create Java patch to update policy guid to unique value.
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 185ca1ca89bb4585c773a31e505502c1ebec51c5
Author: Dineshkumar Yadav <di...@outlook.com>
AuthorDate: Thu Dec 2 10:50:46 2021 +0530
RANGER-3512 : Create Java patch to update policy guid to unique value.
Signed-off-by: pradeep <pr...@apache.org>
---
.../optimized/current/ranger_core_db_mysql.sql | 1 +
.../optimized/current/ranger_core_db_oracle.sql | 1 +
.../optimized/current/ranger_core_db_postgres.sql | 1 +
.../current/ranger_core_db_sqlanywhere.sql | 2 +
.../optimized/current/ranger_core_db_sqlserver.sql | 1 +
.../java/org/apache/ranger/biz/ServiceDBStore.java | 4 +-
.../java/org/apache/ranger/db/XXPolicyDao.java | 33 +++++
...tchPreSql_057_ForUpdateToUniqueGUID_J10052.java | 159 +++++++++++++++++++++
...ForUpdateToUniqueResoureceSignature_J10053.java | 19 ++-
.../main/resources/META-INF/jpa_named_queries.xml | 8 ++
10 files changed, 226 insertions(+), 3 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 df27a9d..28d57a0 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
@@ -1835,6 +1835,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 ('J10049',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 ('J10050',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 ('J10051',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 ('J10052',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 ('J10053',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 ('J10054',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 ('JAVA_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
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 5434f58..c111a28 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
@@ -2039,6 +2039,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,'J10049',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,'J10050',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,'J10051',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,'J10052',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,'J10053',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,'J10054',sys_extract_utc(systimestamp),'Ranger 3.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,'JAVA_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
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 3cfdb81..854a2c6 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
@@ -1983,6 +1983,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 ('J10049',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 ('J10050',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 ('J10051',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 ('J10052',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 ('J10053',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 ('J10054',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 ('JAVA_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
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 40dafaf..b45eace 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
@@ -2395,6 +2395,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 ('J10051',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 ('J10052',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 ('J10053',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 ('J10054',CURRENT_TIMESTAMP,'Ranger 3.0.0',CURRENT_TIMESTAMP,'localhost','Y');
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 0b24c59..adec998 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
@@ -4180,6 +4180,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 ('J10049',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 ('J10050',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 ('J10051',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 ('J10052',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 ('J10053',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 ('J10054',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 ('JAVA_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
diff --git a/security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java b/security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java
index 59acd52..0f0291d 100644
--- a/security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java
+++ b/security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java
@@ -2171,7 +2171,9 @@ public class ServiceDBStore extends AbstractServiceStore {
List<String> policyLabels = policy.getPolicyLabels();
Set<String> uniquePolicyLabels = new TreeSet<>(policyLabels);
policy.setCreateTime(xxExisting.getCreateTime());
- policy.setGuid(xxExisting.getGuid());
+ if (StringUtils.isEmpty(policy.getGuid())) {
+ policy.setGuid(xxExisting.getGuid());
+ }
policy.setVersion(xxExisting.getVersion());
List<XXTrxLog> trxLogList = policyService.getTransactionLog(policy, xxExisting, existing, RangerPolicyService.OPERATION_UPDATE_CONTEXT);
diff --git a/security-admin/src/main/java/org/apache/ranger/db/XXPolicyDao.java b/security-admin/src/main/java/org/apache/ranger/db/XXPolicyDao.java
index 3558337..b068a06 100644
--- a/security-admin/src/main/java/org/apache/ranger/db/XXPolicyDao.java
+++ b/security-admin/src/main/java/org/apache/ranger/db/XXPolicyDao.java
@@ -309,4 +309,37 @@ public class XXPolicyDao extends BaseDao<XXPolicy> {
return new ArrayList<XXPolicy>();
}
}
+
+ public List<String> findDuplicateGUIDByServiceIdAndZoneId(Long serviceId, Long zoneId) {
+ List<String> ret = ListUtils.EMPTY_LIST;
+ if(serviceId == null || zoneId == null) {
+ return ret;
+ }
+ try {
+ ret = getEntityManager().createNamedQuery("XXPolicy.findDuplicateGUIDByServiceIdAndZoneId", String.class)
+ .setParameter("serviceId", serviceId)
+ .setParameter("zoneId", zoneId)
+ .getResultList();
+
+ } catch (Exception e) {
+ }
+ return ret;
+ }
+
+ public List<XXPolicy> findPolicyByGUIDAndServiceIdAndZoneId(String guid, Long serviceId, Long zoneId) {
+ List<XXPolicy> ret = ListUtils.EMPTY_LIST;
+ if (guid == null || serviceId == null || zoneId == null) {
+ return ret;
+ }
+ try {
+ ret = getEntityManager().createNamedQuery("XXPolicy.findPolicyByGUIDAndServiceIdAndZoneId", tClass)
+ .setParameter("guid", guid)
+ .setParameter("serviceId", serviceId)
+ .setParameter("zoneId", zoneId)
+ .getResultList();
+
+ } catch (NoResultException excp) {
+ }
+ return ret;
+ }
}
\ No newline at end of file
diff --git a/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_057_ForUpdateToUniqueGUID_J10052.java b/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_057_ForUpdateToUniqueGUID_J10052.java
new file mode 100644
index 0000000..7e1ead7
--- /dev/null
+++ b/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_057_ForUpdateToUniqueGUID_J10052.java
@@ -0,0 +1,159 @@
+/*
+ * 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.
+ */
+
+package org.apache.ranger.patch;
+
+import java.util.List;
+
+import org.apache.commons.collections.CollectionUtils;
+import org.apache.log4j.Logger;
+import org.apache.ranger.authorization.utils.JsonUtils;
+import org.apache.ranger.biz.ServiceDBStore;
+import org.apache.ranger.common.GUIDUtil;
+import org.apache.ranger.db.RangerDaoManager;
+import org.apache.ranger.entity.XXPolicy;
+import org.apache.ranger.entity.XXSecurityZone;
+import org.apache.ranger.entity.XXService;
+import org.apache.ranger.plugin.model.RangerPolicy;
+import org.apache.ranger.util.CLIUtil;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Qualifier;
+import org.springframework.stereotype.Component;
+import org.springframework.transaction.PlatformTransactionManager;
+
+/**
+ * This patch will regenerate new GUID and update policies which has duplicate GUID for every service.
+ *
+ */
+@Component
+public class PatchPreSql_057_ForUpdateToUniqueGUID_J10052 extends BaseLoader {
+ private static final Logger logger = Logger.getLogger(PatchPreSql_057_ForUpdateToUniqueGUID_J10052.class);
+
+ @Autowired
+ RangerDaoManager daoMgr;
+
+ @Autowired
+ ServiceDBStore svcStore;
+
+ @Autowired
+ GUIDUtil guidUtil;
+
+ @Autowired
+ @Qualifier(value = "transactionManager")
+ PlatformTransactionManager txManager;
+
+
+ public static void main(String[] args) {
+ logger.info("main()");
+ try {
+ PatchPreSql_057_ForUpdateToUniqueGUID_J10052 loader = (PatchPreSql_057_ForUpdateToUniqueGUID_J10052) CLIUtil.getBean(PatchPreSql_057_ForUpdateToUniqueGUID_J10052.class);
+
+ loader.init();
+
+ while (loader.isMoreToProcess()) {
+ loader.load();
+ }
+
+ logger.info("Load complete. Exiting!!!");
+
+ System.exit(0);
+ } catch (Exception e) {
+ logger.error("Error loading", e);
+ System.exit(1);
+ }
+ }
+
+ @Override
+ public void init() throws Exception {
+ // Do Nothing
+ }
+
+ @Override
+ public void execLoad() {
+
+ try {
+ logger.info("==> updatePolicyGUIDToUniqueValue()");
+ updatePolicyGUIDToUniqueValue();
+ } catch (Exception e) {
+ logger.error("Error while updatePolicyGUIDToUniqueValue()", e);
+ System.exit(1);
+ }
+
+ logger.info("<== updatePolicyGUIDToUniqueValue.execLoad()");
+ }
+
+ @Override
+ public void printStats() {
+ logger.info("runnig updatePolicyGUIDToUniqueValue ");
+ }
+
+ private void updatePolicyGUIDToUniqueValue() throws Exception {
+ logger.info("==> updatePolicyGUIDToUniqueValue() ");
+
+ List<XXSecurityZone> allXXZones = null;
+ List<XXService> allXXService = null;
+
+ allXXZones = daoMgr.getXXSecurityZoneDao().getAll();
+ allXXService = daoMgr.getXXService().getAll();
+
+ if (CollectionUtils.isNotEmpty(allXXZones) && CollectionUtils.isNotEmpty(allXXService)) {
+ logger.info("Total number of zones " + allXXZones.size() +", service :" +allXXService.size());
+ for (XXSecurityZone xSecurityZone : allXXZones) {
+ for (XXService xService : allXXService) {
+ logger.info("serching duplicate guid policies for service :" + xService.getName() + " zone : "
+ + xSecurityZone.getName());
+ List<String> duplicateGuidList = daoMgr.getXXPolicy()
+ .findDuplicateGUIDByServiceIdAndZoneId(xService.getId(), xSecurityZone.getId());
+ if (CollectionUtils.isNotEmpty(duplicateGuidList)) {
+ logger.info("Total number of duplicate GUIDs :" + duplicateGuidList.size() + " for service :"
+ + xService.getName() + " and zone :" + xSecurityZone.getName());
+ for (String guid : duplicateGuidList) {
+ List<XXPolicy> xxPolicyList = daoMgr.getXXPolicy().findPolicyByGUIDAndServiceIdAndZoneId(
+ guid, xService.getId(), xSecurityZone.getId());
+ boolean isFirstElement = false;
+ if (CollectionUtils.isNotEmpty(xxPolicyList)) {
+ isFirstElement = true;
+ for (XXPolicy xxPolicy : xxPolicyList) {
+ if (isFirstElement) {
+ isFirstElement = false;
+ continue;
+ }
+ RangerPolicy policy = svcStore.getPolicy(xxPolicy.getId());
+ if (policy != null) {
+ guid = guidUtil.genGUID();
+ xxPolicy.setGuid(guid);
+ policy.setGuid(guid);
+ xxPolicy.setPolicyText(JsonUtils.objectToJson(policy));
+
+ daoMgr.getXXPolicy().update(xxPolicy);
+ }
+ }
+ } else {
+ logger.info("No policy found with guid:" + guid);
+ }
+ }
+ } else {
+ logger.info("No duplicate GUID found in policy for Service :" + xService.getName() + ", Zone : "
+ + xSecurityZone.getName());
+ }
+ }
+ }
+ } else {
+ logger.info("No zone or service found");
+ }
+ }
+}
\ No newline at end of file
diff --git a/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java b/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
index 8da6530..b6a20ab 100644
--- a/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
+++ b/security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java
@@ -21,10 +21,13 @@ import java.util.List;
import org.apache.commons.collections.CollectionUtils;
import org.apache.log4j.Logger;
+import org.apache.ranger.authorization.utils.JsonUtils;
import org.apache.ranger.biz.ServiceDBStore;
+import org.apache.ranger.common.RangerFactory;
import org.apache.ranger.db.RangerDaoManager;
import org.apache.ranger.entity.XXPolicy;
import org.apache.ranger.plugin.model.RangerPolicy;
+import org.apache.ranger.plugin.model.RangerPolicyResourceSignature;
import org.apache.ranger.util.CLIUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
@@ -46,6 +49,9 @@ public class PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053 extends
ServiceDBStore svcStore;
@Autowired
+ RangerFactory factory;
+
+ @Autowired
@Qualifier(value = "transactionManager")
PlatformTransactionManager txManager;
@@ -97,14 +103,23 @@ public class PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053 extends
private void updateDisabledPolicyResourceSignature() throws Exception {
logger.info("==> updateDisabledPolicyResourceSignature() ");
+
List<XXPolicy> xxPolicyList = daoMgr.getXXPolicy().findByPolicyStatus(isPolicyEnabled);
if (CollectionUtils.isNotEmpty(xxPolicyList)) {
- logger.info("==> Total number of disabled policies :"+xxPolicyList.size());
+ logger.info("==> Total number of disabled policies :" + xxPolicyList.size());
+
for (XXPolicy xxPolicy : xxPolicyList) {
RangerPolicy policy = svcStore.getPolicy(xxPolicy.getId());
if (policy != null) {
policy.setResourceSignature(null);
- svcStore.updatePolicy(policy);
+ xxPolicy.setResourceSignature(null);
+ RangerPolicyResourceSignature policySignature = factory.createPolicyResourceSignature(policy);
+ String signature = policySignature.getSignature();
+ policy.setResourceSignature(signature);
+ xxPolicy.setPolicyText(JsonUtils.objectToJson(policy));
+ xxPolicy.setResourceSignature(signature);
+
+ daoMgr.getXXPolicy().update(xxPolicy);
}
}
} else {
diff --git a/security-admin/src/main/resources/META-INF/jpa_named_queries.xml b/security-admin/src/main/resources/META-INF/jpa_named_queries.xml
index a19f7f1..8225e30 100755
--- a/security-admin/src/main/resources/META-INF/jpa_named_queries.xml
+++ b/security-admin/src/main/resources/META-INF/jpa_named_queries.xml
@@ -323,6 +323,14 @@
<query>select obj.id from XXPolicy obj where obj.service = :serviceId</query>
</named-query>
+ <named-query name="XXPolicy.findDuplicateGUIDByServiceIdAndZoneId">
+ <query>select obj.guid from XXPolicy obj where obj.service = :serviceId and obj.zoneId = :zoneId GROUP BY obj.guid HAVING COUNT(obj.guid)>1</query>
+ </named-query>
+
+ <named-query name="XXPolicy.findPolicyByGUIDAndServiceIdAndZoneId">
+ <query>select obj from XXPolicy obj where obj.guid = :guid and obj.service =:serviceId and obj.zoneId = :zoneId order by obj.id </query>
+ </named-query>
+
<named-query name="XXPolicy.getMaxIdOfXXPolicy">
<query>select MAX(obj.id) from XXPolicy obj</query>
</named-query>