http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ---------------------------------------------------------------------- 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 85f3285..75c8faf 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 @@ -253,6 +253,102 @@ IF (OBJECT_ID('x_enum_def_FK_defid') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_enum_def] DROP CONSTRAINT x_enum_def_FK_defid END +IF (OBJECT_ID('x_policy_ref_resource_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_resource_FK_resource_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_resource_def_id +END +IF (OBJECT_ID('x_policy_ref_resource_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_UK +END +IF (OBJECT_ID('x_policy_ref_resource') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_resource] +END +IF (OBJECT_ID('x_policy_ref_access_type_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_access_type_FK_access_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_access_def_id +END +IF (OBJECT_ID('x_policy_ref_access_type_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_UK +END +IF (OBJECT_ID('x_policy_ref_access_type') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_access_type] +END +IF (OBJECT_ID('x_policy_ref_condition_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_condition_FK_condition_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_condition_def_id +END +IF (OBJECT_ID('x_policy_ref_condition_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_UK +END +IF (OBJECT_ID('x_policy_ref_condition') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_condition] +END +IF (OBJECT_ID('x_policy_ref_datamask_type_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_datamask_type_FK_datamask_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_datamask_def_id +END +IF (OBJECT_ID('x_policy_ref_datamask_type_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_UK +END +IF (OBJECT_ID('x_policy_ref_datamask_type') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_datamask_type] +END +IF (OBJECT_ID('x_policy_ref_user_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_user_FK_user_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_user_id +END +IF (OBJECT_ID('x_policy_ref_user_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_UK +END +IF (OBJECT_ID('x_policy_ref_user') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_user] +END +IF (OBJECT_ID('x_policy_ref_group_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_policy_id +END +IF (OBJECT_ID('x_policy_ref_group_FK_group_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_group_id +END +IF (OBJECT_ID('x_policy_ref_group_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_UK +END +IF (OBJECT_ID('x_policy_ref_group') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_group] +END IF (OBJECT_ID('x_enum_element_def_FK_defid') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_enum_element_def] DROP CONSTRAINT x_enum_element_def_FK_defid @@ -341,46 +437,6 @@ IF (OBJECT_ID('x_service_res_FK_upd_by_id') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_service_resource] DROP CONSTRAINT x_service_res_FK_upd_by_id END -IF (OBJECT_ID('x_srvc_res_el_FK_res_def_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element] DROP CONSTRAINT x_srvc_res_el_FK_res_def_id -END -IF (OBJECT_ID('x_srvc_res_el_FK_res_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element] DROP CONSTRAINT x_srvc_res_el_FK_res_id -END -IF (OBJECT_ID('x_srvc_res_el_FK_added_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element] DROP CONSTRAINT x_srvc_res_el_FK_added_by_id -END -IF (OBJECT_ID('x_srvc_res_el_FK_upd_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element] DROP CONSTRAINT x_srvc_res_el_FK_upd_by_id -END -IF (OBJECT_ID('x_tag_attr_def_FK_tag_def_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr_def] DROP CONSTRAINT x_tag_attr_def_FK_tag_def_id -END -IF (OBJECT_ID('x_tag_attr_def_FK_added_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr_def] DROP CONSTRAINT x_tag_attr_def_FK_added_by_id -END -IF (OBJECT_ID('x_tag_attr_def_FK_upd_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr_def] DROP CONSTRAINT x_tag_attr_def_FK_upd_by_id -END -IF (OBJECT_ID('x_tag_attr_FK_tag_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr] DROP CONSTRAINT x_tag_attr_FK_tag_id -END -IF (OBJECT_ID('x_tag_attr_FK_added_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr] DROP CONSTRAINT x_tag_attr_FK_added_by_id -END -IF (OBJECT_ID('x_tag_attr_FK_upd_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_tag_attr] DROP CONSTRAINT x_tag_attr_FK_upd_by_id -END IF (OBJECT_ID('x_tag_res_map_FK_tag_id') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_tag_resource_map] DROP CONSTRAINT x_tag_res_map_FK_tag_id @@ -397,18 +453,6 @@ IF (OBJECT_ID('x_tag_res_map_FK_upd_by_id') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_tag_resource_map] DROP CONSTRAINT x_tag_res_map_FK_upd_by_id END -IF (OBJECT_ID('x_srvc_res_el_val_FK_res_el_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element_val] DROP CONSTRAINT x_srvc_res_el_val_FK_res_el_id -END -IF (OBJECT_ID('x_srvc_res_el_val_FK_add_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element_val] DROP CONSTRAINT x_srvc_res_el_val_FK_add_by_id -END -IF (OBJECT_ID('x_srvc_res_el_val_FK_upd_by_id') IS NOT NULL) -BEGIN - ALTER TABLE [dbo].[x_service_resource_element_val] DROP CONSTRAINT x_srvc_res_el_val_FK_upd_by_id -END IF (OBJECT_ID('x_datamask_type_def_FK_def_id') IS NOT NULL) BEGIN ALTER TABLE [dbo].[x_datamask_type_def] DROP CONSTRAINT x_datamask_type_def_FK_def_id @@ -1161,6 +1205,7 @@ CREATE TABLE [dbo].[x_policy] ( [is_audit_enabled] [tinyint] DEFAULT 0 NOT NULL, [policy_options] [varchar](4000) DEFAULT NULL NULL, [policy_priority] [int] DEFAULT 0 NOT NULL, + [policy_text] [nvarchar](max) DEFAULT NULL NULL, PRIMARY KEY CLUSTERED ( [id] ASC @@ -1601,6 +1646,7 @@ CREATE TABLE [dbo].[x_tag_def]( [name] [varchar](255) NOT NULL, [source] [varchar](128) DEFAULT NULL NULL, [is_enabled] [tinyint] DEFAULT 0 NOT NULL, + [tag_attrs_def_text] [nvarchar](max) DEFAULT NULL NULL, PRIMARY KEY CLUSTERED ( [id] ASC @@ -1628,6 +1674,7 @@ CREATE TABLE [dbo].[x_tag]( [type] [bigint] NOT NULL, [owned_by] [smallint] DEFAULT 0 NOT NULL, [policy_options] [varchar](4000) DEFAULT NULL NULL, + [tag_attrs_text] [nvarchar](max) DEFAULT NULL NULL, PRIMARY KEY CLUSTERED ( [id] ASC @@ -1651,6 +1698,8 @@ CREATE TABLE [dbo].[x_service_resource]( [service_id] [bigint] NOT NULL, [resource_signature] [varchar](128) DEFAULT NULL NULL, [is_enabled] [tinyint] DEFAULT 1 NOT NULL, + [service_resource_elements_text] [nvarchar](max) DEFAULT NULL NULL, + [tags_text] [nvarchar](max) DEFAULT NULL NULL, PRIMARY KEY CLUSTERED ( [id] ASC @@ -1663,58 +1712,6 @@ CONSTRAINT [x_service_resource$x_service_res_UK_guid] UNIQUE NONCLUSTERED SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON -CREATE TABLE [dbo].[x_service_resource_element]( - [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, - [res_id] [bigint] NOT NULL, - [res_def_id] [bigint] NOT NULL, - [is_excludes] [tinyint] DEFAULT 0 NOT NULL, - [is_recursive] [tinyint] DEFAULT 0 NOT 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] -SET ANSI_NULLS ON -SET QUOTED_IDENTIFIER ON -SET ANSI_PADDING ON -CREATE TABLE [dbo].[x_tag_attr_def]( - [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, - [tag_def_id] [bigint] NOT NULL, - [name] [varchar](255) NOT NULL, - [type] [varchar](50) NOT 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] -SET ANSI_NULLS ON -SET QUOTED_IDENTIFIER ON -SET ANSI_PADDING ON -CREATE TABLE [dbo].[x_tag_attr]( - [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, - [tag_id] [bigint] NOT NULL, - [name] [varchar](255) NOT NULL, - [value] [varchar](512) 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] -SET ANSI_NULLS ON -SET QUOTED_IDENTIFIER ON -SET ANSI_PADDING ON CREATE TABLE [dbo].[x_tag_resource_map]( [id] [bigint] IDENTITY(1,1) NOT NULL, [guid] [varchar](64) NOT NULL, @@ -1736,23 +1733,6 @@ CONSTRAINT [x_tag_resource_map$x_tag_resource_map_UK_guid] UNIQUE NONCLUSTERED SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON -CREATE TABLE [dbo].[x_service_resource_element_val]( - [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, - [res_element_id] [bigint] NOT NULL, - [value] [varchar](1024) NOT NULL, - [sort_order] [tinyint] DEFAULT 0 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] -SET ANSI_NULLS ON -SET QUOTED_IDENTIFIER ON -SET ANSI_PADDING ON CREATE TABLE [dbo].[x_datamask_type_def]( [id] [bigint] IDENTITY(1,1) NOT NULL, [guid] [varchar](64) DEFAULT NULL NULL, @@ -1904,6 +1884,138 @@ CREATE TABLE [dbo].[x_ugsync_audit_info]( SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_resource] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [resource_def_id] [bigint] NOT NULL, + [resource_name] [varchar](4000) 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_policy_ref_resource$x_policy_ref_resource_UK] UNIQUE NONCLUSTERED + ( + [policy_id] ASC, [resource_def_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_access_type] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [access_def_id] [bigint] NOT NULL, + [access_type_name] [varchar](4000) 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_policy_ref_access_type$x_policy_ref_access_type_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [access_def_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_condition] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [condition_def_id] [bigint] NOT NULL, + [condition_name] [varchar](4000) 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_policy_ref_condition$x_policy_ref_condition_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [condition_def_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_datamask_type] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [datamask_def_id] [bigint] NOT NULL, + [datamask_type_name] [varchar](4000) 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_policy_ref_datamask_type$x_policy_ref_datamask_type_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [datamask_def_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_user] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [user_id] [bigint] NOT NULL, + [user_name] [varchar](4000) 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_policy_ref_user$x_policy_ref_user_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [user_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [dbo].[x_policy_ref_group] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [group_id] [bigint] NOT NULL, + [group_name] [varchar](4000) 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_policy_ref_group$x_policy_ref_group_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [group_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] +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON ALTER TABLE [dbo].[x_asset] WITH CHECK ADD CONSTRAINT [x_asset_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) @@ -2238,23 +2350,10 @@ ALTER TABLE [dbo].[x_tag] WITH CHECK ADD CONSTRAINT [x_tag_FK_type] FOREIGN KEY( ALTER TABLE [dbo].[x_service_resource] WITH CHECK ADD CONSTRAINT [x_service_res_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_service_resource] WITH CHECK ADD CONSTRAINT [x_service_res_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_service_resource] WITH CHECK ADD CONSTRAINT [x_service_res_FK_service_id] FOREIGN KEY([service_id]) REFERENCES [dbo].[x_service] ([id]) -ALTER TABLE [dbo].[x_service_resource_element] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_FK_res_def_id] FOREIGN KEY([res_def_id]) REFERENCES [dbo].[x_resource_def] ([id]) -ALTER TABLE [dbo].[x_service_resource_element] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_FK_res_id] FOREIGN KEY([res_id]) REFERENCES [dbo].[x_service_resource] ([id]) -ALTER TABLE [dbo].[x_service_resource_element] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_service_resource_element] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_tag_attr_def] WITH CHECK ADD CONSTRAINT [x_tag_attr_def_FK_tag_def_id] FOREIGN KEY([tag_def_id]) REFERENCES [dbo].[x_tag_def] ([id]) -ALTER TABLE [dbo].[x_tag_attr_def] WITH CHECK ADD CONSTRAINT [x_tag_attr_def_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_tag_attr_def] WITH CHECK ADD CONSTRAINT [x_tag_attr_def_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_tag_attr] WITH CHECK ADD CONSTRAINT [x_tag_attr_FK_tag_id] FOREIGN KEY([tag_id]) REFERENCES [dbo].[x_tag] ([id]) -ALTER TABLE [dbo].[x_tag_attr] WITH CHECK ADD CONSTRAINT [x_tag_attr_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_tag_attr] WITH CHECK ADD CONSTRAINT [x_tag_attr_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_tag_resource_map] WITH CHECK ADD CONSTRAINT [x_tag_res_map_FK_tag_id] FOREIGN KEY([tag_id]) REFERENCES [dbo].[x_tag] ([id]) ALTER TABLE [dbo].[x_tag_resource_map] WITH CHECK ADD CONSTRAINT [x_tag_res_map_FK_res_id] FOREIGN KEY([res_id]) REFERENCES [dbo].[x_service_resource] ([id]) ALTER TABLE [dbo].[x_tag_resource_map] WITH CHECK ADD CONSTRAINT [x_tag_res_map_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_tag_resource_map] WITH CHECK ADD CONSTRAINT [x_tag_res_map_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_service_resource_element_val] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_val_FK_res_el_id] FOREIGN KEY([res_element_id]) REFERENCES [dbo].[x_service_resource_element] ([id]) -ALTER TABLE [dbo].[x_service_resource_element_val] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_val_FK_add_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) -ALTER TABLE [dbo].[x_service_resource_element_val] WITH CHECK ADD CONSTRAINT [x_srvc_res_el_val_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_def_id] FOREIGN KEY([def_id]) REFERENCES [dbo].[x_service_def] ([id]) ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_added_by_id] FOREIGN KEY([added_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) ALTER TABLE [dbo].[x_datamask_type_def] WITH CHECK ADD CONSTRAINT [x_datamask_type_def_FK_upd_by_id] FOREIGN KEY([upd_by_id]) REFERENCES [dbo].[x_portal_user] ([id]) @@ -2273,6 +2372,78 @@ ALTER TABLE [dbo].[x_policy_label_map] WITH CHECK ADD CONSTRAINT [x_policy_label ALTER TABLE [dbo].[x_policy_label_map] WITH CHECK ADD CONSTRAINT [x_policy_label_map_FK_policy_id] FOREIGN KEY ([policy_id]) REFERENCES [dbo].[x_policy] ([id]) ALTER TABLE [dbo].[x_policy_label_map] WITH CHECK ADD CONSTRAINT [x_policy_label_map_FK_policy_label_id] FOREIGN KEY ([policy_label_id]) REFERENCES [dbo].[x_policy_label] ([id]) ALTER TABLE [dbo].[x_policy_label_map] WITH CHECK ADD CONSTRAINT [x_policy_label_map$x_policy_label_map_pid_plid] UNIQUE (policy_id, policy_label_id) +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_res_def_id] FOREIGN KEY ([resource_def_id]) +REFERENCES [dbo].[x_resource_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_res_def_id] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_res_def_id] FOREIGN KEY ([access_def_id]) +REFERENCES [dbo].[x_resource_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_res_def_id] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_res_def_id] FOREIGN KEY ([condition_def_id]) +REFERENCES [dbo].[x_policy_condition_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_res_def_id] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_res_def_id] FOREIGN KEY ([datamask_def_id]) +REFERENCES [dbo].[x_datamask_type_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_res_def_id] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_policy_id] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_user_id] FOREIGN KEY ([user_id]) +REFERENCES [dbo].[x_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_user_id] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_added_by] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_upd_by] + +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_group_id] FOREIGN KEY ([group_id]) +REFERENCES [dbo].[x_group] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_group_id] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) CREATE NONCLUSTERED INDEX [x_asset_cr_time] ON [x_asset] ( [create_time] ASC @@ -2825,46 +2996,6 @@ CREATE NONCLUSTERED INDEX [x_service_res_IDX_upd_by_id] ON [x_service_resource] [upd_by_id] ASC ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_srvc_res_el_IDX_added_by_id] ON [x_service_resource_element] -( - [added_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_srvc_res_el_IDX_upd_by_id] ON [x_service_resource_element] -( - [upd_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_def_IDX_tag_def_id] ON [x_tag_attr_def] -( - [tag_def_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_def_IDX_added_by_id] ON [x_tag_attr_def] -( - [added_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_def_IDX_upd_by_id] ON [x_tag_attr_def] -( - [upd_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_IDX_tag_id] ON [x_tag_attr] -( - [tag_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_IDX_added_by_id] ON [x_tag_attr] -( - [added_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_tag_attr_IDX_upd_by_id] ON [x_tag_attr] -( - [upd_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [x_tag_res_map_IDX_tag_id] ON [x_tag_resource_map] ( [tag_id] ASC @@ -2885,21 +3016,6 @@ CREATE NONCLUSTERED INDEX [x_tag_res_map_IDX_upd_by_id] ON [x_tag_resource_map] [upd_by_id] ASC ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_srvc_res_el_val_IDX_resel_id] ON [x_service_resource_element_val] -( - [res_element_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_srvc_res_el_val_IDX_addby_id] ON [x_service_resource_element_val] -( - [added_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_srvc_res_el_val_IDX_updby_id] ON [x_service_resource_element_val] -( - [upd_by_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [x_service_config_def_IDX_def_id] ON [x_service_config_def] ( [def_id] ASC @@ -3005,16 +3121,6 @@ CREATE NONCLUSTERED INDEX [x_service_resource_IDX_service_id] ON [x_service_reso [service_id] ASC ) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_service_resource_element_IDX_res_id] ON [x_service_resource_element] -( - [res_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] -CREATE NONCLUSTERED INDEX [x_service_resource_element_IDX_res_def_id] ON [x_service_resource_element] -( - [res_def_id] ASC -) -WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [x_datamask_type_def_IDX_def_id] ON [x_datamask_type_def] ( [def_id] ASC @@ -3108,6 +3214,9 @@ 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 ('031',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 ('032',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 ('033',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 ('034',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 ('035',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 ('036',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 (1,3,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1); INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (1,1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,1,1); @@ -3145,7 +3254,8 @@ 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 ('J10014',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 ('J10015',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 ('J10016',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 ('J10018',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 ('J10019',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 ('J10020',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 ('JAVA_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y'); GO CREATE VIEW [dbo].[vx_trx_log] AS
http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlserver/patches/035-update-schema-for-x-policy.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/035-update-schema-for-x-policy.sql b/security-admin/db/sqlserver/patches/035-update-schema-for-x-policy.sql new file mode 100644 index 0000000..ebf44ac --- /dev/null +++ b/security-admin/db/sqlserver/patches/035-update-schema-for-x-policy.sql @@ -0,0 +1,453 @@ +-- 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 (OBJECT_ID('x_policy_ref_resource_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_resource_FK_resource_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_FK_resource_def_id +END +GO +IF (OBJECT_ID('x_policy_ref_resource_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_resource] DROP CONSTRAINT x_policy_ref_resource_UK +END +GO +IF (OBJECT_ID('x_policy_ref_resource') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_resource] +END +GO +IF (OBJECT_ID('x_policy_ref_access_type_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_access_type_FK_access_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_FK_access_def_id +END +GO +IF (OBJECT_ID('x_policy_ref_access_type_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_access_type] DROP CONSTRAINT x_policy_ref_access_type_UK +END +GO +IF (OBJECT_ID('x_policy_ref_access_type') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_access_type] +END +GO +IF (OBJECT_ID('x_policy_ref_condition_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_condition_FK_condition_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_FK_condition_def_id +END +GO +IF (OBJECT_ID('x_policy_ref_condition_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_condition] DROP CONSTRAINT x_policy_ref_condition_UK +END +GO +IF (OBJECT_ID('x_policy_ref_condition') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_condition] +END +GO +IF (OBJECT_ID('x_policy_ref_datamask_type_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_datamask_type_FK_datamask_def_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_FK_datamask_def_id +END +GO +IF (OBJECT_ID('x_policy_ref_datamask_type_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_datamask_type] DROP CONSTRAINT x_policy_ref_datamask_type_UK +END +GO +IF (OBJECT_ID('x_policy_ref_datamask_type') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_datamask_type] +END +GO +IF (OBJECT_ID('x_policy_ref_user_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_user_FK_user_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_FK_user_id +END +GO +IF (OBJECT_ID('x_policy_ref_user_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT x_policy_ref_user_UK +END +GO +IF (OBJECT_ID('x_policy_ref_user') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_user] +END +GO +IF (OBJECT_ID('x_policy_ref_group_FK_policy_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_policy_id +END +GO +IF (OBJECT_ID('x_policy_ref_group_FK_group_id') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_FK_group_id +END +GO +IF (OBJECT_ID('x_policy_ref_group_UK') IS NOT NULL) +BEGIN + ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT x_policy_ref_group_UK +END +GO +IF (OBJECT_ID('x_policy_ref_group') IS NOT NULL) +BEGIN + DROP TABLE [dbo].[x_policy_ref_group] +END +GO +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_resource] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [resource_def_id] [bigint] NOT NULL, + [resource_name] [varchar](4000) 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_policy_ref_resource$x_policy_ref_resource_UK] UNIQUE NONCLUSTERED + ( + [policy_id] ASC, [resource_def_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 +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_access_type] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [access_def_id] [bigint] NOT NULL, + [access_type_name] [varchar](4000) 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_policy_ref_access_type$x_policy_ref_access_type_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [access_def_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 +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_condition] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [condition_def_id] [bigint] NOT NULL, + [condition_name] [varchar](4000) 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_policy_ref_condition$x_policy_ref_condition_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [condition_def_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 +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_datamask_type] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [datamask_def_id] [bigint] NOT NULL, + [datamask_type_name] [varchar](4000) 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_policy_ref_datamask_type$x_policy_ref_datamask_type_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [datamask_def_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 +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_user] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [user_id] [bigint] NOT NULL, + [user_name] [varchar](4000) 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_policy_ref_user$x_policy_ref_user_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [user_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 +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +CREATE TABLE [dbo].[x_policy_ref_group] ( + [id] [bigint] IDENTITY (1, 1) NOT NULL, + [guid] [varchar](1024) DEFAULT NULL 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, + [policy_id] [bigint] NOT NULL, + [group_id] [bigint] NOT NULL, + [group_name] [varchar](4000) 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_policy_ref_group$x_policy_ref_group_UK] UNIQUE NONCLUSTERED +( + [policy_id] ASC, [group_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_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_resource_def_id] FOREIGN KEY ([resource_def_id]) +REFERENCES [dbo].[x_resource_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_resource_def_id] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_resource] CHECK CONSTRAINT [x_policy_ref_resource_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_resource] WITH CHECK ADD CONSTRAINT [x_policy_ref_resource_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_access_def_id] FOREIGN KEY ([access_def_id]) +REFERENCES [dbo].[x_access_type_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_access_def_id] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_access_type] CHECK CONSTRAINT [x_policy_ref_access_type_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_access_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_access_type_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_condition_def_id] FOREIGN KEY ([condition_def_id]) +REFERENCES [dbo].[x_policy_condition_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_condition_def_id] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_condition] CHECK CONSTRAINT [x_policy_ref_condition_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_condition] WITH CHECK ADD CONSTRAINT [x_policy_ref_condition_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_datamask_def_id] FOREIGN KEY ([datamask_def_id]) +REFERENCES [dbo].[x_datamask_type_def] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_datamask_def_id] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_datamask_type] CHECK CONSTRAINT [x_policy_ref_datamask_type_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_datamask_type] WITH CHECK ADD CONSTRAINT [x_policy_ref_datamask_type_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) + + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_policy_id] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_user_id] FOREIGN KEY ([user_id]) +REFERENCES [dbo].[x_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_user_id] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_added_by] + +ALTER TABLE [dbo].[x_policy_ref_user] WITH CHECK ADD CONSTRAINT [x_policy_ref_user_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_user] CHECK CONSTRAINT [x_policy_ref_user_FK_upd_by] + +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_policy_id] FOREIGN KEY ([policy_id]) +REFERENCES [dbo].[x_policy] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_policy_id] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_group_id] FOREIGN KEY ([group_id]) +REFERENCES [dbo].[x_group] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_group_id] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_added_by] FOREIGN KEY ([added_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +ALTER TABLE [dbo].[x_policy_ref_group] CHECK CONSTRAINT [x_policy_ref_group_FK_added_by] +ALTER TABLE [dbo].[x_policy_ref_group] WITH CHECK ADD CONSTRAINT [x_policy_ref_group_FK_upd_by] FOREIGN KEY ([upd_by_id]) +REFERENCES [dbo].[x_portal_user] ([id]) +GO +IF NOT EXISTS (SELECT + * + FROM INFORMATION_SCHEMA.columns + WHERE table_name = 'x_policy' + AND column_name = 'policy_text') +BEGIN + ALTER TABLE [dbo].[x_policy] ADD [policy_text] [nvarchar](max) DEFAULT NULL NULL; +END +GO + +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +IF EXISTS ( + SELECT type_desc, type + FROM sys.procedures WITH(NOLOCK) + WHERE NAME = 'removeConstraints' + AND type = 'P' + ) +BEGIN + PRINT 'Proc exist with name dbo.removeConstraints' + DROP PROCEDURE dbo.removeConstraints + PRINT 'Proc dropped dbo.removeConstraints' +END +GO +CREATE PROCEDURE dbo.removeConstraints + -- Add the parameters for the stored procedure here + @tablename nvarchar(100) +AS +BEGIN + + DECLARE @stmt VARCHAR(300); + + -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements + DECLARE cur CURSOR FOR + SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) + + ' DROP CONSTRAINT ' + name + FROM sys.foreign_keys + WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND + OBJECT_NAME(referenced_object_id) = @tablename; + + OPEN cur; + FETCH cur INTO @stmt; + + -- Drop each found foreign key constraint + WHILE @@FETCH_STATUS = 0 + BEGIN + EXEC (@stmt); + FETCH cur INTO @stmt; + END + + CLOSE cur; + DEALLOCATE cur; + +END +GO + +EXEC dbo.removeConstraints 'x_policy_item' +GO + +EXEC dbo.removeConstraints 'x_policy_item_access' +GO + +EXEC dbo.removeConstraints 'x_policy_item_condition' +GO + +EXEC dbo.removeConstraints 'x_policy_item_datamask' +GO + +EXEC dbo.removeConstraints 'x_policy_item_group_perm' +GO + +EXEC dbo.removeConstraints 'x_policy_item_user_perm' +GO + +EXEC dbo.removeConstraints 'x_policy_item_rowfilter' +GO + +EXEC dbo.removeConstraints 'x_policy_resource' +GO + +EXEC dbo.removeConstraints 'x_policy_resource_map' +GO + +EXIT http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/db/sqlserver/patches/036-denormalize-tag-tables.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/sqlserver/patches/036-denormalize-tag-tables.sql b/security-admin/db/sqlserver/patches/036-denormalize-tag-tables.sql new file mode 100644 index 0000000..9bfae30 --- /dev/null +++ b/security-admin/db/sqlserver/patches/036-denormalize-tag-tables.sql @@ -0,0 +1,97 @@ +-- 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_tag_def' and column_name = 'tag_attrs_def_text') +BEGIN + ALTER TABLE [dbo].[x_tag_def] ADD [tag_attrs_def_text] [nvarchar](max) DEFAULT NULL NULL; +END +IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_tag' and column_name = 'tag_attrs_text') +BEGIN + ALTER TABLE [dbo].[x_tag] ADD [tag_attrs_text] [nvarchar](max) DEFAULT NULL NULL; +END +IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_service_resource' and column_name = 'service_resource_elements_text') +BEGIN + ALTER TABLE [dbo].[x_service_resource] ADD [service_resource_elements_text] [nvarchar](max) DEFAULT NULL NULL; +END +IF NOT EXISTS(select * from INFORMATION_SCHEMA.columns where table_name = 'x_service_resource' and column_name = 'tags_text') +BEGIN + ALTER TABLE [dbo].[x_service_resource] ADD [tags_text] [nvarchar](max) DEFAULT NULL NULL; +END +GO + +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_PADDING ON +GO +IF EXISTS ( + SELECT type_desc, type + FROM sys.procedures WITH(NOLOCK) + WHERE NAME = 'removeConstraints' + AND type = 'P' + ) +BEGIN + PRINT 'Proc exist with name dbo.removeConstraints' + DROP PROCEDURE dbo.removeConstraints + PRINT 'Proc dropped dbo.removeConstraints' +END +GO +CREATE PROCEDURE dbo.removeConstraints + -- Add the parameters for the stored procedure here + @tablename nvarchar(100) +AS +BEGIN + + DECLARE @stmt VARCHAR(300); + + -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements + DECLARE cur CURSOR FOR + SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) + + ' DROP CONSTRAINT ' + name + FROM sys.foreign_keys + WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND + OBJECT_NAME(referenced_object_id) = @tablename; + + OPEN cur; + FETCH cur INTO @stmt; + + -- Drop each found foreign key constraint + WHILE @@FETCH_STATUS = 0 + BEGIN + EXEC (@stmt); + FETCH cur INTO @stmt; + END + + CLOSE cur; + DEALLOCATE cur; + +END +GO + +EXEC dbo.removeConstraints 'x_tag_attr_def' +GO + +EXEC dbo.removeConstraints 'x_tag_attr' +GO + +EXEC dbo.removeConstraints 'x_service_resource_element' +GO + +EXEC dbo.removeConstraints 'x_service_resource_element_val' +GO + +EXIT http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/scripts/db_setup.py ---------------------------------------------------------------------- diff --git a/security-admin/scripts/db_setup.py b/security-admin/scripts/db_setup.py index 02701c7..5ac312f 100644 --- a/security-admin/scripts/db_setup.py +++ b/security-admin/scripts/db_setup.py @@ -29,8 +29,9 @@ os_name = os_name.upper() ranger_version='' jisql_debug=True retryPatchAfterSeconds=120 - +java_patch_regex="^Patch.*?J\d{5}.class$" is_unix = os_name == "LINUX" or os_name == "DARWIN" +max_memory='1g' RANGER_ADMIN_HOME = os.getenv("RANGER_ADMIN_HOME") if RANGER_ADMIN_HOME is None: @@ -509,7 +510,7 @@ class MysqlConf(BaseDB): files = os.listdir(javaFiles) if files: for filename in files: - f = re.match("^Patch.*?.class$",filename) + f = re.match(java_patch_regex,filename) if f: version = re.match("Patch.*?_(.*).class",filename) version = version.group(1) @@ -564,7 +565,7 @@ class MysqlConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,ranger_log,path,className) + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,max_memory,ranger_log,path,className) if is_unix: ret = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -1222,7 +1223,7 @@ class OracleConf(BaseDB): files = os.listdir(javaFiles) if files: for filename in files: - f = re.match("^Patch.*?.class$",filename) + f = re.match(java_patch_regex,filename) if f: className = re.match("(Patch.*?)_.*.class",filename) className = className.group(1) @@ -1304,7 +1305,7 @@ class OracleConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Djava.security.egd=file:///dev/urandom -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,ranger_log,path,className) + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Djava.security.egd=file:///dev/urandom -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,max_memory,ranger_log,path,className) if is_unix: ret = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -1399,7 +1400,7 @@ class OracleConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,ranger_log,path,className,'"'+userName+'"','"'+oldPassword+'"','"'+newPassword+'"') + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,max_memory,ranger_log,path,className,userName,oldPassword,newPassword) if is_unix: status = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -1962,7 +1963,7 @@ class PostgresConf(BaseDB): files = os.listdir(javaFiles) if files: for filename in files: - f = re.match("^Patch.*?.class$",filename) + f = re.match(java_patch_regex,filename) if f: className = re.match("(Patch.*?)_.*.class",filename) className = className.group(1) @@ -2019,7 +2020,7 @@ class PostgresConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,ranger_log,path,className) + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,max_memory,ranger_log,path,className) if is_unix: ret = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -2114,7 +2115,7 @@ class PostgresConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,ranger_log,path,className,'"'+userName+'"','"'+oldPassword+'"','"'+newPassword+'"') + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,max_memory,ranger_log,path,className,userName,oldPassword,newPassword) if is_unix: status = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -2619,7 +2620,7 @@ class SqlServerConf(BaseDB): files = os.listdir(javaFiles) if files: for filename in files: - f = re.match("^Patch.*?.class$",filename) + f = re.match(java_patch_regex,filename) if f: className = re.match("(Patch.*?)_.*.class",filename) className = className.group(1) @@ -2676,7 +2677,7 @@ class SqlServerConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,ranger_log,path,className) + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,max_memory,ranger_log,path,className) if is_unix: ret = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -2771,7 +2772,7 @@ class SqlServerConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,ranger_log,path,className,'"'+userName+'"','"'+oldPassword+'"','"'+newPassword+'"') + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,max_memory,ranger_log,path,className,userName,oldPassword,newPassword) if is_unix: status = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -3268,7 +3269,7 @@ class SqlAnywhereConf(BaseDB): files = os.listdir(javaFiles) if files: for filename in files: - f = re.match("^Patch.*?.class$",filename) + f = re.match(java_patch_regex,filename) if f: className = re.match("(Patch.*?)_.*.class",filename) className = className.group(1) @@ -3325,7 +3326,7 @@ class SqlAnywhereConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,ranger_log,path,className) + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.%s"%(self.JAVA_BIN,max_memory,ranger_log,path,className) if is_unix: ret = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": @@ -3441,7 +3442,7 @@ class SqlAnywhereConf(BaseDB): path = os.path.join("%s","WEB-INF","classes","conf:%s","WEB-INF","classes","lib","*:%s","WEB-INF",":%s","META-INF",":%s","WEB-INF","lib","*:%s","WEB-INF","classes",":%s","WEB-INF","classes","META-INF:%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) elif os_name == "WINDOWS": path = os.path.join("%s","WEB-INF","classes","conf;%s","WEB-INF","classes","lib","*;%s","WEB-INF",";%s","META-INF",";%s","WEB-INF","lib","*;%s","WEB-INF","classes",";%s","WEB-INF","classes","META-INF;%s" )%(app_home ,app_home ,app_home, app_home, app_home, app_home ,app_home ,self.SQL_CONNECTOR_JAR) - get_java_cmd = "%s -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,ranger_log,path,className,'"'+userName+'"','"'+oldPassword+'"','"'+newPassword+'"') + get_java_cmd = "%s -XX:MetaspaceSize=100m -XX:MaxMetaspaceSize=200m -Xmx%s -Xms1g -Dlogdir=%s -Dlog4j.configuration=db_patch.log4j.xml -cp %s org.apache.ranger.patch.cliutil.%s %s %s %s -default"%(self.JAVA_BIN,max_memory,ranger_log,path,className,userName,oldPassword,newPassword) if is_unix: status = subprocess.call(shlex.split(get_java_cmd)) elif os_name == "WINDOWS": http://git-wip-us.apache.org/repos/asf/ranger/blob/d424b1a8/security-admin/src/main/java/org/apache/ranger/biz/PolicyRefUpdater.java ---------------------------------------------------------------------- diff --git a/security-admin/src/main/java/org/apache/ranger/biz/PolicyRefUpdater.java b/security-admin/src/main/java/org/apache/ranger/biz/PolicyRefUpdater.java new file mode 100644 index 0000000..25b48bb --- /dev/null +++ b/security-admin/src/main/java/org/apache/ranger/biz/PolicyRefUpdater.java @@ -0,0 +1,286 @@ +/* + * 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. + */ +package org.apache.ranger.biz; + +import java.util.ArrayList; +import java.util.HashSet; +import java.util.List; +import java.util.Set; + +import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.lang.StringUtils; +import org.apache.ranger.db.RangerDaoManager; +import org.apache.ranger.db.XXPolicyRefAccessTypeDao; +import org.apache.ranger.db.XXPolicyRefConditionDao; +import org.apache.ranger.db.XXPolicyRefDataMaskTypeDao; +import org.apache.ranger.db.XXPolicyRefGroupDao; +import org.apache.ranger.db.XXPolicyRefResourceDao; +import org.apache.ranger.db.XXPolicyRefUserDao; +import org.apache.ranger.entity.XXAccessTypeDef; +import org.apache.ranger.entity.XXDataMaskTypeDef; +import org.apache.ranger.entity.XXGroup; +import org.apache.ranger.entity.XXPolicy; +import org.apache.ranger.entity.XXPolicyConditionDef; +import org.apache.ranger.entity.XXPolicyRefAccessType; +import org.apache.ranger.entity.XXPolicyRefCondition; +import org.apache.ranger.entity.XXPolicyRefDataMaskType; +import org.apache.ranger.entity.XXPolicyRefGroup; +import org.apache.ranger.entity.XXPolicyRefResource; +import org.apache.ranger.entity.XXPolicyRefUser; +import org.apache.ranger.entity.XXResourceDef; +import org.apache.ranger.entity.XXServiceDef; +import org.apache.ranger.entity.XXUser; +import org.apache.ranger.plugin.model.RangerPolicy; +import org.apache.ranger.plugin.model.RangerPolicy.RangerDataMaskPolicyItem; +import org.apache.ranger.plugin.model.RangerPolicy.RangerPolicyItem; +import org.apache.ranger.plugin.model.RangerPolicy.RangerPolicyItemAccess; +import org.apache.ranger.plugin.model.RangerPolicy.RangerPolicyItemCondition; +import org.apache.ranger.plugin.model.RangerPolicy.RangerPolicyItemDataMaskInfo; +import org.apache.ranger.service.RangerAuditFields; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; + +@Component +public class PolicyRefUpdater { + + @Autowired + RangerDaoManager daoMgr; + + @Autowired + RangerAuditFields<?> rangerAuditFields; + + public void createNewPolMappingForRefTable(RangerPolicy policy, XXPolicy xPolicy, XXServiceDef xServiceDef) throws Exception { + if(policy == null) { + return; + } + + cleanupRefTables(policy); + + final Set<String> resourceNames = policy.getResources().keySet(); + final Set<String> groupNames = new HashSet<>(); + final Set<String> userNames = new HashSet<>(); + final Set<String> accessTypes = new HashSet<>(); + final Set<String> conditionTypes = new HashSet<>(); + final Set<String> dataMaskTypes = new HashSet<>(); + + for (List<? extends RangerPolicyItem> policyItems : getAllPolicyItems(policy)) { + if (CollectionUtils.isEmpty(policyItems)) { + continue; + } + + for (RangerPolicyItem policyItem : policyItems) { + groupNames.addAll(policyItem.getGroups()); + userNames.addAll(policyItem.getUsers()); + + if (CollectionUtils.isNotEmpty(policyItem.getAccesses())) { + for (RangerPolicyItemAccess access : policyItem.getAccesses()) { + accessTypes.add(access.getType()); + } + } + + if (CollectionUtils.isNotEmpty(policyItem.getConditions())) { + for (RangerPolicyItemCondition condition : policyItem.getConditions()) { + conditionTypes.add(condition.getType()); + } + } + + if (policyItem instanceof RangerDataMaskPolicyItem) { + RangerPolicyItemDataMaskInfo dataMaskInfo = ((RangerDataMaskPolicyItem) policyItem).getDataMaskInfo(); + + dataMaskTypes.add(dataMaskInfo.getDataMaskType()); + } + } + } + + for (String resource : resourceNames) { + XXResourceDef xResDef = daoMgr.getXXResourceDef().findByNameAndPolicyId(resource, policy.getId()); + + if (xResDef == null) { + throw new Exception(resource + ": is not a valid resource-type. policy='"+ policy.getName() + "' service='"+ policy.getService() + "'"); + } + + XXPolicyRefResource xPolRes = rangerAuditFields.populateAuditFields(new XXPolicyRefResource(), xPolicy); + + xPolRes.setPolicyId(policy.getId()); + xPolRes.setResourceDefId(xResDef.getId()); + xPolRes.setResourceName(resource); + + daoMgr.getXXPolicyRefResource().create(xPolRes); + } + + for (String group : groupNames) { + if (StringUtils.isBlank(group)) { + continue; + } + + XXGroup xGroup = daoMgr.getXXGroup().findByGroupName(group); + + if (xGroup == null) { + throw new Exception(group + ": group does not exist. policy='"+ policy.getName() + "' service='"+ policy.getService() + "' group='" + group + "'"); + } + + XXPolicyRefGroup xPolGroup = rangerAuditFields.populateAuditFields(new XXPolicyRefGroup(), xPolicy); + + xPolGroup.setPolicyId(policy.getId()); + xPolGroup.setGroupId(xGroup.getId()); + xPolGroup.setGroupName(group); + + daoMgr.getXXPolicyRefGroup().create(xPolGroup); + } + + for (String user : userNames) { + if (StringUtils.isBlank(user)) { + continue; + } + + XXUser xUser = daoMgr.getXXUser().findByUserName(user); + + if (xUser == null) { + throw new Exception(user + ": user does not exist. policy='"+ policy.getName() + "' service='"+ policy.getService() + "' user='" + user +"'"); + } + + XXPolicyRefUser xPolUser = rangerAuditFields.populateAuditFields(new XXPolicyRefUser(), xPolicy); + + xPolUser.setPolicyId(policy.getId()); + xPolUser.setUserId(xUser.getId()); + xPolUser.setUserName(user); + + daoMgr.getXXPolicyRefUser().create(xPolUser); + } + + for (String accessType : accessTypes) { + XXAccessTypeDef xAccTypeDef = daoMgr.getXXAccessTypeDef().findByNameAndServiceId(accessType, xPolicy.getService()); + + if (xAccTypeDef == null) { + throw new Exception(accessType + ": is not a valid access-type. policy='" + policy.getName() + "' service='" + policy.getService() + "'"); + } + + XXPolicyRefAccessType xPolAccess = rangerAuditFields.populateAuditFields(new XXPolicyRefAccessType(), xPolicy); + + xPolAccess.setPolicyId(policy.getId()); + xPolAccess.setAccessDefId(xAccTypeDef.getId()); + xPolAccess.setAccessTypeName(accessType); + + daoMgr.getXXPolicyRefAccessType().create(xPolAccess); + } + + for (String condition : conditionTypes) { + XXPolicyConditionDef xPolCondDef = daoMgr.getXXPolicyConditionDef().findByServiceDefIdAndName(xServiceDef.getId(), condition); + + if (xPolCondDef == null) { + throw new Exception(condition + ": is not a valid condition-type. policy='"+ xPolicy.getName() + "' service='"+ xPolicy.getService() + "'"); + } + + XXPolicyRefCondition xPolCond = rangerAuditFields.populateAuditFields(new XXPolicyRefCondition(), xPolicy); + + xPolCond.setPolicyId(policy.getId()); + xPolCond.setConditionDefId(xPolCondDef.getId()); + xPolCond.setConditionName(condition); + + daoMgr.getXXPolicyRefCondition().create(xPolCond); + } + + for (String dataMaskType : dataMaskTypes ) { + XXDataMaskTypeDef dataMaskDef = daoMgr.getXXDataMaskTypeDef().findByNameAndServiceId(dataMaskType, xPolicy.getService()); + + if (dataMaskDef == null) { + throw new Exception(dataMaskType + ": is not a valid datamask-type. policy='" + policy.getName() + "' service='" + policy.getService() + "'"); + } + + XXPolicyRefDataMaskType xxDataMaskInfo = new XXPolicyRefDataMaskType(); + + xxDataMaskInfo.setPolicyId(policy.getId()); + xxDataMaskInfo.setDataMaskDefId(dataMaskDef.getId()); + xxDataMaskInfo.setDataMaskTypeName(dataMaskType); + + daoMgr.getXXPolicyRefDataMaskType().create(xxDataMaskInfo); + } + } + + public Boolean cleanupRefTables(RangerPolicy policy) { + final Long policyId = policy == null ? null : policy.getId(); + + if (policyId == null) { + return false; + } + + XXPolicyRefResourceDao xPolResDao = daoMgr.getXXPolicyRefResource(); + XXPolicyRefGroupDao xPolGroupDao = daoMgr.getXXPolicyRefGroup(); + XXPolicyRefUserDao xPolUserDao = daoMgr.getXXPolicyRefUser(); + XXPolicyRefAccessTypeDao xPolAccessDao = daoMgr.getXXPolicyRefAccessType(); + XXPolicyRefConditionDao xPolCondDao = daoMgr.getXXPolicyRefCondition(); + XXPolicyRefDataMaskTypeDao xPolDataMaskDao = daoMgr.getXXPolicyRefDataMaskType(); + + for (XXPolicyRefResource resource : xPolResDao.findByPolicyId(policyId)) { + xPolResDao.remove(resource); + } + + for(XXPolicyRefGroup group : xPolGroupDao.findByPolicyId(policyId)) { + xPolGroupDao.remove(group); + } + + for(XXPolicyRefUser user : xPolUserDao.findByPolicyId(policyId)) { + xPolUserDao.remove(user); + } + + for(XXPolicyRefAccessType access : xPolAccessDao.findByPolicyId(policyId)) { + xPolAccessDao.remove(access); + } + + for(XXPolicyRefCondition condVal : xPolCondDao.findByPolicyId(policyId)) { + xPolCondDao.remove(condVal); + } + + for(XXPolicyRefDataMaskType dataMask : xPolDataMaskDao.findByPolicyId(policyId)) { + xPolDataMaskDao.remove(dataMask); + } + + return true; + } + + static List<List<? extends RangerPolicyItem>> getAllPolicyItems(RangerPolicy policy) { + List<List<? extends RangerPolicyItem>> ret = new ArrayList<>(); + + if (CollectionUtils.isNotEmpty(policy.getPolicyItems())) { + ret.add(policy.getPolicyItems()); + } + + if (CollectionUtils.isNotEmpty(policy.getDenyPolicyItems())) { + ret.add(policy.getDenyPolicyItems()); + } + + if (CollectionUtils.isNotEmpty(policy.getAllowExceptions())) { + ret.add(policy.getAllowExceptions()); + } + + if (CollectionUtils.isNotEmpty(policy.getDenyExceptions())) { + ret.add(policy.getDenyExceptions()); + } + + if (CollectionUtils.isNotEmpty(policy.getDataMaskPolicyItems())) { + ret.add(policy.getDataMaskPolicyItems()); + } + + if (CollectionUtils.isNotEmpty(policy.getRowFilterPolicyItems())) { + ret.add(policy.getRowFilterPolicyItems()); + } + + return ret; + } +}
