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 2021/12/02 08:38:11 UTC
[ranger] 01/02: RANGER-3435: Add unique index on guid, service and zone_id column of x_policy table
This is an automated email from the ASF dual-hosted git repository.
pradeep pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
commit 8968eaef8693b39a32f10c2feebf0035b34e3763
Author: pradeep <pr...@apache.org>
AuthorDate: Mon Nov 22 09:28:37 2021 +0530
RANGER-3435: Add unique index on guid, service and zone_id column of x_policy table
---
.../db/mysql/optimized/current/ranger_core_db_mysql.sql | 2 +-
...unique-constraint-on-x_policy-table-guid-service-column.sql | 6 +++---
.../db/oracle/optimized/current/ranger_core_db_oracle.sql | 2 +-
...unique-constraint-on-x_policy-table-guid-service-column.sql | 10 +++++-----
.../db/postgres/optimized/current/ranger_core_db_postgres.sql | 2 +-
...unique-constraint-on-x_policy-table-guid-service-column.sql | 10 ++++++----
.../optimized/current/ranger_core_db_sqlanywhere.sql | 2 +-
...unique-constraint-on-x_policy-table-guid-service-column.sql | 6 +++---
.../sqlserver/optimized/current/ranger_core_db_sqlserver.sql | 4 ++--
...unique-constraint-on-x_policy-table-guid-service-column.sql | 8 ++++----
10 files changed, 27 insertions(+), 25 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 e444e78..df27a9d 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
@@ -694,7 +694,7 @@ KEY `x_policy_up_time` (`update_time`),
KEY `x_policy_service` (`service`),
KEY `x_policy_resource_signature` (`resource_signature`),
UNIQUE KEY `x_policy_UK_name_service_zone` (`name`(180),`service`, `zone_id`),
-UNIQUE KEY `x_policy_UK_guid_service` (`guid`(180),`service`),
+UNIQUE KEY `x_policy_UK_guid_service_zone` (`guid`(180),`service`, `zone_id`),
UNIQUE KEY `x_policy_UK_service_signature` (`service`,`resource_signature`),
CONSTRAINT `x_policy_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`),
CONSTRAINT `x_policy_FK_upd_by_id` FOREIGN KEY (`upd_by_id`) REFERENCES `x_portal_user` (`id`),
diff --git a/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
index 357b7ef..65a78c9 100644
--- a/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
+++ b/security-admin/db/mysql/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
@@ -18,10 +18,10 @@ drop procedure if exists create_unique_constraint_on_guid_service;
delimiter ;;
create procedure create_unique_constraint_on_guid_service() begin
/* check table and columns exist or not */
- if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('guid','service')) then
+ if exists (select * from information_schema.columns where table_schema=database() and table_name = 'x_policy' and column_name in('guid','service','zone_id')) then
/* check unique constraint exist on guid and service column or not */
- if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_policy' and constraint_name='x_policy_UK_guid_service') then
- ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_guid_service(guid(180),service);
+ if not exists (select * from information_schema.table_constraints where table_schema=database() and table_name = 'x_policy' and constraint_name='x_policy_UK_guid_service_zone') then
+ ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_guid_service_zone(guid(180),service,zone_id);
end if;
end if;
end;;
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 9e5da70..5434f58 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
@@ -794,7 +794,7 @@ policy_text CLOB DEFAULT NULL NULL,
zone_id NUMBER(20) DEFAULT '1' NOT NULL,
primary key (id),
CONSTRAINT x_policy_UK_name_service_zone UNIQUE (name,service,zone_id),
-CONSTRAINT x_policy_UK_guid_service UNIQUE (guid,service),
+CONSTRAINT x_policy_UK_guid_service_zone UNIQUE (guid,service,zone_id),
CONSTRAINT x_policy_UK_service_signature UNIQUE (service,resource_signature),
CONSTRAINT x_policy_FK_added_by_id FOREIGN KEY (added_by_id) REFERENCES x_portal_user (id),
CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY (upd_by_id) REFERENCES x_portal_user (id),
diff --git a/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
index 580841c..aea3efd 100644
--- a/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
+++ b/security-admin/db/oracle/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
@@ -16,14 +16,14 @@
DECLARE
v_count number:=0;
BEGIN
- select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('GUID','SERVICE');
- if (v_count = 2) THEN
+ select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('GUID','SERVICE','ZONE_ID');
+ if (v_count = 3) THEN
v_count:=0;
- select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_GUID_SERVICE' and constraint_type='U';
+ select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_GUID_SERVICE_ZONE' and constraint_type='U';
if (v_count = 0) THEN
- select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('GUID','SERVICE') and index_name='X_POLICY_UK_GUID_SERVICE';
+ select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('GUID','SERVICE','ZONE_ID') and index_name='X_POLICY_UK_GUID_SERVICE_ZONE';
if (v_count = 0) THEN
- execute immediate 'ALTER TABLE X_POLICY ADD CONSTRAINT X_POLICY_UK_GUID_SERVICE UNIQUE (GUID,SERVICE)';
+ execute immediate 'ALTER TABLE X_POLICY ADD CONSTRAINT X_POLICY_UK_GUID_SERVICE_ZONE UNIQUE (GUID,SERVICE,ZONE_ID)';
end if;
commit;
end if;
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 9fd4503..3cfdb81 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
@@ -623,7 +623,7 @@ policy_text TEXT DEFAULT NULL NULL,
zone_id BIGINT DEFAULT '1' NOT NULL,
primary key(id),
CONSTRAINT x_policy_uk_name_service_zone UNIQUE(name,service,zone_id),
-CONSTRAINT x_policy_uk_guid_service UNIQUE(guid,service),
+CONSTRAINT x_policy_uk_guid_service_zone UNIQUE(guid,service,zone_id),
CONSTRAINT x_policy_uk_service_signature UNIQUE(service,resource_signature),
CONSTRAINT x_policy_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id),
CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id),
diff --git a/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
index 81718aa..e0b9e92 100644
--- a/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
+++ b/security-admin/db/postgres/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
@@ -19,13 +19,15 @@ RETURNS void AS $$
DECLARE
v_attnum1 integer := 0;
v_attnum2 integer := 0;
+ v_attnum3 integer := 0;
BEGIN
select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('guid');
select attnum into v_attnum2 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('service');
- IF v_attnum1 > 0 and v_attnum2 > 0 THEN
- IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_policy') and conname='x_policy_uk_guid_service' and contype='u') THEN
- IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_policy') and indkey[0]=v_attnum1 and indkey[1]=v_attnum2) THEN
- ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_guid_service UNIQUE(guid,service);
+ select attnum into v_attnum3 from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('zone_id');
+ IF v_attnum1 > 0 and v_attnum2 > 0 and v_attnum3 > 0 THEN
+ IF not exists (select * from pg_constraint where conrelid in(select oid from pg_class where relname='x_policy') and conname='x_policy_uk_guid_service_zone' and contype='u') THEN
+ IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_policy') and indkey[0]=v_attnum1 and indkey[1]=v_attnum2 and indkey[2]=v_attnum3) THEN
+ ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_guid_service_zone UNIQUE(guid,service,zone_id);
END IF;
END IF;
END IF;
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 bdccecc..40dafaf 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
@@ -574,7 +574,7 @@ create table dbo.x_policy (
zone_id bigint DEFAULT '1' NOT NULL,
CONSTRAINT x_policy_PK_id PRIMARY KEY CLUSTERED(id),
CONSTRAINT x_policy_UK_name_service_zone UNIQUE NONCLUSTERED (name,service,zone_id),
- CONSTRAINT x_policy_UK_guid_service UNIQUE NONCLUSTERED (guid,service),
+ CONSTRAINT x_policy_UK_guid_service_zone UNIQUE NONCLUSTERED (guid,service,zone_id),
CONSTRAINT x_policy_UK_service_signature UNIQUE NONCLUSTERED (service,resource_signature),
)
GO
diff --git a/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
index 16ad476..4ae146f 100644
--- a/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
+++ b/security-admin/db/sqlanywhere/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
@@ -13,9 +13,9 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
BEGIN
- IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname in('guid','service') THEN
- IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_guid_service') THEN
- ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_guid_service UNIQUE NONCLUSTERED (guid,service);
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname in('guid','service','zone_id') THEN
+ IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_guid_service_zone') THEN
+ ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_guid_service_zone UNIQUE NONCLUSTERED (guid,service,zone_id);
END IF;
END IF;
END
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 8515ac0..0b24c59 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
@@ -1465,9 +1465,9 @@ CONSTRAINT [x_policy$x_policy_UK_name_service_zone] UNIQUE NONCLUSTERED
(
[name] ASC, [service] ASC, [zone_id] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
-CONSTRAINT [x_policy$x_policy_UK_guid_service] UNIQUE NONCLUSTERED
+CONSTRAINT [x_policy$x_policy_UK_guid_service_zone] UNIQUE NONCLUSTERED
(
- [guid] ASC, [service] ASC
+ [guid] ASC, [service] ASC, [zone_id] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [x_policy$x_policy_UK_service_signature] UNIQUE NONCLUSTERED
(
diff --git a/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql b/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
index 3037988..baa20dd 100644
--- a/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
+++ b/security-admin/db/sqlserver/patches/057-add-unique-constraint-on-x_policy-table-guid-service-column.sql
@@ -16,13 +16,13 @@
-- limitations under the License.
-IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name in ('guid','service'))
+IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name in ('guid','service','zone_id'))
BEGIN
- IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='guid' and constraint_name = 'x_policy$x_policy_UK_guid_service')
+ IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='guid' and constraint_name = 'x_policy$x_policy_UK_guid_service_zone')
BEGIN
- IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_guid_service' and CONSTRAINT_TYPE='UNIQUE')
+ IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_guid_service_zone' and CONSTRAINT_TYPE='UNIQUE')
BEGIN
- ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_guid_service] UNIQUE ([guid],[service]);
+ ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_guid_service_zone] UNIQUE ([guid],[service],[zone_id]);
END
END
END