ranger-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ab...@apache.org
Subject [7/9] ranger git commit: RANGER-2203, RANGER-2219: Review and update database schema for ranger policies and tag objects to minimize database queries/updates
Date Fri, 19 Oct 2018 17:01:38 GMT
http://git-wip-us.apache.org/repos/asf/ranger/blob/48cfb403/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 4a216fe..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,6 +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 ('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/48cfb403/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/48cfb403/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/48cfb403/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/48cfb403/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;
+	}
+}


Mime
View raw message