ranger-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From me...@apache.org
Subject [8/9] ranger git commit: RANGER-2203, RANGER-2219: Review and update database schema for ranger policies and tag objects to minimize database queries/updates
Date Mon, 22 Oct 2018 06:42:43 GMT
http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
index 2bc58ac..a123911 100644
--- a/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
+++ b/security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql
@@ -465,6 +465,7 @@ is_enabled BOOLEAN DEFAULT '0' NOT NULL,
 is_audit_enabled BOOLEAN DEFAULT '0' NOT NULL,
 policy_options VARCHAR(4000) DEFAULT NULL NULL,
 policy_priority INT DEFAULT 0 NOT NULL,
+policy_text TEXT 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),
@@ -933,6 +934,7 @@ version BIGINT DEFAULT NULL NULL,
 name VARCHAR(255) NOT NULL,
 source VARCHAR(128) DEFAULT NULL NULL,
 is_enabled BOOLEAN DEFAULT '0' NOT NULL,
+tag_attrs_def_text TEXT DEFAULT NULL NULL,
 PRIMARY KEY (id),
 CONSTRAINT x_tag_def_UK_guid UNIQUE (guid),
 CONSTRAINT x_tag_def_UK_name UNIQUE (name),
@@ -955,6 +957,7 @@ version BIGINT DEFAULT NULL NULL,
 type BIGINT NOT NULL,
 owned_by SMALLINT DEFAULT 0 NOT NULL,
 policy_options VARCHAR(4000) DEFAULT NULL NULL,
+tag_attrs_text TEXT 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),
@@ -976,6 +979,8 @@ version BIGINT DEFAULT NULL NULL,
 service_id BIGINT NOT NULL,
 resource_signature VARCHAR(128) DEFAULT NULL NULL,
 is_enabled BOOLEAN DEFAULT '1' NOT NULL,
+service_resource_elements_text TEXT DEFAULT NULL NULL,
+tags_text TEXT 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),
@@ -983,63 +988,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)
 );
 
-DROP TABLE IF EXISTS x_service_resource_element CASCADE;
-DROP SEQUENCE IF EXISTS x_service_resource_element_seq;
-CREATE SEQUENCE x_service_resource_element_seq;
-CREATE TABLE x_service_resource_element(
-id BIGINT DEFAULT nextval('x_service_resource_element_seq'::regclass),
-create_time TIMESTAMP DEFAULT NULL NULL,
-update_time TIMESTAMP DEFAULT NULL NULL,
-added_by_id BIGINT DEFAULT NULL NULL,
-upd_by_id BIGINT DEFAULT NULL NULL,
-res_id BIGINT NOT NULL,
-res_def_id BIGINT NOT NULL,
-is_excludes BOOLEAN DEFAULT '0' NOT NULL,
-is_recursive BOOLEAN 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)
-);
-
-DROP TABLE IF EXISTS x_tag_attr_def CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_attr_def_seq;
-CREATE SEQUENCE x_tag_attr_def_seq;
-CREATE TABLE x_tag_attr_def(
-id BIGINT DEFAULT nextval('x_tag_attr_def_seq'::regclass),
-create_time TIMESTAMP DEFAULT NULL NULL,
-update_time TIMESTAMP DEFAULT NULL NULL,
-added_by_id BIGINT DEFAULT NULL NULL,
-upd_by_id BIGINT DEFAULT NULL NULL,
-tag_def_id BIGINT 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)
-);
-
-
-DROP TABLE IF EXISTS x_tag_attr CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_attr_seq;
-CREATE SEQUENCE x_tag_attr_seq;
-CREATE TABLE x_tag_attr(
-id BIGINT DEFAULT nextval('x_tag_attr_seq'::regclass),
-create_time TIMESTAMP DEFAULT NULL NULL,
-update_time TIMESTAMP DEFAULT NULL NULL,
-added_by_id BIGINT DEFAULT NULL NULL,
-upd_by_id BIGINT DEFAULT NULL NULL,
-tag_id BIGINT 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)
-);
-
 DROP TABLE IF EXISTS x_tag_resource_map CASCADE;
 DROP SEQUENCE IF EXISTS x_tag_resource_map_seq;
 CREATE SEQUENCE x_tag_resource_map_seq;
@@ -1060,24 +1008,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)
 );
 
