| -- 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. |
| |
| GO |
| IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy_export_audit' and column_name = 'zone_name') |
| BEGIN |
| ALTER TABLE [dbo].[x_policy_export_audit] ADD [zone_name] [varchar](255) DEFAULT NULL NULL; |
| END |
| GO |
| IF (OBJECT_ID('x_policy_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_policy] DROP CONSTRAINT x_policy_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_group_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_group] DROP CONSTRAINT x_sz_ref_group_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_group_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_group] DROP CONSTRAINT x_sz_ref_group_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_group_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_group] DROP CONSTRAINT x_sz_ref_group_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_group_FK_group_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_group] DROP CONSTRAINT x_sz_ref_group_FK_group_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_user_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT x_sz_ref_user_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_user_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT x_sz_ref_user_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_user_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT x_sz_ref_user_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_user_FK_user_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT x_sz_ref_user_FK_user_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_user_FK_user_name') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT x_sz_ref_user_FK_user_name |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_resource_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] DROP CONSTRAINT x_sz_ref_resource_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_resource_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] DROP CONSTRAINT x_sz_ref_resource_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_resource_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] DROP CONSTRAINT x_sz_ref_resource_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_resource_FK_resource_def_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] DROP CONSTRAINT x_sz_ref_resource_FK_resource_def_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_service_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_service] DROP CONSTRAINT x_sz_ref_service_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_service_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_service] DROP CONSTRAINT x_sz_ref_service_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_service_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_service] DROP CONSTRAINT x_sz_ref_service_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_service_FK_service_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_service] DROP CONSTRAINT x_sz_ref_service_FK_service_id |
| END |
| IF (OBJECT_ID('x_sz_ref_service_FK_service_name') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_service] DROP CONSTRAINT x_sz_ref_service_FK_service_name |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_tag_service_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] DROP CONSTRAINT x_sz_ref_tag_service_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_tag_service_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] DROP CONSTRAINT x_sz_ref_tag_service_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_tag_service_FK_zone_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] DROP CONSTRAINT x_sz_ref_tag_service_FK_zone_id |
| END |
| GO |
| IF (OBJECT_ID('x_sz_ref_tag_service_FK_tag_service_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] DROP CONSTRAINT x_sz_ref_tag_service_FK_tag_service_id |
| END |
| IF (OBJECT_ID('x_sz_ref_tag_service_FK_tag_service_name') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] DROP CONSTRAINT x_sz_ref_tag_service_FK_tag_service_name |
| END |
| GO |
| IF (OBJECT_ID('x_ranger_global_state_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_ranger_global_state] DROP CONSTRAINT x_ranger_global_state_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_ranger_global_state_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_ranger_global_state] DROP CONSTRAINT x_ranger_global_state_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_FK_added_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone] DROP CONSTRAINT x_security_zone_FK_added_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_FK_upd_by_id') IS NOT NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_security_zone] DROP CONSTRAINT x_security_zone_FK_upd_by_id |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_ref_service') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone_ref_service] |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_ref_tag_srvc') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone_ref_tag_srvc] |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_ref_resource') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone_ref_resource] |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_ref_user') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone_ref_user] |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone_ref_group') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone_ref_group] |
| END |
| GO |
| IF (OBJECT_ID('x_ranger_global_state') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_ranger_global_state] |
| END |
| GO |
| IF (OBJECT_ID('x_security_zone') IS NOT NULL) |
| BEGIN |
| DROP TABLE [dbo].[x_security_zone] |
| END |
| GO |
| CREATE TABLE [dbo].[x_security_zone]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [version] [bigint] DEFAULT NULL NULL, |
| [name] [varchar](255) NOT NULL, |
| [jsonData] [nvarchar](max) DEFAULT NULL NULL, |
| [description] [varchar](1024) DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [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_security_zone$x_security_zone_UK_name] UNIQUE NONCLUSTERED |
| ( |
| [name] ASC |
| )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
| ) ON [PRIMARY] |
| GO |
| CREATE TABLE [dbo].[x_ranger_global_state]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [version] [bigint] DEFAULT NULL NULL, |
| [state_name] [varchar](255) NOT NULL, |
| [app_data] [varchar](255) DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [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_ranger_global_state$x_ranger_global_state_UK_name] UNIQUE NONCLUSTERED |
| ( |
| [state_name] ASC |
| )WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
| ) ON [PRIMARY] |
| GO |
| CREATE TABLE [dbo].[x_security_zone_ref_service]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [zone_id] [bigint] DEFAULT NULL NULL, |
| [service_id] [bigint] DEFAULT NULL NULL, |
| [service_name] [varchar](255) DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [id] ASC |
| )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
| ) ON [PRIMARY] |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_service] WITH CHECK ADD CONSTRAINT [x_sz_ref_service_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_service] WITH CHECK ADD CONSTRAINT [x_sz_ref_service_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_service] WITH CHECK ADD CONSTRAINT [x_sz_ref_service_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_service] WITH CHECK ADD CONSTRAINT [x_sz_ref_service_FK_service_id] FOREIGN KEY([service_id]) REFERENCES [dbo].[x_service] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_service] WITH CHECK ADD CONSTRAINT [x_sz_ref_service_FK_service_name] FOREIGN KEY([service_name]) REFERENCES [dbo].[x_service] ([name]) |
| GO |
| CREATE TABLE [dbo].[x_security_zone_ref_tag_srvc]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [zone_id] [bigint] DEFAULT NULL NULL, |
| [tag_srvc_id] [bigint] DEFAULT NULL NULL, |
| [tag_srvc_name] [varchar](255) DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [id] ASC |
| )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
| ) ON [PRIMARY] |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] WITH CHECK ADD CONSTRAINT [x_sz_ref_tag_service_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] WITH CHECK ADD CONSTRAINT [x_sz_ref_tag_service_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] WITH CHECK ADD CONSTRAINT [x_sz_ref_tag_service_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] WITH CHECK ADD CONSTRAINT [x_sz_ref_tag_service_FK_tag_service_id] FOREIGN KEY([tag_srvc_id]) REFERENCES [dbo].[x_service] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_tag_srvc] WITH CHECK ADD CONSTRAINT [x_sz_ref_tag_service_FK_tag_service_name] FOREIGN KEY([tag_srvc_name]) REFERENCES [dbo].[x_service] ([name]) |
| GO |
| SET ANSI_NULLS ON |
| SET QUOTED_IDENTIFIER ON |
| SET ANSI_PADDING ON |
| CREATE TABLE [dbo].[x_security_zone_ref_resource]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [zone_id] [bigint] DEFAULT NULL NULL, |
| [resource_def_id] [bigint] DEFAULT NULL NULL, |
| [resource_name] [varchar](255) DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [id] ASC |
| )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
| ) ON [PRIMARY] |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] WITH CHECK ADD CONSTRAINT [x_sz_ref_resource_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] WITH CHECK ADD CONSTRAINT [x_sz_ref_resource_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] WITH CHECK ADD CONSTRAINT [x_sz_ref_resource_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_resource] WITH CHECK ADD CONSTRAINT [x_sz_ref_resource_FK_resource_def_id] FOREIGN KEY([resource_def_id]) REFERENCES [dbo].[x_resource_def] ([id]) |
| GO |
| SET ANSI_NULLS ON |
| SET QUOTED_IDENTIFIER ON |
| SET ANSI_PADDING ON |
| CREATE TABLE [dbo].[x_security_zone_ref_user]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [zone_id] [bigint] DEFAULT NULL NULL, |
| [user_id] [bigint] DEFAULT NULL NULL, |
| [user_name] [varchar](767) DEFAULT NULL NULL, |
| [user_type] [tinyint] DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [id] ASC |
| )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
| ) ON [PRIMARY] |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT [x_sz_ref_user_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT [x_sz_ref_user_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT [x_sz_ref_user_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT [x_sz_ref_user_FK_user_id] FOREIGN KEY([user_id]) REFERENCES [dbo].[x_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT [x_sz_ref_user_FK_user_name] FOREIGN KEY([user_name]) REFERENCES [dbo].[x_user] ([user_name]) |
| GO |
| SET ANSI_NULLS ON |
| SET QUOTED_IDENTIFIER ON |
| SET ANSI_PADDING ON |
| CREATE TABLE [dbo].[x_security_zone_ref_group]( |
| [id] [bigint] IDENTITY(1,1) NOT NULL, |
| [create_time] [datetime2] DEFAULT NULL NULL, |
| [update_time] [datetime2] DEFAULT NULL NULL, |
| [added_by_id] [bigint] DEFAULT NULL NULL, |
| [upd_by_id] [bigint] DEFAULT NULL NULL, |
| [zone_id] [bigint] DEFAULT NULL NULL, |
| [group_id] [bigint] DEFAULT NULL NULL, |
| [group_name] [varchar](767) DEFAULT NULL NULL, |
| [group_type] [tinyint] DEFAULT NULL NULL, |
| PRIMARY KEY CLUSTERED |
| ( |
| [id] ASC |
| )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
| ) ON [PRIMARY] |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_group] WITH CHECK ADD CONSTRAINT [x_sz_ref_group_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_group] WITH CHECK ADD CONSTRAINT [x_sz_ref_group_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_group] WITH CHECK ADD CONSTRAINT [x_sz_ref_group_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone_ref_group] WITH CHECK ADD CONSTRAINT [x_sz_ref_group_FK_group_id] FOREIGN KEY([group_id]) REFERENCES [dbo].[x_group] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone] WITH CHECK ADD CONSTRAINT [x_security_zone_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_security_zone] WITH CHECK ADD CONSTRAINT [x_security_zone_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_ranger_global_state] WITH CHECK ADD CONSTRAINT [x_ranger_global_state_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| ALTER TABLE [dbo].[x_ranger_global_state] WITH CHECK ADD CONSTRAINT [x_ranger_global_state_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) |
| GO |
| SET ANSI_NULLS ON |
| GO |
| SET QUOTED_IDENTIFIER ON |
| GO |
| IF EXISTS (SELECT * |
| FROM sys.objects |
| WHERE object_id = OBJECT_ID(N'dbo.getModulesIdByName') |
| AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) |
| DROP FUNCTION dbo.getModulesIdByName |
| PRINT 'Dropped function dbo.getModulesIdByName' |
| |
| GO |
| PRINT 'Creating function dbo.getModulesIdByName' |
| GO |
| CREATE FUNCTION dbo.getModulesIdByName |
| ( |
| |
| @inputValue varchar(200) |
| ) |
| RETURNS int |
| AS |
| BEGIN |
| Declare @myid int; |
| |
| Select @myid = id from x_modules_master where module = @inputValue; |
| |
| return @myid; |
| |
| END |
| GO |
| |
| PRINT 'Created function dbo.getModulesIdByName successfully' |
| GO |
| |
| SET ANSI_NULLS ON |
| GO |
| SET QUOTED_IDENTIFIER ON |
| GO |
| IF EXISTS (SELECT * |
| FROM sys.objects |
| WHERE object_id = OBJECT_ID(N'dbo.getXportalUIdByLoginId') |
| AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) |
| DROP FUNCTION dbo.getXportalUIdByLoginId |
| PRINT 'Dropped function dbo.getXportalUIdByLoginId' |
| |
| GO |
| PRINT 'Creating function dbo.getXportalUIdByLoginId' |
| GO |
| CREATE FUNCTION dbo.getXportalUIdByLoginId |
| ( |
| |
| @inputValue varchar(200) |
| ) |
| RETURNS int |
| AS |
| BEGIN |
| Declare @myid int; |
| |
| Select @myid = id from x_portal_user where x_portal_user.login_id = @inputValue; |
| |
| return @myid; |
| |
| END |
| GO |
| IF NOT EXISTS(select * from x_security_zone where id = 1 and name=' ') |
| BEGIN |
| INSERT INTO x_security_zone(create_time, update_time, added_by_id, upd_by_id, version, name, jsonData, description) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, dbo.getXportalUIdByLoginId('admin'), dbo.getXportalUIdByLoginId('admin'), 1, ' ', '', 'Unzoned zone'); |
| END |
| GO |
| IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy' and column_name in('zone_id')) |
| BEGIN |
| ALTER TABLE [dbo].[x_policy] ADD [zone_id] [bigint] DEFAULT 1 NOT NULL; |
| END |
| GO |
| IF (OBJECT_ID('x_policy_FK_zone_id') IS NULL) |
| BEGIN |
| ALTER TABLE [dbo].[x_policy] ADD CONSTRAINT [x_policy_FK_zone_id] FOREIGN KEY([zone_id]) REFERENCES [dbo].[x_security_zone] ([id]); |
| END |
| GO |
| PRINT 'Created function dbo.getXportalUIdByLoginId successfully' |
| GO |
| IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_policy_export_audit' and column_name = 'zone_name') |
| BEGIN |
| ALTER TABLE [dbo].[x_policy_export_audit] ADD [zone_name] [varchar](255) DEFAULT NULL NULL; |
| END |
| |
| IF NOT EXISTS(select * from x_modules_master where module = 'Security Zone') |
| BEGIN |
| INSERT INTO x_modules_master(create_time,update_time,added_by_id,upd_by_id,module,url) VALUES(CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),'Security Zone',''); |
| END |
| GO |
| IF NOT EXISTS(select * from x_user_module_perm where user_id=dbo.getXportalUIdByLoginId('admin') and module_id=dbo.getModulesIdByName('Security Zone')) |
| BEGIN |
| 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('Security Zone'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); |
| END |
| GO |
| IF NOT EXISTS(select * from x_user_module_perm where user_id=dbo.getXportalUIdByLoginId('rangerusersync') and module_id=dbo.getModulesIdByName('Security Zone')) |
| BEGIN |
| 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('rangerusersync'),dbo.getModulesIdByName('Security Zone'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); |
| END |
| GO |
| IF NOT EXISTS(select * from x_user_module_perm where user_id=dbo.getXportalUIdByLoginId('rangertagsync') and module_id=dbo.getModulesIdByName('Security Zone')) |
| BEGIN |
| 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('rangertagsync'),dbo.getModulesIdByName('Security Zone'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1); |
| END |
| GO |
| exit |
| |