SQL Server数据库有完善的权限管理机制,对于存储过程,其权限分为查看定义,执行和修改,查看SP定义的权限是:VIEW DEFINITION ,执行存储过程的权限是:EXECUTE,修改SP的权限是:ALTER,但是该权限也能修改表结构,视图的定义等数据库对象。数据的读取权限是SELECT,这个查看定义是不同的权限。对于一个数据表,如果仅授予VIEW DEFINITION权限,而没有授予SELECT权限,那么用户只能查看数据表的结构(Schema),而无法查看表中存储的数据。
在管理权限时,可以给特定的用户授予“只能读取数据和执行SP,而不能修改数据”的权限,也就是,使特定的用户只能查看数据(只读,SELECT),只能查看定义(VIEW DEFINITION),和执行SP的权限(EXECUTE),这样的权限设置,既能使用户查看到业务数据,又能避免用户私自修改数据。
对于数据的读取权限,SQL Server内置固定数据库角色 db_datareader,把用户添加到该角色中,用户就被授予了对数据库中所有数据(表或视图)的读取权限,就是说,用户可以对数据表或视图执行select命令读取数据;也可以逆向思考,不允许用户修改数据,把用户添加到固定数据库角色 db_denydatawriter 中,这样,用户不能添加,更新和删除任何数据,就是说,不能对任何数据表执行insert,updae和delete命令。这两个数据库角色,相当于以下两个命令:
grant select to [domain\user]; deny update,delete,insert to [domain\user];
权限的分配分为:授予(grant)和拒绝(deny),对于已分配的权限,也可以通过回收(revoke)命令收回,权限管理是个技术活。
一,授予查看定义的权限
查看数据库对象的权限是VIEW DEFINITION,通常数据库对象是指:数据表,视图,存储过程,函数等,被授予VIEW DEFINITION权限之后,用户只能查看定义,而无法从数据表或视图中查看数据,无法执行SP和函数等。
1,授予SQL Server实例级别的查看定义的权限
以下代码用于授予权限VIEW ANY DEFINITION,代码必须在master数据库中执行,使指定的用户能够查看当前SQL Server实例中的所有数据库对象的定义:
use master go grant view any definition to [domain\user]
2,授予User,只能查看当前数据库对象的定义的权限
以下代码用于授予VIEW DEFINITION,使指定的用户能够查看指定数据库中的所有对象的定义:
use db_name go grant view definition to [domain\user]
3,授予User,只能查看当前数据库的指定数据库对象的定义的权限
以下代码用于授予VIEW DEFINITION,通过on子句,使指定的用户能够查看指定对象的定义:
use db_name go grant view definition on object::schema_name.object_name to [domain\user]
二,授予执行存储过程的权限
以下代码授予用户执行存储过程的权限,通过on子句指定用户只能执行特定的SP:
use db_name go grant execute on object::schema_name.object_name to [domain\user]
如果grant execute省略on子句,表示所有的SP,这样,用户可以执行数据库中的所有SP:
use db_name go grant execute to [domain\user]
三,授予用户修改存储过程的权限
修改存储过程的权限是ALTER,但是,ALTER同时也能修改表结构,视图定义等数据库对象,如下代码所示:
GRANT ALTER TO [domain\user]
如果仅授予用户修改SP的权限,那么必须逐个设置,或者把SP创建在独立的schema下,通过授予用户修改schema,达到控制用户只修改SP的目的:
GRANT ALTER ON SCHEMA::proc_schema TO [domain\user]
四,授予用户查看SP的定义,执行和修改SP的权限
通过GRANT子句,可以一次性把查看SP的定义,执行和修改SP的权限都授予指定的用户:
GRANT ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::[proc_schema] TO [domain\user]
在GRANT子句中省略ON子句,表示授予用户的权限作用于所有的数据库对象,包括数据表,视图,存储过程,函数等。
五,授予Public用户查看定义的权限
当Login没有映射到相应的User时,该Login被映射到默认的Public,设置给用户查看定义的权限,这样,每个登陆到SQL Server实例的用户,都可以查看定义。
use master go grant view any definition to public use dbn_ame go grant view definition to public
六,授予用户查看定义,只读数据和执行SP的权限
存储过程 sp_msforeachdb @command 是微软未公开的存储过程,该存储过程遍历当前的SQL Server实例的所有数据库,在每个数据库中执行相同的命令:
use master go create login [domain\user] from windows; go grant view any definition to [domain\user] ; go exec sp_msforeachdb ' use [?]; if not exists ( select * from sys.database_principals where name=''domain\user'' ) create user [domain\user] for login [domain\user]; alter role db_datareader add member [domain\user]; grant execute to [domain\user]; ' go
遍历数据库的功能,也可以使用游标来实现,本文不再赘述。
参考文档:
Run same command on all SQL Server databases without cursors
Granting View Definition Permission to a User or Role in SQL Server
【江湖人士】(jhrs.com)原创文章,作者:江小编,如若转载,请注明出处:https://jhrs.com/2018/12760.html
扫码加入电报群,让你获得国外网赚一手信息。