SQL Server 创建新用户 --每个用户只对自己的记录有select权限（超详细）

@sql sever的一次实验 第四章：数据库安全性 习题

创建用户的时候比较烦恼,百度上各种查也没明白原理,跟老师探讨一番,恍然大悟,做个记录,大家可以参考,欢迎指正。

首先建表

CREATE TABLE 职工 ( 职工号 INT,
	姓名 CHAR ( 20 ),
	年龄 INT,
	职务 CHAR ( 20 ),
	工资 money,
部门号 INT );
CREATE TABLE 部门 ( 部门号 INT,
	名称 CHAR ( 20 ),
	经理名 CHAR ( 20 ),
地址 CHAR ( 20 ),
电话号 VARCHAR ( 11 ) );

创建新用户

创建用户时要注意 一个登录名只能对应一个用户

• 首先建立一个登录名（一定要在sa用户登录下操作）

CREATE login aa WITH password = '112' 

• 这里登录名是aa 密码 是112
• 然后建一个用户，

CREATE USER [王明] FOR login [aa]

（1）用户王明对两个表有SELECT权限

CREATE   VIEW  s_部门
AS
SELECT *
FROM  [部门]

CREATE   VIEW  s_职工
AS
SELECT *
FROM  [职工]

GRANT  SELECT
ON s_部门
TO 王明
GRANT  SELECT
ON s_职工
TO 王明

（2）用户李勇对两个表有INSERT和DELETE权限

CREATE USER 李勇 without login GRANT INSERT,
DELETE ON s_职工 TO 李勇 GRANT INSERT,
DELETE ON s_部门 TO 李勇

（3）每个职工只对自己的记录有SELECT权限（sql 不适用sql sever）

GRANT  SELECT 
ON 职工
WHEN USER()=NAME
TO  ALL;

sql sever 只能创建视图，然后将视图权限授予用户

代码如下：（有几个职工就创建几个试图，我就不详述了，因为创建表的时候写多了职工，写了好久）

create  view  _李勇
AS(
SELECT  [姓名]
FROM   [职工]
WHERE   [姓名]='李勇'
)
GRANT  ALL PRIVILEGES
ON  _李勇
TO  李勇

（4）用户刘星对职工表有SELECT 权限，对工资字段具有更新权限

CREATE USER 刘星 without login
GRANT  SELECT
ON s_职工
TO 刘星

（5）用户张新具有修改这两个表的权限

CREATE USER 张新 without login
GRANT  update
on  s_部门
TO  张新

GRANT  update
on  s_职工
TO  张新

（6）用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限

CREATE USER 周平 without login
GRANT ALL PRIVILEGES
ON   s_部门
to   周平
WITH GRANT OPTION

（7）用户杨兰具有从每个部门职工中 SELECT 最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

CREATE   VIEW   avg_工资(职务,平均工资)
AS
SELECT  职务, AVG(工资)
FROM    职工
GROUP BY 职务

建立视图，建完是这样的

CREATE   VIEW   MAX_工资(最大工资)
AS
SELECT   max(工资)
FROM    职工

CREATE   VIEW   Min_工资(最小工资)
AS
SELECT   min(工资)
FROM    职工

CREATE USER 杨兰 without login

GRANT  SELECT
ON  [MAX_工资],[Min_工资],[avg_工资]
TO
杨兰

GRANT  SELECT
ON  [avg_工资]
TO
杨兰
GRANT  SELECT
ON  [Min_工资]
TO
杨兰

习题来源 ：
《数据库系统概论》王珊，萨师煊 编著（第五版）
第 155 页习题第 7 题的第 (3) 小题。