访问权限:控制用户对数据库资源的访问权限。
数据 *** 作权限:控制用户对数据库中数据的 *** 作权限,如插入、更新、删除等。
结构 *** 作权限:控制用户对数据库中表、视图、存储过程等结构的 *** 作权限,如创建、修改、删除等。
安全权限:控制用户对数据库安全设置的权限,如设置密码、角色等。
系统权限:控制用户对数据库系统的 *** 作权限,如创建数据库、备份数据库等。
注意:不同的数据库系统可能会有所差异,上述权限类别仅供参考。
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 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 :将数据库的所有自定义存储过程或自定义函数赋权给某个用户(可以继续扩展)
/**********************************************************************************************************
Parameter : 参数说明
***********************************************************************************************************
@type : 'P' 代表存储过程 , 'F' 代表存储过程,如果需要可以扩展其它对象
@user : 某个用户账户
***********************************************************************************************************
Modified DateModified UserVersionModified Reason
***********************************************************************************************************
2013-05-13 Kerry V01.00.01 排除系统存储过程和系统函数的授权处理
2013-05-14 Kerry V01.00.02 增加判断,如果某个存储过程已经赋予权限
则不做任何 *** 作
***********************************************************************************************************/
--=========================================================================================================
CREATE PROCEDURE sp_authorize_right
(
@typeAS CHAR(10),
@userAS VARCHAR(20)
)
AS
DECLARE @sqlTextVARCHAR(1000)
DECLARE @UserIdINT
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 + ''
FROMsys.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 +''
--FROMsys.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 + ''
FROMsys.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 + ''
FROMsys.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
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)