You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by ab...@apache.org on 2021/06/22 16:23:03 UTC
[ranger] branch master updated: RANGER-3320: ranger tags are not
added for when tagging identically named database/tables in two different
Ranger services
This is an automated email from the ASF dual-hosted git repository.
abhay pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/master by this push:
new 798393c RANGER-3320: ranger tags are not added for when tagging identically named database/tables in two different Ranger services
798393c is described below
commit 798393c0bff3ad3ffd270e2dc4640780527838e5
Author: Abhay Kulkarni <ab...@apache.org>
AuthorDate: Tue Jun 22 08:53:33 2021 -0700
RANGER-3320: ranger tags are not added for when tagging identically named database/tables in two different Ranger services
---
.../db/mysql/optimized/current/ranger_core_db_mysql.sql | 2 +-
.../patches/051-create-index-for-service-resource.sql | 9 ++++++---
.../oracle/optimized/current/ranger_core_db_oracle.sql | 2 +-
.../patches/051-create-index-for-service-resource.sql | 9 +++++++--
.../optimized/current/ranger_core_db_postgres.sql | 5 ++---
.../patches/050-create-index-for-resource-signature.sql | 10 ++++++----
.../patches/051-create-index-for-service-resource.sql | 13 +++++++++----
.../optimized/current/ranger_core_db_sqlanywhere.sql | 2 +-
.../patches/051-create-index-for-service-resource.sql | 3 ++-
.../optimized/current/ranger_core_db_sqlserver.sql | 15 +++++++--------
.../patches/052-create-index-for-service-resource.sql | 17 ++++++++---------
.../053-add-unique-constraint-on-change-logs.sql | 12 ++++++------
.../054-add-version-in-x_service_version_info.sql | 3 ++-
13 files changed, 58 insertions(+), 44 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 2024e62..c16a189 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
@@ -1560,7 +1560,7 @@ CREATE TABLE IF NOT EXISTS `x_tag_change_log` (
primary key (`id`)
) ROW_FORMAT=DYNAMIC;
-CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
+CREATE UNIQUE INDEX x_service_resource_IDX_svc_id_resource_signature ON x_service_resource(service_id, resource_signature);
CREATE INDEX x_tag_change_log_IDX_service_id ON x_tag_change_log(service_id);
CREATE INDEX x_tag_change_log_IDX_tag_version 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);
diff --git a/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql b/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql
index 73dc7c3..4bc698c 100644
--- a/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql
+++ b/security-admin/db/mysql/patches/051-create-index-for-service-resource.sql
@@ -17,9 +17,12 @@ drop procedure if exists create_index_for_x_service_resource;
delimiter ;;
create procedure create_index_for_x_service_resource() begin
-if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_service_resource' AND index_name='x_service_resource_IDX_resource_signature') then
- CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
- end if;
+if exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_service_resource' AND index_name='x_service_resource_IDX_resource_signature') then
+ DROP INDEX x_service_resource_IDX_resource_signature on x_service_resource;
+end if;
+if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_service_resource' AND index_name='x_service_resource_IDX_svc_id_resource_signature') then
+ CREATE UNIQUE INDEX x_service_resource_IDX_svc_id_resource_signature ON x_service_resource(service_id, resource_signature);
+end if;
end;;
delimiter ;
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 d2c4f27..e92f5cc 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
@@ -1235,7 +1235,7 @@ CONSTRAINT x_service_res_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_p
CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id)
);
-CREATE UNIQUE INDEX x_svc_res_IDX_res_sgn ON x_service_resource(resource_signature);
+CREATE UNIQUE INDEX x_svc_res_IDX_svc_id_res_sgn ON x_service_resource(service_id, resource_signature);
CREATE TABLE x_tag_resource_map(
id NUMBER(20) NOT NULL,
diff --git a/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql b/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql
index ac1871e..97f6e62 100644
--- a/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql
+++ b/security-admin/db/oracle/patches/051-create-index-for-service-resource.sql
@@ -18,8 +18,13 @@ DECLARE
v_index_exists number:=0;
BEGIN
SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_svc_res_IDX_res_sgn') AND TABLE_NAME= upper('x_service_resource');
- IF (v_index_exists = 0) THEN
- execute IMMEDIATE 'CREATE UNIQUE INDEX x_svc_res_IDX_res_sgn ON x_service_resource(resource_signature)';
+ IF (v_index_exists = 1) THEN
+ execute IMMEDIATE 'DROP INDEX x_svc_res_IDX_res_sgn';
commit;
END IF;
+ SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_svc_res_IDX_svc_id_res_sgn') AND TABLE_NAME= upper('x_service_resource');
+ IF (v_index_exists = 0) THEN
+ execute IMMEDIATE 'CREATE UNIQUE INDEX x_svc_res_IDX_svc_id_res_sgn ON x_service_resource(service_id, resource_signature)';
+ 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 0feeb23..91751a2 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
@@ -1092,11 +1092,10 @@ primary key (id),
CONSTRAINT x_service_res_UK_guid UNIQUE (guid),
CONSTRAINT x_service_res_FK_service_id FOREIGN KEY (service_id) REFERENCES x_service (id),
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)
+CONSTRAINT x_service_res_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id),
+CONSTRAINT x_service_resource_IDX_svc_id_resource_signature UNIQUE (service_id, resource_signature)
);
-CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
-
CREATE SEQUENCE x_tag_resource_map_seq;
CREATE TABLE x_tag_resource_map(
id BIGINT NOT NULL,
diff --git a/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql b/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql
index f4b60bd..2f22d9b 100644
--- a/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql
+++ b/security-admin/db/postgres/patches/050-create-index-for-resource-signature.sql
@@ -17,11 +17,13 @@ select 'delimiter start';
CREATE OR REPLACE FUNCTION create_index_for_x_rms_service_resource()
RETURNS void AS $$
DECLARE
- v_index_exists integer := 0;
+ v_attnum1 integer := 0;
BEGIN
- select count(*) into v_index_exists from pg_class where relname = 'x_rms_service_resource_idx_resource_signature';
- IF v_index_exists = 0 THEN
- CREATE INDEX x_rms_service_resource_IDX_resource_signature ON x_rms_service_resource(resource_signature);
+ select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_rms_service_resource') and attname in('resource_signature');
+ IF v_attnum1 > 0 THEN
+ IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_rms_service_resource') and indkey[0]=v_attnum1) THEN
+ CREATE INDEX x_rms_service_resource_IDX_resource_signature ON x_rms_service_resource(resource_signature);
+ END IF;
END IF;
END;
$$ LANGUAGE plpgsql;
diff --git a/security-admin/db/postgres/patches/051-create-index-for-service-resource.sql b/security-admin/db/postgres/patches/051-create-index-for-service-resource.sql
index c432445..7555dac 100644
--- a/security-admin/db/postgres/patches/051-create-index-for-service-resource.sql
+++ b/security-admin/db/postgres/patches/051-create-index-for-service-resource.sql
@@ -17,11 +17,16 @@ select 'delimiter start';
CREATE OR REPLACE FUNCTION create_index_for_x_service_resource()
RETURNS void AS $$
DECLARE
- v_index_exists integer := 0;
+ v_attnum1 integer := 0;
BEGIN
- select count(*) into v_index_exists from pg_class where relname = 'x_service_resource_idx_resource_signature';
- IF v_index_exists = 0 THEN
- CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
+ select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_service_resource') and attname in('resource_signature');
+ IF v_attnum1 > 0 THEN
+ IF exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_service_resource') and indkey[0]=v_attnum1 and indisunique=true) THEN
+ DROP INDEX IF EXISTS x_service_resource_IDX_resource_signature;
+ END IF;
+ END IF;
+ IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_service_resource') and lower(conname)=lower('x_service_resource_IDX_svc_id_resource_signature') and contype='u') THEN
+ ALTER TABLE x_service_resource ADD CONSTRAINT x_service_resource_IDX_svc_id_resource_signature UNIQUE(service_id, resource_signature);
END IF;
END;
$$ LANGUAGE plpgsql;
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 0ea76b9..4a1d5f4 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
@@ -943,7 +943,7 @@ CREATE TABLE dbo.x_service_resource(
CONSTRAINT x_service_res_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_service_res_UK_guid UNIQUE NONCLUSTERED (guid)
)
-CREATE UNIQUE INDEX x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
+CREATE UNIQUE INDEX x_service_resource_IDX_svc_id_resource_signature ON x_service_resource(service_id, resource_signature);
GO
CREATE TABLE dbo.x_tag_resource_map(
id bigint IDENTITY NOT NULL,
diff --git a/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql b/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql
index d6f5ddb..0cded1d 100644
--- a/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql
+++ b/security-admin/db/sqlanywhere/patches/051-create-index-for-service-resource.sql
@@ -14,7 +14,8 @@
-- limitations under the License.
CREATE INDEX IF NOT EXISTS x_service_resource_IDX_guid ON x_service_resource(guid);
-CREATE INDEX IF NOT EXISTS x_service_resource_IDX_resource_signature ON x_service_resource(resource_signature);
+DROP INDEX IF EXISTS x_service_resource_IDX_resource_signature;
+CREATE UNIQUE INDEX IF NOT EXISTS x_service_resource_IDX_svc_id_resource_signature ON x_service_resource(service_id, resource_signature);
GO
EXIT
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 4eaa77c..4535b68 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
@@ -1965,11 +1965,10 @@ CREATE TABLE [dbo].[x_service_resource](
CONSTRAINT [x_service_resource$x_service_res_UK_guid] UNIQUE NONCLUSTERED
(
[guid] ASC
-)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-) ON [PRIMARY],
-CONSTRAINT [x_service_resource$x_service_res_IDX_resource_signature] UNIQUE NONCLUSTERED
+)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
+CONSTRAINT [x_service_resource$x_service_res_IDX_svc_id_resource_signature] UNIQUE NONCLUSTERED
(
- [resource_signature] ASC
+ [service_id] ASC, [resource_signature] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_NULLS ON
@@ -2444,9 +2443,9 @@ CREATE NONCLUSTERED INDEX [x_policy_change_log_IDX_policy_version] ON [x_policy_
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
-CREATE NONCLUSTERED UNIQUE INDEX [x_policy_change_log_uk_service_id_policy_version] ON [x_policy_change_log]
+CREATE UNIQUE NONCLUSTERED INDEX [x_policy_change_log_uk_service_id_policy_version] ON [x_policy_change_log]
(
- [service_id,policy_version] ASC
+ [service_id] ASC,[policy_version] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
@@ -2622,9 +2621,9 @@ CREATE NONCLUSTERED INDEX [x_tag_change_log_IDX_tag_version] ON [x_tag_change_lo
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
-CREATE NONCLUSTERED UNIQUE INDEX [x_tag_change_log_uk_service_id_service_tags_version] ON [x_tag_change_log]
+CREATE UNIQUE NONCLUSTERED INDEX [x_tag_change_log_uk_service_id_service_tags_version] ON [x_tag_change_log]
(
- [service_id, service_tags_version] ASC
+ [service_id] ASC, [service_tags_version] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
diff --git a/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.sql b/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.sql
index 301aa42..ad09c6c 100644
--- a/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.sql
+++ b/security-admin/db/sqlserver/patches/052-create-index-for-service-resource.sql
@@ -13,14 +13,13 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
GO
-IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_service_resource_IDX_resource_signature' AND object_id = OBJECT_ID('x_service_resource'))
+IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_service_res_IDX_resource_signature' AND object_id = OBJECT_ID('x_service_resource'))
BEGIN
- CREATE UNIQUE INDEX NONCLUSTERED [x_service_res_IDX_resource_signature] ON [x_service_resource]
- (
- [resource_signature] ASC
- )
- WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
+ DROP INDEX [x_service_res_IDX_resource_signature] ON [x_service_resource];
END
-Go
-
-EXIT;
+IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_service_res_IDX_svc_id_resource_signature' AND object_id = OBJECT_ID('x_service_resource'))
+BEGIN
+ CREATE UNIQUE NONCLUSTERED INDEX [x_service_res_IDX_svc_id_resource_signature] ON [x_service_resource] ([service_id] ASC, [resource_signature] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
+END
+GO
+exit
diff --git a/security-admin/db/sqlserver/patches/053-add-unique-constraint-on-change-logs.sql b/security-admin/db/sqlserver/patches/053-add-unique-constraint-on-change-logs.sql
index f3b5709..ce290fc 100644
--- a/security-admin/db/sqlserver/patches/053-add-unique-constraint-on-change-logs.sql
+++ b/security-admin/db/sqlserver/patches/053-add-unique-constraint-on-change-logs.sql
@@ -15,16 +15,16 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
-IF NOT EXISTS(select * from sys.indexes where name='x_policy_change_log_uk_service_id_policy_version')
+IF NOT EXISTS(select * from sys.indexes where name='x_policy_change_log_uk_service_id_policy_version' AND object_id = OBJECT_ID('x_policy_change_log'))
BEGIN
- TRUNCATE x_policy_change_log;
- CREATE NONCLUSTERED UNIQUE INDEX [x_policy_change_log_uk_service_id_policy_version] ON [x_policy_change_log]([service_id, policy_version] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
+ TRUNCATE TABLE x_policy_change_log;
+ CREATE UNIQUE NONCLUSTERED INDEX [x_policy_change_log_uk_service_id_policy_version] ON [x_policy_change_log]([service_id] ASC, [policy_version] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
END
GO
-IF NOT EXISTS(select * from sys.indexes where name='x_tag_change_log_uk_service_id_service_tags_version')
+IF NOT EXISTS(select * from sys.indexes where name='x_tag_change_log_uk_service_id_service_tags_version' AND object_id = OBJECT_ID('x_tag_change_log'))
BEGIN
- TRUNCATE x_tag_change_log;
- CREATE NONCLUSTERED UNIQUE INDEX [x_tag_change_log_uk_service_id_service_tags_version] ON [x_tag_change_log]([service_id, service_tags_version] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
+ TRUNCATE TABLE x_tag_change_log;
+ CREATE UNIQUE NONCLUSTERED INDEX [x_tag_change_log_uk_service_id_service_tags_version] ON [x_tag_change_log]([service_id] ASC, [service_tags_version] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
END
GO
exit
diff --git a/security-admin/db/sqlserver/patches/054-add-version-in-x_service_version_info.sql b/security-admin/db/sqlserver/patches/054-add-version-in-x_service_version_info.sql
index d72d632..f271300 100644
--- a/security-admin/db/sqlserver/patches/054-add-version-in-x_service_version_info.sql
+++ b/security-admin/db/sqlserver/patches/054-add-version-in-x_service_version_info.sql
@@ -16,6 +16,7 @@
GO
IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_service_version_info' and column_name = 'version')
BEGIN
- ALTER TABLE [dbo].[x_service_version_info] ADD [version] [bigint] NOT NULL DEFAULT 1;
+ ALTER TABLE [dbo].[x_service_version_info] ADD [version] [bigint] DEFAULT 1 NOT NULL;
END
+GO
exit
\ No newline at end of file