西西軟件園多重安全檢測(cè)下載網(wǎng)站、值得信賴的軟件下載站!
軟件
軟件
文章
搜索

首頁西西教程數(shù)據(jù)庫教程 → MS SQL數(shù)據(jù)庫怎么 批量給存儲(chǔ)過程/函數(shù)授權(quán)呢?

MS SQL數(shù)據(jù)庫怎么 批量給存儲(chǔ)過程/函數(shù)授權(quán)呢?

相關(guān)軟件相關(guān)文章發(fā)表評(píng)論 來源:西西整理時(shí)間:2013/5/15 8:36:20字體大。A-A+

作者:西西點(diǎn)擊:2次評(píng)論:0次標(biāo)簽: 存儲(chǔ)過程

在工作當(dāng)中遇到一個(gè)類似這樣的問題:要對(duì)數(shù)據(jù)庫賬戶的權(quán)限進(jìn)行清理、設(shè)置,其中有一個(gè)用戶Test,只能擁有數(shù)據(jù)庫MyAssistant的DML(更新、插入、刪除等)操作權(quán)限,另外擁有執(zhí)行數(shù)據(jù)庫存儲(chǔ)過程、函數(shù)的權(quán)限,但是不能進(jìn)行DDL操作(包括新建、修改表、存儲(chǔ)過程等...),于是需要設(shè)置登錄名Test的相關(guān)權(quán)限:

1:右鍵單擊登錄名Test的屬性.

2: 在服務(wù)器角色里面選擇"public"服務(wù)器角色。

3:在用戶映射選項(xiàng)當(dāng)中,選擇"db_datareader"、"db_datawriter"、"public"三個(gè)數(shù)據(jù)庫角色成員。

此時(shí),已經(jīng)實(shí)現(xiàn)了擁有DML操作權(quán)限,如果需要擁有存儲(chǔ)過程和函數(shù)的執(zhí)行權(quán)限,必須使用GRANT語句去授權(quán),一個(gè)生產(chǎn)庫的存儲(chǔ)過程和函數(shù)加起來成千上百,如果手工執(zhí)行的話,那將是一個(gè)辛苦的體力活,而我手頭有十幾個(gè)庫,所以必須用腳本去實(shí)現(xiàn)授權(quán)過程。下面是我寫的一個(gè)存儲(chǔ)過程,亮點(diǎn)主要在于會(huì)判斷存儲(chǔ)過程、函數(shù)是否已經(jīng)授予了EXE或SELECT權(quán)限給某個(gè)用戶。這里主要用到了安全目錄試圖sys.database_permissions,例如,數(shù)據(jù)庫里面有個(gè)存儲(chǔ)過程dbo.sp_authorize_right,如果這個(gè)存儲(chǔ)過程授權(quán)給Test用戶了話,那么在目錄試圖sys.database_permissions里面會(huì)有一條記錄,如下所示:

如果我將該存儲(chǔ)過程授予EXEC權(quán)限給TEST1,那么

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test;

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test1;

SELECT * FROM sys.sysusers WHERE name ='Test' OR name ='Test1'

其實(shí)grantee_principal_id代表向其授予權(quán)限的數(shù)據(jù)庫主體 ID ,所以我就能通過上面兩個(gè)視圖來判斷存儲(chǔ)過程是否授予執(zhí)行權(quán)限給用戶Test與否,同理,對(duì)于函數(shù)也是如此,存儲(chǔ)過程如下所示,其實(shí)這個(gè)存儲(chǔ)過程還可以擴(kuò)展,如果您有特殊的需要的話。

Code Snippet

USE MyAssistant;

GO

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON

GO

IF 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                 :            www.cnblogs.com/kerrycode/        

--        Description          :            將數(shù)據(jù)庫的所有自定義存儲(chǔ)過程或自定義函數(shù)賦權(quán)給某個(gè)用戶(可以繼續(xù)擴(kuò)展)

/**********************************************************************************************************

        Parameter              :                         參數(shù)說明

***********************************************************************************************************

        @type                  :  'P'  代表存儲(chǔ)過程 , 'F' 代表存儲(chǔ)過程,如果需要可以擴(kuò)展其它對(duì)象

        @user                  :  某個(gè)用戶賬戶

***********************************************************************************************************

    Modified Date        Modified User        Version                    Modified Reason

***********************************************************************************************************

    2013-05-13           Kerry                 V01.00.01             排除系統(tǒng)存儲(chǔ)過程和系統(tǒng)函數(shù)的授權(quán)處理

    2013-05-14           Kerry                 V01.00.02             增加判斷,如果某個(gè)存儲(chǔ)過程已經(jīng)賦予權(quán)限

                                                                     則不做任何操作

***********************************************************************************************************/

--=========================================================================================================

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;

           END

GO

    相關(guān)評(píng)論

    閱讀本文后您有什么感想? 已有人給出評(píng)價(jià)!

    • 8 喜歡喜歡
    • 3 頂
    • 1 難過難過
    • 5 囧
    • 3 圍觀圍觀
    • 2 無聊無聊

    熱門評(píng)論

    最新評(píng)論

    發(fā)表評(píng)論 查看所有評(píng)論(0)

    昵稱:
    表情: 高興 可 汗 我不要 害羞 好 下下下 送花 屎 親親
    字?jǐn)?shù): 0/500 (您的評(píng)論需要經(jīng)過審核才能顯示)