-DROP TABLE IF EXISTS x_service_resource_element_val CASCADE;
-DROP SEQUENCE IF EXISTS x_service_res_el_val_seq;
-CREATE SEQUENCE x_service_res_el_val_seq;
-CREATE TABLE x_service_resource_element_val(
-id BIGINT DEFAULT nextval('x_service_res_el_val_seq'::regclass),
-create_time TIMESTAMP DEFAULT NULL NULL,
-update_time TIMESTAMP DEFAULT NULL NULL,
-added_by_id BIGINT DEFAULT NULL NULL,
-upd_by_id BIGINT DEFAULT NULL NULL,
-res_element_id BIGINT NOT NULL,
-value VARCHAR(1024) NOT NULL,
-sort_order SMALLINT 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)
-);
-
 DROP TABLE IF EXISTS x_policy_item_datamask;
 DROP SEQUENCE IF EXISTS x_policy_item_datamask_seq;
 DROP TABLE IF EXISTS x_datamask_type_def;
@@ -1231,6 +1161,133 @@ session_id varchar(255) DEFAULT NULL,
 primary key (id)
 );
 
+DROP TABLE IF EXISTS x_policy_ref_resource CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+CREATE SEQUENCE x_policy_ref_resource_seq;
+CREATE TABLE x_policy_ref_resource(
+id BIGINT DEFAULT nextval('x_policy_ref_resource_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+resource_def_id BIGINT NOT NULL,
+resource_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY(resource_def_id) REFERENCES x_resource_def(id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+CREATE SEQUENCE x_policy_ref_access_type_seq;
+CREATE TABLE x_policy_ref_access_type(
+id BIGINT DEFAULT nextval('x_policy_ref_access_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+access_def_id BIGINT NOT NULL,
+access_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY(access_def_id) REFERENCES x_access_type_def(id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_condition_seq;
+CREATE SEQUENCE x_policy_ref_condition_seq;
+CREATE TABLE x_policy_ref_condition(
+id BIGINT DEFAULT nextval('x_policy_ref_condition_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+condition_def_id BIGINT NOT NULL,
+condition_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY(condition_def_id) REFERENCES x_policy_condition_def(id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+CREATE SEQUENCE x_policy_ref_datamask_type_seq;
+CREATE TABLE x_policy_ref_datamask_type(
+id BIGINT DEFAULT nextval('x_policy_ref_datamask_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+datamask_def_id BIGINT NOT NULL,
+datamask_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmk_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_dmk_FK_dmk_def_id FOREIGN KEY(datamask_def_id) REFERENCES x_datamask_type_def(id),
+CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_dmk_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+CREATE SEQUENCE x_policy_ref_user_seq;
+CREATE TABLE x_policy_ref_user(
+id BIGINT DEFAULT nextval('x_policy_ref_user_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+user_id BIGINT NOT NULL,
+user_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+CREATE SEQUENCE x_policy_ref_group_seq;
+CREATE TABLE x_policy_ref_group(
+id BIGINT DEFAULT nextval('x_policy_ref_group_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+group_id BIGINT NOT NULL,
+group_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+
 CREATE INDEX xa_access_audit_added_by_id ON xa_access_audit(added_by_id);
 CREATE INDEX xa_access_audit_upd_by_id ON xa_access_audit(upd_by_id);
 CREATE INDEX xa_access_audit_cr_time ON xa_access_audit(create_time);
@@ -1340,21 +1397,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_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);
@@ -1376,8 +1422,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);
@@ -1434,6 +1478,9 @@ 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 ('031',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('032',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('034',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('036',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,3,current_timestamp,current_timestamp,1,1,1);
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,1,current_timestamp,current_timestamp,1,1,1);
@@ -1471,6 +1518,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 ('J10014',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10015',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10019',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('JAVA_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
 
 DROP VIEW IF EXISTS vx_trx_log;

http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..cb87cd0
--- /dev/null
+++ b/security-admin/db/postgres/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,197 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+--     http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+DROP TABLE IF EXISTS x_policy_ref_resource CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+CREATE SEQUENCE x_policy_ref_resource_seq;
+CREATE TABLE x_policy_ref_resource(
+id BIGINT DEFAULT nextval('x_policy_ref_resource_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+resource_def_id BIGINT NOT NULL,
+resource_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE (policy_id, resource_def_id),
+CONSTRAINT x_p_ref_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_res_FK_resource_def_id FOREIGN KEY(resource_def_id) REFERENCES x_resource_def(id),
+CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+CREATE SEQUENCE x_policy_ref_access_type_seq;
+CREATE TABLE x_policy_ref_access_type(
+id BIGINT DEFAULT nextval('x_policy_ref_access_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+access_def_id BIGINT NOT NULL,
+access_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE(policy_id, access_def_id),
+CONSTRAINT x_p_ref_acc_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_acc_FK_acc_def_id FOREIGN KEY(access_def_id) REFERENCES x_access_type_def(id),
+CONSTRAINT x_p_ref_acc_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_acc_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_condition_seq;
+CREATE SEQUENCE x_policy_ref_condition_seq;
+CREATE TABLE x_policy_ref_condition(
+id BIGINT DEFAULT nextval('x_policy_ref_condition_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+condition_def_id BIGINT NOT NULL,
+condition_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE(policy_id, condition_def_id),
+CONSTRAINT x_p_ref_cond_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_cond_FK_cond_def_id FOREIGN KEY(condition_def_id) REFERENCES x_policy_condition_def(id),
+CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+CREATE SEQUENCE x_policy_ref_datamask_type_seq;
+CREATE TABLE x_policy_ref_datamask_type(
+id BIGINT DEFAULT nextval('x_policy_ref_datamask_type_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+datamask_def_id BIGINT NOT NULL,
+datamask_type_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE(policy_id, datamask_def_id),
+CONSTRAINT x_p_ref_dmk_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_dmk_FK_dmk_def_id FOREIGN KEY(datamask_def_id) REFERENCES x_datamask_type_def(id),
+CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_dmk_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+CREATE SEQUENCE x_policy_ref_user_seq;
+CREATE TABLE x_policy_ref_user(
+id BIGINT DEFAULT nextval('x_policy_ref_user_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+user_id BIGINT NOT NULL,
+user_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE(policy_id, user_id),
+CONSTRAINT x_p_ref_usr_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_usr_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id),
+CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_usr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+CREATE SEQUENCE x_policy_ref_group_seq;
+CREATE TABLE x_policy_ref_group(
+id BIGINT DEFAULT nextval('x_policy_ref_group_seq'::regclass),
+guid VARCHAR(1024) DEFAULT NULL NULL,
+create_time TIMESTAMP DEFAULT NULL NULL,
+update_time TIMESTAMP DEFAULT NULL NULL,
+added_by_id BIGINT DEFAULT NULL NULL,
+upd_by_id BIGINT DEFAULT NULL NULL,
+policy_id BIGINT NOT NULL,
+group_id BIGINT NOT NULL,
+group_name varchar(4000) DEFAULT NULL,
+primary key(id),
+CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE(policy_id, group_id),
+CONSTRAINT x_p_ref_grp_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id),
+CONSTRAINT x_p_ref_grp_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id),
+CONSTRAINT x_p_ref_grp_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
+CONSTRAINT x_p_ref_grp_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
+);
+commit;
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION add_x_policy_json()
+RETURNS void AS $$
+DECLARE
+ v_column_exists integer := 0;
+BEGIN
+  select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname='policy_text';
+   IF v_column_exists = 0 THEN
+     ALTER TABLE x_policy ADD COLUMN policy_text TEXT DEFAULT NULL NULL;
+   END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select add_x_policy_json();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+  OPEN curs;
+  loop
+	FETCH curs INTO tableName,constraintName;
+	EXIT WHEN NOT FOUND;
+	query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+	execute query;
+  end loop;
+  close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+	perform remove_foreign_key('x_policy_item');
+	perform remove_foreign_key('x_policy_item_access');
+	perform remove_foreign_key('x_policy_item_condition');
+	perform remove_foreign_key('x_policy_item_datamask');
+	perform remove_foreign_key('x_policy_item_group_perm');
+	perform remove_foreign_key('x_policy_resource');
+	perform remove_foreign_key('x_policy_resource_map');
+	perform remove_foreign_key('x_policy_item_user_perm');
+	perform remove_foreign_key('x_policy_item_rowfilter');
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';
+

http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..e5ed272
--- /dev/null
+++ b/security-admin/db/postgres/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,79 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+--     http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+-- function denormalize_tag_tables()
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION denormalize_tag_tables()
+RETURNS void AS $$
+DECLARE
+ v_column_exists integer := 0;
+BEGIN
+ select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_tag_def') and attname='tag_attrs_def_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_tag_def ADD COLUMN tag_attrs_def_text TEXT DEFAULT NULL NULL;
+ END IF;
+  select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_tag') and attname='tag_attrs_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_tag ADD COLUMN tag_attrs_text TEXT DEFAULT NULL NULL;
+ END IF;
+  select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='service_resource_elements_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_service_resource ADD COLUMN service_resource_elements_text TEXT DEFAULT NULL NULL;
+ END IF;
+   select count(*) into v_column_exists from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname='tags_text';
+ IF v_column_exists = 0 THEN
+  ALTER TABLE x_service_resource ADD COLUMN tags_text TEXT DEFAULT NULL NULL;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+select denormalize_tag_tables();
+select 'delimiter end';
+
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION remove_foreign_key(objName varchar(4000))
+RETURNS void AS $$
+declare
+ tableName VARCHAR(256);
+ constraintName VARCHAR(512);
+ query varchar(4000);
+ curs CURSOR FOR SELECT table_name,constraint_name from information_schema.key_column_usage where constraint_catalog=current_catalog and table_name=objName and position_in_unique_constraint notnull;
+begin
+  OPEN curs;
+  loop
+	FETCH curs INTO tableName,constraintName;
+	EXIT WHEN NOT FOUND;
+	query :='ALTER TABLE ' || objName || ' drop constraint ' || constraintName;
+	execute query;
+  end loop;
+  close curs;
+END;
+$$ LANGUAGE plpgsql;
+select 'delimiter end';
+
+CREATE OR REPLACE FUNCTION removekeys()
+RETURNS void AS
+$$
+BEGIN
+	perform remove_foreign_key('x_tag_attr_def');
+	perform remove_foreign_key('x_tag_attr');
+	perform remove_foreign_key('x_service_resource_element');
+	perform remove_foreign_key('x_service_resource_element_val');
+END;
+$$ LANGUAGE plpgsql;
+select removekeys();
+
+select 'delimiter end';

http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
index 1b64eea..142302a 100644
--- a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
+++ b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
@@ -343,6 +343,7 @@ create table dbo.x_policy (
 	is_audit_enabled tinyint DEFAULT 0 NOT NULL,
 	policy_options varchar(4000) DEFAULT NULL NULL,
 	policy_priority int DEFAULT 0 NOT NULL,
+	policy_text text DEFAULT NULL NULL,
 	CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id),
 	CONSTRAINT x_policy_UK_name_service UNIQUE NONCLUSTERED (name,service)
 )
@@ -678,6 +679,7 @@ CREATE TABLE dbo.x_tag_def(
 	name varchar(255) NOT NULL,
 	source varchar(128) DEFAULT NULL NULL,
 	is_enabled tinyint DEFAULT 0 NOT NULL,
+	tag_attrs_def_text text DEFAULT NULL NULL,
 	CONSTRAINT x_tag_def_PK_id PRIMARY KEY CLUSTERED(id),
 	CONSTRAINT x_tag_def_UK_guid UNIQUE NONCLUSTERED (guid),
 	CONSTRAINT x_tag_def_UK_name UNIQUE NONCLUSTERED (name)
@@ -694,6 +696,7 @@ CREATE TABLE dbo.x_tag(
 	type bigint NOT NULL,
 	owned_by smallint DEFAULT 0 NOT NULL,
 	policy_options varchar(4000) DEFAULT NULL NULL,
+	tag_attrs_text text DEFAULT NULL NULL,
 	CONSTRAINT x_tag_PK_id PRIMARY KEY CLUSTERED(id),
 	CONSTRAINT x_tag_UK_guid UNIQUE NONCLUSTERED (guid)
 )
@@ -709,47 +712,12 @@ CREATE TABLE dbo.x_service_resource(
 	service_id bigint NOT NULL,
 	resource_signature varchar(128) DEFAULT NULL NULL,
 	is_enabled tinyint DEFAULT 1 NOT NULL,
+	service_resource_elements_text text DEFAULT NULL NULL,
+	tags_text text DEFAULT NULL NULL,
 	CONSTRAINT x_service_res_PK_id PRIMARY KEY CLUSTERED(id),
 	CONSTRAINT x_service_res_UK_guid UNIQUE NONCLUSTERED (guid)
 )
 GO
-CREATE TABLE dbo.x_service_resource_element(
-	id bigint IDENTITY NOT NULL,
-	create_time datetime DEFAULT NULL NULL,
-	update_time datetime DEFAULT NULL NULL,
-	added_by_id bigint DEFAULT NULL NULL,
-	upd_by_id bigint DEFAULT NULL NULL,
-	res_id bigint NOT NULL,
-	res_def_id bigint NOT NULL,
-	is_excludes tinyint DEFAULT 0 NOT NULL,
-	is_recursive tinyint DEFAULT 0 NOT NULL,
-	CONSTRAINT x_srvc_res_el_PK_id PRIMARY KEY CLUSTERED(id)
-)
-GO
-CREATE TABLE dbo.x_tag_attr_def(
-	id bigint IDENTITY NOT NULL,
-	create_time datetime DEFAULT NULL NULL,
-	update_time datetime DEFAULT NULL NULL,
-	added_by_id bigint DEFAULT NULL NULL,
-	upd_by_id bigint DEFAULT NULL NULL,
-	tag_def_id bigint NOT NULL,
-	name varchar(255) NOT NULL,
-	type varchar(50) NOT NULL,
-	CONSTRAINT x_tag_attr_def_PK_id PRIMARY KEY CLUSTERED(id)
-)
-GO
-CREATE TABLE dbo.x_tag_attr(
-	id bigint IDENTITY NOT NULL,
-	create_time datetime DEFAULT NULL NULL,
-	update_time datetime DEFAULT NULL NULL,
-	added_by_id bigint DEFAULT NULL NULL,
-	upd_by_id bigint DEFAULT NULL NULL,
-	tag_id bigint NOT NULL,
-	name varchar(255) NOT NULL,
-	value varchar(512) DEFAULT NULL NULL,
-	CONSTRAINT x_tag_attr_PK_id PRIMARY KEY CLUSTERED(id)
-)
-GO
 CREATE TABLE dbo.x_tag_resource_map(
 	id bigint IDENTITY NOT NULL,
 	guid varchar(64) NOT NULL,
@@ -763,18 +731,6 @@ CREATE TABLE dbo.x_tag_resource_map(
 	CONSTRAINT x_tag_res_map_UK_guid UNIQUE NONCLUSTERED (guid)
 )
 GO
-CREATE TABLE dbo.x_service_resource_element_val(
-	id bigint IDENTITY NOT NULL,
-	create_time datetime DEFAULT NULL NULL,
-	update_time datetime DEFAULT NULL NULL,
-	added_by_id bigint DEFAULT NULL NULL,
-	upd_by_id bigint DEFAULT NULL NULL,
-	res_element_id bigint NOT NULL,
-	value varchar(1024) NOT NULL,
-	sort_order tinyint DEFAULT 0  NULL,
-	CONSTRAINT x_srvc_res_el_val_PK_id PRIMARY KEY CLUSTERED(id)
-)
-GO
 CREATE TABLE dbo.x_datamask_type_def(
 	id bigint IDENTITY NOT NULL,
 	guid varchar(64) DEFAULT NULL NULL,
@@ -886,6 +842,90 @@ CREATE TABLE dbo.x_ugsync_audit_info(
         CONSTRAINT x_ugsync_audit_info_PK_id PRIMARY KEY CLUSTERED(id)
 )
 GO
+create table dbo.x_policy_ref_resource (
+	id bigint IDENTITY NOT NULL,
+	guid varchar(1024) DEFAULT NULL NULL,
+	create_time datetime DEFAULT NULL NULL,
+	update_time datetime DEFAULT NULL NULL,
+	added_by_id bigint DEFAULT NULL NULL,
+	upd_by_id bigint DEFAULT NULL NULL,
+	policy_id bigint NOT NULL,
+	resource_def_id bigint NOT NULL,
+	resource_name varchar(4000) DEFAULT NULL NULL,
+	CONSTRAINT x_policy_ref_res_PK_id PRIMARY KEY CLUSTERED(id),
+	CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE NONCLUSTERED (policy_id, resource_def_id)
+)
+GO
+create table dbo.x_policy_ref_access_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        access_def_id bigint NOT NULL,
+        access_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_acc_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE NONCLUSTERED (policy_id, access_def_id)
+)
+GO
+create table dbo.x_policy_ref_condition (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        condition_def_id bigint NOT NULL,
+        condition_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_cond_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE NONCLUSTERED (policy_id, condition_def_id)
+)
+GO
+create table dbo.x_policy_ref_datamask_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        datamask_def_id bigint NOT NULL,
+        datamask_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_dmk_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE NONCLUSTERED (policy_id, datamask_def_id)
+)
+GO
+create table dbo.x_policy_ref_user (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        user_id bigint NOT NULL,
+        user_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_user_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE NONCLUSTERED (policy_id, user_id)
+)
+GO
+create table dbo.x_policy_ref_group (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        group_id bigint NOT NULL,
+        group_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_group_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE NONCLUSTERED (policy_id, group_id)
+)
+GO
 
 ALTER TABLE dbo.x_asset ADD CONSTRAINT x_asset_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user(id)
 GO
@@ -1123,26 +1163,6 @@ ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_upd_by_id FOR
 GO
 ALTER TABLE dbo.x_service_resource ADD CONSTRAINT x_service_res_FK_service_id FOREIGN KEY(service_id) REFERENCES dbo.x_service (id)
 GO
-ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_res_def_id FOREIGN KEY(res_def_id) REFERENCES dbo.x_resource_def (id)
-GO
-ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_res_id FOREIGN KEY(res_id) REFERENCES dbo.x_service_resource (id)
-GO
-ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_service_resource_element ADD CONSTRAINT x_srvc_res_el_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_tag_def_id FOREIGN KEY(tag_def_id) REFERENCES dbo.x_tag_def (id)
-GO
-ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_tag_attr_def ADD CONSTRAINT x_tag_attr_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_tag_id FOREIGN KEY(tag_id) REFERENCES dbo.x_tag (id)
-GO
-ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_tag_attr ADD CONSTRAINT x_tag_attr_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id)
-GO
 ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_tag_id FOREIGN KEY(tag_id) REFERENCES dbo.x_tag (id)
 GO
 ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_res_id FOREIGN KEY(res_id) REFERENCES dbo.x_service_resource (id)
@@ -1151,12 +1171,6 @@ ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_added_by_id F
 GO
 ALTER TABLE dbo.x_tag_resource_map ADD CONSTRAINT x_tag_res_map_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id)
 GO
-ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_res_el_id FOREIGN KEY(res_element_id) REFERENCES dbo.x_service_resource_element (id)
-GO
-ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_add_by_id FOREIGN KEY(added_by_id) REFERENCES dbo.x_portal_user (id)
-GO
-ALTER TABLE dbo.x_service_resource_element_val ADD CONSTRAINT x_srvc_res_el_val_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES dbo.x_portal_user (id)
-GO
 ALTER TABLE dbo.x_service ADD CONSTRAINT x_service_FK_tag_service FOREIGN KEY(tag_service) REFERENCES dbo.x_service (id)
 GO
 ALTER TABLE dbo.x_datamask_type_def ADD CONSTRAINT x_datamask_type_def_FK_def_id FOREIGN KEY(def_id) REFERENCES dbo.x_service_def (id)
@@ -1193,7 +1207,55 @@ ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map_FK_policy_i
 GO
 ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map_FK_policy_label_id FOREIGN KEY (policy_label_id) REFERENCES dbo.x_policy_label (id)
 GO
-ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT [x_policy_label_map$x_policy_label_map_pid_plid] UNIQUE (policy_id, policy_label_id)
+ALTER TABLE dbo.x_policy_label_map ADD CONSTRAINT x_policy_label_map$x_policy_label_map_pid_plid UNIQUE (policy_id, policy_label_id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_resource_def_id FOREIGN KEY (resource_def_id) REFERENCES dbo.x_resource_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_resource ADD CONSTRAINT x_policy_ref_resource_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_access_def_id FOREIGN KEY (access_def_id) REFERENCES dbo.x_access_type_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_access_type ADD CONSTRAINT x_policy_ref_access_type_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_condition_def_id FOREIGN KEY (condition_def_id) REFERENCES dbo.x_policy_condition_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_condition ADD CONSTRAINT x_policy_ref_condition_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_datamask_def_id FOREIGN KEY (datamask_def_id) REFERENCES dbo.x_datamask_type_def (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_datamask_type ADD CONSTRAINT x_policy_ref_datamask_type_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_user_id FOREIGN KEY (user_id) REFERENCES dbo.x_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_user ADD CONSTRAINT x_policy_ref_user_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_policy_id FOREIGN KEY (policy_id) REFERENCES dbo.x_policy (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_group_id FOREIGN KEY (group_id) REFERENCES dbo.x_group (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_added_by FOREIGN KEY (added_by_id) REFERENCES dbo.x_portal_user (id)
+GO
+ALTER TABLE dbo.x_policy_ref_group ADD CONSTRAINT x_policy_ref_group_FK_upd_by FOREIGN KEY (upd_by_id) REFERENCES dbo.x_portal_user (id)
 GO
 CREATE NONCLUSTERED INDEX x_asset_cr_time ON dbo.x_asset(create_time ASC)
 GO
@@ -1415,22 +1477,6 @@ CREATE NONCLUSTERED INDEX x_service_res_IDX_added_by_id ON dbo.x_service_resourc
 GO
 CREATE NONCLUSTERED INDEX x_service_res_IDX_upd_by_id ON dbo.x_service_resource(upd_by_id ASC)
 GO
-CREATE NONCLUSTERED INDEX x_srvc_res_el_IDX_added_by_id ON dbo.x_service_resource_element(added_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_srvc_res_el_IDX_upd_by_id ON dbo.x_service_resource_element(upd_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_tag_def_id ON dbo.x_tag_attr_def(tag_def_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_added_by_id ON dbo.x_tag_attr_def(added_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_def_IDX_upd_by_id ON dbo.x_tag_attr_def(upd_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_IDX_tag_id ON dbo.x_tag_attr(tag_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_IDX_added_by_id ON dbo.x_tag_attr(added_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_tag_attr_IDX_upd_by_id ON dbo.x_tag_attr(upd_by_id ASC)
-GO
 CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_tag_id ON dbo.x_tag_resource_map(tag_id ASC)
 GO
 CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_res_id ON dbo.x_tag_resource_map(res_id ASC)
@@ -1439,12 +1485,6 @@ CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_added_by_id ON dbo.x_tag_resource_ma
 GO
 CREATE NONCLUSTERED INDEX x_tag_res_map_IDX_upd_by_id ON dbo.x_tag_resource_map(upd_by_id ASC)
 GO
-CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_resel_id ON dbo.x_service_resource_element_val(res_element_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_addby_id ON dbo.x_service_resource_element_val(added_by_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_srvc_res_el_val_IDX_updby_id ON dbo.x_service_resource_element_val(upd_by_id ASC)
-GO
 CREATE NONCLUSTERED INDEX x_service_config_def_IDX_def_id ON dbo.x_service_config_def(def_id ASC)
 GO
 CREATE NONCLUSTERED INDEX x_resource_def_IDX_def_id ON dbo.x_resource_def(def_id ASC)
@@ -1487,10 +1527,6 @@ CREATE NONCLUSTERED INDEX x_policy_item_group_perm_IDX_group_id ON dbo.x_policy_
 GO
 CREATE NONCLUSTERED INDEX x_service_resource_IDX_service_id ON dbo.x_service_resource(service_id ASC)
 GO
-CREATE NONCLUSTERED INDEX x_service_resource_element_IDX_res_id ON dbo.x_service_resource_element(res_id ASC)
-GO
-CREATE NONCLUSTERED INDEX x_service_resource_element_IDX_res_def_id ON dbo.x_service_resource_element(res_def_id ASC)
-GO
 CREATE NONCLUSTERED INDEX x_datamask_type_def_IDX_def_id ON dbo.x_datamask_type_def(def_id ASC)
 GO
 CREATE NONCLUSTERED INDEX x_policy_item_datamask_IDX_policy_item_id ON dbo.x_policy_item_datamask(policy_item_id ASC)
@@ -1589,6 +1625,12 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('033',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('034',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('035',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('036',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,3,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1);
@@ -1661,7 +1703,11 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10015',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016,CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10016',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10019',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('J10020',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('JAVA_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO

http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
new file mode 100644
index 0000000..24d072f
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/035-update-schema-for-x-policy.sql
@@ -0,0 +1,180 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+--     http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+GO
+create table dbo.x_policy_ref_resource (
+	id bigint IDENTITY NOT NULL,
+	guid varchar(1024) DEFAULT NULL NULL,
+	create_time datetime DEFAULT NULL NULL,
+	update_time datetime DEFAULT NULL NULL,
+	added_by_id bigint DEFAULT NULL NULL,
+	upd_by_id bigint DEFAULT NULL NULL,
+	policy_id bigint NOT NULL,
+	resource_def_id bigint NOT NULL,
+	resource_name varchar(4000) DEFAULT NULL NULL,
+	CONSTRAINT x_policy_ref_res_PK_id PRIMARY KEY CLUSTERED(id),
+	CONSTRAINT x_p_ref_res_UK_polId_resDefId UNIQUE NONCLUSTERED (policy_id, resource_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_access_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        access_def_id bigint NOT NULL,
+        access_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_acc_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_acc_UK_polId_accDefId UNIQUE NONCLUSTERED (policy_id, access_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_condition (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        condition_def_id bigint NOT NULL,
+        condition_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_cond_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_cond_UK_polId_cDefId UNIQUE NONCLUSTERED (policy_id, condition_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_datamask_type (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        datamask_def_id bigint NOT NULL,
+        datamask_type_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_dmk_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_dmk_UK_polId_dDefId UNIQUE NONCLUSTERED (policy_id, datamask_def_id)
+)
+GO
+
+create table dbo.x_policy_ref_user (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        user_id bigint NOT NULL,
+        user_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_user_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_usr_UK_polId_userId UNIQUE NONCLUSTERED (policy_id, user_id)
+)
+GO
+
+create table dbo.x_policy_ref_group (
+        id bigint IDENTITY NOT NULL,
+        guid varchar(1024) DEFAULT NULL NULL,
+        create_time datetime DEFAULT NULL NULL,
+        update_time datetime DEFAULT NULL NULL,
+        added_by_id bigint DEFAULT NULL NULL,
+        upd_by_id bigint DEFAULT NULL NULL,
+        policy_id bigint NOT NULL,
+        group_id bigint NOT NULL,
+        group_name varchar(4000) DEFAULT NULL NULL,
+        CONSTRAINT x_policy_ref_group_PK_id PRIMARY KEY CLUSTERED(id),
+		CONSTRAINT x_p_ref_grp_UK_polId_grpId UNIQUE NONCLUSTERED (policy_id, group_id)
+)
+GO
+
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname='policy_text') THEN
+	ALTER TABLE dbo.x_policy ADD (policy_text text DEFAULT NULL NULL);
+END IF;
+GO
+
+IF EXISTS (
+    SELECT  1
+    FROM    sysobjects
+    WHERE   NAME = 'removeForeignKeyConstraint'
+    AND     TYPE = 'P'
+)
+BEGIN
+    drop procedure dbo.removeForeignKeyConstraint
+END
+GO
+
+CREATE PROCEDURE dbo.removeForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+        DECLARE @stmt VARCHAR(300)
+        DECLARE cur CURSOR FOR
+                select 'alter table dbo.' + table_name + ' drop constraint ' + role
+                from SYS.SYSFOREIGNKEYS
+                where foreign_creator ='dbo' and foreign_tname = table_name
+
+        OPEN cur WITH HOLD
+                fetch cur into @stmt
+                if (@@sqlstatus = 2)
+                BEGIN
+                        close cur
+                        DEALLOCATE CURSOR cur
+                END
+
+                WHILE (@@sqlstatus = 0)
+                BEGIN
+
+                        execute(@stmt)
+                        fetch cur into @stmt
+                END
+        close cur
+        DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_access')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_condition')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_datamask')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_group_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_user_perm')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_item_rowfilter')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource')
+GO
+
+call dbo.removeForeignKeyConstraint('x_policy_resource_map')
+GO
+
+
+exit

http://git-wip-us.apache.org/repos/asf/ranger/blob/3af1f59d/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
new file mode 100644
index 0000000..1fdbfaa
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/036-denormalize-tag-tables.sql
@@ -0,0 +1,71 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+--     http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_tag_def' and cname = 'tag_attrs_def_text') THEN
+		ALTER TABLE dbo.x_tag_def ADD tag_attrs_def_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_tag' and cname = 'tag_attrs_text') THEN
+		ALTER TABLE dbo.x_tag ADD tag_attrs_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'service_resource_elements_text') THEN
+		ALTER TABLE dbo.x_service_resource ADD service_resource_elements_text text DEFAULT NULL NULL;
+END IF;
+IF NOT EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_service_resource' and cname = 'tags_text') THEN
+		ALTER TABLE dbo.x_service_resource ADD tags_text text DEFAULT NULL NULL;
+END IF;
+GO
+
+CREATE PROCEDURE dbo.removeTagForeignKeyConstraint (IN table_name varchar(100))
+AS
+BEGIN
+        DECLARE @stmt VARCHAR(300)
+        DECLARE cur CURSOR FOR
+                select 'alter table dbo.' + table_name + ' drop constraint ' + role
+                from SYS.SYSFOREIGNKEYS
+                where foreign_creator ='dbo' and foreign_tname = table_name
+
+        OPEN cur WITH HOLD
+                fetch cur into @stmt
+                if (@@sqlstatus = 2)
+                BEGIN
+                        close cur
+                        DEALLOCATE CURSOR cur
+                END
+
+                WHILE (@@sqlstatus = 0)
+                BEGIN
+
+                        execute(@stmt)
+                        fetch cur into @stmt
+                END
+        close cur
+        DEALLOCATE CURSOR cur
+
+END
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr_def')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_tag_attr')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element')
+GO
+
+call dbo.removeTagForeignKeyConstraint('x_service_resource_element_val')
+GO
+
+exit


Mime
View raw message