RANGER-2203, RANGER-2219: Review and update database schema for ranger policies
and tag objects to minimize database queries/updates
Conflicts:
security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql
security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
Project: http://git-wip-us.apache.org/repos/asf/ranger/repo
Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/3af1f59d
Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/3af1f59d
Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/3af1f59d
Branch: refs/heads/ranger-1.1
Commit: 3af1f59d5276b5863920e7098ad678bc232a3ce0
Parents: 03b6c6a
Author: Abhay Kulkarni <[email protected]>
Authored: Thu Oct 18 15:22:48 2018 -0700
Committer: Mehul Parikh <[email protected]>
Committed: Mon Oct 22 12:10:58 2018 +0530
----------------------------------------------------------------------
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 | 213 ++--
.../patches/035-update-schema-for-x-policy.sql | 200 +++
.../patches/036-denormalize-tag-tables.sql | 82 ++
.../optimized/current/ranger_core_db_oracle.sql | 203 +--
.../patches/035-update-schema-for-x-policy.sql | 163 +++
.../patches/036-denormalize-tag-tables.sql | 54 +
.../current/ranger_core_db_postgres.sql | 225 ++--
.../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 | 509 +++++---
.../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, 7767 insertions(+), 3194 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/agents-common/pom.xml
----------------------------------------------------------------------
diff --git a/agents-common/pom.xml b/agents-common/pom.xml
index 3390e02..d2ee828 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/3af1f59d/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/3af1f59d/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/3af1f59d/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/3af1f59d/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/3af1f59d/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 3f23b00..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,4 +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
('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/3af1f59d/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/3af1f59d/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/3af1f59d/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 bafdb96..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,5 +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,'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/3af1f59d/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/3af1f59d/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');
