sqlSQL数据库怎么批量为存储过程/函数授权呢?

2023年 4月 18日 87.1k 0

在工作当中遇到一个类似这样的问题:要对数据库账户的权限进行清理、设置,其中有一个用户Test,只能拥有数据库MyAssistant的DML(更新、插入、删除等)操作权限,另外拥有执行数据库

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

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

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

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

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

如果我将该存储过程授予EXEC权限给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'

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

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论