You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by pr...@apache.org on 2018/12/13 06:45:41 UTC
ranger git commit: RANGER-2291: Make optimized db schema script
idempotent for all DB Flavors
Repository: ranger
Updated Branches:
refs/heads/master 563c26448 -> 5b07a8dfd
RANGER-2291: Make optimized db schema script idempotent for all DB Flavors
Project: http://git-wip-us.apache.org/repos/asf/ranger/repo
Commit: http://git-wip-us.apache.org/repos/asf/ranger/commit/5b07a8df
Tree: http://git-wip-us.apache.org/repos/asf/ranger/tree/5b07a8df
Diff: http://git-wip-us.apache.org/repos/asf/ranger/diff/5b07a8df
Branch: refs/heads/master
Commit: 5b07a8dfd7a3ab0d0f10690657d74345e964f163
Parents: 563c264
Author: Pradeep <pr...@apache.org>
Authored: Tue Nov 27 11:14:25 2018 +0530
Committer: Pradeep <pr...@apache.org>
Committed: Thu Dec 13 12:15:19 2018 +0530
----------------------------------------------------------------------
.../optimized/current/ranger_core_db_mysql.sql | 59 ++--
.../optimized/current/ranger_core_db_oracle.sql | 173 +++++++++-
.../db/oracle/patches/009-updated_schema.sql | 64 ++++
.../db/oracle/patches/013-permissionmodel.sql | 29 ++
.../016-updated-schema-for-tag-based-policy.sql | 39 +++
.../db/oracle/patches/020-datamask-policy.sql | 29 ++
.../oracle/patches/022-split-service-table.sql | 25 ++
.../025-create-schema-for-plugin-info.sql | 25 ++
.../oracle/patches/030-policy-labels-schema.sql | 28 ++
...31-create-schema-for-usersync-audit-info.sql | 25 ++
.../patches/035-update-schema-for-x-policy.sql | 36 ++
.../current/ranger_core_db_postgres.sql | 248 +++++++------
.../current/ranger_core_db_sqlanywhere.sql | 346 ++++++++++++++-----
.../016-updated-schema-for-tag-based-policy.sql | 38 ++
.../sqlanywhere/patches/020-datamask-policy.sql | 28 ++
.../patches/022-split-service-table.sql | 24 ++
.../025-create-schema-for-plugin-info.sql | 24 ++
.../patches/030-policy-labels-schema.sql | 27 ++
...31-create-schema-for-usersync-audit-info.sql | 24 ++
.../patches/035-update-schema-for-x-policy.sql | 33 ++
.../current/ranger_core_db_sqlserver.sql | 40 ++-
21 files changed, 1110 insertions(+), 254 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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 a4fa130..f743a65 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
@@ -14,6 +14,15 @@
-- limitations under the License.
DROP VIEW IF EXISTS `vx_trx_log`;
+DROP TABLE IF EXISTS `x_policy_ref_group`;
+DROP TABLE IF EXISTS `x_policy_ref_user`;
+DROP TABLE IF EXISTS `x_policy_ref_datamask_type`;
+DROP TABLE IF EXISTS `x_policy_ref_condition`;
+DROP TABLE IF EXISTS `x_policy_ref_access_type`;
+DROP TABLE IF EXISTS `x_policy_ref_resource`;
+DROP TABLE IF EXISTS `x_ugsync_audit_info`;
+DROP TABLE IF EXISTS `x_policy_label_map`;
+DROP TABLE IF EXISTS `x_policy_label`;
DROP TABLE IF EXISTS `x_plugin_info`;
DROP TABLE IF EXISTS `x_service_version_info`;
DROP TABLE IF EXISTS `x_policy_item_rowfilter`;
@@ -38,10 +47,6 @@ 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`;
@@ -51,8 +56,6 @@ DROP TABLE IF EXISTS `x_access_type_def_grants`;
DROP TABLE IF EXISTS `x_access_type_def`;
DROP TABLE IF EXISTS `x_resource_def`;
DROP TABLE IF EXISTS `x_service_config_def`;
-DROP TABLE IF EXISTS `x_policy_label_map`;
-DROP TABLE IF EXISTS `x_policy_label`;
DROP TABLE IF EXISTS `x_policy`;
DROP TABLE IF EXISTS `x_service`;
DROP TABLE IF EXISTS `x_service_def`;
@@ -62,8 +65,6 @@ 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`;
@@ -86,27 +87,6 @@ CREATE TABLE `x_db_version_h` (
`active` ENUM('Y', 'N') DEFAULT 'Y'
)ROW_FORMAT=DYNAMIC;
-CREATE TABLE IF NOT EXISTS `x_ugsync_audit_info`(
-`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,
-`event_time` datetime NULL DEFAULT NULL,
-`user_name` varchar(255) NOT NULL,
-`sync_source` varchar(128) NOT NULL,
-`no_of_new_users` bigint(20) NOT NULL,
-`no_of_new_groups` bigint(20) NOT NULL,
-`no_of_modified_users` bigint(20) NOT NULL,
-`no_of_modified_groups` bigint(20) NOT NULL,
-`sync_source_info` varchar(4000) NOT NULL,
-`session_id` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `x_ugsync_audit_info_etime`(`event_time`),
- KEY `x_ugsync_audit_info_sync_src`(`sync_source`),
- KEY `x_ugsync_audit_info_uname`(`user_name`)
-)ROW_FORMAT=DYNAMIC;
-
CREATE TABLE `x_portal_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT NULL,
@@ -1196,6 +1176,27 @@ 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;
+CREATE TABLE IF NOT EXISTS `x_ugsync_audit_info`(
+`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,
+`event_time` datetime NULL DEFAULT NULL,
+`user_name` varchar(255) NOT NULL,
+`sync_source` varchar(128) NOT NULL,
+`no_of_new_users` bigint(20) NOT NULL,
+`no_of_new_groups` bigint(20) NOT NULL,
+`no_of_modified_users` bigint(20) NOT NULL,
+`no_of_modified_groups` bigint(20) NOT NULL,
+`sync_source_info` varchar(4000) NOT NULL,
+`session_id` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `x_ugsync_audit_info_etime`(`event_time`),
+ KEY `x_ugsync_audit_info_sync_src`(`sync_source`),
+ KEY `x_ugsync_audit_info_uname`(`user_name`)
+)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,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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 0949cbd..a7d6f73 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
@@ -13,7 +13,78 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
--- create sequences
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
+/
+
+call spdropsequence('SEQ_GEN_IDENTITY');
+call spdropsequence('X_ACCESS_AUDIT_SEQ');
+call spdropsequence('X_ASSET_SEQ');
+call spdropsequence('X_AUDIT_MAP_SEQ');
+call spdropsequence('X_AUTH_SESS_SEQ');
+call spdropsequence('X_CRED_STORE_SEQ');
+call spdropsequence('X_DB_BASE_SEQ');
+call spdropsequence('X_GROUP_SEQ');
+call spdropsequence('X_GROUP_USERS_SEQ');
+call spdropsequence('X_GROUP_GROUPS_SEQ');
+call spdropsequence('X_PERM_MAP_SEQ');
+call spdropsequence('X_POLICY_EXPORT_SEQ');
+call spdropsequence('X_PORTAL_USER_SEQ');
+call spdropsequence('X_PORTAL_USER_ROLE_SEQ');
+call spdropsequence('X_RESOURCE_SEQ');
+call spdropsequence('X_TRX_LOG_SEQ');
+call spdropsequence('X_USER_SEQ');
+call spdropsequence('V_TRX_LOG_SEQ');
+call spdropsequence('XA_ACCESS_AUDIT_SEQ');
+call spdropsequence('X_SERVICE_DEF_SEQ');
+call spdropsequence('X_SERVICE_SEQ');
+call spdropsequence('X_POLICY_SEQ');
+call spdropsequence('X_SERVICE_CONFIG_DEF_SEQ');
+call spdropsequence('X_ENUM_ELEMENT_DEF_SEQ');
+call spdropsequence('X_RESOURCE_DEF_SEQ');
+call spdropsequence('X_ACCESS_TYPE_DEF_SEQ');
+call spdropsequence('X_ACCESS_TYPE_DEF_GRANTS_SEQ');
+call spdropsequence('X_POLICY_CONDITION_DEF_SEQ');
+call spdropsequence('X_ENUM_DEF_SEQ');
+call spdropsequence('X_SERVICE_CONFIG_MAP_SEQ');
+call spdropsequence('X_POLICY_RESOURCE_SEQ');
+call spdropsequence('X_POLICY_RESOURCE_MAP_SEQ');
+call spdropsequence('X_POLICY_ITEM_SEQ');
+call spdropsequence('X_POLICY_ITEM_ACCESS_SEQ');
+call spdropsequence('X_POLICY_ITEM_CONDITION_SEQ');
+call spdropsequence('X_CONTEXT_ENRICHER_DEF_SEQ');
+call spdropsequence('X_POLICY_ITEM_USER_PERM_SEQ');
+call spdropsequence('X_POLICY_ITEM_GROUP_PERM_SEQ');
+call spdropsequence('X_POLICY_REF_RESOURCE_SEQ');
+call spdropsequence('X_POLICY_REF_ACCESS_TYPE_SEQ');
+call spdropsequence('X_POLICY_REF_CONDITION_SEQ');
+call spdropsequence('X_POLICY_REF_DATAMASK_TYPE_SEQ');
+call spdropsequence('X_POLICY_REF_USER_SEQ');
+call spdropsequence('X_POLICY_REF_GROUP_SEQ');
+call spdropsequence('X_DATA_HIST_SEQ');
+call spdropsequence('X_MODULES_MASTER_SEQ');
+call spdropsequence('X_USER_MODULE_PERM_SEQ');
+call spdropsequence('X_GROUP_MODULE_PERM_SEQ');
+call spdropsequence('X_TAG_DEF_SEQ');
+call spdropsequence('X_TAG_SEQ');
+call spdropsequence('X_SERVICE_RESOURCE_SEQ');
+call spdropsequence('X_TAG_RESOURCE_MAP_SEQ');
+call spdropsequence('X_DATAMASK_TYPE_DEF_SEQ');
+call spdropsequence('X_POLICY_ITEM_DATAMASK_SEQ');
+call spdropsequence('X_POLICY_ITEM_ROWFILTER_SEQ');
+call spdropsequence('X_SERVICE_VERSION_INFO_SEQ');
+call spdropsequence('X_PLUGIN_INFO_SEQ');
+call spdropsequence('X_POLICY_LABEL_MAP_SEQ');
+call spdropsequence('X_POLICY_LABEL_SEQ');
+call spdropsequence('X_UGSYNC_AUDIT_INFO_SEQ');
+call spdropsequence('X_DB_VERSION_H_SEQ');
CREATE SEQUENCE SEQ_GEN_IDENTITY START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_ACCESS_AUDIT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_ASSET_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
@@ -74,11 +145,109 @@ CREATE SEQUENCE X_PLUGIN_INFO_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_POLICY_LABEL_MAP_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_POLICY_LABEL_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_UGSYNC_AUDIT_INFO_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
---CREATE SEQUENCE X_DB_VERSION_H_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
+CREATE SEQUENCE X_DB_VERSION_H_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
commit;
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+
+CREATE OR REPLACE PROCEDURE spdropview(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP VIEW ' || ObjName;
+ end if;
+END;/
+/
+
+call spdropview('vx_trx_log');
+
+call spdroptable('x_policy_ref_group');
+call spdroptable('x_policy_ref_user');
+call spdroptable('x_policy_ref_datamask_type');
+call spdroptable('x_policy_ref_condition');
+call spdroptable('x_policy_ref_access_type');
+call spdroptable('x_policy_ref_resource');
+call spdroptable('x_ugsync_audit_info');
+call spdroptable('x_policy_label_map');
+call spdroptable('x_policy_label');
+call spdroptable('x_plugin_info');
+call spdroptable('x_service_version_info');
+call spdroptable('x_policy_item_rowfilter');
+call spdroptable('x_policy_item_datamask');
+call spdroptable('x_datamask_type_def');
+call spdroptable('x_service_resource_element_val');
+call spdroptable('x_tag_resource_map');
+call spdroptable('x_tag_attr');
+call spdroptable('x_tag_attr_def');
+call spdroptable('x_service_resource_element');
+call spdroptable('x_service_resource');
+call spdroptable('x_tag');
+call spdroptable('x_tag_def');
+call spdroptable('x_group_module_perm');
+call spdroptable('x_user_module_perm');
+call spdroptable('x_modules_master');
+call spdroptable('x_data_hist');
+call spdroptable('x_policy_item_group_perm');
+call spdroptable('x_policy_item_user_perm');
+call spdroptable('x_policy_item_condition');
+call spdroptable('x_policy_item_access');
+call spdroptable('x_policy_item');
+call spdroptable('x_policy_resource_map');
+call spdroptable('x_policy_resource');
+call spdroptable('x_service_config_map');
+call spdroptable('x_enum_element_def');
+call spdroptable('x_enum_def');
+call spdroptable('x_context_enricher_def');
+call spdroptable('x_policy_condition_def');
+call spdroptable('x_access_type_def_grants');
+call spdroptable('x_access_type_def');
+call spdroptable('x_resource_def');
+call spdroptable('x_service_config_def');
+call spdroptable('x_policy');
+call spdroptable('x_service');
+call spdroptable('x_service_def');
+call spdroptable('x_audit_map');
+call spdroptable('x_perm_map');
+call spdroptable('x_trx_log');
+call spdroptable('x_resource');
+call spdroptable('x_policy_export_audit');
+call spdroptable('x_group_users');
+call spdroptable('x_user');
+call spdroptable('x_group_groups');
+call spdroptable('x_group');
+call spdroptable('x_db_base');
+call spdroptable('x_cred_store');
+call spdroptable('x_auth_sess');
+call spdroptable('x_asset');
+call spdroptable('xa_access_audit');
+call spdroptable('x_portal_user_role');
+call spdroptable('x_portal_user');
+call spdroptable('x_db_version_h');
+
-- create tables
+create table X_DB_VERSION_H (
+ id NUMBER(20) NOT NULL,
+ version VARCHAR(64) NOT NULL,
+ inst_at DATE DEFAULT SYSDATE NOT NULL,
+ inst_by VARCHAR(256) NOT NULL,
+ updated_at DATE DEFAULT SYSDATE NOT NULL,
+ updated_by VARCHAR(256) NOT NULL,
+ active VARCHAR(1) DEFAULT 'Y'
+);
+
CREATE TABLE x_portal_user (
id NUMBER(20) NOT NULL,
create_time DATE DEFAULT NULL NULL ,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/009-updated_schema.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/009-updated_schema.sql b/security-admin/db/oracle/patches/009-updated_schema.sql
index 7e21f69..2f0996c 100644
--- a/security-admin/db/oracle/patches/009-updated_schema.sql
+++ b/security-admin/db/oracle/patches/009-updated_schema.sql
@@ -12,7 +12,71 @@
-- 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 OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_SERVICE_DEF_SEQ');
+call spdropsequence('X_SERVICE_SEQ');
+call spdropsequence('X_POLICY_SEQ');
+call spdropsequence('X_SERVICE_CONFIG_DEF_SEQ');
+call spdropsequence('X_RESOURCE_DEF_SEQ');
+call spdropsequence('X_ACCESS_TYPE_DEF_SEQ');
+call spdropsequence('X_ACCESS_TYPE_DEF_GRANTS_SEQ');
+call spdropsequence('X_POLICY_CONDITION_DEF_SEQ');
+call spdropsequence('X_ENUM_DEF_SEQ');
+call spdropsequence('X_ENUM_ELEMENT_DEF_SEQ');
+call spdropsequence('X_SERVICE_CONFIG_MAP_SEQ');
+call spdropsequence('X_POLICY_RESOURCE_SEQ');
+call spdropsequence('X_POLICY_RESOURCE_MAP_SEQ');
+call spdropsequence('X_POLICY_ITEM_SEQ');
+call spdropsequence('X_POLICY_ITEM_ACCESS_SEQ');
+call spdropsequence('X_POLICY_ITEM_CONDITION_SEQ');
+call spdropsequence('X_CONTEXT_ENRICHER_DEF_SEQ');
+call spdropsequence('X_POLICY_ITEM_USER_PERM_SEQ');
+call spdropsequence('X_POLICY_ITEM_GROUP_PERM_SEQ');
+call spdropsequence('X_DATA_HIST_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_data_hist');
+call spdroptable('x_policy_item_group_perm');
+call spdroptable('x_policy_item_user_perm');
+call spdroptable('x_policy_item_condition');
+call spdroptable('x_policy_item_access');
+call spdroptable('x_policy_item');
+call spdroptable('x_policy_resource_map');
+call spdroptable('x_policy_resource');
+call spdroptable('x_service_config_map');
+call spdroptable('x_enum_element_def');
+call spdroptable('x_enum_def');
+call spdroptable('x_context_enricher_def');
+call spdroptable('x_policy_condition_def');
+call spdroptable('x_access_type_def_grants');
+call spdroptable('x_access_type_def');
+call spdroptable('x_resource_def');
+call spdroptable('x_service_config_def');
+call spdroptable('x_policy');
+call spdroptable('x_service');
+call spdroptable('x_service_def')
+
CREATE SEQUENCE X_SERVICE_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_SERVICE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE X_POLICY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/013-permissionmodel.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/013-permissionmodel.sql b/security-admin/db/oracle/patches/013-permissionmodel.sql
index 4ac7901..a76e8c5 100644
--- a/security-admin/db/oracle/patches/013-permissionmodel.sql
+++ b/security-admin/db/oracle/patches/013-permissionmodel.sql
@@ -13,7 +13,36 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_MODULES_MASTER_SEQ');
+call spdropsequence('X_USER_MODULE_PERM_SEQ');
+call spdropsequence('X_GROUP_MODULE_PERM_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_group_module_perm');
+call spdroptable('x_user_module_perm');
+call spdroptable('x_modules_master');
+
CREATE SEQUENCE X_MODULES_MASTER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_modules_master(
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql
index 12627f5..31a2553 100644
--- a/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql
+++ b/security-admin/db/oracle/patches/016-updated-schema-for-tag-based-policy.sql
@@ -12,7 +12,46 @@
-- 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 OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+call spdropsequence('X_TAG_DEF_SEQ');
+call spdropsequence('X_TAG_SEQ');
+call spdropsequence('X_SERVICE_RESOURCE_SEQ');
+call spdropsequence('X_SERVICE_RESOURCE_ELEMENT_SEQ');
+call spdropsequence('X_TAG_ATTR_DEF_SEQ');
+call spdropsequence('X_TAG_ATTR_SEQ');
+call spdropsequence('X_TAG_RESOURCE_MAP_SEQ');
+call spdropsequence('X_SERVICE_RES_EL_VAL_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_service_resource_element_val');
+call spdroptable('x_tag_resource_map');
+call spdroptable('x_tag_attr');
+call spdroptable('x_tag_attr_def');
+call spdroptable('x_service_resource_element');
+call spdroptable('x_service_resource');
+call spdroptable('x_tag');
+call spdroptable('x_tag_def');
+
CREATE SEQUENCE X_TAG_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_tag_def (
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/020-datamask-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/020-datamask-policy.sql b/security-admin/db/oracle/patches/020-datamask-policy.sql
index 8448a85..489fd3a 100644
--- a/security-admin/db/oracle/patches/020-datamask-policy.sql
+++ b/security-admin/db/oracle/patches/020-datamask-policy.sql
@@ -13,7 +13,36 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_DATAMASK_TYPE_DEF_SEQ');
+call spdropsequence('X_POLICY_ITEM_DATAMASK_SEQ');
+call spdropsequence('X_POLICY_ITEM_ROWFILTER_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_policy_item_rowfilter');
+call spdroptable('x_policy_item_datamask');
+call spdroptable('x_datamask_type_def');
+
CREATE SEQUENCE X_DATAMASK_TYPE_DEF_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_datamask_type_def (
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/022-split-service-table.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/022-split-service-table.sql b/security-admin/db/oracle/patches/022-split-service-table.sql
index 9b4f69c..bc9466e 100644
--- a/security-admin/db/oracle/patches/022-split-service-table.sql
+++ b/security-admin/db/oracle/patches/022-split-service-table.sql
@@ -13,7 +13,32 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_SERVICE_VERSION_INFO_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_service_version_info');
+
CREATE SEQUENCE X_SERVICE_VERSION_INFO_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_service_version_info(
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql b/security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql
index bedd0a2..62a5212 100644
--- a/security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql
+++ b/security-admin/db/oracle/patches/025-create-schema-for-plugin-info.sql
@@ -13,7 +13,32 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_PLUGIN_INFO_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_plugin_info');
+
CREATE SEQUENCE X_PLUGIN_INFO_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_plugin_info(
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/030-policy-labels-schema.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/030-policy-labels-schema.sql b/security-admin/db/oracle/patches/030-policy-labels-schema.sql
index 894b934..f13163a 100644
--- a/security-admin/db/oracle/patches/030-policy-labels-schema.sql
+++ b/security-admin/db/oracle/patches/030-policy-labels-schema.sql
@@ -12,7 +12,35 @@
-- 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 OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_POLICY_LABEL_SEQ');
+call spdropsequence('X_POLICY_LABEL_MAP_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_policy_label_map');
+call spdroptable('x_policy_label');
+
CREATE SEQUENCE X_POLICY_LABEL_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_policy_label (
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql b/security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql
index cb52065..238f54c 100644
--- a/security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql
+++ b/security-admin/db/oracle/patches/031-create-schema-for-usersync-audit-info.sql
@@ -13,7 +13,32 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
/
+
+call spdropsequence('X_UGSYNC_AUDIT_INFO_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
+/
+
+call spdroptable('x_ugsync_audit_info');
+
CREATE SEQUENCE X_UGSYNC_AUDIT_INFO_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE TABLE x_ugsync_audit_info(
id NUMBER(20) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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
index c75e620..3afdf47 100644
--- 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
@@ -12,7 +12,43 @@
-- 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 OR REPLACE PROCEDURE spdropsequence(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'DROP SEQUENCE ' || ObjName;
+ end if;
+END;/
+/
+
+call spdropsequence('X_POLICY_REF_RESOURCE_SEQ');
+call spdropsequence('X_POLICY_REF_ACCESS_TYPE_SEQ');
+call spdropsequence('X_POLICY_REF_CONDITION_SEQ');
+call spdropsequence('X_POLICY_REF_DATAMASK_TYPE_SEQ');
+call spdropsequence('X_POLICY_REF_USER_SEQ');
+call spdropsequence('X_POLICY_REF_GROUP_SEQ');
+
+CREATE OR REPLACE PROCEDURE spdroptable(ObjName IN varchar2)
+IS
+v_counter integer;
+BEGIN
+ select count(*) into v_counter from user_tables where table_name = upper(ObjName);
+ if (v_counter > 0) then
+ execute immediate 'drop table ' || ObjName || ' cascade constraints';
+ end if;
+END;/
/
+
+call spdroptable('x_policy_ref_group');
+call spdroptable('x_policy_ref_user');
+call spdroptable('x_policy_ref_datamask_type');
+call spdroptable('x_policy_ref_condition');
+call spdroptable('x_policy_ref_access_type');
+call spdroptable('x_policy_ref_resource');
+
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;
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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 a0e02e0..8969c26 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
@@ -13,7 +13,126 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+DROP TABLE IF EXISTS x_policy_ref_group CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_user CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_datamask_type CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_condition CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_access_type CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_resource CASCADE;
+DROP TABLE IF EXISTS x_ugsync_audit_info CASCADE;
+DROP TABLE IF EXISTS x_policy_label_map CASCADE;
+DROP TABLE IF EXISTS x_policy_label CASCADE;
+DROP TABLE IF EXISTS x_plugin_info CASCADE;
+DROP TABLE IF EXISTS x_service_version_info;
+DROP TABLE IF EXISTS x_policy_item_rowfilter;
+DROP TABLE IF EXISTS x_policy_item_datamask;
+DROP TABLE IF EXISTS x_datamask_type_def;
+DROP TABLE IF EXISTS x_service_resource_element_val CASCADE;
+DROP TABLE IF EXISTS x_tag_resource_map CASCADE;
+DROP TABLE IF EXISTS x_tag_attr CASCADE;
+DROP TABLE IF EXISTS x_tag_attr_def CASCADE;
+DROP TABLE IF EXISTS x_service_resource_element CASCADE;
+DROP TABLE IF EXISTS x_service_resource CASCADE;
+DROP TABLE IF EXISTS x_tag CASCADE;
+DROP TABLE IF EXISTS x_tag_def CASCADE;
+DROP TABLE IF EXISTS x_group_module_perm CASCADE;
+DROP TABLE IF EXISTS x_user_module_perm CASCADE;
+DROP TABLE IF EXISTS x_modules_master CASCADE;
+DROP TABLE IF EXISTS x_data_hist CASCADE;
+DROP TABLE IF EXISTS x_policy_item_group_perm CASCADE;
+DROP TABLE IF EXISTS x_policy_item_user_perm CASCADE;
+DROP TABLE IF EXISTS x_policy_item_condition CASCADE;
+DROP TABLE IF EXISTS x_policy_item_access CASCADE;
+DROP TABLE IF EXISTS x_policy_item CASCADE;
+DROP TABLE IF EXISTS x_policy_resource_map CASCADE;
+DROP TABLE IF EXISTS x_policy_resource CASCADE;
+DROP TABLE IF EXISTS x_service_config_map CASCADE;
+DROP TABLE IF EXISTS x_enum_element_def CASCADE;
+DROP TABLE IF EXISTS x_enum_def CASCADE;
+DROP TABLE IF EXISTS x_context_enricher_def CASCADE;
+DROP TABLE IF EXISTS x_policy_condition_def CASCADE;
+DROP TABLE IF EXISTS x_access_type_def_grants CASCADE;
+DROP TABLE IF EXISTS x_access_type_def CASCADE;
+DROP TABLE IF EXISTS x_resource_def CASCADE;
+DROP TABLE IF EXISTS x_service_config_def CASCADE;
+DROP TABLE IF EXISTS x_policy CASCADE;
+DROP TABLE IF EXISTS x_service CASCADE;
+DROP TABLE IF EXISTS x_service_def CASCADE;
+DROP TABLE IF EXISTS x_audit_map CASCADE;
+DROP TABLE IF EXISTS x_perm_map CASCADE;
+DROP TABLE IF EXISTS x_trx_log CASCADE;
+DROP TABLE IF EXISTS x_resource CASCADE;
+DROP TABLE IF EXISTS x_policy_export_audit CASCADE;
+DROP TABLE IF EXISTS x_group_users CASCADE;
+DROP TABLE IF EXISTS x_user CASCADE;
+DROP TABLE IF EXISTS x_group_groups;
+DROP TABLE IF EXISTS x_group CASCADE;
+DROP TABLE IF EXISTS x_db_base CASCADE;
+DROP TABLE IF EXISTS x_cred_store CASCADE;
+DROP TABLE IF EXISTS x_auth_sess CASCADE;
+DROP TABLE IF EXISTS x_asset CASCADE;
+DROP TABLE IF EXISTS xa_access_audit CASCADE;
+DROP TABLE IF EXISTS x_portal_user_role CASCADE;
+DROP TABLE IF EXISTS x_portal_user CASCADE;
DROP TABLE IF EXISTS x_db_version_h CASCADE;
+
+DROP SEQUENCE IF EXISTS x_policy_ref_group_seq;
+DROP SEQUENCE IF EXISTS x_policy_ref_user_seq;
+DROP SEQUENCE IF EXISTS x_policy_ref_datamask_type_seq;
+DROP SEQUENCE IF EXISTS x_policy_ref_access_type_seq;
+DROP SEQUENCE IF EXISTS x_policy_ref_resource_seq;
+DROP SEQUENCE IF EXISTS x_ugsync_audit_info_seq;
+DROP SEQUENCE IF EXISTS x_policy_label_map_seq;
+DROP SEQUENCE IF EXISTS x_policy_label_seq;
+DROP SEQUENCE IF EXISTS x_plugin_info_seq;
+DROP SEQUENCE IF EXISTS x_service_version_info_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_rowfilter_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_datamask_seq;
+DROP SEQUENCE IF EXISTS x_datamask_type_def_seq;
+DROP SEQUENCE IF EXISTS x_tag_resource_map_seq;
+DROP SEQUENCE IF EXISTS x_service_resource_seq;
+DROP SEQUENCE IF EXISTS x_tag_seq;
+DROP SEQUENCE IF EXISTS x_tag_def_seq;
+DROP SEQUENCE IF EXISTS x_group_module_perm_seq;
+DROP SEQUENCE IF EXISTS x_user_module_perm_seq;
+DROP SEQUENCE IF EXISTS x_modules_master_seq;
+DROP SEQUENCE IF EXISTS x_data_hist_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_group_perm_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_user_perm_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_condition_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_access_seq;
+DROP SEQUENCE IF EXISTS x_policy_item_seq;
+DROP SEQUENCE IF EXISTS x_policy_resource_map_seq;
+DROP SEQUENCE IF EXISTS x_policy_resource_seq;
+DROP SEQUENCE IF EXISTS x_service_config_map_seq;
+DROP SEQUENCE IF EXISTS x_enum_element_def_seq;
+DROP SEQUENCE IF EXISTS x_enum_def_seq;
+DROP SEQUENCE IF EXISTS x_context_enricher_def_seq;
+DROP SEQUENCE IF EXISTS x_policy_condition_def_seq;
+DROP SEQUENCE IF EXISTS x_access_type_def_grants_seq;
+DROP SEQUENCE IF EXISTS x_access_type_def_seq;
+DROP SEQUENCE IF EXISTS x_resource_def_seq;
+DROP SEQUENCE IF EXISTS x_service_config_def_seq;
+DROP SEQUENCE IF EXISTS x_policy_seq;
+DROP SEQUENCE IF EXISTS x_service_seq;
+DROP SEQUENCE IF EXISTS x_service_def_seq;
+DROP SEQUENCE IF EXISTS x_audit_map_seq;
+DROP SEQUENCE IF EXISTS x_perm_map_seq;
+DROP SEQUENCE IF EXISTS x_trx_log_seq;
+DROP SEQUENCE IF EXISTS x_resource_seq;
+DROP SEQUENCE IF EXISTS x_policy_export_seq;
+DROP SEQUENCE IF EXISTS x_group_users_seq;
+DROP SEQUENCE IF EXISTS x_user_seq;
+DROP SEQUENCE IF EXISTS x_group_groups_seq;
+DROP SEQUENCE IF EXISTS x_group_seq;
+DROP SEQUENCE IF EXISTS x_db_base_seq;
+DROP SEQUENCE IF EXISTS x_cred_store_seq;
+DROP SEQUENCE IF EXISTS x_auth_sess_seq;
+DROP SEQUENCE IF EXISTS x_asset_seq;
+DROP SEQUENCE IF EXISTS xa_access_audit_seq;
+DROP SEQUENCE IF EXISTS x_portal_user_role_seq;
+DROP SEQUENCE IF EXISTS x_portal_user_seq;
+
create table x_db_version_h(
id SERIAL primary key,
version varchar(64) NOT NULL,
@@ -24,8 +143,6 @@ updated_by varchar(256) NOT NULL,
active VARCHAR(1) CHECK (active IN ('Y','N')) DEFAULT 'Y'
);
-DROP TABLE IF EXISTS x_portal_user CASCADE;
-DROP SEQUENCE IF EXISTS x_portal_user_seq;
CREATE SEQUENCE x_portal_user_seq;
CREATE TABLE x_portal_user(
id BIGINT DEFAULT nextval('x_portal_user_seq'::regclass),
@@ -49,8 +166,6 @@ CONSTRAINT x_portal_user_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_po
CONSTRAINT x_portal_user_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_portal_user_role CASCADE;
-DROP SEQUENCE IF EXISTS x_portal_user_role_seq;
CREATE SEQUENCE x_portal_user_role_seq;
CREATE TABLE x_portal_user_role(
id BIGINT DEFAULT nextval('x_portal_user_role_seq'::regclass),
@@ -67,8 +182,6 @@ CONSTRAINT x_portal_user_role_FK_updby FOREIGN KEY(upd_by_id) REFERENCES x_porta
CONSTRAINT x_portal_user_role_FK_user_id FOREIGN KEY(user_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS xa_access_audit CASCADE;
-DROP SEQUENCE IF EXISTS xa_access_audit_seq;
CREATE SEQUENCE xa_access_audit_seq;
CREATE TABLE xa_access_audit(
id BIGINT DEFAULT nextval('xa_access_audit_seq'::regclass),
@@ -97,8 +210,6 @@ resource_type VARCHAR(255) DEFAULT NULL NULL,
PRIMARY KEY(id)
);
-DROP TABLE IF EXISTS x_asset CASCADE;
-DROP SEQUENCE IF EXISTS x_asset_seq;
CREATE SEQUENCE x_asset_seq;
CREATE TABLE x_asset(
id BIGINT DEFAULT nextval('x_asset_seq'::regclass),
@@ -117,8 +228,6 @@ CONSTRAINT x_asset_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_u
CONSTRAINT x_asset_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_auth_sess CASCADE;
-DROP SEQUENCE IF EXISTS x_auth_sess_seq;
CREATE SEQUENCE x_auth_sess_seq;
CREATE TABLE x_auth_sess(
id BIGINT DEFAULT nextval('x_auth_sess_seq'::regclass),
@@ -142,9 +251,6 @@ CONSTRAINT x_auth_sess_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_u
CONSTRAINT x_auth_sess_FK_user_id FOREIGN KEY(user_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_cred_store CASCADE;
-DROP SEQUENCE IF EXISTS x_cred_store_seq;
CREATE SEQUENCE x_cred_store_seq;
CREATE TABLE x_cred_store(
id BIGINT DEFAULT nextval('x_cred_store_seq'::regclass),
@@ -159,9 +265,6 @@ CONSTRAINT x_cred_store_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_por
CONSTRAINT x_cred_store_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_db_base CASCADE;
-DROP SEQUENCE IF EXISTS x_db_base_seq;
CREATE SEQUENCE x_db_base_seq;
CREATE TABLE x_db_base(
id BIGINT DEFAULT nextval('x_db_base_seq'::regclass),
@@ -174,8 +277,6 @@ CONSTRAINT x_db_base_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal
CONSTRAINT x_db_base_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_group CASCADE;
-DROP SEQUENCE IF EXISTS x_group_seq;
CREATE SEQUENCE x_group_seq;
CREATE TABLE x_group(
id BIGINT DEFAULT nextval('x_group_seq'::regclass),
@@ -197,8 +298,6 @@ CONSTRAINT X_GROUP_FK_CRED_STORE_ID FOREIGN KEY(CRED_STORE_ID) REFERENCES X_CRED
CONSTRAINT X_GROUP_FK_UPD_BY_ID FOREIGN KEY(UPD_BY_ID) REFERENCES X_PORTAL_USER(ID)
);
-DROP TABLE IF EXISTS x_group_groups;
-DROP SEQUENCE IF EXISTS x_group_groups_seq;
CREATE SEQUENCE x_group_groups_seq;
CREATE TABLE x_group_groups(
id BIGINT DEFAULT nextval('x_group_groups_seq'::regclass),
@@ -216,8 +315,6 @@ CONSTRAINT x_group_groups_FK_p_group_id FOREIGN KEY(p_group_id) REFERENCES x_gro
CONSTRAINT x_group_groups_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_user CASCADE;
-DROP SEQUENCE IF EXISTS x_user_seq;
CREATE SEQUENCE x_user_seq;
CREATE TABLE x_user(
id BIGINT DEFAULT nextval('x_user_seq'::regclass),
@@ -237,8 +334,6 @@ CONSTRAINT x_user_FK_cred_store_id FOREIGN KEY(cred_store_id) REFERENCES x_cred_
CONSTRAINT x_user_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_group_users CASCADE;
-DROP SEQUENCE IF EXISTS x_group_users_seq;
CREATE SEQUENCE x_group_users_seq;
CREATE TABLE x_group_users(
id BIGINT DEFAULT nextval('x_group_users_seq'::regclass),
@@ -257,8 +352,6 @@ CONSTRAINT x_group_users_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal
CONSTRAINT x_group_users_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id)
);
-DROP TABLE IF EXISTS x_policy_export_audit CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_export_seq;
CREATE SEQUENCE x_policy_export_seq;
CREATE TABLE x_policy_export_audit(
id BIGINT DEFAULT nextval('x_policy_export_seq'::regclass),
@@ -279,8 +372,6 @@ CONSTRAINT x_policy_export_audit_FK_added FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_policy_export_audit_FK_upd FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_resource CASCADE;
-DROP SEQUENCE IF EXISTS x_resource_seq;
CREATE SEQUENCE x_resource_seq;
CREATE TABLE x_resource(
id BIGINT DEFAULT nextval('x_resource_seq'::regclass),
@@ -316,9 +407,6 @@ CONSTRAINT x_resource_FK_parent_id FOREIGN KEY(parent_id) REFERENCES x_resource(
CONSTRAINT x_resource_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_trx_log CASCADE;
-DROP SEQUENCE IF EXISTS x_trx_log_seq;
CREATE SEQUENCE x_trx_log_seq;
CREATE TABLE x_trx_log(
id BIGINT DEFAULT nextval('x_trx_log_seq'::regclass),
@@ -345,8 +433,6 @@ CONSTRAINT x_trx_log_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal
CONSTRAINT x_trx_log_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_perm_map CASCADE;
-DROP SEQUENCE IF EXISTS x_perm_map_seq;
CREATE SEQUENCE x_perm_map_seq;
CREATE TABLE x_perm_map(
id BIGINT DEFAULT nextval('x_perm_map_seq'::regclass),
@@ -372,8 +458,6 @@ CONSTRAINT x_perm_map_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_us
CONSTRAINT x_perm_map_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id)
);
-DROP TABLE IF EXISTS x_audit_map CASCADE;
-DROP SEQUENCE IF EXISTS x_audit_map_seq;
CREATE SEQUENCE x_audit_map_seq;
CREATE TABLE x_audit_map(
id BIGINT DEFAULT nextval('x_audit_map_seq'::regclass),
@@ -393,8 +477,6 @@ CONSTRAINT X_AUDIT_MAP_FK_UPD_BY_ID FOREIGN KEY(UPD_BY_ID) REFERENCES X_PORTAL_U
CONSTRAINT X_AUDIT_MAP_FK_USER_ID FOREIGN KEY(USER_ID) REFERENCES X_USER(ID)
);
-DROP TABLE IF EXISTS x_service_def CASCADE;
-DROP SEQUENCE IF EXISTS x_service_def_seq;
CREATE SEQUENCE x_service_def_seq;
CREATE TABLE x_service_def(
id BIGINT DEFAULT nextval('x_service_def_seq'::regclass),
@@ -417,8 +499,6 @@ CONSTRAINT x_service_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_po
CONSTRAINT x_service_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_service CASCADE;
-DROP SEQUENCE IF EXISTS x_service_seq;
CREATE SEQUENCE x_service_seq;
CREATE TABLE x_service(
id BIGINT DEFAULT nextval('x_service_seq'::regclass),
@@ -445,8 +525,6 @@ CONSTRAINT x_service_FK_type FOREIGN KEY(type) REFERENCES x_service_def(id),
CONSTRAINT x_service_FK_tag_service FOREIGN KEY (tag_service) REFERENCES x_service(id)
);
-DROP TABLE IF EXISTS x_policy CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_seq;
CREATE SEQUENCE x_policy_seq;
CREATE TABLE x_policy(
id BIGINT DEFAULT nextval('x_policy_seq'::regclass),
@@ -473,8 +551,6 @@ CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user
CONSTRAINT x_policy_FK_service FOREIGN KEY(service) REFERENCES x_service(id)
);
-DROP TABLE IF EXISTS x_service_config_def CASCADE;
-DROP SEQUENCE IF EXISTS x_service_config_def_seq;
CREATE SEQUENCE x_service_config_def_seq;
CREATE TABLE x_service_config_def(
id BIGINT DEFAULT nextval('x_service_config_def_seq'::regclass),
@@ -505,8 +581,6 @@ CONSTRAINT x_service_conf_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_service_conf_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_resource_def CASCADE;
-DROP SEQUENCE IF EXISTS x_resource_def_seq;
CREATE SEQUENCE x_resource_def_seq;
CREATE TABLE x_resource_def(
id BIGINT DEFAULT nextval('x_resource_def_seq'::regclass),
@@ -545,8 +619,6 @@ CONSTRAINT x_resource_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_p
CONSTRAINT x_resource_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_access_type_def CASCADE;
-DROP SEQUENCE IF EXISTS x_access_type_def_seq;
CREATE SEQUENCE x_access_type_def_seq;
CREATE TABLE x_access_type_def(
id BIGINT DEFAULT nextval('x_access_type_def_seq'::regclass),
@@ -569,8 +641,6 @@ CONSTRAINT x_access_type_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_p
CONSTRAINT x_access_type_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_access_type_def_grants CASCADE;
-DROP SEQUENCE IF EXISTS x_access_type_def_grants_seq;
CREATE SEQUENCE x_access_type_def_grants_seq;
CREATE TABLE x_access_type_def_grants(
id BIGINT DEFAULT nextval('x_access_type_def_grants_seq'::regclass),
@@ -587,8 +657,6 @@ CONSTRAINT x_atd_grants_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_por
CONSTRAINT x_atd_grants_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_policy_condition_def CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_condition_def_seq;
CREATE SEQUENCE x_policy_condition_def_seq;
CREATE TABLE x_policy_condition_def(
id BIGINT DEFAULT nextval('x_policy_condition_def_seq'::regclass),
@@ -617,9 +685,6 @@ CONSTRAINT x_policy_cond_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_p
CONSTRAINT x_policy_cond_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_context_enricher_def CASCADE;
-DROP SEQUENCE IF EXISTS x_context_enricher_def_seq;
CREATE SEQUENCE x_context_enricher_def_seq;
CREATE TABLE x_context_enricher_def(
id BIGINT DEFAULT nextval('x_context_enricher_def_seq'::regclass),
@@ -640,9 +705,6 @@ CONSTRAINT x_context_enricher_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERE
CONSTRAINT x_context_enricher_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_enum_def CASCADE;
-DROP SEQUENCE IF EXISTS x_enum_def_seq;
CREATE SEQUENCE x_enum_def_seq;
CREATE TABLE x_enum_def(
id BIGINT DEFAULT nextval('x_enum_def_seq'::regclass),
@@ -661,8 +723,6 @@ CONSTRAINT x_enum_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_porta
CONSTRAINT x_enum_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_enum_element_def CASCADE;
-DROP SEQUENCE IF EXISTS x_enum_element_def_seq;
CREATE SEQUENCE x_enum_element_def_seq;
CREATE TABLE x_enum_element_def(
id BIGINT DEFAULT nextval('x_enum_element_def_seq'::regclass),
@@ -683,9 +743,6 @@ CONSTRAINT x_enum_element_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_enum_element_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_service_config_map CASCADE;
-DROP SEQUENCE IF EXISTS x_service_config_map_seq;
CREATE SEQUENCE x_service_config_map_seq;
CREATE TABLE x_service_config_map(
id BIGINT DEFAULT nextval('x_service_config_map_seq'::regclass),
@@ -703,9 +760,6 @@ CONSTRAINT x_service_conf_map_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_service_conf_map_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_policy_resource CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_resource_seq;
CREATE SEQUENCE x_policy_resource_seq;
CREATE TABLE x_policy_resource(
id BIGINT DEFAULT nextval('x_policy_resource_seq'::regclass),
@@ -725,9 +779,6 @@ CONSTRAINT x_policy_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_por
CONSTRAINT x_policy_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_policy_resource_map CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_resource_map_seq;
CREATE SEQUENCE x_policy_resource_map_seq;
CREATE TABLE x_policy_resource_map(
id BIGINT DEFAULT nextval('x_policy_resource_map_seq'::regclass),
@@ -745,9 +796,6 @@ CONSTRAINT x_policy_res_map_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_po
CONSTRAINT x_policy_res_map_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_policy_item CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_item_seq;
CREATE SEQUENCE x_policy_item_seq;
CREATE TABLE x_policy_item(
id BIGINT DEFAULT nextval('x_policy_item_seq'::regclass),
@@ -768,8 +816,6 @@ CONSTRAINT x_policy_item_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_po
CONSTRAINT x_policy_item_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_policy_item_access CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_item_access_seq;
CREATE SEQUENCE x_policy_item_access_seq;
CREATE TABLE x_policy_item_access(
id BIGINT DEFAULT nextval('x_policy_item_access_seq'::regclass),
@@ -789,9 +835,6 @@ CONSTRAINT x_plc_item_access_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_p
CONSTRAINT x_plc_item_access_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_policy_item_condition CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_item_condition_seq;
CREATE SEQUENCE x_policy_item_condition_seq;
CREATE TABLE x_policy_item_condition(
id BIGINT DEFAULT nextval('x_policy_item_condition_seq'::regclass),
@@ -811,8 +854,6 @@ CONSTRAINT x_plc_item_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_plc_item_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_policy_item_user_perm CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_item_user_perm_seq;
CREATE SEQUENCE x_policy_item_user_perm_seq;
CREATE TABLE x_policy_item_user_perm(
id BIGINT DEFAULT nextval('x_policy_item_user_perm_seq'::regclass),
@@ -831,9 +872,6 @@ CONSTRAINT x_plc_itm_usr_perm_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_plc_itm_usr_perm_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_policy_item_group_perm CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_item_group_perm_seq;
CREATE SEQUENCE x_policy_item_group_perm_seq;
CREATE TABLE x_policy_item_group_perm(
id BIGINT DEFAULT nextval('x_policy_item_group_perm_seq'::regclass),
@@ -852,9 +890,6 @@ CONSTRAINT x_plc_itm_grp_perm_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_
CONSTRAINT x_plc_itm_grp_perm_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id)
);
-
-DROP TABLE IF EXISTS x_data_hist CASCADE;
-DROP SEQUENCE IF EXISTS x_data_hist_seq;
CREATE SEQUENCE x_data_hist_seq;
CREATE TABLE x_data_hist(
id BIGINT DEFAULT nextval('x_data_hist_seq'::regclass),
@@ -872,8 +907,6 @@ content TEXT NOT NULL,
primary key(id)
);
-DROP TABLE IF EXISTS x_modules_master CASCADE;
-DROP SEQUENCE IF EXISTS x_modules_master_seq;
CREATE SEQUENCE x_modules_master_seq;
CREATE TABLE x_modules_master(
id BIGINT DEFAULT nextval('x_modules_master_seq'::regclass),
@@ -886,8 +919,6 @@ url VARCHAR(1024) DEFAULT NULL NULL,
PRIMARY KEY(id)
);
-DROP TABLE IF EXISTS x_user_module_perm CASCADE;
-DROP SEQUENCE IF EXISTS x_user_module_perm_seq;
CREATE SEQUENCE x_user_module_perm_seq;
CREATE TABLE x_user_module_perm(
id BIGINT DEFAULT nextval('x_user_module_perm_seq'::regclass),
@@ -903,8 +934,6 @@ CONSTRAINT x_user_module_perm_FK_moduleid FOREIGN KEY (module_id) REFERENCES x_m
CONSTRAINT x_user_module_perm_FK_userid FOREIGN KEY (user_id) REFERENCES x_portal_user(id)
);
-DROP TABLE IF EXISTS x_group_module_perm CASCADE;
-DROP SEQUENCE IF EXISTS x_group_module_perm_seq;
CREATE SEQUENCE x_group_module_perm_seq;
CREATE TABLE x_group_module_perm(
id BIGINT DEFAULT nextval('x_group_module_perm_seq'::regclass),
@@ -920,8 +949,6 @@ CONSTRAINT x_grp_module_perm_FK_module_id FOREIGN KEY (module_id) REFERENCES x_m
CONSTRAINT x_grp_module_perm_FK_group_id FOREIGN KEY (group_id) REFERENCES x_group(id)
);
-DROP TABLE IF EXISTS x_tag_def CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_def_seq;
CREATE SEQUENCE x_tag_def_seq;
CREATE TABLE x_tag_def(
id BIGINT DEFAULT nextval('x_tag_def_seq'::regclass),
@@ -942,9 +969,6 @@ CONSTRAINT x_tag_def_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_porta
CONSTRAINT x_tag_def_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-
-DROP TABLE IF EXISTS x_tag CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_seq;
CREATE SEQUENCE x_tag_seq;
CREATE TABLE x_tag(
id BIGINT DEFAULT nextval('x_tag_seq'::regclass),
@@ -965,8 +989,6 @@ CONSTRAINT x_tag_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_us
CONSTRAINT x_tag_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-DROP TABLE IF EXISTS x_service_resource CASCADE;
-DROP SEQUENCE IF EXISTS x_service_resource_seq;
CREATE SEQUENCE x_service_resource_seq;
CREATE TABLE x_service_resource(
id BIGINT DEFAULT nextval('x_service_resource_seq'::regclass),
@@ -988,8 +1010,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_tag_resource_map CASCADE;
-DROP SEQUENCE IF EXISTS x_tag_resource_map_seq;
CREATE SEQUENCE x_tag_resource_map_seq;
CREATE TABLE x_tag_resource_map(
id BIGINT NOT NULL,
@@ -1008,10 +1028,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_policy_item_datamask;
-DROP SEQUENCE IF EXISTS x_policy_item_datamask_seq;
-DROP TABLE IF EXISTS x_datamask_type_def;
-DROP SEQUENCE IF EXISTS x_datamask_type_def_seq;
CREATE SEQUENCE x_datamask_type_def_seq;
CREATE TABLE x_datamask_type_def (
id BIGINT DEFAULT nextval('x_datamask_type_def_seq'::regclass),
@@ -1055,8 +1071,6 @@ CREATE TABLE x_policy_item_datamask (
CONSTRAINT x_policy_item_datamask_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-DROP TABLE IF EXISTS x_policy_item_rowfilter;
-DROP SEQUENCE IF EXISTS x_policy_item_rowfilter_seq;
CREATE SEQUENCE x_policy_item_rowfilter_seq;
CREATE TABLE x_policy_item_rowfilter (
id BIGINT DEFAULT nextval('x_policy_item_rowfilter_seq'::regclass),
@@ -1073,8 +1087,6 @@ CREATE TABLE x_policy_item_rowfilter (
CONSTRAINT x_policy_item_rowfilter_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-DROP TABLE IF EXISTS x_service_version_info;
-DROP SEQUENCE IF EXISTS x_service_version_info_seq;
CREATE SEQUENCE x_service_version_info_seq;
CREATE TABLE x_service_version_info (
id BIGINT DEFAULT nextval('x_service_version_info_seq'::regclass),
@@ -1087,8 +1099,6 @@ primary key (id),
CONSTRAINT x_service_version_info_service_id FOREIGN KEY (service_id) REFERENCES x_service (id)
);
-DROP TABLE IF EXISTS x_plugin_info CASCADE;
-DROP SEQUENCE IF EXISTS x_plugin_info_seq;
CREATE SEQUENCE x_plugin_info_seq;
CREATE TABLE x_plugin_info (
id BIGINT DEFAULT nextval('x_plugin_info_seq'::regclass),
@@ -1103,10 +1113,6 @@ primary key (id),
CONSTRAINT x_plugin_info_UK UNIQUE (service_name, host_name, app_type)
);
-DROP TABLE IF EXISTS x_policy_label_map CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_label_map_seq;
-DROP TABLE IF EXISTS x_policy_label CASCADE;
-DROP SEQUENCE IF EXISTS x_policy_label_seq;
CREATE SEQUENCE x_policy_label_seq;
CREATE TABLE x_policy_label (
id BIGINT DEFAULT nextval('x_policy_label_seq'::regclass),
@@ -1140,8 +1146,6 @@ CONSTRAINT x_policy_label_map_FK_policy_id FOREIGN KEY (policy_id) REFERENCES x_
CONSTRAINT x_policy_label_map_FK_policy_label_id FOREIGN KEY (policy_label_id) REFERENCES x_policy_label (id)
);
-DROP TABLE IF EXISTS x_ugsync_audit_info CASCADE;
-DROP SEQUENCE IF EXISTS x_ugsync_audit_info_seq;
CREATE SEQUENCE x_ugsync_audit_info_seq;
CREATE TABLE x_ugsync_audit_info (
id BIGINT DEFAULT nextval('x_ugsync_audit_info_seq'::regclass),
@@ -1161,8 +1165,6 @@ 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),
@@ -1182,8 +1184,7 @@ CONSTRAINT x_p_ref_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_port
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),
@@ -1224,8 +1225,7 @@ CONSTRAINT x_p_ref_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_por
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),
@@ -1245,8 +1245,7 @@ CONSTRAINT x_p_ref_dmk_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_port
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),
@@ -1266,8 +1265,7 @@ CONSTRAINT x_p_ref_usr_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_port
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),
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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 db8ebc3..9dc2515 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
@@ -5,7 +5,7 @@
--(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
+-- 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,
@@ -13,6 +13,164 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_group')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_user')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_datamask_type')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_condition')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_access_type')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_resource')
+GO
+call dbo.removeForeignKeysAndTable('x_ugsync_audit_info')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_label_map')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_label')
+GO
+call dbo.removeForeignKeysAndTable('x_plugin_info')
+GO
+call dbo.removeForeignKeysAndTable('x_service_version_info')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_rowfilter')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_datamask')
+GO
+call dbo.removeForeignKeysAndTable('x_datamask_type_def')
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource_element_val')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_resource_map')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_attr')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_attr_def')
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource_element')
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource')
+GO
+call dbo.removeForeignKeysAndTable('x_tag')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_def')
+GO
+call dbo.removeForeignKeysAndTable('x_group_module_perm')
+GO
+call dbo.removeForeignKeysAndTable('x_user_module_perm')
+GO
+call dbo.removeForeignKeysAndTable('x_modules_master')
+GO
+call dbo.removeForeignKeysAndTable('x_data_hist')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_group_perm')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_user_perm')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_condition')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_access')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_resource_map')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_resource')
+GO
+call dbo.removeForeignKeysAndTable('x_service_config_map')
+GO
+call dbo.removeForeignKeysAndTable('x_enum_element_def')
+GO
+call dbo.removeForeignKeysAndTable('x_enum_def')
+GO
+call dbo.removeForeignKeysAndTable('x_context_enricher_def')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_condition_def')
+GO
+call dbo.removeForeignKeysAndTable('x_access_type_def_grants')
+GO
+call dbo.removeForeignKeysAndTable('x_access_type_def')
+GO
+call dbo.removeForeignKeysAndTable('x_resource_def')
+GO
+call dbo.removeForeignKeysAndTable('x_service_config_def')
+GO
+call dbo.removeForeignKeysAndTable('x_policy')
+GO
+call dbo.removeForeignKeysAndTable('x_service')
+GO
+call dbo.removeForeignKeysAndTable('x_service_def')
+GO
+call dbo.removeForeignKeysAndTable('x_audit_map')
+GO
+call dbo.removeForeignKeysAndTable('x_perm_map')
+GO
+DROP VIEW IF EXISTS dbo.vx_trx_log
+GO
+call dbo.removeForeignKeysAndTable('x_trx_log')
+GO
+call dbo.removeForeignKeysAndTable('x_resource')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_export_audit')
+GO
+call dbo.removeForeignKeysAndTable('x_group_users')
+GO
+call dbo.removeForeignKeysAndTable('x_user')
+GO
+call dbo.removeForeignKeysAndTable('x_group_groups')
+GO
+call dbo.removeForeignKeysAndTable('x_group')
+GO
+call dbo.removeForeignKeysAndTable('x_db_base')
+GO
+call dbo.removeForeignKeysAndTable('x_cred_store')
+GO
+call dbo.removeForeignKeysAndTable('x_auth_sess')
+GO
+call dbo.removeForeignKeysAndTable('x_asset')
+GO
+call dbo.removeForeignKeysAndTable('xa_access_audit')
+GO
+call dbo.removeForeignKeysAndTable('x_portal_user_role')
+GO
+call dbo.removeForeignKeysAndTable('x_portal_user')
+GO
+call dbo.removeForeignKeysAndTable('x_db_version_h')
+GO
+create table dbo.x_db_version_h(
+id bigint identity not null primary key,
+version varchar(64) not null,
+inst_at datetime not null,
+inst_by varchar(256) not null,
+updated_at datetime not null,
+updated_by varchar(256) not null,
+active varchar(1) default 'Y' check(active IN ('Y', 'N'))
+)
+GO
create table dbo.x_portal_user(
id bigint IDENTITY NOT NULL,
create_time datetime DEFAULT NULL NULL,
@@ -788,58 +946,58 @@ CREATE TABLE dbo.x_service_version_info(
)
GO
CREATE TABLE dbo.x_plugin_info(
- id bigint IDENTITY NOT NULL,
- create_time datetime DEFAULT NULL NULL,
- update_time datetime DEFAULT NULL NULL,
- service_name varchar(255) NOT NULL,
- app_type varchar(128) NOT NULL,
- host_name varchar(255) NOT NULL,
- ip_address varchar(64) NOT NULL,
- info varchar(1024) NOT NULL,
- CONSTRAINT x_plugin_info_PK_id PRIMARY KEY CLUSTERED(id),
- CONSTRAINT x_plugin_info_UK UNIQUE NONCLUSTERED (service_name, host_name, app_type)
+ id bigint IDENTITY NOT NULL,
+ create_time datetime DEFAULT NULL NULL,
+ update_time datetime DEFAULT NULL NULL,
+ service_name varchar(255) NOT NULL,
+ app_type varchar(128) NOT NULL,
+ host_name varchar(255) NOT NULL,
+ ip_address varchar(64) NOT NULL,
+ info varchar(1024) NOT NULL,
+ CONSTRAINT x_plugin_info_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_plugin_info_UK UNIQUE NONCLUSTERED (service_name, host_name, app_type)
)
GO
CREATE TABLE dbo.x_policy_label (
- id bigint IDENTITY NOT NULL,
- guid varchar(64) 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,
- label_name varchar(512) DEFAULT NULL,
- CONSTRAINT x_policy_label_PK_id PRIMARY KEY CLUSTERED(id),
- CONSTRAINT x_policy_label_UK_label_name UNIQUE NONCLUSTERED (label_name)
+ id bigint IDENTITY NOT NULL,
+ guid varchar(64) 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,
+ label_name varchar(512) DEFAULT NULL,
+ CONSTRAINT x_policy_label_PK_id PRIMARY KEY CLUSTERED(id),
+ CONSTRAINT x_policy_label_UK_label_name UNIQUE NONCLUSTERED (label_name)
)
GO
CREATE TABLE dbo.x_policy_label_map (
- id bigint IDENTITY NOT NULL,
- guid varchar(64) 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,
- policy_label_id bigint NOT NULL,
- CONSTRAINT x_policy_label_map_PK_id PRIMARY KEY CLUSTERED(id)
+ id bigint IDENTITY NOT NULL,
+ guid varchar(64) 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,
+ policy_label_id bigint NOT NULL,
+ CONSTRAINT x_policy_label_map_PK_id PRIMARY KEY CLUSTERED(id)
)
GO
CREATE TABLE dbo.x_ugsync_audit_info(
- 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,
- event_time datetime DEFAULT NULL NULL,
- user_name varchar(255) NOT NULL,
- sync_source varchar(128) NOT NULL,
- no_of_new_users bigint NOT NULL,
- no_of_new_groups bigint NOT NULL,
- no_of_modified_users bigint NOT NULL,
- no_of_modified_groups bigint NOT NULL,
- sync_source_info varchar(4000) NOT NULL,
- session_id varchar(255) DEFAULT NULL NULL,
- CONSTRAINT x_ugsync_audit_info_PK_id PRIMARY KEY CLUSTERED(id)
+ 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,
+ event_time datetime DEFAULT NULL NULL,
+ user_name varchar(255) NOT NULL,
+ sync_source varchar(128) NOT NULL,
+ no_of_new_users bigint NOT NULL,
+ no_of_new_groups bigint NOT NULL,
+ no_of_modified_users bigint NOT NULL,
+ no_of_modified_groups bigint NOT NULL,
+ sync_source_info varchar(4000) NOT NULL,
+ session_id varchar(255) DEFAULT NULL NULL,
+ CONSTRAINT x_ugsync_audit_info_PK_id PRIMARY KEY CLUSTERED(id)
)
GO
create table dbo.x_policy_ref_resource (
@@ -857,72 +1015,72 @@ create table dbo.x_policy_ref_resource (
)
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),
+ 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),
+ 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),
+ 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),
+ 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),
+ 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
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql
index f3b64d0..a1c2f51 100644
--- a/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql
+++ b/security-admin/db/sqlanywhere/patches/016-updated-schema-for-tag-based-policy.sql
@@ -13,6 +13,44 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource_element_val')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_resource_map')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_attr')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_attr_def')
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource_element')
+GO
+call dbo.removeForeignKeysAndTable('x_service_resource')
+GO
+call dbo.removeForeignKeysAndTable('x_tag')
+GO
+call dbo.removeForeignKeysAndTable('x_tag_def')
+GO
+
CREATE TABLE dbo.x_tag_def(
id bigint IDENTITY NOT NULL,
guid varchar(64) NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql b/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql
index fe6fa9f..3cc0447 100644
--- a/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql
+++ b/security-admin/db/sqlanywhere/patches/020-datamask-policy.sql
@@ -13,6 +13,34 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_rowfilter')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_item_datamask')
+GO
+call dbo.removeForeignKeysAndTable('x_datamask_type_def')
+GO
+
CREATE TABLE dbo.x_datamask_type_def(
id bigint IDENTITY NOT NULL,
guid varchar(64) DEFAULT NULL NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/022-split-service-table.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/022-split-service-table.sql b/security-admin/db/sqlanywhere/patches/022-split-service-table.sql
index d32966d..a45a14e 100644
--- a/security-admin/db/sqlanywhere/patches/022-split-service-table.sql
+++ b/security-admin/db/sqlanywhere/patches/022-split-service-table.sql
@@ -13,6 +13,30 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_service_version_info')
+GO
+
CREATE TABLE dbo.x_service_version_info(
id bigint IDENTITY NOT NULL,
service_id bigint NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql b/security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql
index 6e94779..8c289d9 100644
--- a/security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql
+++ b/security-admin/db/sqlanywhere/patches/025-create-schema-for-plugin-info.sql
@@ -13,6 +13,30 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_plugin_info')
+GO
+
CREATE TABLE dbo.x_plugin_info(
id bigint IDENTITY NOT NULL,
create_time datetime DEFAULT NULL NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql b/security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql
index b2ed238..0cd1cbc 100644
--- a/security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql
+++ b/security-admin/db/sqlanywhere/patches/030-policy-labels-schema.sql
@@ -12,6 +12,33 @@
-- 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 OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_policy_label_map')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_label')
+GO
+
CREATE TABLE dbo.x_policy_label (
id bigint IDENTITY NOT NULL,
guid varchar(64) DEFAULT NULL NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql b/security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql
index 8ed84e3..5a12f2c 100644
--- a/security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql
+++ b/security-admin/db/sqlanywhere/patches/031-create-schema-for-usersync-audit-info.sql
@@ -13,6 +13,30 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
+CREATE OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_ugsync_audit_info')
+GO
+
CREATE TABLE dbo.x_ugsync_audit_info(
id bigint IDENTITY NOT NULL,
create_time datetime DEFAULT NULL NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/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
index c079014..20d76f7 100644
--- 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
@@ -13,6 +13,39 @@
-- 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 OR REPLACE PROCEDURE dbo.removeForeignKeysAndTable (IN table_name varchar(100))
+AS
+BEGIN
+ DECLARE @stmt VARCHAR(300)
+ DECLARE @tblname VARCHAR(300)
+ DECLARE @drpstmt VARCHAR(1000)
+ 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
+ WHILE (@@sqlstatus = 0)
+ BEGIN
+ execute(@stmt)
+ fetch cur into @stmt
+ END
+ close cur
+ DEALLOCATE CURSOR cur
+ SET @tblname ='dbo.' + table_name;
+ SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
+ execute(@drpstmt)
+END
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_group')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_user')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_datamask_type')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_condition')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_access_type')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_resource')
GO
create table dbo.x_policy_ref_resource (
id bigint IDENTITY NOT NULL,
http://git-wip-us.apache.org/repos/asf/ranger/blob/5b07a8df/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
----------------------------------------------------------------------
diff --git a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
index 522b57b..8351c70 100644
--- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
+++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
@@ -513,6 +513,42 @@ IF (OBJECT_ID('vx_trx_log') IS NOT NULL)
BEGIN
DROP VIEW [dbo].[vx_trx_log]
END
+IF (OBJECT_ID('x_policy_ref_group') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_group]
+END
+IF (OBJECT_ID('x_policy_ref_user') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_user]
+END
+IF (OBJECT_ID('x_policy_ref_datamask_type') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_datamask_type]
+END
+IF (OBJECT_ID('x_policy_ref_condition') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_condition]
+END
+IF (OBJECT_ID('x_policy_ref_access_type') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_access_type]
+END
+IF (OBJECT_ID('x_policy_ref_resource') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_resource]
+END
+IF (OBJECT_ID('x_ugsync_audit_info') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_ugsync_audit_info]
+END
+IF (OBJECT_ID('x_policy_label_map') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_label_map]
+END
+IF (OBJECT_ID('x_policy_label') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_label]
+END
IF (OBJECT_ID('x_plugin_info') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_plugin_info]
@@ -729,10 +765,6 @@ IF (OBJECT_ID('x_portal_user') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_portal_user]
END
-IF (OBJECT_ID('x_ugsync_audit_info') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_ugsync_audit_info]
-END
IF (OBJECT_ID('x_db_version_h') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_db_version_h]