You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by ab...@apache.org on 2018/10/19 02:28:44 UTC
[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
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 <ak...@hortonworks.com>
Authored: Thu Oct 18 19:28:20 2018 -0700
Committer: Abhay Kulkarni <ak...@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