This is an automated email from the ASF dual-hosted git repository.
pradeep pushed a commit to branch ranger-2.2
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/ranger-2.2 by this push:
new 596c30c RANGER-3324: Make optimised db schema script idempotent for
all DB Flavors
596c30c is described below
commit 596c30c74692ab528dc18038c609ef98d0d0e0cf
Author: pradeep <[email protected]>
AuthorDate: Wed Jun 30 13:38:29 2021 +0530
RANGER-3324: Make optimised db schema script idempotent for all DB Flavors
---
.../optimized/current/ranger_core_db_mysql.sql | 50 +++-----
.../optimized/current/ranger_core_db_oracle.sql | 61 +++------
.../052-add-unique-constraint-on-change-logs.sql | 8 +-
.../optimized/current/ranger_core_db_postgres.sql | 34 ++---
.../current/ranger_core_db_sqlanywhere.sql | 82 ++++--------
.../optimized/current/ranger_core_db_sqlserver.sql | 138 ++++++++-------------
6 files changed, 125 insertions(+), 248 deletions(-)
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 c16a189..1ba3e69 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,9 +14,18 @@
-- limitations under the License.
DROP VIEW IF EXISTS `vx_trx_log`;
-DROP TABLE IF EXISTS `x_security_zone_ref_resource`;
-DROP TABLE IF EXISTS `x_policy_change_log`;
+DROP TABLE IF EXISTS `x_rms_mapping_provider`;
+DROP TABLE IF EXISTS `x_rms_resource_mapping`;
+DROP TABLE IF EXISTS `x_rms_notification`;
+DROP TABLE IF EXISTS `x_rms_service_resource`;
DROP TABLE IF EXISTS `x_tag_change_log`;
+DROP TABLE IF EXISTS `x_role_ref_role`;
+DROP TABLE IF EXISTS `x_policy_ref_role`;
+DROP TABLE IF EXISTS `x_role_ref_group`;
+DROP TABLE IF EXISTS `x_role_ref_user`;
+DROP TABLE IF EXISTS `x_role`;
+DROP TABLE IF EXISTS `x_policy_change_log`;
+DROP TABLE IF EXISTS `x_security_zone_ref_resource`;
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`;
@@ -31,11 +40,7 @@ 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`;
DROP TABLE IF EXISTS `x_tag_resource_map`;
-DROP TABLE IF EXISTS `x_tag_attr`;
-DROP TABLE IF EXISTS `x_tag_attr_def`;
-DROP TABLE IF EXISTS `x_service_resource_element`;
DROP TABLE IF EXISTS `x_service_resource`;
DROP TABLE IF EXISTS `x_tag`;
DROP TABLE IF EXISTS `x_tag_def`;
@@ -62,8 +67,8 @@ DROP TABLE IF EXISTS `x_service_config_def`;
DROP TABLE IF EXISTS `x_policy`;
DROP TABLE IF EXISTS `x_security_zone_ref_group`;
DROP TABLE IF EXISTS `x_security_zone_ref_user`;
-DROP TABLE IF EXISTS `x_security_zone_ref_service`;
DROP TABLE IF EXISTS `x_security_zone_ref_tag_srvc`;
+DROP TABLE IF EXISTS `x_security_zone_ref_service`;
DROP TABLE IF EXISTS `x_ranger_global_state`;
DROP TABLE IF EXISTS `x_security_zone`;
DROP TABLE IF EXISTS `x_service`;
@@ -77,11 +82,6 @@ DROP TABLE IF EXISTS `x_group_users`;
DROP TABLE IF EXISTS `x_user`;
DROP TABLE IF EXISTS `x_group_groups`;
DROP TABLE IF EXISTS `x_group`;
-DROP TABLE IF EXISTS `x_role_ref_role`;
-DROP TABLE IF EXISTS `x_policy_ref_role`;
-DROP TABLE IF EXISTS `x_role_ref_group`;
-DROP TABLE IF EXISTS `x_role_ref_user`;
-DROP TABLE IF EXISTS `x_role`;
DROP TABLE IF EXISTS `x_db_base`;
DROP TABLE IF EXISTS `x_cred_store`;
DROP TABLE IF EXISTS `x_auth_sess`;
@@ -1081,9 +1081,6 @@ CONSTRAINT `x_group_module_perm_FK_module_id` FOREIGN KEY
(`module_id`) REFERENC
CONSTRAINT `x_group_module_perm_FK_user_id` FOREIGN KEY (`group_id`)
REFERENCES `x_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ROW_FORMAT=DYNAMIC;
--- -----------------------------------------------------
--- Table `x_tag_def`
--- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `x_tag_def` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`guid` VARCHAR(64) NOT NULL,
@@ -1104,9 +1101,7 @@ KEY `x_tag_def_IDX_upd_by_id` (`upd_by_id`),
CONSTRAINT `x_tag_def_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES
`x_portal_user` (`id`),
CONSTRAINT `x_tag_def_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES
`x_portal_user` (`id`)
)ROW_FORMAT=DYNAMIC;
--- -----------------------------------------------------
--- Table `x_tag`
--- -----------------------------------------------------
+
CREATE TABLE IF NOT EXISTS `x_tag` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`guid` VARCHAR(64) NOT NULL,
@@ -1128,9 +1123,7 @@ CONSTRAINT `x_tag_FK_type` FOREIGN KEY (`type`)
REFERENCES `x_tag_def` (`id`),
CONSTRAINT `x_tag_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES
`x_portal_user` (`id`),
CONSTRAINT `x_tag_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES
`x_portal_user` (`id`)
)ROW_FORMAT=DYNAMIC;
--- -----------------------------------------------------
--- Table `x_service_resource`
--- -----------------------------------------------------
+
CREATE TABLE IF NOT EXISTS `x_service_resource` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`guid` VARCHAR(64) NOT NULL,
@@ -1152,9 +1145,7 @@ CONSTRAINT `x_service_res_FK_service_id` FOREIGN KEY
(`service_id`) REFERENCES `
CONSTRAINT `x_service_res_FK_added_by_id` FOREIGN KEY (`added_by_id`)
REFERENCES `x_portal_user` (`id`),
CONSTRAINT `x_service_res_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES
`x_portal_user` (`id`)
)ROW_FORMAT=DYNAMIC;
--- -----------------------------------------------------
--- Table `x_tag_resource_map`
--- -----------------------------------------------------
+
CREATE TABLE IF NOT EXISTS `x_tag_resource_map` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`guid` VARCHAR(64) NOT NULL,
@@ -1322,7 +1313,6 @@ CREATE TABLE IF NOT EXISTS `x_ugsync_audit_info`(
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,
`guid` varchar(1024) DEFAULT NULL,
@@ -1341,7 +1331,6 @@ CREATE TABLE IF NOT EXISTS `x_policy_ref_resource` (
CONSTRAINT `x_policy_ref_res_FK_resource_def_id` FOREIGN KEY
(`resource_def_id`) REFERENCES `x_resource_def` (`id`)
) ROW_FORMAT=DYNAMIC;
-DROP TABLE IF EXISTS `x_policy_ref_access_type`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_access_type` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(1024) DEFAULT NULL,
@@ -1360,7 +1349,6 @@ CREATE TABLE IF NOT EXISTS `x_policy_ref_access_type` (
CONSTRAINT `x_policy_ref_access_FK_access_def_id` FOREIGN KEY
(`access_def_id`) REFERENCES `x_access_type_def` (`id`)
) ROW_FORMAT=DYNAMIC;
-DROP TABLE IF EXISTS `x_policy_ref_condition`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_condition` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(1024) DEFAULT NULL,
@@ -1379,7 +1367,6 @@ CREATE TABLE IF NOT EXISTS `x_policy_ref_condition` (
CONSTRAINT `x_policy_ref_condition_FK_condition_def_id` FOREIGN KEY
(`condition_def_id`) REFERENCES `x_policy_condition_def` (`id`)
) ROW_FORMAT=DYNAMIC;
-DROP TABLE IF EXISTS `x_policy_ref_datamask_type`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_datamask_type` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(1024) DEFAULT NULL,
@@ -1398,7 +1385,6 @@ CREATE TABLE IF NOT EXISTS `x_policy_ref_datamask_type` (
CONSTRAINT `x_policy_ref_datamask_FK_datamask_def_id` FOREIGN KEY
(`datamask_def_id`) REFERENCES `x_datamask_type_def` (`id`)
) ROW_FORMAT=DYNAMIC;
-DROP TABLE IF EXISTS `x_policy_ref_user`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(1024) DEFAULT NULL,
@@ -1417,7 +1403,6 @@ CREATE TABLE IF NOT EXISTS `x_policy_ref_user` (
CONSTRAINT `x_policy_ref_user_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES
`x_user` (`id`)
) ROW_FORMAT=DYNAMIC;
-DROP TABLE IF EXISTS `x_policy_ref_group`;
CREATE TABLE IF NOT EXISTS `x_policy_ref_group` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(1024) DEFAULT NULL,
@@ -1618,11 +1603,6 @@ END $$
DELIMITER ;
-DROP TABLE IF EXISTS `x_rms_notification`;
-DROP TABLE IF EXISTS `x_rms_resource_mapping`;
-DROP TABLE IF EXISTS `x_rms_mapping_provider`;
-DROP TABLE IF EXISTS `x_rms_service_resource`;
-
CREATE TABLE `x_rms_service_resource` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` varchar(64) NOT NULL,
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 e92f5cc..021c34c 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
@@ -204,9 +204,18 @@ END;/
/
call spdropview('vx_trx_log');
-call spdroptable('x_security_zone_ref_resource');
-call spdroptable('x_policy_change_log');
+call spdroptable('X_RMS_MAPPING_PROVIDER');
+call spdroptable('X_RMS_RESOURCE_MAPPING');
+call spdroptable('X_RMS_NOTIFICATION');
+call spdroptable('X_RMS_SERVICE_RESOURCE');
call spdroptable('x_tag_change_log');
+call spdroptable('x_role_ref_role');
+call spdroptable('x_policy_ref_role');
+call spdroptable('x_role_ref_group');
+call spdroptable('x_role_ref_user');
+call spdroptable('x_role');
+call spdroptable('x_policy_change_log');
+call spdroptable('x_security_zone_ref_resource');
call spdroptable('x_policy_ref_group');
call spdroptable('x_policy_ref_user');
call spdroptable('x_policy_ref_datamask_type');
@@ -221,11 +230,7 @@ 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');
@@ -252,8 +257,8 @@ call spdroptable('x_service_config_def');
call spdroptable('x_policy');
call spdroptable('x_security_zone_ref_group');
call spdroptable('x_security_zone_ref_user');
-call spdroptable('x_security_zone_ref_service');
call spdroptable('x_security_zone_ref_tag_srvc');
+call spdroptable('x_security_zone_ref_service');
call spdroptable('x_ranger_global_state');
call spdroptable('x_security_zone');
call spdroptable('x_service');
@@ -264,13 +269,6 @@ call spdroptable('x_trx_log');
call spdroptable('x_resource');
call spdroptable('x_policy_export_audit');
call spdroptable('x_group_users');
-
-call spdroptable('x_role_ref_role');
-call spdroptable('x_policy_ref_role');
-call spdroptable('x_role_ref_group');
-call spdroptable('x_role_ref_user');
-call spdroptable('x_role');
-
call spdroptable('x_user');
call spdroptable('x_group_groups');
call spdroptable('x_group');
@@ -1510,7 +1508,7 @@ policy_id NUMBER(20) DEFAULT NULL NULL,
);
CREATE INDEX x_plcy_chng_log_IDX_service_id ON x_policy_change_log(service_id);
CREATE INDEX x_plcy_chng_log_IDX_policy_ver ON
x_policy_change_log(policy_version);
-CREATE UNIQUE INDEX X_POLICY_CHANGE_LOG_UK_SERVICE_ID_POLICY_VERSION ON
x_policy_change_log(service_id, policy_version);
+CREATE UNIQUE INDEX XPLCYCHNGLOG_UK_SRVCID_PLCYVER ON
x_policy_change_log(service_id, policy_version);
COMMIT;
CREATE TABLE x_tag_change_log (
@@ -1525,7 +1523,7 @@ primary key (id)
);
CREATE INDEX x_tag_chng_log_IDX_service_id ON x_tag_change_log(service_id);
CREATE INDEX x_tag_chng_log_IDX_tag_ver ON
x_tag_change_log(service_tags_version);
-CREATE UNIQUE INDEX X_TAG_CHANGE_LOG_UK_SERVICE_ID_SERVICE_TAGS_VERSION ON
x_tag_change_log(service_id, service_tags_version);
+CREATE UNIQUE INDEX XTAGCHNGLOG_UK_SRVCID_TAGVER ON
x_tag_change_log(service_id, service_tags_version);
COMMIT;
CREATE TABLE x_role(
@@ -1810,22 +1808,10 @@ end;
END; /
/
-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_RMS_SERVICE_RESOURCE_SEQ');
call spdropsequence('X_RMS_NOTIFICATION_SEQ');
call spdropsequence('X_RMS_RESOURCE_MAPPING_SEQ');
call spdropsequence('X_RMS_MAPPING_PROVIDER_SEQ');
-
commit;
CREATE SEQUENCE X_RMS_SERVICE_RESOURCE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
@@ -1833,23 +1819,6 @@ CREATE SEQUENCE X_RMS_NOTIFICATION_SEQ START WITH 1
INCREMENT BY 1 NOCACHE NOCYC
CREATE SEQUENCE X_RMS_RESOURCE_MAPPING_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
CREATE SEQUENCE X_RMS_MAPPING_PROVIDER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE
NOCYCLE;
-
-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_RMS_NOTIFICATION');
-call spdroptable('X_RMS_RESOURCE_MAPPING');
-call spdroptable('X_RMS_MAPPING_PROVIDER');
-call spdroptable('X_RMS_SERVICE_RESOURCE');
-
CREATE TABLE x_rms_service_resource(
id NUMBER(20) NOT NULL,
guid VARCHAR(1024) DEFAULT NULL NULL,
@@ -1868,7 +1837,7 @@ CONSTRAINT x_rms_svc_res_FK_service_id FOREIGN KEY
(service_id) REFERENCES x_ser
);
CREATE INDEX x_rms_svc_res_IDX_service_id ON
x_rms_service_resource(service_id);
-CREATE INDEX x_rms_svc_res_IDX_resource_signature ON
x_rms_service_resource(resource_signature);
+CREATE INDEX x_rms_svc_res_IDX_res_sign ON
x_rms_service_resource(resource_signature);
CREATE TABLE x_rms_notification (
id NUMBER(20) NOT NULL,
diff --git
a/security-admin/db/oracle/patches/052-add-unique-constraint-on-change-logs.sql
b/security-admin/db/oracle/patches/052-add-unique-constraint-on-change-logs.sql
index 8c06720..af39c74 100644
---
a/security-admin/db/oracle/patches/052-add-unique-constraint-on-change-logs.sql
+++
b/security-admin/db/oracle/patches/052-add-unique-constraint-on-change-logs.sql
@@ -16,17 +16,17 @@
DECLARE
v_count number:=0;
BEGIN
- select count(*) into v_count from user_ind_columns where
table_name='X_POLICY_CHANGE_LOG' and
index_name='X_POLICY_CHANGE_LOG_UK_SERVICE_ID_POLICY_VERSION';
+ select count(*) into v_count from user_ind_columns where
table_name='X_POLICY_CHANGE_LOG' and
index_name='XPLCYCHNGLOG_UK_SRVCID_PLCYVER';
if (v_count = 0) THEN
execute immediate 'TRUNCATE TABLE X_POLICY_CHANGE_LOG';
- execute immediate 'CREATE UNIQUE INDEX
X_POLICY_CHANGE_LOG_UK_SERVICE_ID_POLICY_VERSION ON
X_POLICY_CHANGE_LOG(service_id, policy_version)';
+ execute immediate 'CREATE UNIQUE INDEX
XPLCYCHNGLOG_UK_SRVCID_PLCYVER ON X_POLICY_CHANGE_LOG(service_id,
policy_version)';
commit;
end if;
- select count(*) into v_count from user_ind_columns where
table_name='X_TAG_CHANGE_LOG' and
index_name='X_TAG_CHANGE_LOG_UK_SERVICE_ID_SERVICE_TAGS_VERSION';
+ select count(*) into v_count from user_ind_columns where
table_name='X_TAG_CHANGE_LOG' and index_name='XTAGCHNGLOG_UK_SRVCID_TAGVER';
if (v_count = 0) THEN
execute immediate 'TRUNCATE TABLE X_TAG_CHANGE_LOG';
- execute immediate 'CREATE UNIQUE INDEX
X_TAG_CHANGE_LOG_UK_SERVICE_ID_SERVICE_TAGS_VERSION ON
X_TAG_CHANGE_LOG(service_id, service_tags_version)';
+ execute immediate 'CREATE UNIQUE INDEX
XTAGCHNGLOG_UK_SRVCID_TAGVER ON X_TAG_CHANGE_LOG(service_id,
service_tags_version)';
commit;
end if;
END;/
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 91751a2..89e9e77 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,9 +13,18 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
-DROP TABLE IF EXISTS x_security_zone_ref_resource CASCADE;
-DROP TABLE IF EXISTS x_policy_change_log;
+DROP TABLE IF EXISTS x_rms_mapping_provider CASCADE;
+DROP TABLE IF EXISTS x_rms_resource_mapping CASCADE;
+DROP TABLE IF EXISTS x_rms_notification CASCADE;
+DROP TABLE IF EXISTS x_rms_service_resource CASCADE;
DROP TABLE IF EXISTS x_tag_change_log;
+DROP TABLE IF EXISTS x_role_ref_role CASCADE;
+DROP TABLE IF EXISTS x_policy_ref_role CASCADE;
+DROP TABLE IF EXISTS x_role_ref_group CASCADE;
+DROP TABLE IF EXISTS x_role_ref_user CASCADE;
+DROP TABLE IF EXISTS x_role CASCADE;
+DROP TABLE IF EXISTS x_policy_change_log;
+DROP TABLE IF EXISTS x_security_zone_ref_resource CASCADE;
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;
@@ -30,11 +39,7 @@ 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;
@@ -61,8 +66,8 @@ DROP TABLE IF EXISTS x_service_config_def CASCADE;
DROP TABLE IF EXISTS x_policy CASCADE;
DROP TABLE IF EXISTS x_security_zone_ref_group CASCADE;
DROP TABLE IF EXISTS x_security_zone_ref_user CASCADE;
-DROP TABLE IF EXISTS x_security_zone_ref_service CASCADE;
DROP TABLE IF EXISTS x_security_zone_ref_tag_srvc CASCADE;
+DROP TABLE IF EXISTS x_security_zone_ref_service CASCADE;
DROP TABLE IF EXISTS x_ranger_global_state CASCADE;
DROP TABLE IF EXISTS x_security_zone CASCADE;
DROP TABLE IF EXISTS x_service CASCADE;
@@ -72,13 +77,6 @@ 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_role_ref_role CASCADE;
-DROP TABLE IF EXISTS x_policy_ref_role CASCADE;
-DROP TABLE IF EXISTS x_role_ref_group CASCADE;
-DROP TABLE IF EXISTS x_role_ref_user CASCADE;
-DROP TABLE IF EXISTS x_role CASCADE;
-
DROP TABLE IF EXISTS x_group_users CASCADE;
DROP TABLE IF EXISTS x_user CASCADE;
DROP TABLE IF EXISTS x_group_groups;
@@ -92,11 +90,6 @@ 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 TABLE IF EXISTS x_rms_service_resource CASCADE;
-DROP TABLE IF EXISTS x_rms_notification CASCADE;
-DROP TABLE IF EXISTS x_rms_resource_mapping CASCADE;
-DROP TABLE IF EXISTS x_rms_mapping_provider CASCADE;
-
DROP SEQUENCE IF EXISTS x_sec_zone_ref_group_seq;
DROP SEQUENCE IF EXISTS x_sec_zone_ref_user_seq;
DROP SEQUENCE IF EXISTS x_sec_zone_ref_resource_seq;
@@ -152,13 +145,11 @@ 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_role_ref_role_SEQ;
DROP SEQUENCE IF EXISTS x_policy_ref_role_SEQ;
DROP SEQUENCE IF EXISTS x_role_ref_group_SEQ;
DROP SEQUENCE IF EXISTS x_role_ref_user_SEQ;
DROP SEQUENCE IF EXISTS x_role_SEQ;
-
DROP SEQUENCE IF EXISTS x_user_seq;
DROP SEQUENCE IF EXISTS x_group_groups_seq;
DROP SEQUENCE IF EXISTS x_group_seq;
@@ -169,7 +160,6 @@ 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;
-
DROP SEQUENCE IF EXISTS X_RMS_SERVICE_RESOURCE_SEQ;
DROP SEQUENCE IF EXISTS X_RMS_NOTIFICATION_SEQ;
DROP SEQUENCE IF EXISTS X_RMS_RESOURCE_MAPPING_SEQ;
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 4a1d5f4..b90a48e 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
@@ -34,13 +34,34 @@ BEGIN
SET @drpstmt = 'DROP TABLE IF EXISTS ' + @tblname;
execute(@drpstmt)
END
+
GO
-call dbo.removeForeignKeysAndTable('x_security_zone_ref_resource')
+DROP VIEW IF EXISTS dbo.vx_trx_log
GO
-call dbo.removeForeignKeysAndTable('x_policy_change_log')
+call dbo.removeForeignKeysAndTable('x_rms_mapping_provider')
+GO
+call dbo.removeForeignKeysAndTable('x_rms_resource_mapping')
+GO
+call dbo.removeForeignKeysAndTable('x_rms_notification')
+GO
+call dbo.removeForeignKeysAndTable('x_rms_service_resource')
GO
call dbo.removeForeignKeysAndTable('x_tag_change_log')
GO
+call dbo.removeForeignKeysAndTable('x_role_ref_role')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_ref_role')
+GO
+call dbo.removeForeignKeysAndTable('x_role_ref_group')
+GO
+call dbo.removeForeignKeysAndTable('x_role_ref_user')
+GO
+call dbo.removeForeignKeysAndTable('x_role')
+GO
+call dbo.removeForeignKeysAndTable('x_policy_change_log')
+GO
+call dbo.removeForeignKeysAndTable('x_security_zone_ref_resource')
+GO
call dbo.removeForeignKeysAndTable('x_policy_ref_group')
GO
call dbo.removeForeignKeysAndTable('x_policy_ref_user')
@@ -69,16 +90,8 @@ 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')
@@ -131,10 +144,10 @@ call
dbo.removeForeignKeysAndTable('x_security_zone_ref_group')
GO
call dbo.removeForeignKeysAndTable('x_security_zone_ref_user')
GO
-call dbo.removeForeignKeysAndTable('x_security_zone_ref_service')
-GO
call dbo.removeForeignKeysAndTable('x_security_zone_ref_tag_srvc')
GO
+call dbo.removeForeignKeysAndTable('x_security_zone_ref_service')
+GO
call dbo.removeForeignKeysAndTable('x_ranger_global_state')
GO
call dbo.removeForeignKeysAndTable('x_security_zone')
@@ -147,8 +160,6 @@ 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')
@@ -157,18 +168,6 @@ call dbo.removeForeignKeysAndTable('x_policy_export_audit')
GO
call dbo.removeForeignKeysAndTable('x_group_users')
GO
-
-call dbo.removeForeignKeysAndTable('x_role_ref_role')
-GO
-call dbo.removeForeignKeysAndTable('x_policy_ref_role')
-GO
-call dbo.removeForeignKeysAndTable('x_role_ref_group')
-GO
-call dbo.removeForeignKeysAndTable('x_role_ref_user')
-GO
-call dbo.removeForeignKeysAndTable('x_role')
-
-GO
call dbo.removeForeignKeysAndTable('x_user')
GO
call dbo.removeForeignKeysAndTable('x_group_groups')
@@ -2064,37 +2063,6 @@ GO
CREATE NONCLUSTERED INDEX x_tag_change_log_uk_service_id_service_tags_version
ON dbo.x_tag_change_log((service_id, service_tags_version) ASC);
GO
-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_rms_notification')
-GO
-call dbo.removeForeignKeysAndTable('x_rms_resource_mapping')
-GO
-call dbo.removeForeignKeysAndTable('x_rms_mapping_provider')
-GO
-call dbo.removeForeignKeysAndTable('x_rms_service_resource')
-GO
-
CREATE TABLE dbo.x_rms_service_resource(
id BIGINT IDENTITY NOT NULL,
guid VARCHAR(64) NOT NULL,
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 4535b68..7e03289 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
@@ -621,14 +621,58 @@ IF (OBJECT_ID('vx_trx_log') IS NOT NULL)
BEGIN
DROP VIEW [dbo].[vx_trx_log]
END
-IF (OBJECT_ID('x_policy_change_log') IS NOT NULL)
+IF (OBJECT_ID('x_rms_mapping_provider') IS NOT NULL)
BEGIN
- DROP TABLE [dbo].[x_policy_change_log]
+ DROP TABLE [dbo].[x_rms_mapping_provider]
+END
+IF (OBJECT_ID('x_rms_resource_mapping') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_rms_resource_mapping]
+END
+IF (OBJECT_ID('x_rms_notification') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_rms_notification]
+END
+IF (OBJECT_ID('x_rms_service_resource') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_rms_service_resource]
END
IF (OBJECT_ID('x_tag_change_log') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_tag_change_log]
END
+IF (OBJECT_ID('x_role_ref_role') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_role_ref_role]
+END
+
+IF (OBJECT_ID('x_policy_ref_role') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_ref_role]
+END
+
+IF (OBJECT_ID('x_role_ref_group') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_role_ref_group]
+END
+
+IF (OBJECT_ID('x_role_ref_user') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_role_ref_user]
+END
+
+IF (OBJECT_ID('x_role') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_role]
+END
+IF (OBJECT_ID('x_policy_change_log') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_policy_change_log]
+END
+IF (OBJECT_ID('x_security_zone_ref_resource') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_security_zone_ref_resource]
+END
IF (OBJECT_ID('x_policy_ref_group') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_policy_ref_group]
@@ -685,26 +729,10 @@ IF (OBJECT_ID('x_datamask_type_def') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_datamask_type_def]
END
-IF (OBJECT_ID('x_service_resource_element_val') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_service_resource_element_val]
-END
IF (OBJECT_ID('x_tag_resource_map') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_tag_resource_map]
END
-IF (OBJECT_ID('x_tag_attr') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_tag_attr]
-END
-IF (OBJECT_ID('x_tag_attr_def') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_tag_attr_def]
-END
-IF (OBJECT_ID('x_service_resource_element') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_service_resource_element]
-END
IF (OBJECT_ID('x_service_resource') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_service_resource]
@@ -797,37 +825,25 @@ IF (OBJECT_ID('x_service_config_def') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_service_config_def]
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_policy') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_policy]
END
-IF (OBJECT_ID('x_security_zone_ref_service') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_security_zone_ref_service]
-END
-IF (OBJECT_ID('x_security_zone_ref_tag_srvc') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_security_zone_ref_tag_srvc]
-END
-IF (OBJECT_ID('x_security_zone_ref_resource') IS NOT NULL)
+IF (OBJECT_ID('x_security_zone_ref_group') IS NOT NULL)
BEGIN
- DROP TABLE [dbo].[x_security_zone_ref_resource]
+ DROP TABLE [dbo].[x_security_zone_ref_group]
END
IF (OBJECT_ID('x_security_zone_ref_user') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_security_zone_ref_user]
END
-IF (OBJECT_ID('x_security_zone_ref_group') IS NOT NULL)
+IF (OBJECT_ID('x_security_zone_ref_tag_srvc') IS NOT NULL)
BEGIN
- DROP TABLE [dbo].[x_security_zone_ref_group]
+ DROP TABLE [dbo].[x_security_zone_ref_tag_srvc]
+END
+IF (OBJECT_ID('x_security_zone_ref_service') IS NOT NULL)
+BEGIN
+ DROP TABLE [dbo].[x_security_zone_ref_service]
END
IF (OBJECT_ID('x_ranger_global_state') IS NOT NULL)
BEGIN
@@ -869,32 +885,6 @@ IF (OBJECT_ID('x_group_users') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_group_users]
END
-
-IF (OBJECT_ID('x_role_ref_role') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_role_ref_role]
-END
-
-IF (OBJECT_ID('x_policy_ref_role') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_policy_ref_role]
-END
-
-IF (OBJECT_ID('x_role_ref_group') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_role_ref_group]
-END
-
-IF (OBJECT_ID('x_role_ref_user') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_role_ref_user]
-END
-
-IF (OBJECT_ID('x_role') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_role]
-END
-
IF (OBJECT_ID('x_user') IS NOT NULL)
BEGIN
DROP TABLE [dbo].[x_user]
@@ -3894,26 +3884,6 @@ BEGIN
ALTER TABLE [dbo].[x_rms_resource_mapping] DROP CONSTRAINT
x_rms_res_map_FK_ll_res_id
END
GO
-IF (OBJECT_ID('x_rms_notification') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_rms_notification]
-END
-GO
-IF (OBJECT_ID('x_rms_resource_mapping') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_rms_resource_mapping]
-END
-GO
-IF (OBJECT_ID('x_rms_mapping_provider') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_rms_mapping_provider]
-END
-GO
-IF (OBJECT_ID('x_rms_service_resource') IS NOT NULL)
-BEGIN
- DROP TABLE [dbo].[x_rms_service_resource]
-END
-GO
SET ANSI_NULLS ON
