SQL Server数据库安全性控制

最新推荐文章于 2022-11-08 15:57:36 发布
转载最新推荐文章于 2022-11-08 15:57:36 发布 · 2.7k 阅读
作业专栏收录该内容
4 篇文章
订阅专栏
本文详细介绍了在SQLServer2008中进行权限管理的过程，包括创建登录名、角色、数据库，以及对用户进行授权、收回权限等操作，通过具体案例展示了如何控制不同用户对数据库的访问级别。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
1. 创建登录名并配置角色及用户

（1）使用sa登录SQLServer2008；

（2）创建数据库sa_test，其中创建数据表dbo.saTable；

CREATE DATABASE sa_test

USE sa_test

CREATE TABLE saTable

(

SNO INT PRIMARY KEY,

SNAME CHAR(10),

SSEX CHAR(4),

SAGE INT,

SDEPT CHAR(4));

INSERT INTO saTable VALUES('16121000','张虎','男','18','CS')

INSERT INTO saTable VALUES('16121002','李振','男','20','CS')

INSERT INTO saTable VALUES('16121004','黄飒','男','21','CS')

INSERT INTO saTable VALUES('16121006','曾强','男','19','IS')

INSERT INTO saTable VALUES('16121007','张翠','女','20','IS')

INSERT INTO saTable VALUES('16122005','赵坤','男','23','MA')

INSERT INTO saTable VALUES('15124001','李丽','女','22','IS')

INSERT INTO saTable VALUES('15124003','宋美丽','女','18','IS')

INSERT INTO saTable VALUES('15125006','李铭','男','21','MA')

INSERT INTO saTable VALUES('14253610','司马颖','女','22','MA')

（3）创建登录名L1，具有dbcreator权限，影射用户名U1，默认数据库sa_test，数据库角色为public；

（4）创建登录名L2，具有dbcreator权限，影射用户名U2，默认数据库sa_test，数据库角色为public。

2. 使用新登录名登录服务器并验证权限

（1）使用L1登录SQLServer2008，验证是否可以浏览表dbo.saTable；

不可以浏览表dbo.saTable。

（2）创建数据库L1_test，用户名默认为dbo，模式默认为dbo；

CREATE DATABASE L1_test

（3）在L1_test中创建数据表dbo.L1Table；

USE L1_test

CREATE TABLE L1Table

(SNAME CHAR(10),

ISBN CHAR(20) PRIMARY KEY,

PRICE FLOAT,

COUNT INT);

INSERT INTO L1Table VALUES('121564','三国演义','52.3','6');

INSERT INTO L1Table VALUES('441148','西游记','55.5','20');

INSERT INTO L1Table VALUES('414584','红楼梦','60','12');

INSERT INTO L1Table VALUES('824132','水浒传','48.5','12');

INSERT INTO L1Table VALUES('524631','西厢记','36.8','25');

INSERT INTO L1Table VALUES('142535','蛙','56.9','50');

INSERT INTO L1Table VALUES('755365','平凡的世界','45.5','16');

INSERT INTO L1Table VALUES('692354','隋唐英雄传','52.4','20');

INSERT INTO L1Table VALUES('796354','数据库系统概论','39.6','21');

INSERT INTO L1Table VALUES('452687','数据结构','40.3','18');

（4）使用L2登录SQLServer2008，验证是否可以浏览表dbo.saTable；

不可以浏览表dbo.saTable

（5）创建数据库L2_test，用户名默认为dbo，模式默认为dbo；

CREATE DATABASE L2_test

（6）在L2_test中创建数据表dbo.L2Table。

USE L2_test

CREATE TABLE L2Table

(SNAME CHAR(10),

ISBN CHAR(20) PRIMARY KEY,

PRICE FLOAT,

COUNT INT);

INSERT INTO L2Table VALUES('英语','4145154','20.5','5');

INSERT INTO L2Table VALUES('高数','7445155','23.5','6');

INSERT INTO L2Table VALUES('体育','8442241','32.5','12');

INSERT INTO L2Table VALUES('C#','7241584','56.6','21');

INSERT INTO L2Table VALUES('C++','9541233','58.3','32');

INSERT INTO L2Table VALUES('计算机导论','54142521','49.9','45');

INSERT INTO L2Table VALUES('近代史','41518546','32.2','20');

INSERT INTO L2Table VALUES('Java','45558888','45.6','35');

INSERT INTO L2Table VALUES('安卓开发','71621341','75.3','17');

INSERT INTO L2Table VALUES('PHP设计','44654521','48.7','29');

3. sa用户对新建用户授权

（1）使用sa登录SQLServer2008；

（2）设置U1用户在L2_test中具备public权限；

（3）设置U2用户在L1_test中具备public权限；

（4）授权U1在dbo.saTable上的select权限；

USE sa_test

GRANT SELECT

ON dbo.saTable

TO U1

WITH GRANT OPTION

（5）授权U2在dbo.saTable上的insert权限。

USE sa_test

GRANT INSERT 

ON saTable

TO U2

4. 新建用户之间授权

（1）使用L1登录SQLServer2008，验证是否可以浏览表dbo.saTable中的数据，是否可以插入数据；

USE sa_test

SELECT *

FROM saTable/*可以*/

INSERT INTO saTable VALUES('151250','李民','男','21','MA')/*不可以*/

（2）授权U2在dbo.L1Table上的select权限和insert权限；

