数据库 实验四 SQL数据库安全控制

1. 创建登录用户st1，st2

       create login st1  with   password='123'

CREATE LOGIN st1 WITH PASSWORD='123456'
CREATE LOGIN st2 WITH PASSWORD='123456'

2. 使st1,st2成为stu_db的合法用户

  EXEC sp_grantdbaccess N'st1'  /sp_grantdbaccess为存储过程

EXEC sp_grantdbaccess 'st1';
EXEC sp_grantdbaccess 'st2';

3. 以st1登录，是否能对student表进行查询、修改、删除？\

无法进行查询,修改,删除.

4.  以管理员登录，授予st1查询、修改student,sc,course表的权限，并允许st1将权限转授。将student表中学生年龄增加1岁。

授予权限

GRANT SELECT,UPDATE 
ON T.Student
TO "st1" 
WITH GRANT OPTION;
GRANT SELECT,UPDATE 
ON T.SC
TO "st1" 
WITH GRANT OPTION;

将student表中学生年龄增加1岁。

UPDATE T.Student SET sage=sage+1;

5.  以st1登录，将对student，sc，course表的查询、修改权限授予st2。

GRANT SELECT,UPDATE 
ON T.Student
TO "st2";

GRANT SELECT,UPDATE 
ON T.SC
TO "st2";

6.  以st2登录

7  查询王华同学的平均成绩;

SELECT Student.Sno,Sname,AVG(Grade) '平均分'
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE Sname = '王华'
GROUP BY Student.Sno,Sname

8.  查询张亮同学超过他平均成绩的课程名称。

SELECT Cname
FROM SC
JOIN Course AS C ON SC.Cno = C.Cno
WHERE SC.Sno = '2023610632'
  AND SC.Grade > (
    SELECT AVG(Grade)
    FROM SC
    WHERE Sno = '2023610632'
  );

9.  将sc表中李丽同学的成绩增加10%。

UPDATE SC
SET Grade = Grade * 1.1
WHERE Sno = '2023610633';
SELECT Sname '姓名',Grade '成绩'
FROM SC
JOIN Student ON SC.Sno = Student.Sno
WHERE Sname = '李丽'

10. 收回st1对表student,sc的查询、修改权限。

    REVOKE SELECT,UPDATE 
ON T.Student
FROM "st1"
CASCADE;

11. 删除用户st1,st2。删除登录名st1,st2。

删除用户st1,st2    

EXEC sp_dropuser 'st1';
EXEC sp_droplogin 'st1';
EXEC sp_dropuser 'st2';
EXEC sp_droplogin 'st2';

删除登录名st1,st2

DROP USER "st1";
DROP USER "st2";
DROP LOGIN "st1";
DROP LOGIN "st2";