站点图标 江湖人士

SQL Server 如何使用脚本执行授权操作(权限管理)

SQL Server数据库有完善的权限管理机制,对于存储过程,其权限分为查看定义,执行和修改,查看SP定义的权限是:VIEW DEFINITION ,执行存储过程的权限是:EXECUTE,修改SP的权限是:ALTER,但是该权限也能修改表结构,视图的定义等数据库对象。数据的读取权限是SELECT,这个查看定义是不同的权限。对于一个数据表,如果仅授予VIEW DEFINITION权限,而没有授予SELECT权限,那么用户只能查看数据表的结构(Schema),而无法查看表中存储的数据。

SQL Server 如何使用脚本执行授权操作(权限管理) 1

在管理权限时,可以给特定的用户授予“只能读取数据和执行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

退出移动版