Fork me on GitHub

T-Sql(七)用户权限操作(grant)

  一般数据库的权限操作我们很少用,除非一些大型的项目,需要给数据库配置不同的用户及权限,防患于未然,今天我们就来了解下t-sql中配置用户权限操作。

  先看示例代码:

 1 --创建登录名
 2 create login text1
 3 with password='password1',
 4 check_policy =off;
 5 
 6 --修改登录名
 7 alter login text1
 8 with name = test1
 9 go
10 alter login test1 disable
11 
12 --修改登录名密码
13 alter login test1
14 with password='123456'
15 old_password='password1';
16 
17 --删除登录名
18 drop login test1
19 ----------------
20 --创建用户
21 use AdventureWorks
22 create user user1
23 for login text1
24 
25 --修改用户
26 alter user user1
27 with name=use2;
28 
29 --删除用户
30 drop user user2
31 
32 --查看当前数据库对应的用户
33 select USER_NAME()
34 SELECT CURRENT_USER AS 'Current User Name';

  这些语法都很简单我们主需要记住就行,当然通过SQLSERVER的可视化界面也可以配置。

  下面我们也通过一个实例来理解和运用配置用户权限。

  实例要求:

    1.建立两个用户,user1,user2
    2.user1具有操作数据库ExamMis的所有权限
    3.转换上下文到USer1下,建立一个函数:f_GetQuestionDetails,该函数返回题目内容(包括题干,分题项,题型三个表的关联内容)
    4.赋权限给user2:让其只具有执行该函数的权限;
    5.转换上下文到user2,执行函数调用。

1,建立两个用户,user1,user2

  创建两个登陆名,并在为登陆名创建user1和user2用户。

  示例代码:

 1 create login TestUser1
 2 with password='password1';
 3 
 4 create user User1
 5 for login TestUser1
 6 with default_schema=dbo;
 7 
 8 create login TestUser2
 9 with password='password2';
10 
11 create user User2
12 for login TestUser2

2,user1具有操作数据库ExamMis的所有权限

  这个操作有三种方法:

    1,SQLSERVER可视化操作。

    2,grant all去给用户分配权限。

    3,通过SQLSERVER系统自带的存储过程分配权限。

  这里我们先讲一下grant的用法,grant是用来给用户分配权限用的。

  我们先看一个示例代码:

1 grant all 
2 on database::ExamMis
3 to User1
4 WITH GRANT OPTION

  这段代码的意思就是给User1分配具有操作ExamMis数据库的一切权限。all关键字的意思是所有权限,也可以指定某一权限,比如查询权限就可以写grant select,

  当然 on 后面也可以是表,函数,存储过程,视图等。

  示例代码:

1 grant select,insert,update
2 on table::student
3 to User1
4 WITH GRANT OPTION

   这段代码的意思是给用户user1在student表分配具有查询,插入,更新的权限,但是没有删除得权限。

  通过SQLSERVER系统自带的存储过程分配权限,示例代码:

1 EXEC sp_addrolemember N'db_owner', N'User1' 

   sp_addrolemember是系统自带的存储过程,db_owner的意思是所有者,可视化操作的时候我们也会看到。当然还有一些其他的一些存储过程,在文章的后面会给大家整理出来。

3,转换上下文到User1下,建立一个函数:f_GetQuestionDetails

  执行上下面到User1下:

1 EXECUTE AS LOGIN = 'TestUser1'

  我们可以通过select USER_NAME(); 来查看当前用户名。

  下面我们创建函数:f_GetQuestionDetails,函数具有返回值,返回table。

  示例代码:

1 create function Select_Questions()
2 returns table
3 as
4 return
5 (
6     select t1.*,t2.TypeName,t2.Score,t3.SelectionNo,t3.SelectionTitle,t3.IsAnswer from dbo.Questions t1
7     inner join dbo.QuestionType t2 on t1.TypeNo=t2.[no]
8     inner join dbo.QuestionSelections t3 on t1.[no]=t3.QuestionNo
9 ); 

  函数的用法很简单,就不单列去讲解了,和我们编程时候用的方法类似,只是语法不同而已,大家熟悉用就行。

4,函数赋权限给user2:让其只具有执行该函数的权限;

  这一步我们可以直接用上面提到的grant去给用户user2分配权限。

  这里我们就多做一部,创建一个角色,然这个角色具有操作这个函数的权限,然后把角色分配给用户user2。

  一般我们项目的权限管理都是这个设计:用户->角色->权限。

  示例代码:

1 create role Select_Fuction  --创建角色
2 
3 GRANT select
4    ON OBJECT::dbo.Select_Questions
5    TO Select_Fuction;
6 
7 exec sp_addrolemember 'Select_Fuction','User2'  --将Select_Fuction角色添加到User2用户中

5.转换上下文到user2,执行函数调用。

  示例代码:

1 REVERT;
2 EXECUTE AS LOGIN = 'TestUser2'
3 select USER_NAME()
4 
5 select * from Select_Questions()

  REVERT;的意思是切换上下文。

   执行完操作我们把登陆名,用户名删掉:

1 drop function Select_Questions
2 drop login TestUser1
3 drop login TestUser2
4 drop user user1
5 drop user user2

 用户权限配置系统存储过程

 1 sp_addlogin 登录名,登陆密码,默认数据库,默认语言,安全码,是否加密
 2 sp_password 旧密码,新密码,指定登录号
 3 sp_defaultdb 指定登录号,默认数据库
 4 sp_defaultlanguage 指定登录号,默认语言
 5 sp_helplogins 指定登录号
 6 sp_droplogin 指定登录号
 7 
 8 -------------------------数据库用户管理---
 9 sp_grantdbaccess 登录号,数据库用户名
10 sp_helpuser 数据库用户名
11 sp_revokedbaccess 指定数据库用户名
12 
13 ------------------------服务器角色--------
14 sp_addsrvrolermemeber 登陆账号名,服务器角色名
15 sp_dropsrvrolermember 登陆用户名,服务器角色名
16 
17 ------------------------数据库角色---------
18 
19 sp_addrole 数据库角色名,数据库角色的所有者
20 sp_droprole 数据库角色名
21 
22 ------------创建数据库角色的成员----
23 
24 sp_addrolemember 数据库角色名,数据库用户
25 sp_droprolemember 数据库角色名,数据库用户

  t-sql中的用户权限配置就讲到这,这一部分的内容我们很少去涉及,但是还是了解一些,说不准哪天DB不在,经理让你去配数据库用户呢,编程的都不会,就你会,自己是不是很有面子啊。哈哈。

  以后继续整理一下编程相关的知识,请大家多多关注。。。

  

posted @ 2013-10-18 09:35  田园里的蟋蟀  阅读(7744)  评论(2编辑  收藏  举报