Experiment 7: view, index and data security

1, Experimental purpose

1. Master view characteristics;
2. Master the creation and management of views using SQL Server 2008;
3. Master the creation and management of indexes in object resource manager and T-SQL statements;

2, Experimental class hours

2 class hours

3, Experimental requirements

1. Master the use of interface and command methods to create and manage views;
2. Create, modify, delete and use indexes by using object resource manager and T-SQL statements.
3. Complete the experiment report.

4, Experimental content

1. Take the experimental database as the basic data and use the object explorer to create the following views:
1) Create an information view stu of all students' student numbers, names and ages_ info

create view stu_info
as
select Sno,Sname,Sage
from student

2) Create CS department student basic information view stu_cs

create view stu_cs
as
select *
from student
where Sdept='CS'

3) Create an information view stu for students with an elective course score of more than 80_ 80, including students' basic information and grades.

create view stu_80
as
select student.Sno,Sname,Sage,Sdept,Grade 
from student,SC
where student.Sno=SC.Sno and SC.grade>80

1. Based on the experimental database, please use T-SQL statement to complete the following contents and write the SQL statement in the experimental report book:
   1) Create CS_age view, including the student number, name and age of students in CS department. When it is required to modify and insert, it is still necessary to ensure that this view is only for students in CS department;

create view CS_age
as
select Sno,Sname,Sage
from student
where Sdept='CS'
with check option

2) Create CS_age_20 view, including basic information of CS students over the age of 20; And ensure that the modification of the view text meets the condition that the age is greater than 20.

create view CS_age_20
as
select *
from student
where Sdept='CS'and Sage>20
with check option

3) Create a view stu_cg, used to view student number, name, course and score information, and encrypted WITH ENCRYPTION.

create view stu_cg
with encryption
as
select Student.Sno,Sname,Cname,Grade
from student,Course,SC

4) View stu to CS students_ Insert a new student record in CS, student number 200515026, name "Zhao Hongping", gender "male", age 21;

insert into stu_cs
values('200515026','Zhao Hongping','male','21','CS')

5) Using view stu_CS modifies the name of the student with student number 200515001 as "Zhao Qingqing";

update stu_cs
set Sname='Zhao Qingqing'
where Sno='200515001'

6) Using view stu_CS deletes the record with CS department student number 200515003;

delete from stu_cs
where Sno='200515003'

7) Delete view CS_age_20；

drop view CS_age_20

3. In object explorer, create and manage the following indexes using graphical methods:
1) Create a non clustered index idx for the sname field in the data table student_ name；

create nonclustered index idx_name on student(Sname)

2) Create a composite index idx for the sno and sname columns of the student table_ no_ name;

  create  index idx_no_name
  on student(Sname,Sno)

3) Delete index idx of data table student_ name；

use student 
drop index idx_name on student

1. Use T-SQL statements to create and manage the following indexes:
   1) Create a nonclustered index idx on the CNAME column of the course table_ cname;

 create nonclustered index idx_cname on course(cname)

2) Create a composite index idx on the SnO and CNO columns of the sc table_ sno_ cno;

 create index idx_sno_cno on SC(Sno,Cno)

3) Using system stored procedure sp_helpidex view index idx_cname information;

 Exec sp_helpindex 'course'

4) Delete index idx_cname.

drop index idx_cname on course

1. Data security. Log in to the SQL server server as a system administrator, implement the following operations in the SQL Server 2008 interface, and write the corresponding program code.
   1) Add a user zhang in the current computer with the password of secret. Enable this user to log in to the SQL Server server in windows mode with the login name of zhang;

create login zhang with password='secret'
create user zhang for login zhang

2) The login names of new users logging in to SQL Server server in mixed mode are stu1, stu2 and stu3 respectively, the login password is secret, and the default login database is stu;
Add the login accounts stu1, stu2 and stu3 to the server role sysadmin;

create login stu1 with password='secret',
DEFAULT_DATABASE=student
GO
create login stu2 with password='secret',
DEFAULT_DATABASE=student
GO
create login stu3 with password='secret',
DEFAULT_DATABASE=student
GO

EXEC sp_addsrvrolemember 'stu1','sysadmin'
EXEC sp_addsrvrolemember 'stu2','sysadmin'
EXEC sp_addsrvrolemember 'stu3','sysadmin'

3) Delete stu2 and stu3 members of the server role sysadmin;

EXEC sp_dropsrvrolemember 'stu1','sysadmin'
EXEC sp_dropsrvrolemember 'stu2','sysadmin'
EXEC sp_dropsrvrolemember 'stu3','sysadmin'

4) Create the user zhang in the database stu, and the login account is zhang;

create user zhang for login zhang 

5) Create users stu1, stu2 and stu3 in the database stu, and the login accounts are stu1, stu2 and stu3;

create user stu1 for login stu1
create user stu2 for login stu2
create user stu3 for login stu3

6) Give the database user zhang permission to create data tables;

Grant create table to zhang

7) Give the database user stu1 the permission to insert, modify and delete the sc table;

Grant insert,update,delete on SC to zhang

8) Give database users stu2 and stu3 all operation permissions on student table and course table and operation permissions for querying sc, and allow re authorization to other users;

GRANT ALL 
ON student
TO stu2,stu3
WITH GRANT OPTION;

GRANT ALL
ON course
TO stu2,stu3
WITH GRANT OPTION;

9) Reclaim the permission of database user stu2 to delete student table and course table;

REVOKE DELETE
on student
TO stu2,stu3 CASCADE

REVOKE DELETE
on course
TO stu2,stu3 CASCADE