SQL Server 权限管理与数据库操作实战

SQL Server 权限管理与数据库操作实战

本文将通过一系列操作案例，详细讲解SQL Server的权限管理和数据库操作，帮助您深入理解和掌握相关的知识。

一、以Windows身份验证模式登录到服务器上，在对象资源管理器中可视化操作完成以下操作：

1. 选择和设置SQL Server身份验证模式。 在服务器的属性对话框中，选择'SQL Server'和'Windows'身份验证模式。
2. 创建一个服务器登录账户，选择'SQL Server'身份验证，设置登录名('Login')和密码。
3. 创建'studentscore'数据库用户，设置用户名('User1')并关联登录名('Login')。
4. 以'SQL Server'身份验证模式登录，登录名为'Login'，输入设置的密码登录到服务器上，查看是否能够对'studentscore'数据库操作，思考为什么？

操作步骤：

1. 在对象资源管理器中右键点击服务器，选择属性，选择安全选项卡，在服务器身份验证中选择'SQL Server'和'Windows'身份验证模式。
2. 在服务器对象资源管理器中，展开安全性，右键点击登录，选择新建登录。在新建登录对话框中，选择'SQL Server'身份验证，设置登录名('Login')和密码。
3. 在服务器对象资源管理器中，展开数据库，选择'studentscore'，右键点击用户，选择新建用户。在新建用户对话框中，设置用户名('User1')并关联登录名('Login')。
4. 在SQL Server Management Studio中，选择'SQL Server'身份验证模式登录，登录名为'Login'，输入设置的密码登录到服务器上。此时无法对'studentscore'数据库进行操作，因为还没有为'User1'用户授权。

二、以Windows身份验证模式登录到服务器上，在SQL查询编辑器中编写SQL语句完成以下操作（当前数据库为'studentscore'）：

1. 授予用户'User1'对'Student'表、'course'表和'sc'表的查询权限。
2. 授予用户'User1'对'course'表数据的修改权限。
3. 授予用户'User1'对'Sc'表数据的删除权限。
4. 建立计算机系学生基本信息视图（视图名：'V_JSJSTUDENT'），在该视图中保存计算机系所有学生的信息。
5. 建立选修了“数据库原理及应用”课程的学生信息视图（视图名：'V_DBCOURSE'），视图中保存选修了“数据库原理及应用”课程的所有学生学号、姓名、性别和所在系信息。
6. 授予用户'User1'对以上建立的两个视图的查询权限。

SQL语句：

1. GRANT SELECT ON Student TO User1; GRANT SELECT ON course TO User1; GRANT SELECT ON sc TO User1;
2. GRANT UPDATE ON course TO User1;
3. GRANT DELETE ON sc TO User1;
4. CREATE VIEW V_JSJSTUDENT AS SELECT * FROM student WHERE department='计算机系';
5. CREATE VIEW V_DBCOURSE AS SELECT student.sno, student.sname, student.ssex, student.department FROM student, sc WHERE student.sno=sc.sno AND sc.cno='001';
6. GRANT SELECT ON V_JSJSTUDENT TO User1; GRANT SELECT ON V_DBCOURSE TO User1;

三、以'Login'登录到服务器上，在查询编辑器中编写SQL语句完成以下操作（当前数据库为'studentscore'），并回答问题。

1. 删除计算机系所有学生的信息。该操作是否可以完成，为什么？
2. 修改“数据库原理及应用”课程的学分，改为4学分。该操作是否可以完成，为什么？
3. 修改所有成绩不及格的学生成绩，将成绩改为60分。该操作是否可以完成，为什么？
4. 查询计算机系所有选修了“数据库原理及应用”课程的学生学号、姓名和性别。要求对视图进行查询。该操作是否可以完成，为什么？
5. 修改计算机系某学生的年龄数据，对视图'V_JSJSTUDENT'操作。该操作是否可以完成，为什么？

SQL语句及分析：

1. DELETE FROM student WHERE department='计算机系'; 该操作不能完成，因为'User1'用户没有对'Student'表的删除权限。
2. UPDATE course SET credit=4 WHERE cno='001'; 该操作不能完成，因为'User1'用户没有对'course'表的修改权限。
3. UPDATE sc SET grade=60 WHERE grade<60; 该操作不能完成，因为'User1'用户没有对'Sc'表的修改权限。
4. SELECT sno, sname, ssex FROM V_DBCOURSE; 该操作可以完成，因为'User1'用户被授予了对'V_DBCOURSE'视图的查询权限。
5. UPDATE V_JSJSTUDENT SET sage=20 WHERE sno='001'; 该操作不能完成，因为视图是只读的，不能进行数据的修改。

四、以Windows身份验证模式重新登录到服务器上，在查询编辑器中编写SQL语句完成以下操作（当前数据库为'studentscore'），并回答问题。

1. 回收'User1'用户对'Student'表的查询权限。再以'Login'登录到服务器上，查看是否能够查询'Student'表中的数据。结果是什么，为什么？
2. 回收'User1'用户对'course'表的修改权限。以'Login'登录到服务器上，查看是否能够修改'course'表中的数据。结果是什么，为什么？

SQL语句及分析：

1. REVOKE SELECT ON Student FROM User1; 以'Login'登录到服务器上，尝试查询'Student'表中的数据，会提示无权限。因为'User1'用户被回收了对'Student'表的查询权限。
2. REVOKE UPDATE ON course FROM User1; 以'Login'登录到服务器上，尝试修改'course'表中的数据，会提示无权限。因为'User1'用户被回收了对'course'表的修改权限。

通过以上案例，您可以了解到SQL Server中权限管理的重要性和具体操作方法。合理设置权限，不仅可以保证数据库安全，还可以有效地控制用户对数据库的访问和操作，提高数据库管理的效率。