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

--基本上完美处理了insert、update、delete、select、control、execute等权限
--不能映射alter权限的处理

set nocount on

--清理临时表
begin
    if not object_id('tempdb..#perminssion')  is null
        drop table #perminssion
    if not object_id('tempdb..#usergroup')  is null
        drop table #usergroup
end

begin
    --所有的显示权限
    select *
    into #perminssion
    from (
            select distinct user_name(grantee_principal_id) as grantee_name,grantee_principal_id
            ,class,class_desc
            ,case class
                when 0 then db_name()
                when 1 then object_name(major_id)
                when 3 then schema_name(major_id)
                when 4 then user_name(major_id)
                end
            as object_name
            ,(case when b.type is null and class = 3 then 'SCM' else b.type end ) as object_type
            ,permission_name,state_desc
            ,isnull(b.is_ms_shipped,0) as  is_ms_shipped
            from sys.database_permissions  a left join sys.objects b on a.major_id = b.object_id
    ) t
    where    class in (1,3)    --数据库、对象、构架
            and isnull(is_ms_shipped,0) = 0
            and not (class = 3 and object_name = 'sys')    --dbo架构
            and object_name not like 'sp%'
            and object_name not like 'sys%'
            and object_name not in ('fn_diagramobjects')
            and not (class = 1 and object_type is null)
            and permission_name not in ('VIEW DEFINITION')
    order by grantee_name,object_name


    --数据库用户的隶属关系
    select *
    into #usergroup
    from
    (
    select c.name as username,c.type,c.principal_id as user_principal_id,a.name as role_name,a.principal_id as role_principal_id,1 as is_role
    from sys.database_principals a inner join sys.database_role_members b on a.principal_id = b.role_principal_id inner join sys.database_principals c on b.member_principal_id = c.principal_id
    union all
    select name as username,type,principal_id as user_principal_id,'' as role_name,'' as role_principal_id,0 as is_role
    from sys.database_principals
    where type = 's'
    union all
    select name as username,type,principal_id as user_principal_id,'public' as role_name,0 as role_principal_id,1 as is_role
    from sys.database_principals
    where type = 's'
    )t
    where username not in ('guest')
    order by username,is_role,role_name
    /*
    select * from #usergroup
    select * from #perminssion
    */
end



--脚本开始部分
begin
    print replicate(char(9),0) + '--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
    print replicate(char(9),0) + '--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
    print replicate(char(9),0) + 'DECLARE'
    print replicate(char(9),1) + 'VA_EXIST_USER    INT:=0;'
    print replicate(char(9),1) + 'VA_EXIST_PRIV    INT:=0;'   
    print replicate(char(9),1) + 'VA_EXIST_OBJ    INT:=0;'   
    print replicate(char(9),0) + 'BEGIN'
end


--变量声明部分
begin
    declare @iscreaterole int --（0.不生成角色；1.生成角色）
        set @iscreaterole = 1
    declare @iscreateuser int --（0.不生成用户；1.生成用户）
        set @iscreateuser = 1
    declare @isobjectgrant int --（0.不生成对象及系统权限；1.生成对象及系统权限）
        set @isobjectgrant = 1   
    declare @role_name sysname
    declare @role_name_grant sysname
    declare @username sysname
    declare @usernameold sysname
    declare @is_role int

    declare @grantee_name sysname
    declare @class int
    declare @object_name sysname
    declare @object_type sysname
    declare @permission_name sysname
    declare @state_desc sysname
    declare @grantorrevoke sysname
    declare @oraclepermission sysname
    declare @exec nvarchar(1000)
    declare @execgrant nvarchar(1000)

    declare @sub_object_name sysname
    declare @sub_object_type sysname
end

