ranger-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ab...@apache.org
Subject [8/8] ranger git commit: RANGER-2203, RANGER-2219: Review and update database schema for ranger policies and tag objects to minimize database queries/updates; back-port of RANGER-2186, RANGER-2195
Date Fri, 19 Oct 2018 02:28:44 GMT
RANGER-2203, RANGER-2219: Review and update database schema for ranger policies and tag objects to minimize database queries/updates; back-port of RANGER-2186, RANGER-2195


Project: http://git-wip-us.apache.org/repos/asf/ranger/repo
Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/c84b98fb
Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/c84b98fb
Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/c84b98fb

Branch: refs/heads/ranger-0.7
Commit: c84b98fbae6e089c637848218743e195e1259fa9
Parents: 9890a90
Author: Abhay Kulkarni <akulkarni@hortonworks.com>
Authored: Thu Oct 18 19:28:20 2018 -0700
Committer: Abhay Kulkarni <akulkarni@hortonworks.com>
Committed: Thu Oct 18 19:28:20 2018 -0700

----------------------------------------------------------------------
 .../ranger/authorization/utils/JsonUtils.java   |  112 ++
 .../plugin/store/AbstractServiceStore.java      |    2 +-
 .../apache/ranger/plugin/store/TagStore.java    |    2 +
 .../scripts/ranger-admin-services.sh            |   13 +-
 .../patches/035-update-schema-for-x-policy.sql  |  199 +++
 .../patches/036-denormalize-tag-tables.sql      |   82 ++
 .../patches/035-update-schema-for-x-policy.sql  |  164 +++
 .../patches/036-denormalize-tag-tables.sql      |   54 +
 .../patches/035-update-schema-for-x-policy.sql  |  198 +++
 .../patches/036-denormalize-tag-tables.sql      |   79 ++
 .../patches/035-update-schema-for-x-policy.sql  |  179 +++
 .../patches/036-denormalize-tag-tables.sql      |   71 +
 .../current/ranger_core_db_sqlserver.sql        |  284 +++-
 .../patches/035-update-schema-for-x-policy.sql  |  453 +++++++
 .../patches/036-denormalize-tag-tables.sql      |   97 ++
 security-admin/scripts/db_setup.py              |   75 +-
 .../org/apache/ranger/biz/PolicyRefUpdater.java |  286 ++++
 .../ranger/biz/RangerPolicyRetriever.java       |  579 +++-----
 .../apache/ranger/biz/RangerTagDBRetriever.java |  466 ++-----
 .../org/apache/ranger/biz/ServiceDBStore.java   | 1235 ++++++++----------
 .../java/org/apache/ranger/biz/TagDBStore.java  |  281 +---
 ...RangerTransactionSynchronizationAdapter.java |  154 ++-
 .../org/apache/ranger/db/RangerDaoManager.java  |   26 +-
 .../apache/ranger/db/RangerDaoManagerBase.java  |   48 +-
 .../apache/ranger/db/XXAccessTypeDefDao.java    |    1 -
 .../apache/ranger/db/XXDataMaskTypeDefDao.java  |    1 -
 .../java/org/apache/ranger/db/XXGroupDao.java   |   16 -
 .../ranger/db/XXPolicyConditionDefDao.java      |   28 -
 .../apache/ranger/db/XXPolicyItemAccessDao.java |   25 -
 .../ranger/db/XXPolicyItemConditionDao.java     |   40 -
 .../ranger/db/XXPolicyItemDataMaskInfoDao.java  |   25 -
 .../ranger/db/XXPolicyItemGroupPermDao.java     |   13 -
 .../ranger/db/XXPolicyItemRowFilterInfoDao.java |   13 -
 .../ranger/db/XXPolicyItemUserPermDao.java      |   13 -
 .../ranger/db/XXPolicyRefAccessTypeDao.java     |  100 ++
 .../ranger/db/XXPolicyRefConditionDao.java      |  111 ++
 .../ranger/db/XXPolicyRefDataMaskTypeDao.java   |   86 ++
 .../apache/ranger/db/XXPolicyRefGroupDao.java   |   99 ++
 .../ranger/db/XXPolicyRefResourceDao.java       |   98 ++
 .../apache/ranger/db/XXPolicyRefUserDao.java    |  111 ++
 .../apache/ranger/db/XXPolicyResourceDao.java   |   14 -
 .../ranger/db/XXPolicyResourceMapDao.java       |   13 -
 .../org/apache/ranger/db/XXResourceDefDao.java  |    1 -
 .../apache/ranger/db/XXServiceResourceDao.java  |   36 +-
 .../ranger/db/XXServiceResourceElementDao.java  |   12 -
 .../db/XXServiceResourceElementValueDao.java    |   13 -
 .../ranger/db/XXServiceVersionInfoDao.java      |   14 +-
 .../org/apache/ranger/db/XXTagAttributeDao.java |   12 -
 .../apache/ranger/db/XXTagAttributeDefDao.java  |   12 -
 .../java/org/apache/ranger/db/XXTagDao.java     |   13 -
 .../java/org/apache/ranger/db/XXTagDefDao.java  |   53 +-
 .../apache/ranger/db/XXTagResourceMapDao.java   |   11 -
 .../java/org/apache/ranger/db/XXUserDao.java    |   20 +-
 .../java/org/apache/ranger/entity/XXDBBase.java |    6 +
 .../org/apache/ranger/entity/XXPolicyBase.java  |   14 +-
 .../ranger/entity/XXPolicyRefAccessType.java    |  191 +++
 .../ranger/entity/XXPolicyRefCondition.java     |  191 +++
 .../ranger/entity/XXPolicyRefDataMaskType.java  |  192 +++
 .../apache/ranger/entity/XXPolicyRefGroup.java  |  206 +++
 .../ranger/entity/XXPolicyRefResource.java      |  191 +++
 .../apache/ranger/entity/XXPolicyRefUser.java   |  191 +++
 .../apache/ranger/entity/XXServiceResource.java |   30 +
 .../java/org/apache/ranger/entity/XXTag.java    |   16 +-
 .../java/org/apache/ranger/entity/XXTagDef.java |   14 +
 .../PatchForUpdatingPolicyJson_J10019.java      | 1125 ++++++++++++++++
 .../patch/PatchForUpdatingTagsJson_J10020.java  |  788 +++++++++++
 .../org/apache/ranger/rest/ServiceREST.java     |   14 +-
 .../ranger/service/RangerAuditFields.java       |   12 +-
 .../ranger/service/RangerPolicyServiceBase.java |   21 +-
 .../service/RangerServiceResourceService.java   |  194 ++-
 .../RangerServiceResourceServiceBase.java       |   33 +-
 .../ranger/service/RangerTagDefService.java     |   24 +
 .../apache/ranger/service/RangerTagService.java |   53 +-
 .../resources/META-INF/jpa_named_queries.xml    |  381 +++---
 .../apache/ranger/biz/TestServiceDBStore.java   |  371 ++++--
 .../org/apache/ranger/rest/TestServiceREST.java |    2 +-
 .../service/TestRangerServiceDefService.java    |    9 -
 .../ranger/service/TestRangerTagDefService.java |   41 +-
 78 files changed, 7875 insertions(+), 2578 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/agents-common/src/main/java/org/apache/ranger/authorization/utils/JsonUtils.java
