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/11/24 15:15:00 UTC
[ranger] branch master updated: RANGER-3493: Add unique index on service and resource_signature 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
The following commit(s) were added to refs/heads/master by this push:
new de8f5e1 RANGER-3493: Add unique index on service and resource_signature column of x_policy table
de8f5e1 is described below
commit de8f5e197fb93fcb924f7a59a88013b99bd1194b
Author: pradeep <pr...@apache.org>
AuthorDate: Thu Oct 21 17:50:50 2021 +0530
RANGER-3493: Add unique index on service and resource_signature column of x_policy table
---
.../optimized/current/ranger_core_db_mysql.sql | 2 ++
...-x_policy-table-service-resourcesign-column.sql | 32 +++++++++++++++++++++
.../optimized/current/ranger_core_db_oracle.sql | 2 ++
...-x_policy-table-service-resourcesign-column.sql | 32 +++++++++++++++++++++
.../optimized/current/ranger_core_db_postgres.sql | 2 ++
...-x_policy-table-service-resourcesign-column.sql | 33 ++++++++++++++++++++++
.../current/ranger_core_db_sqlanywhere.sql | 9 ++++--
...-x_policy-table-service-resourcesign-column.sql | 32 +++++++++++++++++++++
.../optimized/current/ranger_core_db_sqlserver.sql | 5 ++++
...-x_policy-table-service-resourcesign-column.sql | 28 ++++++++++++++++++
10 files changed, 174 insertions(+), 3 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 57af321..44a0976 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
@@ -695,6 +695,7 @@ 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_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`),
CONSTRAINT `x_policy_FK_service` FOREIGN KEY (`service`) REFERENCES `x_service` (`id`),
@@ -1742,6 +1743,7 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('055',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('056',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('057',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('058',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',UTC_TIMESTAMP(),'Ranger 1.0.0',UTC_TIMESTAMP(),'localhost','Y');
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
diff --git a/security-admin/db/mysql/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql b/security-admin/db/mysql/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
new file mode 100644
index 0000000..055f646
--- /dev/null
+++ b/security-admin/db/mysql/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
@@ -0,0 +1,32 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+drop procedure if exists create_unique_constraint_on_service_and_resourcesignature;
+
+delimiter ;;
+create procedure create_unique_constraint_on_service_and_resourcesignature() 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('resource_signature','service')) then
+ /* check unique constraint exist on resource_signature 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_service_signature') then
+ ALTER TABLE x_policy ADD UNIQUE INDEX x_policy_UK_service_signature(service,resource_signature);
+ end if;
+ end if;
+end;;
+
+delimiter ;
+call create_unique_constraint_on_service_and_resourcesignature();
+
+drop procedure if exists create_unique_constraint_on_service_and_resourcesignature;
\ No newline at end of file
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 25ee44c..8884f60 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
@@ -795,6 +795,7 @@ 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_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),
CONSTRAINT x_policy_FK_service FOREIGN KEY (service) REFERENCES x_service (id),
@@ -1967,6 +1968,7 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act
INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '055',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '056',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '057',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
+INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '058',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,getXportalUIdByLoginId('admin'),getModulesIdByName('Reports'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/oracle/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql b/security-admin/db/oracle/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
new file mode 100644
index 0000000..63bed8b
--- /dev/null
+++ b/security-admin/db/oracle/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
@@ -0,0 +1,32 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+DECLARE
+ v_count number:=0;
+ sql_stmt VARCHAR2(1000);
+BEGIN
+ select count(*) into v_count from user_tab_cols where table_name='X_POLICY' and column_name IN('SERVICE','RESOURCE_SIGNATURE');
+ if (v_count = 2) then
+ v_count:=0;
+ select count(*) into v_count from user_constraints where table_name='X_POLICY' and constraint_name='X_POLICY_UK_SERVICE_SIGNATURE' and constraint_type='U';
+ if (v_count = 0) then
+ v_count:=0;
+ select count(*) into v_count from user_ind_columns WHERE table_name='X_POLICY' and column_name IN('SERVICE','RESOURCE_SIGNATURE') and index_name='X_POLICY_UK_SERVICE_SIGNATURE';
+ if (v_count = 0) THEN
+ EXECUTE immediate 'ALTER TABLE X_POLICY ADD CONSTRAINT X_POLICY_UK_SERVICE_SIGNATURE UNIQUE (SERVICE,RESOURCE_SIGNATURE)';
+ end if;
+ commit;
+ end if;
+ 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 78cee1a..4b69cb1 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
@@ -624,6 +624,7 @@ 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_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),
CONSTRAINT x_policy_FK_service FOREIGN KEY(service) REFERENCES x_service(id),
@@ -1890,6 +1891,7 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('055',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('056',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('057',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('058',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',current_timestamp,'Ranger 1.0.0',current_timestamp,'localhost','Y');
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES
diff --git a/security-admin/db/postgres/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql b/security-admin/db/postgres/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
new file mode 100644
index 0000000..cf8a2b8
--- /dev/null
+++ b/security-admin/db/postgres/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
@@ -0,0 +1,33 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+select 'delimiter start';
+CREATE OR REPLACE FUNCTION create_unique_constraint_on_service_and_resourcesignature()
+RETURNS void AS $$
+DECLARE
+ v_attnum integer := 0;
+BEGIN
+ select attnum into v_attnum from pg_attribute where attrelid in(select oid from pg_class where relname='x_policy') and attname in('resource_signature');
+ IF v_attnum > 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_service_signature' 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[1]=v_attnum) THEN
+ ALTER TABLE x_policy ADD CONSTRAINT x_policy_uk_service_signature UNIQUE(service,resource_signature);
+ END IF;
+ END IF;
+ END IF;
+
+END;
+$$ LANGUAGE plpgsql;
+select create_unique_constraint_on_service_and_resourcesignature();
+select 'delimiter end';
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 632aefe..d84e64e 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
@@ -555,7 +555,7 @@ CREATE TABLE dbo.x_ranger_global_state(
GO
create table dbo.x_policy (
id bigint IDENTITY NOT NULL,
- guid varchar(1024) DEFAULT NULL NULL,
+ guid varchar(1024) NOT NULL,
create_time datetime DEFAULT NULL NULL,
update_time datetime DEFAULT NULL NULL,
added_by_id bigint DEFAULT NULL NULL,
@@ -565,7 +565,7 @@ create table dbo.x_policy (
name varchar(512) NOT NULL,
policy_type int DEFAULT 0 NULL,
description varchar(1024) DEFAULT NULL NULL,
- resource_signature varchar(128) DEFAULT NULL NULL,
+ resource_signature varchar(128) NOT NULL,
is_enabled tinyint DEFAULT 0 NOT NULL,
is_audit_enabled tinyint DEFAULT 0 NOT NULL,
policy_options varchar(4000) DEFAULT NULL NULL,
@@ -574,7 +574,8 @@ 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 UNIQUE NONCLUSTERED (guid,service),
+ CONSTRAINT x_policy_UK_service_signature UNIQUE NONCLUSTERED (service,resource_signature),
)
GO
create table dbo.x_service_config_def (
@@ -2258,6 +2259,8 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('057',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('058',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+GO
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
GO
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/sqlanywhere/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql b/security-admin/db/sqlanywhere/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
new file mode 100644
index 0000000..c1c7b78
--- /dev/null
+++ b/security-admin/db/sqlanywhere/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
@@ -0,0 +1,32 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+BEGIN
+DECLARE tableID INT = 0;
+DECLARE columnID INT = 0;
+DECLARE guTableID INT = 0;
+DECLARE guColumnID INT = 0;
+ IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_policy' and cname='resource_signature' THEN
+ IF NOT EXISTS(select * from SYS.SYSCONSTRAINT where constraint_name = 'x_policy_UK_service_signature') THEN
+ select table_id into tableID from SYS.SYSTAB where table_name = 'x_policy';
+ select column_id into columnID from SYS.SYSTABCOL where table_id=tableID and column_name = 'resource_signature';
+ IF NOT EXISTS(select * from SYS.SYSIDXCOL where table_id=tableID and column_id=columnID) THEN
+ DROP INDEX x_policy_resource_signature;
+ ALTER TABLE dbo.x_policy ADD CONSTRAINT x_policy_UK_service_signature UNIQUE NONCLUSTERED (service,resource_signature);
+ CREATE NONCLUSTERED INDEX x_policy_resource_signature ON dbo.x_policy(resource_signature ASC);
+ END IF;
+ END IF;
+ END IF;
+END
+GO
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 fa29da0..6671ec7 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
@@ -1468,6 +1468,10 @@ CONSTRAINT [x_policy$x_policy_UK_name_service_zone] UNIQUE NONCLUSTERED
CONSTRAINT [x_policy$x_policy_UK_guid_service] UNIQUE NONCLUSTERED
(
[guid] ASC, [service] 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
+(
+ [service] 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
@@ -4106,6 +4110,7 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('055',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('056',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('057',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('058',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource Based Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/sqlserver/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql b/security-admin/db/sqlserver/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
new file mode 100644
index 0000000..0e192b0
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/058-add-unique-constraint-on-x_policy-table-service-resourcesign-column.sql
@@ -0,0 +1,28 @@
+
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+IF EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name = 'resource_signature')
+BEGIN
+ IF NOT EXISTS(select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='x_policy' and column_name='resource_signature' and constraint_name = 'x_policy$x_policy_UK_service_signature')
+ BEGIN
+ IF NOT EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='x_policy' and constraint_name = 'x_policy$x_policy_UK_service_signature' and CONSTRAINT_TYPE='UNIQUE')
+ BEGIN
+ ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy$x_policy_UK_service_signature] UNIQUE ([service],[resource_signature]);
+ END
+ END
+END
+GO
+exit