RANGER-2203, RANGER-2219: Review and update database schema for ranger policies and tag objects to minimize database queries/updates
Project: http://git-wip-us.apache.org/repos/asf/ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/d424b1a8 Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/d424b1a8 Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/d424b1a8 Branch: refs/heads/master Commit: d424b1a8dff7755b646279b10e11976d6ab88ef5 Parents: 64cb80b Author: Abhay Kulkarni <[email protected]> Authored: Thu Oct 18 15:22:48 2018 -0700 Committer: Abhay Kulkarni <[email protected]> Committed: Thu Oct 18 15:22:48 2018 -0700 ---------------------------------------------------------------------- agents-common/pom.xml | 12 + .../ranger/authorization/utils/JsonUtils.java | 15 +- .../ranger/plugin/model/RangerServiceDef.java | 42 +- .../apache/ranger/plugin/store/TagStore.java | 2 + .../scripts/ranger-admin-services.sh | 13 +- .../optimized/current/ranger_core_db_mysql.sql | 214 ++-- .../patches/035-update-schema-for-x-policy.sql | 200 +++ .../patches/036-denormalize-tag-tables.sql | 82 ++ .../optimized/current/ranger_core_db_oracle.sql | 204 +-- .../patches/035-update-schema-for-x-policy.sql | 163 +++ .../patches/036-denormalize-tag-tables.sql | 54 + .../current/ranger_core_db_postgres.sql | 226 ++-- .../patches/035-update-schema-for-x-policy.sql | 197 +++ .../patches/036-denormalize-tag-tables.sql | 79 ++ .../current/ranger_core_db_sqlanywhere.sql | 252 ++-- .../patches/035-update-schema-for-x-policy.sql | 180 +++ .../patches/036-denormalize-tag-tables.sql | 71 ++ .../current/ranger_core_db_sqlserver.sql | 510 +++++--- .../patches/035-update-schema-for-x-policy.sql | 453 +++++++ .../patches/036-denormalize-tag-tables.sql | 97 ++ security-admin/scripts/db_setup.py | 31 +- .../org/apache/ranger/biz/PolicyRefUpdater.java | 286 +++++ .../ranger/biz/RangerPolicyRetriever.java | 604 +++------ .../apache/ranger/biz/RangerTagDBRetriever.java | 478 ++----- .../org/apache/ranger/biz/ServiceDBStore.java | 642 ++-------- .../java/org/apache/ranger/biz/TagDBStore.java | 281 +---- .../common/RangerServicePoliciesCache.java | 1 + .../apache/ranger/db/RangerDaoManagerBase.java | 24 + .../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 - .../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 | 22 +- .../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 | 14 + .../java/org/apache/ranger/entity/XXTagDef.java | 14 + .../PatchForUpdatingPolicyJson_J10019.java | 1188 ++++++++++++++++++ .../patch/PatchForUpdatingTagsJson_J10020.java | 789 ++++++++++++ .../org/apache/ranger/rest/ServiceREST.java | 19 +- .../ranger/service/RangerAuditFields.java | 2 +- .../ranger/service/RangerPolicyServiceBase.java | 41 +- .../service/RangerServiceDefServiceBase.java | 2 +- .../service/RangerServiceResourceService.java | 194 +-- .../RangerServiceResourceServiceBase.java | 33 +- .../ranger/service/RangerTagDefService.java | 24 + .../ranger/service/RangerTagDefServiceBase.java | 2 +- .../apache/ranger/service/RangerTagService.java | 53 +- .../ranger/service/XPortalUserService.java | 8 - .../resources/META-INF/jpa_named_queries.xml | 381 +++--- .../apache/ranger/biz/TestServiceDBStore.java | 515 ++++---- .../org/apache/ranger/rest/TestServiceREST.java | 2 +- .../ranger/service/TestRangerTagDefService.java | 42 +- .../ranger/service/TestRangerTagService.java | 19 +- 84 files changed, 7766 insertions(+), 3199 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/agents-common/pom.xml ---------------------------------------------------------------------- diff --git a/agents-common/pom.xml b/agents-common/pom.xml index 09a32fa..4486af3 100644 --- a/agents-common/pom.xml +++ b/agents-common/pom.xml @@ -20,6 +20,18 @@ <artifactId>ranger-plugins-common</artifactId> <name>Common library for Plugins</name> <description>Plugins Common</description> + <build> + <plugins> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-compiler-plugin</artifactId> + <configuration> + <source>8</source> + <target>8</target> + </configuration> + </plugin> + </plugins> + </build> <packaging>jar</packaging> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/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 index bc4a8b5..74555ee 100644 --- 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 @@ -42,7 +42,6 @@ public class JsonUtils { static { gson = new GsonBuilder().setDateFormat("yyyyMMdd-HH:mm:ss.SSS-Z") - .setPrettyPrinting() .create(); } @@ -70,6 +69,20 @@ public class JsonUtils { 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; http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/agents-common/src/main/java/org/apache/ranger/plugin/model/RangerServiceDef.java ---------------------------------------------------------------------- diff --git a/agents-common/src/main/java/org/apache/ranger/plugin/model/RangerServiceDef.java b/agents-common/src/main/java/org/apache/ranger/plugin/model/RangerServiceDef.java index adafb99..50d84a4 100644 --- a/agents-common/src/main/java/org/apache/ranger/plugin/model/RangerServiceDef.java +++ b/agents-common/src/main/java/org/apache/ranger/plugin/model/RangerServiceDef.java @@ -1225,27 +1225,27 @@ public class RangerServiceDef extends RangerBaseModelObject implements java.io.S public static class RangerResourceDef implements java.io.Serializable { private static final long serialVersionUID = 1L; - private Long itemId; - private String name; - private String type; - private Integer level; - private String parent; - private Boolean mandatory; - private Boolean lookupSupported; - private Boolean recursiveSupported; - private Boolean excludesSupported; - private String matcher; - private Map<String, String> matcherOptions; - private String validationRegEx; - private String validationMessage; - private String uiHint; - private String label; - private String description; - private String rbKeyLabel; - private String rbKeyDescription; - private String rbKeyValidationMessage; - private Set<String> accessTypeRestrictions; - private Boolean isValidLeaf; + private Long itemId = null; + private String name = null; + private String type = null; + private Integer level = null; + private String parent = null; + private Boolean mandatory = null; + private Boolean lookupSupported = null; + private Boolean recursiveSupported = null; + private Boolean excludesSupported = null; + private String matcher = null; + private Map<String, String> matcherOptions = null; + private String validationRegEx = null; + private String validationMessage = null; + private String uiHint = null; + private String label = null; + private String description = null; + private String rbKeyLabel = null; + private String rbKeyDescription = null; + private String rbKeyValidationMessage = null; + private Set<String> accessTypeRestrictions = null; + private Boolean isValidLeaf = null; public RangerResourceDef() { this(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null); http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/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/d424b1a8/embeddedwebserver/scripts/ranger-admin-services.sh ---------------------------------------------------------------------- diff --git a/embeddedwebserver/scripts/ranger-admin-services.sh b/embeddedwebserver/scripts/ranger-admin-services.sh index b8ca6c7..990d3c7 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 @@ -145,11 +146,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/d424b1a8/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql index 32cf6db..70447fa 100644 --- a/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql +++ b/security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql @@ -38,6 +38,10 @@ DROP TABLE IF EXISTS `x_policy_item_access`; DROP TABLE IF EXISTS `x_policy_item`; DROP TABLE IF EXISTS `x_policy_resource_map`; DROP TABLE IF EXISTS `x_policy_resource`; +DROP TABLE IF EXISTS `x_policy_ref_resource`; +DROP TABLE IF EXISTS `x_policy_ref_access_type`; +DROP TABLE IF EXISTS `x_policy_ref_condition`; +DROP TABLE IF EXISTS `x_policy_ref_datamask_type`; DROP TABLE IF EXISTS `x_service_config_map`; DROP TABLE IF EXISTS `x_enum_element_def`; DROP TABLE IF EXISTS `x_enum_def`; @@ -58,6 +62,8 @@ DROP TABLE IF EXISTS `x_trx_log`; DROP TABLE IF EXISTS `x_resource`; DROP TABLE IF EXISTS `x_policy_export_audit`; DROP TABLE IF EXISTS `x_group_users`; +DROP TABLE IF EXISTS `x_policy_ref_user`; +DROP TABLE IF EXISTS `x_policy_ref_group`; DROP TABLE IF EXISTS `x_user`; DROP TABLE IF EXISTS `x_group_groups`; DROP TABLE IF EXISTS `x_group`; @@ -574,6 +580,7 @@ CREATE TABLE `x_policy` ( `is_audit_enabled` tinyint(1) NOT NULL DEFAULT '0', `policy_options` varchar(4000) NULL DEFAULT NULL, `policy_priority` int NOT NULL DEFAULT '0', +`policy_text` MEDIUMTEXT NULL DEFAULT NULL, primary key (`id`), KEY `x_policy_added_by_id` (`added_by_id`), KEY `x_policy_upd_by_id` (`upd_by_id`), @@ -986,6 +993,7 @@ CREATE TABLE IF NOT EXISTS `x_tag_def` ( `name` VARCHAR(255) NOT NULL, `source` VARCHAR(128) NULL DEFAULT NULL, `is_enabled` TINYINT(1) NOT NULL DEFAULT '0', +`tag_attrs_def_text` MEDIUMTEXT NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `x_tag_def_UK_guid` (`guid`), UNIQUE KEY `x_tag_def_UK_name` (`name`), @@ -1008,6 +1016,7 @@ CREATE TABLE IF NOT EXISTS `x_tag` ( `type` BIGINT(20) NOT NULL, `owned_by` SMALLINT DEFAULT 0 NOT NULL, `policy_options` varchar(4000) NULL DEFAULT NULL, +`tag_attrs_text` MEDIUMTEXT NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `x_tag_UK_guid` (`guid`), KEY `x_tag_IDX_type` (`type`), @@ -1031,6 +1040,8 @@ CREATE TABLE IF NOT EXISTS `x_service_resource` ( `service_id` BIGINT(20) NOT NULL, `resource_signature` varchar(128) NULL DEFAULT NULL, `is_enabled` TINYINT NOT NULL DEFAULT '1', +`service_resource_elements_text` MEDIUMTEXT NULL DEFAULT NULL, +`tags_text` MEDIUMTEXT NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `x_service_res_UK_guid` (`guid`), KEY `x_service_res_IDX_added_by_id` (`added_by_id`), @@ -1040,67 +1051,6 @@ CONSTRAINT `x_service_res_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES CONSTRAINT `x_service_res_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) )ROW_FORMAT=DYNAMIC; -- ----------------------------------------------------- --- Table `x_service_resource_element` --- ----------------------------------------------------- -CREATE TABLE IF NOT EXISTS `x_service_resource_element` ( -`id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME NULL DEFAULT NULL, -`update_time` DATETIME NULL DEFAULT NULL, -`added_by_id` BIGINT(20) NULL DEFAULT NULL, -`upd_by_id` BIGINT(20) NULL DEFAULT NULL, -`res_id` BIGINT(20) NOT NULL, -`res_def_id` BIGINT(20) NOT NULL, -`is_excludes` TINYINT(1) NOT NULL DEFAULT '0', -`is_recursive` TINYINT(1) NOT NULL DEFAULT '0', -PRIMARY KEY (`id`), -KEY `x_srvc_res_el_IDX_added_by_id` (`added_by_id`), -KEY `x_srvc_res_el_IDX_upd_by_id` (`upd_by_id`), -CONSTRAINT `x_srvc_res_el_FK_res_def_id` FOREIGN KEY (`res_def_id`) REFERENCES `x_resource_def` (`id`), -CONSTRAINT `x_srvc_res_el_FK_res_id` FOREIGN KEY (`res_id`) REFERENCES `x_service_resource` (`id`), -CONSTRAINT `x_srvc_res_el_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), -CONSTRAINT `x_srvc_res_el_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) -)ROW_FORMAT=DYNAMIC; --- ----------------------------------------------------- --- Table `x_tag_attr_def` --- ----------------------------------------------------- -CREATE TABLE IF NOT EXISTS `x_tag_attr_def` ( -`id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME NULL DEFAULT NULL, -`update_time` DATETIME NULL DEFAULT NULL, -`added_by_id` BIGINT(20) NULL DEFAULT NULL, -`upd_by_id` BIGINT(20) NULL DEFAULT NULL, -`tag_def_id` BIGINT(20) NOT NULL, -`name` VARCHAR(255) NOT NULL, -`type` VARCHAR(50) NOT NULL, -PRIMARY KEY (`id`), -KEY `x_tag_attr_def_IDX_tag_def_id` (`tag_def_id`), -KEY `x_tag_attr_def_IDX_added_by_id` (`added_by_id`), -KEY `x_tag_attr_def_IDX_upd_by_id` (`upd_by_id`), -CONSTRAINT `x_tag_attr_def_FK_tag_def_id` FOREIGN KEY (`tag_def_id`) REFERENCES `x_tag_def` (`id`), -CONSTRAINT `x_tag_attr_def_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), -CONSTRAINT `x_tag_attr_def_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) -)ROW_FORMAT=DYNAMIC; --- ----------------------------------------------------- --- Table `x_tag_attr` --- ----------------------------------------------------- -CREATE TABLE IF NOT EXISTS `x_tag_attr` ( -`id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME NULL DEFAULT NULL, -`update_time` DATETIME NULL DEFAULT NULL, -`added_by_id` BIGINT(20) NULL DEFAULT NULL, -`upd_by_id` BIGINT(20) NULL DEFAULT NULL, -`tag_id` BIGINT(20) NOT NULL, -`name` VARCHAR(255) NOT NULL, -`value` VARCHAR(512) NULL, -PRIMARY KEY (`id`), -KEY `x_tag_attr_IDX_tag_id` (`tag_id`), -KEY `x_tag_attr_IDX_added_by_id` (`added_by_id`), -KEY `x_tag_attr_IDX_upd_by_id` (`upd_by_id`), -CONSTRAINT `x_tag_attr_FK_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `x_tag` (`id`), -CONSTRAINT `x_tag_attr_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), -CONSTRAINT `x_tag_attr_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) -)ROW_FORMAT=DYNAMIC; --- ----------------------------------------------------- -- Table `x_tag_resource_map` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `x_tag_resource_map` ( @@ -1123,28 +1073,7 @@ CONSTRAINT `x_tag_res_map_FK_res_id` FOREIGN KEY (`res_id`) REFERENCES `x_servic CONSTRAINT `x_tag_res_map_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), CONSTRAINT `x_tag_res_map_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) )ROW_FORMAT=DYNAMIC; --- ----------------------------------------------------- --- Table `x_service_resource_element_val` --- ----------------------------------------------------- -CREATE TABLE IF NOT EXISTS `x_service_resource_element_val` ( -`id` BIGINT(20) NOT NULL AUTO_INCREMENT, -`create_time` DATETIME NULL DEFAULT NULL, -`update_time` DATETIME NULL DEFAULT NULL, -`added_by_id` BIGINT(20) NULL DEFAULT NULL, -`upd_by_id` BIGINT(20) NULL DEFAULT NULL, -`res_element_id` BIGINT(20) NOT NULL, -`value` VARCHAR(1024) NOT NULL, -`sort_order` tinyint(3) NULL DEFAULT '0', -PRIMARY KEY (`id`), -KEY `x_srvc_res_el_val_IDX_resel_id` (`res_element_id`), -KEY `x_srvc_res_el_val_IDX_addby_id` (`added_by_id`), -KEY `x_srvc_res_el_val_IDX_updby_id` (`upd_by_id`), -CONSTRAINT `x_srvc_res_el_val_FK_res_el_id` FOREIGN KEY (`res_element_id`) REFERENCES `x_service_resource_element` (`id`), -CONSTRAINT `x_srvc_res_el_val_FK_add_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`), -CONSTRAINT `x_srvc_res_el_val_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`) -)ROW_FORMAT=DYNAMIC; INSERT INTO `x_modules_master` VALUES (6,now(),now(),1,1,'Tag Based Policies',''); - CREATE TABLE `x_datamask_type_def` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , `guid` varchar(64) NULL DEFAULT NULL, @@ -1267,6 +1196,120 @@ CONSTRAINT `x_policy_label_map_FK_policy_id` FOREIGN KEY (`policy_id`) REFERENCE CONSTRAINT `x_policy_label_map_FK_policy_label_id` FOREIGN KEY (`policy_label_id`) REFERENCES `x_policy_label` (`id`) )ROW_FORMAT=DYNAMIC; +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; + CREATE INDEX x_service_config_def_IDX_def_id ON x_service_config_def(def_id); CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id); CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id); @@ -1288,8 +1331,6 @@ CREATE INDEX x_policy_item_user_perm_IDX_user_id ON x_policy_item_user_perm(user CREATE INDEX x_policy_item_group_perm_IDX_policy_item_id ON x_policy_item_group_perm(policy_item_id); CREATE INDEX x_policy_item_group_perm_IDX_group_id ON x_policy_item_group_perm(group_id); CREATE INDEX x_service_resource_IDX_service_id ON x_service_resource(service_id); -CREATE INDEX x_service_resource_element_IDX_res_id ON x_service_resource_element(res_id); -CREATE INDEX x_service_resource_element_IDX_res_def_id ON x_service_resource_element(res_def_id); CREATE INDEX x_datamask_type_def_IDX_def_id ON x_datamask_type_def(def_id); CREATE INDEX x_policy_item_datamask_IDX_policy_item_id ON x_policy_item_datamask(policy_item_id); CREATE INDEX x_policy_item_rowfilter_IDX_policy_item_id ON x_policy_item_rowfilter(policy_item_id); @@ -1346,6 +1387,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 ('032',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('034',UTC_TIMESTAMP(),'Ranger 2.0.0',UTC_TIMESTAMP(),'localhost','Y'); +INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',UTC_TIMESTAMP(),'Ranger 2.0.0',UTC_TIMESTAMP(),'localhost','Y'); +INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('036',UTC_TIMESTAMP(),'Ranger 2.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,3,UTC_TIMESTAMP(),UTC_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,UTC_TIMESTAMP(),UTC_TIMESTAMP(),1,1,1); @@ -1383,5 +1426,6 @@ 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 ('J10014',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10015',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); -INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10018',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); +INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10019',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); +INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('JAVA_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y'); http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/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..ee82ae3 --- /dev/null +++ b/security-admin/db/mysql/patches/035-update-schema-for-x-policy.sql @@ -0,0 +1,200 @@ +-- 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/d424b1a8/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/d424b1a8/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql index 2e577f3..8b51307 100644 --- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql +++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql @@ -52,6 +52,12 @@ CREATE SEQUENCE X_POLICY_ITEM_CONDITION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE CREATE SEQUENCE X_CONTEXT_ENRICHER_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_POLICY_ITEM_USER_PERM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_POLICY_ITEM_GROUP_PERM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; +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; CREATE SEQUENCE X_DATA_HIST_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_MODULES_MASTER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_USER_MODULE_PERM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; @@ -59,11 +65,7 @@ CREATE SEQUENCE X_GROUP_MODULE_PERM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCY CREATE SEQUENCE X_TAG_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_TAG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_SERVICE_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; -CREATE SEQUENCE X_SERVICE_RESOURCE_ELEMENT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; -CREATE SEQUENCE X_TAG_ATTR_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; -CREATE SEQUENCE X_TAG_ATTR_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_TAG_RESOURCE_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; -CREATE SEQUENCE X_SERVICE_RES_EL_VAL_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_DATAMASK_TYPE_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_POLICY_ITEM_DATAMASK_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE SEQUENCE X_POLICY_ITEM_ROWFILTER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; @@ -458,6 +460,7 @@ is_enabled NUMBER(1) DEFAULT '0' NOT NULL, is_audit_enabled NUMBER(1) DEFAULT '0' NOT NULL, policy_options varchar(4000) DEFAULT NULL NULL, policy_priority NUMBER(11) DEFAULT 0 NOT NULL, +policy_text CLOB DEFAULT NULL NULL, primary key (id), CONSTRAINT x_policy_UK_name_service UNIQUE (name,service), CONSTRAINT x_policy_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), @@ -860,6 +863,7 @@ version NUMBER(20) DEFAULT NULL NULL, name VARCHAR(255) NOT NULL, source VARCHAR(128) DEFAULT NULL NULL, is_enabled NUMBER(1) DEFAULT '0' NOT NULL, +TAG_ATTRS_DEF_TEXT CLOB DEFAULT NULL NULL, PRIMARY KEY (id), CONSTRAINT x_tag_def_UK_guid UNIQUE (guid), CONSTRAINT x_tag_def_UK_name UNIQUE (name), @@ -878,6 +882,7 @@ version NUMBER(20) DEFAULT NULL NULL, type NUMBER(20) NOT NULL, owned_by NUMBER(6) DEFAULT 0 NOT NULL, policy_options varchar(4000) DEFAULT NULL NULL, +TAG_ATTRS_TEXT CLOB DEFAULT NULL NULL, primary key (id), CONSTRAINT x_tag_UK_guid UNIQUE (guid), CONSTRAINT x_tag_FK_type FOREIGN KEY (type) REFERENCES x_tag_def (id), @@ -896,6 +901,8 @@ version NUMBER(20) DEFAULT NULL NULL, service_id NUMBER(20) NOT NULL, resource_signature VARCHAR(128) DEFAULT NULL NULL, is_enabled NUMBER(1) DEFAULT '1' NOT NULL, +SERVICE_RESOURCE_ELEMENTS_TEXT CLOB DEFAULT NULL NULL, +TAGS_TEXT CLOB DEFAULT NULL NULL, primary key (id), CONSTRAINT x_service_res_UK_guid UNIQUE (guid), CONSTRAINT x_service_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id), @@ -903,53 +910,6 @@ CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_p CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) ); -CREATE TABLE x_service_resource_element( -id NUMBER(20) NOT 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, -res_id NUMBER(20) NOT NULL, -res_def_id NUMBER(20) NOT NULL, -is_excludes NUMBER(1) DEFAULT '0' NOT NULL, -is_recursive NUMBER(1) DEFAULT '0' NOT NULL, -primary key (id), -CONSTRAINT x_srvc_res_el_FK_res_def_id FOREIGN KEY (res_def_id) REFERENCES x_resource_def (id), -CONSTRAINT x_srvc_res_el_FK_res_id FOREIGN KEY (res_id) REFERENCES x_service_resource (id), -CONSTRAINT x_srvc_res_el_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), -CONSTRAINT x_srvc_res_el_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) -); - -CREATE TABLE x_tag_attr_def( -id NUMBER(20) NOT 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, -tag_def_id NUMBER(20) NOT NULL, -name VARCHAR(255) NOT NULL, -type VARCHAR(50) NOT NULL, -primary key (id), -CONSTRAINT x_tag_attr_def_FK_tag_def_id FOREIGN KEY (tag_def_id) REFERENCES x_tag_def (id), -CONSTRAINT x_tag_attr_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), -CONSTRAINT x_tag_attr_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) -); - -CREATE TABLE x_tag_attr( -id NUMBER(20) NOT 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, -tag_id NUMBER(20) NOT NULL, -name VARCHAR(255) NOT NULL, -value VARCHAR(512) DEFAULT NULL NULL, -primary key (id), -CONSTRAINT x_tag_attr_FK_tag_id FOREIGN KEY (tag_id) REFERENCES x_tag (id), -CONSTRAINT x_tag_attr_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), -CONSTRAINT x_tag_attr_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) -); - CREATE TABLE x_tag_resource_map( id NUMBER(20) NOT NULL, guid VARCHAR(64) NOT NULL, @@ -967,20 +927,6 @@ CONSTRAINT x_tag_res_map_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_p CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) ); -CREATE TABLE x_service_resource_element_val( -id NUMBER(20) NOT 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, -res_element_id NUMBER(20) NOT NULL, -value VARCHAR(1024) NOT NULL, -sort_order NUMBER(3) DEFAULT '0' NULL, -primary key (id), -CONSTRAINT x_srvc_res_el_val_FK_res_el_id FOREIGN KEY (res_element_id) REFERENCES x_service_resource_element (id), -CONSTRAINT x_srvc_res_el_val_FK_add_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id), -CONSTRAINT x_srvc_res_el_val_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id) -); INSERT INTO x_modules_master VALUES(X_MODULES_MASTER_SEQ.NEXTVAL,SYSDATE,SYSDATE,1,1,'Tag Based Policies',''); commit; @@ -1115,6 +1061,115 @@ session_id VARCHAR(255) DEFAULT NULL, ); 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; + CREATE VIEW vx_trx_log AS select x_trx_log.id AS id,x_trx_log.create_time AS create_time,x_trx_log.update_time AS update_time,x_trx_log.added_by_id AS added_by_id,x_trx_log.upd_by_id AS upd_by_id,x_trx_log.class_type AS class_type,x_trx_log.object_id AS object_id,x_trx_log.parent_object_id AS parent_object_id,x_trx_log.parent_object_class_type AS parent_object_class_type,x_trx_log.attr_name AS attr_name,x_trx_log.parent_object_name AS parent_object_name,x_trx_log.object_name AS object_name,x_trx_log.prev_val AS prev_val,x_trx_log.new_val AS new_val,x_trx_log.trx_id AS trx_id,x_trx_log.action AS action,x_trx_log.sess_id AS sess_id,x_trx_log.req_id AS req_id,x_trx_log.sess_type AS sess_type from x_trx_log where id in(select min(x_trx_log.id) from x_trx_log group by x_trx_log.trx_id); commit; @@ -1227,21 +1282,10 @@ CREATE INDEX x_tag_IDX_added_by_id ON x_tag(added_by_id); CREATE INDEX x_tag_IDX_upd_by_id ON x_tag(upd_by_id); CREATE INDEX x_service_res_IDX_added_by_id ON x_service_resource(added_by_id); CREATE INDEX x_service_res_IDX_upd_by_id ON x_service_resource(upd_by_id); -CREATE INDEX x_srvc_res_el_IDX_added_by_id ON x_service_resource_element(added_by_id); -CREATE INDEX x_srvc_res_el_IDX_upd_by_id ON x_service_resource_element(upd_by_id); -CREATE INDEX x_tag_attr_def_IDX_tag_def_id ON x_tag_attr_def(tag_def_id); -CREATE INDEX x_tag_attr_def_IDX_added_by_id ON x_tag_attr_def(added_by_id); -CREATE INDEX x_tag_attr_def_IDX_upd_by_id ON x_tag_attr_def(upd_by_id); -CREATE INDEX x_tag_attr_IDX_tag_id ON x_tag_attr(tag_id); -CREATE INDEX x_tag_attr_IDX_added_by_id ON x_tag_attr(added_by_id); -CREATE INDEX x_tag_attr_IDX_upd_by_id ON x_tag_attr(upd_by_id); CREATE INDEX x_tag_res_map_IDX_tag_id ON x_tag_resource_map(tag_id); CREATE INDEX x_tag_res_map_IDX_res_id ON x_tag_resource_map(res_id); CREATE INDEX x_tag_res_map_IDX_added_by_id ON x_tag_resource_map(added_by_id); CREATE INDEX x_tag_res_map_IDX_upd_by_id ON x_tag_resource_map(upd_by_id); -CREATE INDEX x_srvc_res_el_val_IDX_resel_id ON x_service_resource_element_val(res_element_id); -CREATE INDEX x_srvc_res_el_val_IDX_addby_id ON x_service_resource_element_val(added_by_id); -CREATE INDEX x_srvc_res_el_val_IDX_updby_id ON x_service_resource_element_val(upd_by_id); CREATE INDEX x_service_conf_def_IDX_defid ON x_service_config_def(def_id); CREATE INDEX x_resource_def_IDX_def_id ON x_resource_def(def_id); CREATE INDEX x_access_type_def_IDX_def_id ON x_access_type_def(def_id); @@ -1263,8 +1307,6 @@ CREATE INDEX x_plc_itm_usr_perm_IDX_user_id ON x_policy_item_user_perm(user_id); CREATE INDEX x_plc_itm_grp_perm_IDX_pi_id ON x_policy_item_group_perm(policy_item_id); CREATE INDEX x_plc_itm_grp_perm_IDX_grp_id ON x_policy_item_group_perm(group_id); CREATE INDEX x_srvc_res_IDX_service_id ON x_service_resource(service_id); -CREATE INDEX x_srvc_res_el_IDX_res_def_id ON x_service_resource_element(res_id); -CREATE INDEX x_srvc_res_el_IDX_res_id ON x_service_resource_element(res_def_id); CREATE INDEX x_dm_type_def_IDX_def_id ON x_datamask_type_def(def_id); CREATE INDEX x_plc_item_dm_IDX_plc_item_id ON x_policy_item_datamask(policy_item_id); CREATE INDEX x_plc_item_rf_IDX_plc_item_id ON x_policy_item_rowfilter(policy_item_id); @@ -1321,6 +1363,9 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '031',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '032',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '033',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '034',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '035',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '036',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,1,3,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),1,1,1); INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,1,1,sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),1,1,1); @@ -1358,6 +1403,7 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10014',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10015',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10016',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); -INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10018',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10019',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); +INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'J10020',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval,'JAVA_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y'); commit; http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/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..11b4172 --- /dev/null +++ b/security-admin/db/oracle/patches/035-update-schema-for-x-policy.sql @@ -0,0 +1,163 @@ +-- 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/d424b1a8/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');