----------------------------------------------------------------------
diff --git a/agents-common/src/main/java/org/apache/ranger/authorization/utils/JsonUtils.java b/agents-common/src/main/java/org/apache/ranger/authorization/utils/JsonUtils.java
new file mode 100644
index 0000000..98d9c0a
--- /dev/null
+++ b/agents-common/src/main/java/org/apache/ranger/authorization/utils/JsonUtils.java
@@ -0,0 +1,112 @@
+/*
+ * 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.authorization.utils;
+
+import com.google.gson.Gson;
+import com.google.gson.GsonBuilder;
+import com.google.gson.reflect.TypeToken;
+import org.apache.commons.collections.CollectionUtils;
+import org.apache.commons.collections.MapUtils;
+import org.apache.commons.lang.StringUtils;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
+import java.lang.reflect.Type;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class JsonUtils {
+    private static final Log LOG = LogFactory.getLog(JsonUtils.class);
+
+    private static final HashMap<String, String> MAP_STRING_STRING = new HashMap<>();
+
+    private static final Gson gson;
+
+    static {
+        gson = new GsonBuilder().setDateFormat("yyyyMMdd-HH:mm:ss.SSS-Z")
+                .create();
+    }
+
+    public static String mapToJson(Map<?, ?> map) {
+        String ret = null;
+        if (MapUtils.isNotEmpty(map)) {
+            try {
+                ret = gson.toJson(map);
+            } catch (Exception e) {
+                LOG.error("Invalid input data: ", e);
+            }
+        }
+        return ret;
+    }
+
+    public static String listToJson(List<?> list) {
+        String ret = null;
+        if (CollectionUtils.isNotEmpty(list)) {
+            try {
+                ret = gson.toJson(list);
+            } catch (Exception e) {
+                LOG.error("Invalid input data: ", e);
+            }
+        }
+        return ret;
+    }
+
+    public static String objectToJson(Object object) {
+        String ret = null;
+
+        if(object != null) {
+            try {
+                ret = gson.toJson(object);
+            } catch(Exception excp) {
+                LOG.warn("objectToJson() failed to convert object to Json", excp);
+            }
+        }
+
+        return ret;
+    }
+
+    public static <T> T jsonToObject(String jsonStr, Class<T> clz) {
+        T ret = null;
+
+        if(StringUtils.isNotEmpty(jsonStr)) {
+            try {
+                ret = gson.fromJson(jsonStr, clz);
+            } catch(Exception excp) {
+                LOG.warn("jsonToObject() failed to convert json to object: " + jsonStr, excp);
+            }
+        }
+
+        return ret;
+    }
+
+    public static Map<String, String> jsonToMapStringString(String jsonStr) {
+        Map<String, String> ret = null;
+
+        if(StringUtils.isNotEmpty(jsonStr)) {
+            try {
+                ret = gson.fromJson(jsonStr, MAP_STRING_STRING.getClass());
+            } catch(Exception excp) {
+                LOG.warn("jsonToObject() failed to convert json to object: " + jsonStr, excp);
+            }
+        }
+
+        return ret;
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
----------------------------------------------------------------------
diff --git a/agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java b/agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
index a75ca59..bee7520 100644
--- a/agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
+++ b/agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java
@@ -138,7 +138,7 @@ public abstract class AbstractServiceStore implements ServiceStore {
 		}
 	}
 
-	protected final long getNextVersion(Long currentVersion) {
+	public static long getNextVersion(Long currentVersion) {
 		return currentVersion == null ? 1L : currentVersion + 1;
 	}
 

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/agents-common/src/main/java/org/apache/ranger/plugin/store/TagStore.java
----------------------------------------------------------------------
diff --git a/agents-common/src/main/java/org/apache/ranger/plugin/store/TagStore.java b/agents-common/src/main/java/org/apache/ranger/plugin/store/TagStore.java
index 5918b12..fe4b278 100644
--- a/agents-common/src/main/java/org/apache/ranger/plugin/store/TagStore.java
+++ b/agents-common/src/main/java/org/apache/ranger/plugin/store/TagStore.java
@@ -84,6 +84,8 @@ public interface TagStore {
 
     RangerServiceResource updateServiceResource(RangerServiceResource resource) throws Exception;
 
+    void refreshServiceResource(Long resourceId) throws Exception;
+
     void deleteServiceResource(Long id) throws Exception;
 
     void deleteServiceResourceByGuid(String guid) throws Exception;

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/embeddedwebserver/scripts/ranger-admin-services.sh
----------------------------------------------------------------------
diff --git a/embeddedwebserver/scripts/ranger-admin-services.sh b/embeddedwebserver/scripts/ranger-admin-services.sh
index 19dab2a..350826f 100755
--- a/embeddedwebserver/scripts/ranger-admin-services.sh
+++ b/embeddedwebserver/scripts/ranger-admin-services.sh
@@ -28,11 +28,12 @@ action=`echo $action | tr '[:lower:]' '[:upper:]'`
 realScriptPath=`readlink -f $0`
 realScriptDir=`dirname $realScriptPath`
 XAPOLICYMGR_DIR=`(cd $realScriptDir/..; pwd)`
+max_memory=1g
 
 XAPOLICYMGR_EWS_DIR=${XAPOLICYMGR_DIR}/ews
 RANGER_JAAS_LIB_DIR="${XAPOLICYMGR_EWS_DIR}/ranger_jaas"
 RANGER_JAAS_CONF_DIR="${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/conf/ranger_jaas"
-JAVA_OPTS=" ${JAVA_OPTS} -XX:MaxPermSize=256m -Xmx1024m -Xms1024m "
+JAVA_OPTS=" ${JAVA_OPTS} -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx${max_memory} -Xms1g -Xloggc:${XAPOLICYMGR_EWS_DIR}/logs/gc-worker.log -verbose:gc -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=1m -XX:+PrintGCDetails -XX:+PrintHeapAtGC -XX:+PrintGCDateStamps"
 if [[ ${JAVA_OPTS} != *"-Duser.timezone"* ]] ;then  export JAVA_OPTS=" ${JAVA_OPTS} -Duser.timezone=UTC" ;fi
 if [ -f ${XAPOLICYMGR_DIR}/ews/webapp/WEB-INF/classes/conf/java_home.sh ]; then
         . ${XAPOLICYMGR_DIR}/ews/webapp/WEB-INF/classes/conf/java_home.sh
@@ -137,11 +138,11 @@ stop(){
 }
 
 metric(){
-  if [ "$JAVA_HOME" == "" ]; then
-    echo "[E] JAVA_HOME environment variable not defined, aborting Apache Ranger Admin metric collection" 1>&2;
-    exit 1;
-  fi
-  java ${JAVA_OPTS} -Duser=${USER} -Dhostname=${HOSTNAME} -Dlogdir=${RANGER_ADMIN_LOG_DIR} -cp "${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/conf:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/lib/*:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/META-INF:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/lib/*:${XAPOLICYMGR_EWS_DIR}/webapp/META-INF:${XAPOLICYMGR_EWS_DIR}/lib/*:${RANGER_JAAS_LIB_DIR}/*:${RANGER_JAAS_CONF_DIR}:${JAVA_HOME}/lib/*:${RANGER_HADOOP_CONF_DIR}/*:$CLASSPATH" org.apache.ranger.patch.cliutil.MetricUtil ${arg2} ${arg3} 2>/dev/null
+ if [ "$JAVA_HOME" == "" ]; then
+  echo "[E] JAVA_HOME environment variable not defined, aborting Apache Ranger Admin metric collection"
+  exit 1;
+ fi
+ java ${JAVA_OPTS} -Dlogdir=${RANGER_ADMIN_LOG_DIR} -cp "${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/conf:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/lib/*:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/classes/META-INF:${XAPOLICYMGR_EWS_DIR}/webapp/WEB-INF/lib/*:${XAPOLICYMGR_EWS_DIR}/webapp/META-INF:${XAPOLICYMGR_EWS_DIR}/lib/*:${RANGER_JAAS_LIB_DIR}/*:${RANGER_JAAS_CONF_DIR}:${JAVA_HOME}/lib/*:${RANGER_HADOOP_CONF_DIR}/*:$CLASSPATH" org.apache.ranger.patch.cliutil.MetricUtil ${arg2} ${arg3} 2>/dev/null
 }
 
 if [ "${action}" == "START" ]; then

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql b/security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..05bd850
--- /dev/null
+++ b/security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,199 @@
+-- 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 alter_table_x_policy;
+
+delimiter ;;
+create procedure alter_table_x_policy() begin
+
+if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy') then
+  if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name = 'policy_text') then
+    ALTER TABLE `x_policy` ADD `policy_text` MEDIUMTEXT DEFAULT NULL;
+  end if;
+ end if; 
+end;;
+
+delimiter ;
+call alter_table_x_policy();
+
+drop procedure if exists alter_table_x_policy;
+
+DROP PROCEDURE IF EXISTS removeConstraints;
+DELIMITER ;;
+CREATE PROCEDURE removeConstraints(vTableName varchar(128))
+BEGIN
+  DECLARE done INT DEFAULT FALSE;
+  DECLARE cName VARCHAR(64);
+  DECLARE cur CURSOR FOR
+          SELECT DISTINCT CONSTRAINT_NAME
+          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
+          WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = vTableName
+          AND REFERENCED_TABLE_NAME IS NOT NULL;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+  SET AUTOCOMMIT=0;
+  SET FOREIGN_KEY_CHECKS=0;
+
+  OPEN cur;
+
+  read_loop: LOOP
+    FETCH cur INTO cName;
+    IF done THEN
+      LEAVE read_loop;
+    END IF;
+    SET @sql = CONCAT('ALTER TABLE ',vTableName,' DROP FOREIGN KEY ',cName,';');
+    PREPARE stmt FROM @sql;
+    EXECUTE stmt;
+    DEALLOCATE PREPARE stmt;
+  END LOOP;
+
+  CLOSE cur;
+
+  SET FOREIGN_KEY_CHECKS=1;
+  COMMIT;
+  SET AUTOCOMMIT=1;
+END ;;
+DELIMITER ;
+
+call removeConstraints('x_policy_item');
+call removeConstraints('x_policy_item_access');
+call removeConstraints('x_policy_item_condition');
+call removeConstraints('x_policy_item_datamask');
+call removeConstraints('x_policy_item_group_perm');
+call removeConstraints('x_policy_item_user_perm');
+call removeConstraints('x_policy_item_rowfilter');
+call removeConstraints('x_policy_resource');
+call removeConstraints('x_policy_resource_map');
+
+DROP PROCEDURE removeConstraints;
+
+DROP TABLE IF EXISTS `x_policy_ref_resource`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_resource` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `resource_def_id` bigint(20) NOT NULL,
+  `resource_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_res_UK_polId_resDefId`(`policy_id`, `resource_def_id`),
+  CONSTRAINT `x_policy_ref_res_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_res_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_res_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_res_FK_resource_def_id` FOREIGN KEY (`resource_def_id`) REFERENCES `x_resource_def` (`id`)
+
+) ROW_FORMAT=DYNAMIC;
+
+
+DROP TABLE IF EXISTS `x_policy_ref_access_type`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_access_type` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `access_def_id` bigint(20) NOT NULL,
+  `access_type_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_access_UK_polId_accessDefId`(`policy_id`, `access_def_id`),
+  CONSTRAINT `x_policy_ref_access_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_access_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_access_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_access_FK_access_def_id` FOREIGN KEY (`access_def_id`) REFERENCES `x_access_type_def` (`id`)
+) ROW_FORMAT=DYNAMIC;
+
+
+DROP TABLE IF EXISTS `x_policy_ref_condition`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_condition` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `condition_def_id` bigint(20) NOT NULL,
+  `condition_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_condition_UK_polId_condDefId`(`policy_id`, `condition_def_id`),
+  CONSTRAINT `x_policy_ref_condition_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_condition_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_condition_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_condition_FK_condition_def_id` FOREIGN KEY (`condition_def_id`) REFERENCES `x_policy_condition_def` (`id`)
+  
+) ROW_FORMAT=DYNAMIC;
+
+DROP TABLE IF EXISTS `x_policy_ref_datamask_type`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_datamask_type` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `datamask_def_id` bigint(20) NOT NULL,
+  `datamask_type_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_datamask_UK_polId_dmaskDefId`(`policy_id`, `datamask_def_id`),
+  CONSTRAINT `x_policy_ref_datamask_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_datamask_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_datamask_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_datamask_FK_datamask_def_id` FOREIGN KEY (`datamask_def_id`) REFERENCES `x_datamask_type_def` (`id`)
+) ROW_FORMAT=DYNAMIC;
+
+DROP TABLE IF EXISTS `x_policy_ref_user`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_user` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `user_id` bigint(20) NOT NULL,
+  `user_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_user_UK_polId_userId`(`policy_id`, `user_id`),
+  CONSTRAINT `x_policy_ref_user_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_user_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_user_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_user_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_user` (`id`)
+) ROW_FORMAT=DYNAMIC;
+
+
+DROP TABLE IF EXISTS `x_policy_ref_group`;
+CREATE TABLE IF NOT EXISTS `x_policy_ref_group` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `guid` varchar(1024) DEFAULT NULL,
+  `create_time` datetime DEFAULT NULL,
+  `update_time` datetime DEFAULT NULL,
+  `added_by_id` bigint(20) DEFAULT NULL,
+  `upd_by_id` bigint(20) DEFAULT NULL,
+  `policy_id` bigint(20) NOT NULL,
+  `group_id` bigint(20) NOT NULL,
+  `group_name` varchar(4000) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `x_policy_ref_group_UK_polId_groupId`(`policy_id`, `group_id`),
+  CONSTRAINT `x_policy_ref_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_group_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
+  CONSTRAINT `x_policy_ref_group_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCES `x_policy` (`id`),
+  CONSTRAINT `x_policy_ref_group_FK_group_id` FOREIGN KEY (`group_id`) REFERENCES `x_group` (`id`)
+) ROW_FORMAT=DYNAMIC;

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/mysql/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/mysql/patches/036-denormalize-tag-tables.sql b/security-admin/db/mysql/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..63035bc
--- /dev/null
+++ b/security-admin/db/mysql/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,82 @@
+-- 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 denormalize_tag_tables;
+
+delimiter ;;
+create procedure denormalize_tag_tables() begin
+
+if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_tag_def' and column_name='tag_attrs_def_text') then
+        ALTER TABLE x_tag_def ADD tag_attrs_def_text MEDIUMTEXT NULL DEFAULT NULL;
+end if;
+if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_tag' and column_name='tag_attrs_text') then
+        ALTER TABLE x_tag ADD tag_attrs_text MEDIUMTEXT NULL DEFAULT NULL;
+end if;
+if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_service_resource' and column_name='service_resource_elements_text') then
+        ALTER TABLE x_service_resource ADD service_resource_elements_text MEDIUMTEXT NULL DEFAULT NULL;
+end if;
+if not exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_service_resource' and column_name='tags_text') then
+        ALTER TABLE x_service_resource ADD tags_text MEDIUMTEXT NULL DEFAULT NULL;
+end if;
+end;;
+
+delimiter ;
+call denormalize_tag_tables();
+
+drop procedure if exists denormalize_tag_tables;
+
+DROP PROCEDURE IF EXISTS removeConstraints;
+DELIMITER ;;
+CREATE PROCEDURE removeConstraints(vTableName varchar(128))
+BEGIN
+  DECLARE done INT DEFAULT FALSE;
+  DECLARE cName VARCHAR(64);
+  DECLARE cur CURSOR FOR
+          SELECT DISTINCT CONSTRAINT_NAME
+          FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
+          WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = vTableName
+          AND REFERENCED_TABLE_NAME IS NOT NULL;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+
+  SET AUTOCOMMIT=0;
+  SET FOREIGN_KEY_CHECKS=0;
+
+  OPEN cur;
+
+  read_loop: LOOP
+    FETCH cur INTO cName;
+    IF done THEN
+      LEAVE read_loop;
+    END IF;
+    SET @sql = CONCAT('ALTER TABLE ',vTableName,' DROP FOREIGN KEY ',cName,';');
+    PREPARE stmt FROM @sql;
+    EXECUTE stmt;
+    DEALLOCATE PREPARE stmt;
+  END LOOP;
+
+  CLOSE cur;
+
+  SET FOREIGN_KEY_CHECKS=1;
+  COMMIT;
+  SET AUTOCOMMIT=1;
+END ;;
+DELIMITER ;
+
+call removeConstraints('x_tag_attr_def');
+call removeConstraints('x_tag_attr');
+call removeConstraints('x_service_resource_element');
+call removeConstraints('x_service_resource_element_val');
+
+DROP PROCEDURE removeConstraints;

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql b/security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..745f7f0
--- /dev/null
+++ b/security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,164 @@
+-- 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 SEQUENCE X_POLICY_REF_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_POLICY_REF_ACCESS_TYPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_POLICY_REF_CONDITION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_POLICY_REF_DATAMASK_TYPE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_POLICY_REF_USER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_POLICY_REF_GROUP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+commit;
+CREATE TABLE x_policy_ref_resource (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+resource_def_id NUMBER(20) NOT NULL,
+resource_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY (resource_def_id) REFERENCES x_resource_def (id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+CREATE TABLE x_policy_ref_access_type (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+access_def_id NUMBER(20) NOT NULL,
+access_type_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY (access_def_id) REFERENCES x_access_type_def (id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+CREATE TABLE x_policy_ref_condition (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+condition_def_id NUMBER(20) NOT NULL,
+condition_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY (condition_def_id) REFERENCES x_policy_condition_def (id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+CREATE TABLE x_policy_ref_datamask_type (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+datamask_def_id NUMBER(20) NOT NULL,
+datamask_type_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_dmsk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmsk_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_dmsk_FK_dmk_def_id FOREIGN KEY (datamask_def_id) REFERENCES x_datamask_type_def (id),
+CONSTRAINT x_p_ref_dmsk_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_dmsk_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+CREATE TABLE x_policy_ref_user (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+user_id NUMBER(20) NOT NULL,
+user_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY (user_id) REFERENCES x_user (id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+CREATE TABLE x_policy_ref_group (
+id NUMBER(20) NOT NULL,
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time DATE DEFAULT NULL NULL,
+update_time DATE DEFAULT NULL NULL,
+added_by_id NUMBER(20) DEFAULT NULL NULL,
+upd_by_id NUMBER(20) DEFAULT NULL NULL,
+policy_id NUMBER(20) NOT NULL,
+group_id NUMBER(20) NOT NULL,
+group_name VARCHAR(4000) DEFAULT NULL NULL,
+primary key (id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_policy (id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY (group_id) REFERENCES x_group (id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
+);
+commit;
+DECLARE
+	v_column_exists number := 0;
+BEGIN
+Select count(*) into v_column_exists from user_tab_cols where column_name = upper('policy_text') and table_name = upper('x_policy');
+	if (v_column_exists = 0) then
+		execute immediate 'ALTER TABLE x_policy ADD policy_text CLOB DEFAULT NULL NULL';
+		commit;
+	end if;
+end;/
+
+CREATE OR REPLACE PROCEDURE removeConstraints(ObjName IN varchar2) IS
+BEGIN
+FOR rec IN(
+select owner, constraint_name
+from all_constraints
+where owner = sys_context('userenv','current_schema')
+and table_name = ObjName
+and constraint_type = 'R')
+LOOP
+execute immediate 'ALTER TABLE ' || rec.owner || '.' || ObjName || ' DROP CONSTRAINT ' || rec.constraint_name;
+END LOOP;
+END;/
+/
+
+CALL removeConstraints('X_POLICY_ITEM');
+CALL removeConstraints('X_POLICY_ITEM_ACCESS');
+CALL removeConstraints('X_POLICY_ITEM_CONDITION');
+CALL removeConstraints('X_POLICY_ITEM_DATAMASK');
+CALL removeConstraints('X_POLICY_ITEM_GROUP_PERM');
+CALL removeConstraints('X_POLICY_RESOURCE');
+CALL removeConstraints('X_POLICY_RESOURCE_MAP');
+CALL removeConstraints('X_POLICY_ITEM_USER_PERM');
+CALL removeConstraints('X_POLICY_ITEM_ROWFILTER');
+

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/oracle/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/036-denormalize-tag-tables.sql b/security-admin/db/oracle/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..cae2927
--- /dev/null
+++ b/security-admin/db/oracle/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,54 @@
+-- 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.
+DECLARE
+        v_count number:=0;
+BEGIN   
+        select count(*) into v_count from user_tab_cols where table_name='X_TAG_DEF' and column_name='TAG_ATTRS_DEF_TEXT';
+        if (v_count = 0) then 
+                execute immediate 'ALTER TABLE X_TAG_DEF ADD TAG_ATTRS_DEF_TEXT CLOB DEFAULT NULL NULL';
+        end if; 
+        select count(*) into v_count from user_tab_cols where table_name='X_TAG' and column_name='TAG_ATTRS_TEXT';
+        if (v_count = 0) then 
+                execute immediate 'ALTER TABLE X_TAG ADD TAG_ATTRS_TEXT CLOB DEFAULT NULL NULL';
+        end if; 
+        select count(*) into v_count from user_tab_cols where table_name='X_SERVICE_RESOURCE' and column_name='SERVICE_RESOURCE_ELEMENTS_TEXT';
+        if (v_count = 0) then 
+                execute immediate 'ALTER TABLE X_SERVICE_RESOURCE ADD SERVICE_RESOURCE_ELEMENTS_TEXT CLOB DEFAULT NULL NULL';
+        end if; 
+        select count(*) into v_count from user_tab_cols where table_name='X_SERVICE_RESOURCE' and column_name='TAGS_TEXT';
+        if (v_count = 0) then 
+                execute immediate 'ALTER TABLE X_SERVICE_RESOURCE ADD TAGS_TEXT CLOB DEFAULT NULL NULL';
+        end if; 
+        commit; 
+END;/
+
+CREATE OR REPLACE PROCEDURE removeConstraints(ObjName IN varchar2) IS
+BEGIN
+FOR rec IN(
+select owner, constraint_name
+from all_constraints
+where owner = sys_context('userenv','current_schema')
+and table_name = ObjName
+and constraint_type = 'R')
+LOOP
+execute immediate 'ALTER TABLE ' || rec.owner || '.' || ObjName || ' DROP CONSTRAINT ' || rec.constraint_name;
+END LOOP;
+END;/
+/
+
+CALL removeConstraints('X_TAG_ATTR_DEF');
+CALL removeConstraints('X_TAG_ATTR');
+CALL removeConstraints('X_SERVICE_RESOURCE_ELEMENT');
+CALL removeConstraints('X_SERVICE_RESOURCE_ELEMENT_VAL');

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..1414fe3
--- /dev/null
+++ b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,198 @@
+-- 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_policy_ref_resource CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+CREATE SEQUENCE x_policy_ref_resource_seq;
+CREATE TABLE x_policy_ref_resource(
+id BIGINT DEFAULT nextval('x_policy_ref_resource_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+resource_def_id BIGINT NOT NULL,
+resource_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY(resource_def_id) REFERENCES x_resource_def(id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+CREATE SEQUENCE x_policy_ref_access_type_seq;
+CREATE TABLE x_policy_ref_access_type(
+id BIGINT DEFAULT nextval('x_policy_ref_access_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+access_def_id BIGINT NOT NULL,
+access_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY(access_def_id) REFERENCES x_access_type_def(id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_condition_seq;
+CREATE SEQUENCE x_policy_ref_condition_seq;
+CREATE TABLE x_policy_ref_condition(
+id BIGINT DEFAULT nextval('x_policy_ref_condition_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+condition_def_id BIGINT NOT NULL,
+condition_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY(condition_def_id) REFERENCES x_policy_condition_def(id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+CREATE SEQUENCE x_policy_ref_datamask_type_seq;
+CREATE TABLE x_policy_ref_datamask_type(
+id BIGINT DEFAULT nextval('x_policy_ref_datamask_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+datamask_def_id BIGINT NOT NULL,
+datamask_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmk_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_dmk_FK_dmk_def_id FOREIGN KEY(datamask_def_id) REFERENCES x_datamask_type_def(id),
+CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_dmk_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+CREATE SEQUENCE x_policy_ref_user_seq;
+CREATE TABLE x_policy_ref_user(
+id BIGINT DEFAULT nextval('x_policy_ref_user_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+user_id BIGINT NOT NULL,
+user_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+CREATE SEQUENCE x_policy_ref_group_seq;
+CREATE TABLE x_policy_ref_group(
+id BIGINT DEFAULT nextval('x_policy_ref_group_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+group_id BIGINT NOT NULL,
+group_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION add_x_policy_json()
+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_policy') and attname='policy_text';
+   IF v_column_exists = 0 THEN
+     ALTER TABLE x_policy ADD COLUMN policy_text TEXT DEFAULT NULL NULL;
+   END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select add_x_policy_json();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+  OPEN curs;
+  loop
+	FETCH curs INTO tableName,constraintName;
+	EXIT WHEN NOT FOUND;
+	query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+	execute query;
+  end loop;
+  close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+	perform remove_foreign_key('x_policy_item');
+	perform remove_foreign_key('x_policy_item_access');
+	perform remove_foreign_key('x_policy_item_condition');
+	perform remove_foreign_key('x_policy_item_datamask');
+	perform remove_foreign_key('x_policy_item_group_perm');
+	perform remove_foreign_key('x_policy_resource');
+	perform remove_foreign_key('x_policy_resource_map');
+	perform remove_foreign_key('x_policy_item_user_perm');
+	perform remove_foreign_key('x_policy_item_rowfilter');
+
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';
+

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..e5ed272
--- /dev/null
+++ b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,79 @@
+-- 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.
+
+-- function denormalize_tag_tables()
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION denormalize_tag_tables()
+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_tag_def') and attname='tag_attrs_def_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_tag_def ADD COLUMN tag_attrs_def_text TEXT DEFAULT NULL NULL;
+ END IF;
+  select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_tag') and attname='tag_attrs_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_tag ADD COLUMN tag_attrs_text TEXT DEFAULT NULL NULL;
+ END IF;
+  select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='service_resource_elements_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_service_resource ADD COLUMN service_resource_elements_text TEXT DEFAULT NULL NULL;
+ END IF;
+   select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='tags_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_service_resource ADD COLUMN tags_text TEXT DEFAULT NULL NULL;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select denormalize_tag_tables();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+  OPEN curs;
+  loop
+	FETCH curs INTO tableName,constraintName;
+	EXIT WHEN NOT FOUND;
+	query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+	execute query;
+  end loop;
+  close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+	perform remove_foreign_key('x_tag_attr_def');
+	perform remove_foreign_key('x_tag_attr');
+	perform remove_foreign_key('x_service_resource_element');
+	perform remove_foreign_key('x_service_resource_element_val');
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..5da4538
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,179 @@
+
+-- 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.
+GO
+create table dbo.x_policy_ref_resource (
+	id bigint IDENTITY NOT NULL,
+	guid varchar(1024) DEFAULT NULL NULL,
+	create_time datetime DEFAULT NULL NULL,
+	update_time datetime DEFAULT NULL NULL,
+	added_by_id bigint DEFAULT NULL NULL,
+	upd_by_id bigint DEFAULT NULL NULL,
+	policy_id bigint NOT NULL,
+	resource_def_id bigint NOT NULL,
+	resource_name varchar(4000) DEFAULT NULL NULL,
+	CONSTRAINT x_policy_ref_res_PK_id PRIMARY KEY CLUSTERED(id),
+	CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE NONCLUSTERED (policy_id, resource_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_access_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        access_def_id bigint NOT NULL,
+        access_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_acc_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE NONCLUSTERED (policy_id, access_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_condition (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        condition_def_id bigint NOT NULL,
+        condition_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_cond_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE NONCLUSTERED (policy_id, condition_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_datamask_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        datamask_def_id bigint NOT NULL,
+        datamask_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_dmk_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE NONCLUSTERED (policy_id, datamask_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_user (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        user_id bigint NOT NULL,
+        user_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_user_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE NONCLUSTERED (policy_id, user_id)
+)
+GO
+
+create table dbo.x_policy_ref_group (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        group_id bigint NOT NULL,
+        group_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_group_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE NONCLUSTERED (policy_id, group_id)
+)
+GO
+
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname='policy_text') THEN
+	ALTER TABLE dbo.x_policy ADD (policy_text text DEFAULT NULL NULL);
+END IF;
+GO
+
+IF EXISTS (
+    SELECT  1
+    FROM    sysobjects
+    WHERE   NAME = 'removeForeignKeyConstraint'
+    AND     TYPE = 'P'
+)
+BEGIN
+    drop procedure dbo.removeForeignKeyConstraint
+END
+GO
+
+CREATE PROCEDURE dbo.removeForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+        DECLARE @stmt VARCHAR(300)
+        DECLARE cur CURSOR FOR
+                select 'alter table dbo.' + table_name + ' drop constraint ' + role
+                from SYS.SYSFOREIGNKEYS
+                where foreign_creator ='dbo' and foreign_tname = table_name
+
+        OPEN cur WITH HOLD
+                fetch cur into @stmt
+                if (@@sqlstatus = 2)
+                BEGIN
+                        close cur
+                        DEALLOCATE CURSOR cur
+                END
+
+                WHILE (@@sqlstatus = 0)
+                BEGIN
+
+                        execute(@stmt)
+                        fetch cur into @stmt
+                END
+        close cur
+        DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_access')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_condition')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_datamask')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_group_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_user_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_rowfilter')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource_map')
+GO
+
+exit

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..1fdbfaa
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,71 @@
+-- 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 NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_tag_def' and cname = 'tag_attrs_def_text') THEN
+		ALTER TABLE dbo.x_tag_def ADD tag_attrs_def_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_tag' and cname = 'tag_attrs_text') THEN
+		ALTER TABLE dbo.x_tag ADD tag_attrs_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'service_resource_elements_text') THEN
+		ALTER TABLE dbo.x_service_resource ADD service_resource_elements_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'tags_text') THEN
+		ALTER TABLE dbo.x_service_resource ADD tags_text text DEFAULT NULL NULL;
+END IF;
+GO
+
+CREATE PROCEDURE dbo.removeTagForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+        DECLARE @stmt VARCHAR(300)
+        DECLARE cur CURSOR FOR
+                select 'alter table dbo.' + table_name + ' drop constraint ' + role
+                from SYS.SYSFOREIGNKEYS
+                where foreign_creator ='dbo' and foreign_tname = table_name
+
+        OPEN cur WITH HOLD
+                fetch cur into @stmt
+                if (@@sqlstatus = 2)
+                BEGIN
+                        close cur
+                        DEALLOCATE CURSOR cur
+                END
+
+                WHILE (@@sqlstatus = 0)
+                BEGIN
+
+                        execute(@stmt)
+                        fetch cur into @stmt
+                END
+        close cur
+        DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr_def')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element_val')
+GO
+
+exit

http://git-wip-us.apache.org/repos/asf/ranger/blob/c84b98fb/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
----------------------------------------------------------------------
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 bf73c34..d33dae0 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
@@ -253,6 +253,78 @@ IF (OBJECT_ID('x_enum_def_FK_defid') IS NOT NULL)
 BEGIN
     ALTER TABLE [dbo].[x_enum_def] DROP CONSTRAINT x_enum_def_FK_defid
 END
+IF (OBJECT_ID('x_policy_ref_resource_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_resource_FK_res_def_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_res_def_id
+END
+IF (OBJECT_ID('x_policy_ref_resource') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_resource]
+END
+IF (OBJECT_ID('x_policy_ref_access_type_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_access_type_FK_access_def_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_access_def_id
+END
+IF (OBJECT_ID('x_policy_ref_access_type') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_access_type]
+END
+IF (OBJECT_ID('x_policy_ref_condition_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_condition_FK_condition_def_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_condition_def_id
+END
+IF (OBJECT_ID('x_policy_ref_condition') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_condition]
+END
+IF (OBJECT_ID('x_policy_ref_datamask_type_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_datamask_type_FK_datamask_def_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_datamask_def_id
+END
+IF (OBJECT_ID('x_policy_ref_datamask_type') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_datamask_type]
+END
+IF (OBJECT_ID('x_policy_ref_user_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_user_FK_user_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_user_id
+END
+IF (OBJECT_ID('x_policy_ref_user') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_user]
+END
+IF (OBJECT_ID('x_policy_ref_group_FK_policy_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_policy_id
+END
+IF (OBJECT_ID('x_policy_ref_group_FK_group_id') IS NOT NULL)
+BEGIN
+  ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_group_id
+END
+IF (OBJECT_ID('x_policy_ref_group') IS NOT NULL)
+BEGIN
+  DROP TABLE [dbo].[x_policy_ref_group]
+END
 IF (OBJECT_ID('x_enum_element_def_FK_defid') IS NOT NULL)
 BEGIN
     ALTER TABLE [dbo].[x_enum_element_def] DROP CONSTRAINT x_enum_element_def_FK_defid
@@ -937,7 +1009,7 @@ PRIMARY KEY CLUSTERED
 CONSTRAINT [[x_group_users$x_group_users_UK_uid_gname] UNIQUE NONCLUSTERED
 (
         [user_id] ASC,
-	[group_name] ASC
+        [group_name] ASC
 )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 
@@ -1147,6 +1219,7 @@ CREATE TABLE [dbo].[x_policy] (
         [resource_signature] [varchar](128) DEFAULT NULL NULL,
         [is_enabled] [tinyint] DEFAULT 0 NOT NULL,
         [is_audit_enabled] [tinyint] DEFAULT 0 NOT NULL,
+        [policy_text] [nvarchar](max) DEFAULT NULL NULL,
 PRIMARY KEY CLUSTERED
 (
         [id] ASC
@@ -1587,6 +1660,7 @@ CREATE TABLE [dbo].[x_tag_def](
         [name] [varchar](255) NOT NULL,
         [source] [varchar](128) DEFAULT NULL NULL,
         [is_enabled] [tinyint] DEFAULT 0 NOT NULL,
+        [tag_attrs_def_text] [nvarchar](max) DEFAULT NULL NULL,
         PRIMARY KEY CLUSTERED
 (
         [id] ASC
@@ -1613,6 +1687,7 @@ CREATE TABLE [dbo].[x_tag](
         [version] [bigint] DEFAULT NULL NULL,
         [type] [bigint] NOT NULL,
         [owned_by] [smallint] DEFAULT 0 NOT NULL,
+        [tag_attrs_text] [nvarchar](max) DEFAULT NULL NULL,
         PRIMARY KEY CLUSTERED
 (
         [id] ASC
@@ -1636,6 +1711,8 @@ CREATE TABLE [dbo].[x_service_resource](
         [service_id] [bigint] NOT NULL,
         [resource_signature] [varchar](128) DEFAULT NULL NULL,
         [is_enabled] [tinyint] DEFAULT 1 NOT NULL,
+        [service_resource_elements_text] [nvarchar](max) DEFAULT NULL NULL,
+        [tags_text] [nvarchar](max) DEFAULT NULL NULL,
         PRIMARY KEY CLUSTERED
 (
         [id] ASC
@@ -1829,6 +1906,138 @@ CONSTRAINT [x_plugin_info$x_plugin_info_UK] UNIQUE NONCLUSTERED
 SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_resource] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [resource_def_id] [bigint] NOT NULL,
+  [resource_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+  CONSTRAINT [x_policy_ref_resource$x_policy_ref_resource_UK] UNIQUE NONCLUSTERED
+  (
+	[policy_id] ASC, [resource_def_id] ASC
+  )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_access_type] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [access_def_id] [bigint] NOT NULL,
+  [access_type_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+    CONSTRAINT [x_policy_ref_access_type$x_policy_ref_access_type_UK] UNIQUE NONCLUSTERED
+(
+        [policy_id] ASC, [access_def_id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_condition] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [condition_def_id] [bigint] NOT NULL,
+  [condition_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+    CONSTRAINT [x_policy_ref_condition$x_policy_ref_condition_UK] UNIQUE NONCLUSTERED
+(
+        [policy_id] ASC, [condition_def_id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_datamask_type] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [datamask_def_id] [bigint] NOT NULL,
+  [datamask_type_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+    CONSTRAINT [x_policy_ref_datamask_type$x_policy_ref_datamask_type_UK] UNIQUE NONCLUSTERED
+(
+        [policy_id] ASC, [datamask_def_id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_user] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [user_id] [bigint] NOT NULL,
+  [user_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+    CONSTRAINT [x_policy_ref_user$x_policy_ref_user_UK] UNIQUE NONCLUSTERED
+(
+        [policy_id] ASC, [user_id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
+CREATE TABLE [dbo].[x_policy_ref_group] (
+  [id] [bigint] IDENTITY (1, 1) NOT NULL,
+  [guid] [varchar](1024) DEFAULT NULL NULL,
+  [create_time] [datetime2] DEFAULT NULL NULL,
+  [update_time] [datetime2] DEFAULT NULL NULL,
+  [added_by_id] [bigint] DEFAULT NULL NULL,
+  [upd_by_id] [bigint] DEFAULT NULL NULL,
+  [policy_id] [bigint] NOT NULL,
+  [group_id] [bigint] NOT NULL,
+  [group_name] [varchar](4000) DEFAULT NULL NULL,
+  PRIMARY KEY CLUSTERED
+  (
+  [id] ASC
+  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+    CONSTRAINT [x_policy_ref_group$x_policy_ref_group_UK] UNIQUE NONCLUSTERED
+(
+        [policy_id] ASC, [group_id] ASC
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+SET ANSI_NULLS ON
+SET QUOTED_IDENTIFIER ON
+SET ANSI_PADDING ON
 
 ALTER TABLE [dbo].[x_asset]  WITH CHECK ADD  CONSTRAINT [x_asset_FK_added_by_id] FOREIGN KEY([added_by_id])
 REFERENCES [dbo].[x_portal_user] ([id])
@@ -2191,6 +2400,73 @@ ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_
 ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_item_rowfilter_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id])
 ALTER TABLE [dbo].[x_policy_item_rowfilter] WITH CHECK ADD CONSTRAINT [x_policy_item_rowfilter_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id])
 ALTER TABLE [dbo].[x_service_version_info] WITH CHECK ADD CONSTRAINT [x_service_version_info_service_id] FOREIGN KEY([service_id]) REFERENCES [dbo].[x_service] ([id])
+ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_resource_def_id] FOREIGN KEY ([resource_def_id])
+REFERENCES [dbo].[x_resource_def] ([id])
+ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_resource_def_id]
+ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_access_def_id] FOREIGN KEY ([access_def_id])
+REFERENCES [dbo].[x_access_type_def] ([id])
+ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_access_def_id]
+ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_condition_def_id] FOREIGN KEY ([condition_def_id])
+REFERENCES [dbo].[x_policy_condition_def] ([id])
+ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_condition_def_id]
+ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_datamask_def_id] FOREIGN KEY ([datamask_def_id])
+REFERENCES [dbo].[x_datamask_type_def] ([id])
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_datamask_def_id]
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_user_id] FOREIGN KEY ([user_id])
+REFERENCES [dbo].[x_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_user_id]
+ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_upd_by]
+ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_policy_id] FOREIGN KEY ([policy_id])
+REFERENCES [dbo].[x_policy] ([id])
+ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_policy_id]
+ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_group_id] FOREIGN KEY ([group_id])
+REFERENCES [dbo].[x_group] ([id])
+ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_group_id]
+ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_added_by] FOREIGN KEY ([added_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
+ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_added_by]
+ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_upd_by] FOREIGN KEY ([upd_by_id])
+REFERENCES [dbo].[x_portal_user] ([id])
 CREATE NONCLUSTERED INDEX [x_asset_cr_time] ON [x_asset]
 (
    [create_time] ASC
@@ -2999,6 +3275,8 @@ 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 ('028',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('029',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('040',CURRENT_TIMESTAMP,'Ranger 0.7.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 0.7.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 (1,3,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
@@ -3026,7 +3304,11 @@ 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 ('J10006',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10007',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10008',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10009',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10010',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10011',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10017',CURRENT_TIMESTAMP,'Ranger 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',CURRENT_TIMESTAMP,'Ranger 0.7.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 0.7.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
 CREATE VIEW [dbo].[vx_trx_log] AS


Mime
View raw message