Sqlserver2005迁移至Oracle系列之五：角色、用户、及权限

核心提示： --创建必要用户并添加到角色if @iscreateuser = 1begin declare cr_usergroup cursor for select distinct username,role_name,is_role from #usergroup where usern

--创建必要用户并添加到角色

if @iscreateuser = 1
begin
　　　 declare cr_usergroup cursor for
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 select distinct username,role_name,is_role
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 from #usergroup
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 where　　　 username not in　 ('dbo','sys','95013')
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and username in (select distinct username from #usergroup where is_role = 0 )
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and role_name not in ('public','guest')
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 order by username,is_role,role_name
　　　 set @usernameold = ''
　　　 set @role_name_grant = ''
　　　 open cr_usergroup
　　　 fetch next from cr_usergroup into @username,@role_name,@is_role
　　　 while @@fetch_status = 0
　　　 begin
　　　　　　　 if @is_role = 0
　　　　　　　 begin
　　　　　　　　　　　 if @role_name_grant <> ''
　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 '--添加用户[' + upper(@usernameold) + ':' + @usernameold + ']到角色[' + upper(@role_name_grant) + ']中'
　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'BEGIN'
　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'EXECUTE IMMEDIATE (''GRANT ' + upper(@role_name_grant) + ' TO ' + upper(@usernameold) +　 ''');'
　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'EXECUTE IMMEDIATE (''ALTER USER ' + upper(@usernameold) +　 ' DEFAULT ROLE ' + upper(@role_name_grant) + ''');'
　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'END;'
　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 ''
　　　　　　　　　　　　　　　 set @role_name_grant = ''
　　　　　　　　　　　 end
　　　　　　　　　　　 print replicate(char(9),1) +　 '--创建用户[' + upper(@username) + ':' + @username + ']'
　　　　　　　　　　　 print replicate(char(9),1) +　 'BEGIN'
　　　　　　　　　　　 print replicate(char(9),2) +　 'VA_EXIST_USER := 0;'
　　　　　　　　　　　 print replicate(char(9),2) +　 'SELECT COUNT(USER_ID) INTO VA_EXIST_USER FROM DBA_USERS WHERE USERNAME = ''' + upper(@username) + ''';'
　　　　　　　　　　　 print replicate(char(9),2) +　 'IF VA_EXIST_USER = 0 THEN '
　　　　　　　　　　　 print replicate(char(9),3) +　 'EXECUTE IMMEDIATE (''CREATE USER ' + upper(@username) + ' IDENTIFIED BY ' + lower(@username) + ''');'
　　　　　　　　　　　 print replicate(char(9),2) +　 'ELSE'
　　　　　　　　　　　 print replicate(char(9),3) +　 'BEGIN'
　　　　　　　　　　　 print replicate(char(9),4) +　 'SELECT USER_ID INTO VA_EXIST_USER FROM DBA_USERS WHERE USERNAME = ''' + upper(@username) + ''';'
　　　　　　　　　　　 print replicate(char(9),4) +　 'IF VA_EXIST_USER < 80 AND VA_EXIST_USER > 0 THEN'
　　　　　　　　　　　 print replicate(char(9),5) +　 'NULL;'
　　　　　　　　　　　 print replicate(char(9),4) +　 'ELSE'
　　　　　　　　　　　 print replicate(char(9),5) +　 'EXECUTE IMMEDIATE (''DROP USER ' + upper(@username) + ' CASCADE '');'
　　　　　　　　　　　 print replicate(char(9),5) +　 'EXECUTE IMMEDIATE (''CREATE USER ' + upper(@username) + ' IDENTIFIED BY ' + lower(@username) + ''');'
　　　　　　　　　　　 print replicate(char(9),4) +　 'END IF;'
　　　　　　　　　　　 print replicate(char(9),3) +　 'END;'
　　　　　　　　　　　 print replicate(char(9),2) +　 'END IF;'
　　　　　　　　　　　 print replicate(char(9),2) +　 'EXECUTE IMMEDIATE (''GRANT CREATE SESSION,ALTER SESSION,DEBUG CONNECT SESSION,DEBUG ANY PROCEDURE,UNLIMITED TABLESPACE TO ' + upper(@username) +　 ''');'　　　　　　　　　　　
　　　　　　　　　　　 print replicate(char(9),1) +　 'END;'
　　　　　　　　　　　 print replicate(char(9),1) +　 ''
　　　　　　　　　　　 set @usernameold = @username
　　　　　　　 end
　　　　　　　 else
　　　　　　　 begin
　　　　　　　　　　　 if @usernameold = @username
　　　　　　　　　　　　　　　 set @role_name_grant = case @role_name_grant when '' then　 @role_name else @role_name_grant + ',' + @role_name end
　　　　　　　 end

　　　　　　　 fetch next from cr_usergroup into @username,@role_name,@is_role
　　　 end
　　　 close cr_usergroup
　　　 deallocate cr_usergroup
　　　
　　　 if @role_name_grant <> ''
　　　 begin
　　　　　　　 print replicate(char(9),1) +　 '--添加用户[' + upper(@usernameold) + ':' + @usernameold + ']到角色[' + upper(@role_name_grant) + ']中'
　　　　　　　 print replicate(char(9),1) +　 'EXECUTE IMMEDIATE (''GRANT ' + upper(@role_name_grant) + ' TO ' + upper(@usernameold) +　 ''');'
　　　　　　　 print replicate(char(9),1) +　 ''
　　　　　　　 set @role_name_grant = ''
　　　 end
　　　
end