--创建必要的角色
if @iscreaterole = 1
begin
    declare cr_group cursor for
                                    select distinct role_name
                                    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')
                                            and is_role = 1
                                    order by role_name
    open cr_group
    fetch next from cr_group into @role_name
    while @@fetch_status = 0
    begin
        print replicate(char(9),1) +  '--创建角色[' + upper(@role_name) + ']'
        print replicate(char(9),1) +  'BEGIN'
        print replicate(char(9),2) +  'VA_EXIST_USER := 0;'
        print replicate(char(9),2) +  'SELECT COUNT(ROLE) INTO VA_EXIST_USER FROM DBA_ROLES WHERE ROLE = ''' + upper(@role_name) + ''';'
        print replicate(char(9),2) +  'IF VA_EXIST_USER > 0 THEN '
        print replicate(char(9),3) +  'EXECUTE IMMEDIATE (''DROP ROLE ' + upper(@role_name) + ''');'
        print replicate(char(9),3) +  'EXECUTE IMMEDIATE (''CREATE ROLE ' + upper(@role_name) + ''');'
        print replicate(char(9),2) +  'ELSE'
        print replicate(char(9),3) +  'EXECUTE IMMEDIATE (''CREATE ROLE ' + upper(@role_name) + ''');'
        print replicate(char(9),2) +  'END IF;'
        print replicate(char(9),1) +  'END;'
       
        --处理sqlserver固定数据库角色权限，可按需添加定义
        begin
            /*
            db_owner    db_accessadmin    db_securityadmin    db_ddladmin   
            db_backupoperator    db_datareader    db_datawriter    db_denydatareader    db_denydatawriter
            */
            if @role_name = 'db_owner'
            begin
                    print replicate(char(9),1) +  'BEGIN'
                    print replicate(char(9),2) +  'EXECUTE IMMEDIATE (''GRANT DBA TO DB_OWNER'');'
                    print replicate(char(9),1) +  'END;'
            end
            else if @role_name = 'db_datareader'
            begin
                    print replicate(char(9),1) +  'BEGIN'
                    print replicate(char(9),2) +  'EXECUTE IMMEDIATE (''GRANT SELECT ANY TABLE TO DB_DATAREADER'');'
                    print replicate(char(9),1) +  'END;'
            end
        end
       
        print replicate(char(9),1) +  ''
        fetch next from cr_group into @role_name
    end
    close cr_group
    deallocate cr_group
end

--创建必要用户并添加到角色
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

--创建对象及系统权限
--构架权限映射到对象权限上
--只考虑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
       
        --数据库对象                     
        if @class = 1
        begin
            if @object_type = 'U'
                set @oraclepermission = case 
                                            when @permission_name in ('CONTROL','ALTER') then 'ALL'
                                            when @permission_name in ('INSERT','DELETE','SELECT','UPDATE') then @permission_name
                                            else null
                                        end
            else if @object_type in ('P','FN','IF','TF')
                set @oraclepermission = case 
                                            when @permission_name in ('CONTROL','ALTER') then 'ALL'
                                            when @permission_name in ('EXECUTE') then @permission_name
                                            else null
                                        end
            if @grantorrevoke = 'GRANT'
                set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@object_name,30)) + ' TO ' + upper(@grantee_name) + ''
            else if @grantorrevoke = 'REVOKE'
                set @exec = @grantorrevoke + @oraclepermission + ' ON DBO.' + upper(left(@object_name,30)) + ' FROM ' + upper(@grantee_name) + ''

            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(@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(@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) + '--对象类型是: ' + @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(@object_name,30)) +  ''';'
                    print replicate(char(9),2) +  'IF VA_EXIST_OBJ > 0 THEN '
                    print replicate(char(9),3) +  'EXECUTE IMMEDIATE (''' + @exec + ''');' + CHAR(9) + '--对象类型是: ' + @object_type
                    print replicate(char(9),2) +  'END IF;'
                    print replicate(char(9),1) +  'END;'
                end
            end
        end
       
        fetch next from cr into @grantee_name,@class,@object_name,@object_type,@permission_name,@state_desc
    end
    close cr
    deallocate cr

    print replicate(char(9),1) +  ''
end

--脚本结束部分
begin
    print replicate(char(9),0) + 'END;'
    print replicate(char(9),0) + '--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
    print replicate(char(9),0) + '--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
end