数据库SQL的安全性控制

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');/*不可以*/