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 <[email protected]> Authored: Thu Oct 18 19:28:20 2018 -0700 Committer: Abhay Kulkarni <[email protected]> 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
