关于存储过程赋权限

[pbsh]

给只读用户赋与一个存储过程的执行权限,则如果该存储过程内容涉及赠删改,只读用户也能执行,因为存储过程的EXECUTE权限不再验证内部包含对象的权限。
有没有办法让只读用户即使拥有该存储过程的执行权限，但如果存储过程内容涉及赠删改时停止执行，如果内容只涉及select时正常执行？

[sql_lover]

判断权限并回滚事务

[sql_sf]

在存储过程中涉及的表上同样做权限设计?

[pbsh]

[Quote=引用 1 楼 sql_lover 的回复:]
判断权限并回滚事务
[/Quote]
如果不增加执行脚本的复杂度呢？

[pbsh]

[Quote=引用 2 楼 sql_sf 的回复:]
在存储过程中涉及的表上同样做权限设计?
[/Quote]

没用的

[永生天地]

PERMISSIONS
返回一个包含位图的值，表明当前用户的语句、对象或列权限。

语法
PERMISSIONS ( [ objectid [ , 'column' ] ] )

参数
objectid

对象的 ID。如果未指定 objectid，则位图值包含当前用户的语法权限；否则，位图包含当前用户在该对象 ID 上的对象权限。指定的对象必须在当前数据库中。将 OBJECT_ID 函数用于对象名以确定 objectid 值。

column

返回其权限信息的列的可选名。该列必须是 objectid 所指定表中的有效列名。

返回类型
int

注释
可使用 PERMISSIONS 确定当前用户是否有执行某个语句所需的权限，或者是否有将某个对象上的权限授予另一用户所需的权限。

所返回的权限信息是 32 位位图。

低 16 位反映对当前用户的安全帐户所授予的权限，以及应用于当前用户所在的 Microsoft® Windows NT® 组或 Microsoft SQL Server™ 角色的权限。例如，当没有指定 objectid 时，将返回值 66（十六进制值 0x42），表示当前用户有执行 CREATE TABLE（十进制值 2）和 BACKUP DATABASE（十进制值 64）语句的权限。

高 16 位反映当前用户可以授予其他用户的权限。除左移 16 位（与 65536 相乘）之外，高 16 位的解释方式与下表中所介绍的低 16 位的解释方式完全相同。例如，位 0x8（十进制值 8）说明当指定 objectid 时的 INSERT 权限。而 0x80000（十进制值 524288）说明 GRANT INSERT 权限的能力，这是因为 524288 = 8 x 65536。由于角色中的成员资格，该用户可能没有执行语句的权限，但仍然能够将该权限授予他人。

下表显示语句权限所使用的位（未指定 objectid）。

位（十进制） 位（十六进制） 语句权限 
1 0x1 CREATE DATABASE（仅限于 master 数据库） 
2 0x2 CREATE TABLE 
4 0x4 CREATE PROCEDURE 
8 0x8 CREATE VIEW 
16 0x10 CREATE RULE 
32 0x20 CREATE DEFAULT 
64 0x40 BACKUP DATABASE 
128 0x80 BACKUP LOG 
256 0x100 保留 


下表显示当仅指定 objectid 时，返回的对象权限所使用的位。

位（十进制） 位（十六进制） 语句权限 
1 0x1 SELECT ALL 
2 0x2 UPDATE ALL 
4 0x4 REFERENCES ALL 
8 0x8 INSERT 
16 0x10 DELETE 
32 0x20 EXECUTE（仅限于过程） 
4096 0x1000 SELECT ANY（至少一列） 
8192 0x2000 UPDATE ANY 
16384 0x4000 REFERENCES ANY 


下表显示当同时指定 objectid 和 column 时，返回的列级对象权限所使用的位。

位（十进制） 位（十六进制） 语句权限 
1 0x1 SELECT 
2 0x2 UPDATE 
4 0x4 REFERENCES 


如果指定的参数为 NULL 值或无效（例如，objectid 或 column 不存在），则返回 NULL 值。没有定义不适用的权限所使用的位值（例如，表的 EXECUTE 权限、位 0x20）。

使用按位 AND (&) 运算符确定 PERMISSIONS 函数返回的位图中的每个位集。

还可使用 sp_helprotect 系统存储过程返回某位用户在当前数据库中的对象权限列表。

示例
A. 对语句权限使用 PERMISSIONS 函数
本示例确定当前用户是否能够执行 CREATE TABLE 语句。

IF PERMISSIONS()&2=2
   CREATE TABLE test_table (col1 INT)
ELSE
   PRINT 'ERROR: The current user cannot create a table.'

B. 对对象权限使用 PERMISSIONS 函数
本示例确定当前用户是否能够在 authors 表中插入数据行。

IF PERMISSIONS(OBJECT_ID('authors'))&8=8 
   PRINT 'The current user can insert data into authors.'
ELSE
   PRINT 'ERROR: The current user cannot insert data into authors.'

C. 对可授予的权限使用 PERMISSIONS 函数
本示例确定当前用户是否能够将 authors 表中的 INSERT 权限授予另一用户。

IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
   PRINT 'INSERT on authors is grantable.'
ELSE
   PRINT 'You may not GRANT INSERT permissions on authors.'

[pbsh]

永生哥来点实在的

[csw200201]

5楼 already gave you the method to detect permission within your stored proc based on user and table.

However if you don't want to modify your stored proc, you need to take a different approach. You should set up 2 custom roles and assign read-only user to one role (say call it read-only) and other users to the other role (say call it read-write). Simply then assign execute permission on stored procs that modifies data only to read-write role and assign execute permission to stord procs that do not modify data to both read-only and read-write roles.

[pbsh]

我希望能一个用户搞定这件事情,如果做不到,就只能在赋权限的时候进行判断,如果存储过程内容包含增删改,则仅赋予VIEW DEFINITION权限,如果没有就赋予EXECUTE权限。

[csw200201]

[Quote=引用 8 楼 pbsh 的回复:]
我希望能一个用户搞定这件事情,如果做不到,就只能在赋权限的时候进行判断,如果存储过程内容包含增删改,则仅赋予VIEW DEFINITION权限,如果没有就赋予EXECUTE权限。
[/Quote]

If you don't want to use the permission method suggested by #5楼 and incorporate them into stored procs, then you are going to have to create separate roles.