Sqlserver2005迁移至Oracle系列之五:角色、用户、及
分类:计算机教程

在工作当中遇到一个类似这样的问题:要对数据库账户的权限进行清理、设置,其中有一个用户Test,只能拥有数据库MyAssistant的DML(更新、插入、删除等)操作权限,另外拥有执行数据库存储过程、函数的权限,但是不能进行DDL操作,于是需要设置登录名Test的相关权限:

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

1:右键单击登录名Test的属性.

set nocount on

2: 在服务器角色里面选择"public"服务器角色。

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

3:在用户映射选项当中,选择"db_datareader"、"db_datawriter"、"public"三个数据库角色成员。

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

此时,已经实现了拥有DML操作权限,如果需要拥有存储过程和函数的执行权限,必须使用GRANT语句去授权,一个生产库的存储过程和函数加起来成千上百,如果手工执行的话,那将是一个辛苦的体力活,而我手头有十几个库,所以必须用脚本去实现授权过程。下面是我写的一个存储过程,亮点主要在于会判断存储过程、函数是否已经授予了EXE或SELECT权限给某个用户。这里主要用到了安全目录试图sys.database_permissions,例如,数据库里面有个存储过程dbo.sp_authorize_right,如果这个存储过程授权给Test用户了话,那么在目录试图sys.database_permissions里面会有一条记录,如下所示:

    --数据库用户的隶属关系
    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

如果我将该存储过程授予EXEC权限给TEST1,那么

--脚本开始部分
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

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test;

--变量声明部分
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

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test1;

    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)

SELECT * FROM sys.sysusersWHERE name ='Test' OR name ='Test1'

    declare @sub_object_男篮世界杯赔率,name sysname
    declare @sub_object_type sysname
end

其实grantee_principal_id代表向其授予权限的数据库主体 ID ,所以我就能通过上面两个视图来判断存储过程是否授予执行权限给用户Test与否,同理,对于函数也是如此,存储过程如下所示,其实这个存储过程还可以扩展,如果您有特殊的需要的话。复制代码 代码如下:Code SnippetUSE MyAssistant;GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ONGOIF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_authorize_right') AND OBJECTPROPERTY(id, 'IsProcedure') =1) DROP PROCEDURE sp_authorize_right;GO--=========================================================================================================-- ProcedureName : sp_authorize_right-- Author : Kerry -- CreateDate : 2013-05-10 -- Blog : -- Description : 将数据库的所有自定义存储过程或自定义函数赋权给某个用户(可以继续扩展)/********************************************************************************************************** Parameter : 参数说明*********************************************************************************************************** @type : 'P' 代表存储过程 , 'F' 代表存储过程,如果需要可以扩展其它对象 @user : 某个用户账户*********************************************************************************************************** Modified Date Modified User Version Modified Reason*********************************************************************************************************** 2013-05-13 Kerry V01.00.01 排除系统存储过程和系统函数的授权处理 2013-05-14 Kerry V01.00.02 增加判断,如果某个存储过程已经赋予权限 则不做任何操作***********************************************************************************************************/--=========================================================================================================CREATE PROCEDURE sp_authorize_right( @type AS CHAR(10) , @user AS VARCHAR(20))AS DECLARE @sqlTextVARCHAR(1000); DECLARE @UserId INT;SELECT @UserId = uid FROM sys.sysusers WHERE name=@user; IF @type = 'P' BEGIN CREATE TABLE #ProcedureName( SqlText VARCHAR(max)); INSERT INTO #ProcedureName SELECT 'GRANT EXECUTE ON ' p.name ' TO ' @user ';' FROM sys.procedures p WHERE NOT EXISTS( SELECT 1 FROM sys.database_permissions r WHERE r.major_id = p.object_id AND r.grantee_principal_id = @UserId AND r.permission_name IS NOT NULL ) SELECT * FROM #ProcedureName; --SELECT 'GRANT EXECUTE ON ' NAME ' TO ' @user ';' --FROM sys.procedures; --SELECT 'GRANT EXECUTE ON ' [name] ' TO ' @user ';' -- FROM sys.all_objects --WHERE [type]='P' OR [type]='X' OR [type]='PC' DECLARE cr_procedure CURSOR FOR SELECT * FROM #ProcedureName; OPEN cr_procedure; FETCH NEXT FROM cr_procedure INTO @sqlText; WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE(@sqlText); FETCH NEXT FROM cr_procedure INTO @sqlText; END CLOSE cr_procedure; DEALLOCATE cr_procedure; END ELSE IF @type='F' BEGIN CREATE TABLE #FunctionSet( functionName VARCHAR(1000)); INSERT INTO #FunctionSet SELECT 'GRANT EXEC ON ' name ' TO ' @user ';' FROM sys.all_objects s WHERE NOT EXISTS( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = @UserId) AND schema_id = SCHEMA_ID('dbo') AND( s.[type] = 'FN' OR s.[type] = 'AF' OR s.[type] = 'FS' OR s.[type] = 'FT' ) ; SELECT * FROM #FunctionSet; --SELECT 'GRANT EXEC ON ' name ' TO ' @user ';' FROM sys.all_objects -- WHERE schema_id =schema_id('dbo') -- AND ([type]='FN' OR [type] ='AF' OR [type]='FS' OR [type]='FT' ); INSERT INTO #FunctionSet SELECT 'GRANT SELECT ON ' name ' TO ' @user ';' FROM sys.all_objects s WHERE NOT EXISTS( SELECT 1 FROM sys.database_permissions p WHERE p.major_id = s.object_id AND p.grantee_principal_id = @UserId) AND schema_id = SCHEMA_ID('dbo') AND( s.[type] = 'TF' OR s.[type] = 'IF' ) ; SELECT * FROM #FunctionSet; --SELECT 'GRANT SELECT ON ' name ' TO ' @user ';' FROM sys.all_objects -- WHERE schema_id =schema_id('dbo') -- AND ([type]='TF' OR [type]='IF') ; DECLARE cr_Function CURSOR FOR SELECT functionName FROM #FunctionSet; OPEN cr_Function; FETCH NEXT FROM cr_Function INTO @sqlText; WHILE @@FETCH_STATUS = 0 BEGIN PRINT(@sqlText); EXEC(@sqlText); FETCH NEXT FROM cr_Function INTO @sqlText; END CLOSE cr_Function; DEALLOCATE cr_Function; ENDGO

--创建必要的角色
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 '

本文由美洲杯赔率发布于计算机教程,转载请注明出处:Sqlserver2005迁移至Oracle系列之五:角色、用户、及

上一篇:SQL SERVER中各类触发器的完整语法及参数说明 下一篇:没有了
猜你喜欢
热门排行
精彩图文