This is an automated email from the ASF dual-hosted git repository.
mehul pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ranger.git
The following commit(s) were added to refs/heads/master by this push:
new 8d3ef6f RANGER-3071 : Supporting character set which is not included
in latin1
8d3ef6f is described below
commit 8d3ef6f8c9bf017f90d8edf52648ce2b04d0cbe6
Author: Dineshkumar Yadav <[email protected]>
AuthorDate: Tue Nov 10 16:09:34 2020 +0530
RANGER-3071 : Supporting character set which is not included in latin1
Signed-off-by: Mehul Parikh <[email protected]>
---
.../optimized/current/ranger_core_db_sqlserver.sql | 41 ++---
.../patches/051-update-datatype-to-nvarchar.sql | 175 +++++++++++++++++++++
2 files changed, 196 insertions(+), 20 deletions(-)
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 7afabba..04cd470 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
@@ -966,10 +966,10 @@ CREATE TABLE [dbo].[x_portal_user](
[update_time] [datetime2] DEFAULT NULL NULL,
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
- [first_name] [varchar](256) DEFAULT NULL NULL,
- [last_name] [varchar](256) DEFAULT NULL NULL,
+ [first_name] [nvarchar](256) DEFAULT NULL NULL,
+ [last_name] [nvarchar](256) DEFAULT NULL NULL,
[pub_scr_name] [varchar](2048) DEFAULT NULL NULL,
- [login_id] [varchar](767) DEFAULT NULL NULL,
+ [login_id] [nvarchar](767) DEFAULT NULL NULL,
[password] [varchar](512) NOT NULL,
[email] [varchar](512) DEFAULT NULL NULL,
[status] [int] DEFAULT 0 NOT NULL,
@@ -1120,8 +1120,8 @@ CREATE TABLE [dbo].[x_group](
[update_time] [datetime2] DEFAULT NULL NULL,
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
- [group_name] [varchar](767) NOT NULL,
- [descr] [varchar](4000) NOT NULL,
+ [group_name] [nvarchar](767) NOT NULL,
+ [descr] [nvarchar](4000) NOT NULL,
[status] [int] DEFAULT 0 NOT NULL,
[group_type] [int] DEFAULT 0 NOT NULL,
[cred_store_id] [bigint] DEFAULT NULL NULL,
@@ -1147,7 +1147,7 @@ CREATE TABLE [dbo].[x_group_groups](
[update_time] [datetime2] DEFAULT NULL NULL,
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
- [group_name] [varchar](1024) NOT NULL,
+ [group_name] [nvarchar](1024) NOT NULL,
[p_group_id] [bigint] DEFAULT NULL NULL,
[group_id] [bigint] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
@@ -1165,8 +1165,8 @@ CREATE TABLE [dbo].[x_user](
[update_time] [datetime2] DEFAULT NULL NULL,
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
- [user_name] [varchar](767) NOT NULL,
- [descr] [varchar](4000) NOT NULL,
+ [user_name] [nvarchar](767) NOT NULL,
+ [descr] [nvarchar](4000) NOT NULL,
[status] [int] DEFAULT 0 NOT NULL,
[cred_store_id] [bigint] DEFAULT NULL NULL,
[is_visible] [int] DEFAULT 1 NOT NULL,
@@ -1190,7 +1190,7 @@ CREATE TABLE [dbo].[x_group_users](
[update_time] [datetime2] DEFAULT NULL NULL,
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
- [group_name] [varchar](767) NOT NULL,
+ [group_name] [nvarchar](767) NOT NULL,
[p_group_id] [bigint] DEFAULT NULL NULL,
[user_id] [bigint] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
@@ -2128,7 +2128,7 @@ CREATE TABLE [dbo].[x_ugsync_audit_info](
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
[event_time] [datetime2] DEFAULT NULL NULL,
- [user_name] [varchar](255) NOT NULL,
+ [user_name] [nvarchar](255) NOT NULL,
[sync_source] [varchar](128) NOT NULL,
[no_of_new_users] [bigint] NOT NULL,
[no_of_new_groups] [bigint] NOT NULL,
@@ -2241,7 +2241,7 @@ CREATE TABLE [dbo].[x_policy_ref_user] (
[upd_by_id] [bigint] DEFAULT NULL NULL,
[policy_id] [bigint] NOT NULL,
[user_id] [bigint] NOT NULL,
- [user_name] [varchar](4000) DEFAULT NULL NULL,
+ [user_name] [nvarchar](4000) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
@@ -2263,7 +2263,7 @@ CREATE TABLE [dbo].[x_policy_ref_group] (
[upd_by_id] [bigint] DEFAULT NULL NULL,
[policy_id] [bigint] NOT NULL,
[group_id] [bigint] NOT NULL,
- [group_name] [varchar](4000) DEFAULT NULL NULL,
+ [group_name] [nvarchar](4000) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
@@ -2363,7 +2363,7 @@ CREATE TABLE [dbo].[x_security_zone_ref_user](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[zone_id] [bigint] DEFAULT NULL NULL,
[user_id] [bigint] DEFAULT NULL NULL,
- [user_name] [varchar](767) DEFAULT NULL NULL,
+ [user_name] [nvarchar](767) DEFAULT NULL NULL,
[user_type] [tinyint] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
@@ -2392,7 +2392,7 @@ CREATE TABLE [dbo].[x_security_zone_ref_group](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[zone_id] [bigint] DEFAULT NULL NULL,
[group_id] [bigint] DEFAULT NULL NULL,
- [group_name] [varchar](767) DEFAULT NULL NULL,
+ [group_name] [nvarchar](767) DEFAULT NULL NULL,
[group_type] [tinyint] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
@@ -2448,8 +2448,8 @@ CREATE TABLE [dbo].[x_role](
[added_by_id] [bigint] DEFAULT NULL NULL,
[upd_by_id] [bigint] DEFAULT NULL NULL,
[version] [bigint] DEFAULT NULL NULL,
-[name] [varchar](255) NOT NULL,
-[description] [varchar](1024) DEFAULT NULL NULL,
+[name] [nvarchar](255) NOT NULL,
+[description] [nvarchar](1024) DEFAULT NULL NULL,
[role_options] [varchar](4000) DEFAULT NULL NULL,
[role_text] [nvarchar](max) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
@@ -2479,7 +2479,7 @@ CREATE TABLE [dbo].[x_role_ref_user](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[role_id] [bigint] NOT NULL,
[user_id] [bigint] DEFAULT NULL NULL,
-[user_name] [varchar](767) DEFAULT NULL NULL,
+[user_name] [nvarchar](767) DEFAULT NULL NULL,
[priv_type] [int] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
@@ -2507,7 +2507,7 @@ CREATE TABLE [dbo].[x_role_ref_group](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[role_id] [bigint] NOT NULL,
[group_id] [bigint] DEFAULT NULL NULL,
-[group_name] [varchar](767) DEFAULT NULL NULL,
+[group_name] [nvarchar](767) DEFAULT NULL NULL,
[priv_type] [int] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
@@ -2535,7 +2535,7 @@ CREATE TABLE [dbo].[x_policy_ref_role](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[policy_id] [bigint] NOT NULL,
[role_id] [bigint] NOT NULL,
-[role_name] [varchar](255) DEFAULT NULL NULL,
+[role_name] [nvarchar](255) DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
@@ -2566,7 +2566,7 @@ CREATE TABLE [dbo].[x_role_ref_role](
[upd_by_id] [bigint] DEFAULT NULL NULL,
[role_ref_id] [bigint] DEFAULT NULL NULL,
[role_id] [bigint] NOT NULL,
-[role_name] [varchar](255) DEFAULT NULL NULL,
+[role_name] [nvarchar](255) DEFAULT NULL NULL,
[priv_type] [int] DEFAULT NULL NULL,
PRIMARY KEY CLUSTERED
(
@@ -4103,6 +4103,7 @@ INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('048',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
('049',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
('050',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
('051',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_db_version_h
(version,inst_at,inst_by,updated_at,updated_by,active) VALUES
('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger
1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES
(dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
INSERT INTO x_user_module_perm
(user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed)
VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource
Based
Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git
a/security-admin/db/sqlserver/patches/051-update-datatype-to-nvarchar.sql
b/security-admin/db/sqlserver/patches/051-update-datatype-to-nvarchar.sql
new file mode 100644
index 0000000..9307a20
--- /dev/null
+++ b/security-admin/db/sqlserver/patches/051-update-datatype-to-nvarchar.sql
@@ -0,0 +1,175 @@
+-- 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
+
+DECLARE @SQL nvarchar(4000),
+ @TableName sysname,
+ @ColumnName sysname,
+ @DefaultConstraint varchar(256),
+ @SchemaName sysname,
+ @Definition varchar(256),
+ @Return int
+
+-- Cursor of all default constraints in the current database
+DECLARE cursObjects CURSOR FAST_FORWARD
+FOR
+SELECT object_name(d.parent_object_id) TableName, col_name(parent_object_id,
parent_column_id) ColumnName, d.name DefaultConstraint,
schema_name(d.schema_id) SchemaName, definition FROM sys.default_constraints d
INNER JOIN sys.columns c ON d.parent_column_id = c.column_id WHERE
d.is_system_named = 1
+
+OPEN cursObjects
+
+-- get the first row
+FETCH NEXT FROM cursObjects
+ INTO @TableName, @ColumnName, @DefaultConstraint, @SchemaName, @Definition
+
+-- Set the return code to 0
+SET @Return = 0
+
+-- start a transaction
+BEGIN TRAN
+
+-- Cycle through the rows of the cursor to change default constraint name
+WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
+ BEGIN
+ -- Drop current constraint
+ SET @SQL = 'alter table '+@SchemaName+'.'+@TableName+' drop constraint ' +
@DefaultConstraint
+ EXEC @Return = sp_executesql @SQL
+
+ -- Create constraint
+ SET @SQL = 'alter table '+@SchemaName+'.'+@TableName+' add constraint
df_'+@TableName+'_'+@ColumnName+' default
+
+'+@Definition+' for '+@ColumnName
+ EXEC @Return = sp_executesql @SQL
+
+ -- Get the next row
+ FETCH NEXT FROM cursObjects
+ INTO @TableName, @ColumnName, @DefaultConstraint, @SchemaName, @Definition
+ END
+
+-- Close cursor
+CLOSE cursObjects
+DEALLOCATE cursObjects
+
+-- Check to see if the WHILE loop exited with an error.
+IF (@Return = 0)
+ COMMIT TRAN
+ELSE
+ BEGIN
+ ROLLBACK TRAN
+ SET @SQL = 'Error encountered in ['+ @SchemaName + '].[' + @Tablename +
'].[' + @ColumnName + ']'
+ RAISERROR(@SQL, 16, 1)
+ END
+GO
+
+
+GO
+IF (OBJECT_ID('x_group$x_group_UK_group_name') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_group] DROP CONSTRAINT x_group$x_group_UK_group_name;
+ ALTER TABLE [dbo].[x_group] ALTER COLUMN group_name nvarchar(767);
+ ALTER TABLE [dbo].[x_group] ADD CONSTRAINT [x_group$x_group_UK_group_name]
UNIQUE (group_name);
+ ALTER TABLE [dbo].[x_group] ALTER COLUMN descr nvarchar(4000);
+END
+GO
+
+BEGIN
+ ALTER TABLE [dbo].[x_group_users] DROP CONSTRAINT
[[x_group_users$x_group_users_UK_uid_gname];
+ ALTER TABLE [dbo].[x_group_users] ALTER COLUMN group_name nvarchar(767);
+ ALTER TABLE [dbo].[x_group_users] ADD CONSTRAINT
[[x_group_users$x_group_users_UK_uid_gname] UNIQUE (user_id,group_name);
+END
+GO
+ ALTER TABLE [dbo].[x_group_groups] ALTER COLUMN group_name nvarchar(1024);
+GO
+ ALTER TABLE [dbo].[x_security_zone_ref_group] DROP CONSTRAINT
df_x_security_zone_ref_group_group_name;
+ ALTER TABLE [dbo].[x_security_zone_ref_group] ALTER COLUMN group_name
nvarchar(767);
+ ALTER TABLE [dbo].[x_security_zone_ref_group] ADD CONSTRAINT
df_x_security_zone_ref_group_group_name DEFAULT NULL FOR group_name ;
+GO
+ ALTER TABLE [dbo].[x_policy_ref_group] DROP CONSTRAINT
df_x_policy_ref_group_group_name;
+ ALTER TABLE [dbo].[x_policy_ref_group] ALTER COLUMN group_name nvarchar(4000)
;
+ ALTER TABLE [dbo].[x_policy_ref_group] ADD CONSTRAINT
df_x_policy_ref_group_group_name DEFAULT NULL FOR group_name ;
+GO
+ ALTER TABLE [dbo].[x_role_ref_group] DROP CONSTRAINT
df_x_role_ref_group_group_name;
+ ALTER TABLE [dbo].[x_role_ref_group] ALTER COLUMN group_name nvarchar(767);
+ ALTER TABLE [dbo].[x_role_ref_group] ADD CONSTRAINT
df_x_role_ref_group_group_name DEFAULT NULL FOR group_name ;
+GO
+IF (OBJECT_ID('x_portal_user$x_portal_user_UK_login_id') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_portal_user] DROP CONSTRAINT
x_portal_user$x_portal_user_UK_login_id;
+ ALTER TABLE [dbo].[x_portal_user] DROP CONSTRAINT df_x_portal_user_login_id;
+ ALTER TABLE [dbo].[x_portal_user] ALTER COLUMN login_id nvarchar(767);
+ ALTER TABLE [dbo].[x_portal_user] ADD CONSTRAINT df_x_portal_user_login_id
DEFAULT NULL FOR login_id ;
+ ALTER TABLE [dbo].[x_portal_user] ADD CONSTRAINT
[x_portal_user$x_portal_user_UK_login_id] UNIQUE (login_id) ;
+
+ DROP INDEX x_portal_user_name ON [dbo].[x_portal_user];
+ ALTER TABLE [dbo].[x_portal_user] DROP CONSTRAINT df_x_portal_user_first_name;
+ ALTER TABLE [dbo].[x_portal_user] ALTER COLUMN first_name nvarchar(767);
+ ALTER TABLE [dbo].[x_portal_user] ADD CONSTRAINT df_x_portal_user_first_name
DEFAULT NULL FOR first_name ;
+ CREATE NONCLUSTERED INDEX [x_portal_user_name] ON [x_portal_user]
([first_name] ASC) WITH (SORT_IN_TEMPDB = OFF,DROP_EXISTING =
OFF,IGNORE_DUP_KEY = OFF,ONLINE = OFF) ON [PRIMARY];
+ ALTER TABLE [dbo].[x_portal_user] DROP CONSTRAINT df_x_portal_user_last_name;
+ ALTER TABLE [dbo].[x_portal_user] ALTER COLUMN last_name nvarchar(767);
+ ALTER TABLE [dbo].[x_portal_user] ADD CONSTRAINT df_x_portal_user_last_name
DEFAULT NULL FOR last_name ;
+END
+GO
+IF (OBJECT_ID('x_user$x_user_UK_user_name') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT
x_sz_ref_user_FK_user_name;
+ ALTER TABLE [dbo].[x_user] DROP CONSTRAINT x_user$x_user_UK_user_name;
+ ALTER TABLE [dbo].[x_user] ALTER COLUMN user_name nvarchar(767);
+ ALTER TABLE [dbo].[x_user] ADD CONSTRAINT [x_user$x_user_UK_user_name] UNIQUE
(user_name);
+ ALTER TABLE [dbo].[x_security_zone_ref_user] DROP CONSTRAINT
df_x_security_zone_ref_user_user_name;
+ ALTER TABLE [dbo].[x_security_zone_ref_user] ALTER COLUMN user_name
nvarchar(767);
+ ALTER TABLE [dbo].[x_security_zone_ref_user] ADD CONSTRAINT
df_x_security_zone_ref_user_user_name DEFAULT NULL FOR user_name ;
+ ALTER TABLE [dbo].[x_security_zone_ref_user] WITH CHECK ADD CONSTRAINT
[x_sz_ref_user_FK_user_name] FOREIGN KEY([user_name]) REFERENCES [dbo].[x_user]
([user_name]);
+ ALTER TABLE [dbo].[x_user] ALTER COLUMN descr nvarchar(4000);
+END
+GO
+ DROP INDEX x_ugsync_audit_info_uname ON [dbo].[x_ugsync_audit_info];
+ ALTER TABLE [dbo].[x_ugsync_audit_info] ALTER COLUMN user_name nvarchar(767);
+ CREATE NONCLUSTERED INDEX [x_ugsync_audit_info_uname] ON
[x_ugsync_audit_info] ([user_name] ASC )
+ WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE
= OFF) ON [PRIMARY];
+GO
+
+ ALTER TABLE [dbo].[x_policy_ref_user] DROP CONSTRAINT
df_x_policy_ref_user_user_name;
+ ALTER TABLE [dbo].[x_policy_ref_user] ALTER COLUMN user_name nvarchar(4000);
+ ALTER TABLE [dbo].[x_policy_ref_user] ADD CONSTRAINT
df_x_policy_ref_user_user_name DEFAULT NULL FOR user_name ;
+GO
+
+ ALTER TABLE [dbo].[x_role_ref_user] DROP CONSTRAINT
df_x_role_ref_user_user_name;
+ ALTER TABLE [dbo].[x_role_ref_user] ALTER COLUMN user_name nvarchar(767);
+ ALTER TABLE [dbo].[x_role_ref_user] ADD CONSTRAINT
df_x_role_ref_user_user_name DEFAULT NULL FOR user_name ;
+GO
+
+IF (OBJECT_ID('x_role$x_role_UK_name') IS NOT NULL)
+BEGIN
+ ALTER TABLE [dbo].[x_role] DROP CONSTRAINT x_role$x_role_UK_name;
+ ALTER TABLE [dbo].[x_role] ALTER COLUMN name nvarchar(255);
+ ALTER TABLE [dbo].[x_role] ADD CONSTRAINT [x_role$x_role_UK_name] UNIQUE
(name);
+
+ ALTER TABLE [dbo].[x_role] DROP CONSTRAINT df_x_role_description;
+ ALTER TABLE [dbo].[x_role] ALTER COLUMN description nvarchar(1024);
+ ALTER TABLE [dbo].[x_role] ADD CONSTRAINT df_x_role_description DEFAULT NULL
FOR description ;
+END
+GO
+
+ ALTER TABLE [dbo].[x_policy_ref_role] DROP CONSTRAINT
df_x_policy_ref_role_role_name;
+ ALTER TABLE [dbo].[x_policy_ref_role] ALTER COLUMN role_name nvarchar(255);
+ ALTER TABLE [dbo].[x_policy_ref_role] ADD CONSTRAINT
df_x_policy_ref_role_role_name DEFAULT NULL FOR role_name ;
+
+GO
+ ALTER TABLE [dbo].[x_role_ref_role] DROP CONSTRAINT
df_x_role_ref_role_role_name;
+ ALTER TABLE [dbo].[x_role_ref_role] ALTER COLUMN role_name nvarchar(255);
+ ALTER TABLE [dbo].[x_role_ref_role] ADD CONSTRAINT
df_x_role_ref_role_role_name DEFAULT NULL FOR role_name ;
+GO
+exit
