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

核心提示： --创建对象及系统权限--构架权限映射到对象权限上--只考虑insert、delete、update、execute、alter、control权限--对alter的权限暂时解释不是很明确if @isobjectgrant = 1begin print replicate(char(9),

--创建对象及系统权限

--构架权限映射到对象权限上
--只考虑insert、delete、update、execute、alter、control权限
--对alter的权限暂时解释不是很明确
if @isobjectgrant = 1
begin
　　　 print replicate(char(9),1) +　 '--处理角色及用户的系统权限和对象权限'
　　　 declare cr cursor for
　　　　　　　　　　　　　　　　　　　　　　　 select grantee_name,class,object_name,object_type,permission_name,state_desc
　　　　　　　　　　　　　　　　　　　　　　　 from #perminssion
　　　　　　　　　　　　　　　　　　　　　　　 order by state_desc desc
　　　　　　　　　　　　　　　　　　　　　　　
　　　 open cr
　　　 fetch next from cr into @grantee_name,@class,@object_name,@object_type,@permission_name,@state_desc
　　　 while @@fetch_status = 0
　　　 begin
　　　　　　　 set @exec = ''
　　　　　　　 set @grantorrevoke = case @state_desc
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when 'DENY' then 'REVOKE '
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when 'GRANT' then 'GRANT '
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when 'GRANT_WITH_GRANT_OPTION' then 'GRANT '
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 else null
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　 --构架
　　　　　　　 if @class = 3
　　　　　　　 begin
　　　　　　　　　　　 declare cr_obj cursor for
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 select name,type
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 from sys.objects a
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 where　　　 isnull(is_ms_shipped,0) = 0
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and (a.type in ('U','P','FN','IF','TF'))　　　 --表、存储过程、标量函数、视图
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and (a.name not like 'sp_%')　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and (a.name not in ('sysdiagrams','fn_diagramobjects'))
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 and (a.schema_id = schema_id('dbo'))　　　 --dbo架构
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 order by type,name
　　　　　　　　　　　　　　　　　　　　　　　　　　　
　　　　　　　　　　　 open cr_obj
　　　　　　　　　　　 fetch next from cr_obj into @sub_object_name,@sub_object_type
　　　　　　　　　　　 while @@fetch_status = 0
　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　 set @exec = ''
　　　　　　　　　　　　　　　 if @sub_object_type in ('U','V')
　　　　　　　　　　　　　　　　　　　 set @oraclepermission = case　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when @permission_name in ('CONTROL') then 'ALL'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when @permission_name in ('ALTER','INSERT','DELETE','SELECT','UPDATE') then @permission_name
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 else null
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　 else if @sub_object_type in ('P','FN','IF','TF')
　　　　　　　　　　　　　　　　　　　 set @oraclepermission = case　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when @permission_name in ('CONTROL','EXECUTE') then 'ALL'
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 when @permission_name in ('ALTER') then null
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 else null
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 if @grantorrevoke = 'GRANT'
　　　　　　　　　　　　　　　　　　　 set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@sub_object_name,30)) + ' TO ' + upper(@grantee_name) + ''
　　　　　　　　　　　　　　　 else if @grantorrevoke = 'REVOKE'
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@sub_object_name,30)) + ' FROM ' + upper(@grantee_name) + ''
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　 if @exec <> ''
　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　 if @grantorrevoke = 'REVOKE'
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'BEGIN'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'VA_EXIST_PRIV := 0;'
　　　　　　　　　　　　　　　　　　　　　　　 if @oraclepermission = 'ALL'
　　　　　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE OWNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@sub_object_name,30)) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
　　　　　　　　　　　　　　　　　　　　　　　 else
　　　　　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'SELECT COUNT(*) INTO VA_EXIST_PRIV FROM DBA_TAB_PRIVS WHERE OWNER = ''DBO'' AND TABLE_NAME = ''' + upper(left(@sub_object_name,30)) + ''' AND PRIVILEGE = ''' + upper(@oraclepermission) + ''' AND GRANTEE = ''' + upper(@grantee_name) + ''';'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'IF VA_EXIST_PRIV > 0 THEN '
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),3) +　 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--对象类型是: ' + @sub_object_type
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'END IF;'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'END;'
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　　　　　 else if @grantorrevoke = 'GRANT'
　　　　　　　　　　　　　　　　　　　 begin
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'BEGIN'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'VA_EXIST_OBJ := 0;'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'SELECT COUNT(*) INTO VA_EXIST_OBJ FROM DBA_OBJECTS WHERE OWNER = ''DBO'' AND OBJECT_NAME = ''' + upper(left(@sub_object_name,30)) +　 ''';'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'IF VA_EXIST_OBJ > 0 THEN '
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),3) +　 'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--对象类型是: ' + @sub_object_type
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),2) +　 'END IF;'
　　　　　　　　　　　　　　　　　　　　　　　 print replicate(char(9),1) +　 'END;'
　　　　　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　 end
　　　　　　　　　　　　　　　
　　　　　　　　　　　　　　　 fetch next from　 cr_obj into @sub_object_name,@sub_object_type
　　　　　　　　　　　 end
　　　　　　　　　　　 close cr_obj
　　　　　　　　　　　 deallocate cr_obj
　　　　　　　 end