GRANT SELECT,INSERT 

ON L1Table

TO U2

（3）授权U2在L1_test中创建表的权限；

GRANT CREATE TABLE 

TO U2

（4）授权U2在L1_test中创建模式的权限。

GRANT CREATE SCHEMA 

TO U2

 

5. 验证用户权限并对指定属性授权

（1）使用L2登录SQLServer2008，验证是否可以浏览表dbo.saTable中的数据，是否可以插入数据；

USE sa_test

SELECT *

FROM saTable/*不可以*/

INSERT INTO saTable VALUES('1512510','李三民','男','21','MA')/*可以*/

（2）验证是否可以浏览表dbo.L1Table中数据，是否可以插入数据；

USE L1_test

SELECT *

FROM L1Table/*可以*/

USE L1_test

INSERT INTO L1Table

VALUES('175236','三国群英传','55.3','6');/*可以*/

USE L1_TEST

UPDATE L1TABLE SET SNAME='414584';/*不可以*/

USE L1_TEST

DELETE FROM L1TABLE WHERE(SNAME='414584');/*不可以*/

（3）创建U2用户的数据库模式UU，验证是否成功；

CREATE SCHEMA "UU" AUTHORIZATION U2/*该模式存在登录名L1下的L1_test*/

（4）创建数据表UU.Test，验证是否成功（语句创建成功）；

              CREATE TABLE UU.Test(COL1 INT PRIMARY KEY,

COL2 CHAR(10));/*存在于L2下的L1_test*/

（5）验证U2是否具备UU.Test的全部权限，如drop；

 USE L1_test

SELECT *

FROM UU.Test;

 USE L1_test

   DROP UU.Test;

 USE L1_test

INSERT INTO UU.Test VALUES('12','QQQQQ');/*全部都可以*/

（6）授权U1在dbo.L2Table上的select权限和修改指定列的权限。

USE L2_TEST

GRANT SELECT,UPDATE(PRICE)

ON L2Table

TO U1

 

6. 收回权限并验证

（1）使用L1登录SQLServer2008，验证是否可以浏览dbo.L2Table中数据，是否可以修改指定的列；

USE L2_test

SELECT *

FROM L2Table/*可以*/

USE L2_test

UPDATE L2Table SET PRICE='0'/*可以*/

（2）收回U2在dbo.L1Table上的insert权限；

USE L1_test

REVOKE INSERT

ON L1Table

FROM U2/*收回成功*/

（3）使用L2登录SQLServer2008，验证是否可以浏览表uu1.L1Table中数据，是否可以插入数据。

USE L2_test

SELECT *

FROM UU.L1Table

 USE L2_test

INSERT INTO L1Table VALUES('12564','三国','52.3','6');/*都不可以*/

7. 创建角色并授权

（1）使用L1登录SQLServer2008，在L1_test中创建数据表dbo.L1Table2；

USE L1_test

CREATE TABLE L1Table2

(SNAME CHAR(10),

ISBN CHAR(20) PRIMARY KEY);

INSERT INTO L1Table2 VALUES('英语','40145154');

INSERT INTO L1Table2 VALUES('高数','74045155');

INSERT INTO L1Table2 VALUES('体育','84402241');

INSERT INTO L1Table2 VALUES('C#','72415804');

INSERT INTO L1Table2 VALUES('C++','95412303');

INSERT INTO L1Table2 VALUES('计算机导论','054142521');

INSERT INTO L1Table2 VALUES('近代史','415180546');

INSERT INTO L1Table2 VALUES('Java','455588088');

INSERT INTO L1Table2 VALUES('安卓开发','716211341');

INSERT INTO L1Table2 VALUES('PHP设计','446054521');

（2）创建数据库角色R1；

CREATE ROLE R1/*在L1登录名下L1_test角色里*/

（3）将在dbo.L1Table表上的查询和插入权限授予角色R1，将dbo.L1Table2表上的查询权限和修改指定列权限授予角色R1；

GRANT SELECT,INSERT 

ON dbo.L1Table

TO R1

GRANT SELECT,UPDATE(SNAME)

ON dbo.L1Table2

TO R1

（4）将用户添加到角色R1中；

EXEC SP_ADDROLEMEMBER R1,U2

（5）使用L2登录SQLServer2008，验证对L1_test中数据表的操作权限。

USE L1_test

SELECT *

FROM L1Table/*可以*/

USE L1_test

INSERT INTO L1Table

VALUES('1752306','三国群雄传','55.3','6');/*可以*/

USE L1_TEST

UPDATE L1TABLE SET SNAME='1752306';/*不可以*/

USE L1_TEST

DELETE FROM L1TABLE WHERE(SNAME='1752306');/*不可以*/

8. 收回角色的权限

（1）使用L1登录SQLServer2008，收回角色R1在dbo.L1Table表上的插入权限；

REVOKE INSERT

ON L1Table FROM R1

（2）1使用L2登录SQLServer2008，验证对L1_test中数据表的操作权限。

USE L1_test

SELECT *

FROM L1Table/*可以*/

USE L1_test

INSERT INTO L1Table

VALUES('1752306','三国群雄传','55.3','6');/*不可以*/

USE L1_TEST

UPDATE L1TABLE SET SNAME='1752306';/*不可以*/

USE L1_TEST

DELETE FROM L1TABLE WHERE(SNAME='1752306');/*不可以*/