数据库实验2-数据库管理

（1）实验目的

掌握Oracle中系统权限和对象权限的概念，能熟练进行用户权限的授予与回收;理解角色的基本概念，能熟练使用角色进行权限的授予与回收。掌握数据库对象如触发器、存储过程和函数的定义和使用。掌握ORACLE数据库系统逻辑备份和恢复的方法。

（2）实验环境

Oracle 11g，windows 10；

（3）实验内容

实验内容第一部分

1. 用SYSTEM账户登录数据库，创建用户A、B、C，密码分别为A、B、C；

create user A identified by A;
create user B identified by B;
create user C identified by C;

2. 用SYSTEM账户把CREATE SESSION和CREATE TABLE权限授予给用户A，并允许用户A传递获得的权限；
   使用以下的ALTER USER命令修改用户A的默认表空间为users，使用户A能在student中插入数据；

ALTER USER A DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
grant create session,create table to A with admin option;

3. 用户A连接登录数据库，创建关系模式student(sno,sname,sage)(自定义属性的数据类型)，
   用户A向表student插入数据（插入内容自定义），并执行commit提交数据；

create table student(
    sno varchar(20),
    sname varchar(20),
    sage int
);
insert into student(sno,sname,sage) values('001','熊一',18);
insert into student(sno,sname,sage) values('002','熊二',19);
commit;

4. 用户A把CREATE SESSION、对表student的select和Insert权限授予给B，并允许B传递获得的权限
   （CREATE SESSION属于系统权限，对表student的select和Insert权限属于对象权限。
   在Oracle中，系统权限采用的选项是WITH ADMIN OPTION，对象权限采用的选项是WITH GRANT OPTION），
   然后使用用户B登录数据库测试用户B获取的权限（用户B获得student表的权限后，访问student表必须使用A.student）；

grant create session to B with admin option; 
grant select,insert on student to B with grant option;

5. 用户B把CREATE SESSION、对表student的select权限授予给用户C；

grant create session to C;
grant select on A.student to C;

6. 用户C登录数据库，测试获得的权限；

select * from A.student;

7. 用户A把student的属性sname、sage的修改权限授予用户B；

grant update(sname,sage) on student to B;

8. 用户A收回用户B对student表的select权限，测试用户B、C是否仍然具有对student表的select权限；

create role MyRole;
grant select on student to MyRole;
grant create session to MyRole;

9. 用户A回收用户B的CREATE SESSION权限，测试用户B、C是否仍然可以连接登录到数据库；

grant MyRole to B with admin option;
select * from A.student;

10. 由系统管理员授予用户A创建角色的权限；

grant create role to A ;

11. 用户A创建角色MyRole，授予角色MyRole对表Student的select权限以及CREATE SESSION权限；

create role MyRole;
grant select on student to MyRole;
grant create session to MyRole;

12. 用户A把角色MyRole授予给用户B，并允许用户B对角色进行管理，测试用户B获取的权限（授予的角色权限在用户下次登陆才生效）；

grant MyRole to B with admin option;
select * from A.student;

13. 用户B把角色MyRole授予给用户C，测试用户C是否具有对Student表的select权限；

grant MyRole to C;
select * from A.student;

14. 用户A回收用户B的MyRole角色权限，测试B、C拥有的对Student表的select权限是否已经回收（角色权限回收并不影响当前已建立连接的用户，用户在下次登陆才失效）；

revoke MyRole from B;

实验内容第二部分

1. 编写带有一个输入参数和一个输出参数的存储过程，输入参数为教师的编号类型，输出参数与tm表的workdays类型一致，
   存储过程的功能是，根据输入参数教工号的值，计算出该教师为其参与的所有项目的工作总天数；

create or replace procedure findsumworkdays(t_tno varchar2)
as
t_workdays int;
begin
select a1.sumworkdays into t_workdays from(select sum(workdays) 
sumworkdays,tno from tm group by tno)a1 where t_tno like a1.tno; 
exception
when NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('没有找到该教师编号的记录');
end findsumworkdays;

2. 编写代码测试存储过程（若无信息输出，请在代码前面加上set

serveroutput on;）;
set serveroutput on;
begin
findsumworkdays('t001');
commit;
end;

3. 编写一个函数，计算某个教师负责的经费总数；

create or replace function sum_fund(t_tno varchar2)
return int
as
sumfund int;
begin 
select sum(pfund) into sumfund from myproject where t_tno like tno;
return sumfund;
end;

4. 编写代码测试函数；

select tname,sum_fund(tno) from teacher;

5. 编写一个行级前触发器，当插入、修改Teacher表中教师的工资时，如果工资高于8000，则把工资改为8000；

create or replace trigger teacher_salary
before insert or update of tsalary on teacher
for each row 
when(new.tsalary>8000)
begin
:new.tsalary:=8000;
end;

6. 编写代码测试触发器是否工作正常；

update teacher set tsalary=9200 where tno='t001';
select * from teacher where tno='t001';

7. 删除触发器；

drop trigger teacher_salary;

8. 删除存储过程；

drop procedure findsumworkdays;

9. 删除函数；

drop function sum_fund;

实验内容第三部分（本部分需要截图）

1. 用SYSTEM用户创建数据库用户DBLESSON，并授予RESOURCE，CONNECT角色权限；

CREATE USER DBLESSON IDENTIFIED BY DBLESSON;
GRANT RESOURCE,CONNECT TO DBLESSON;

2. 利用附录中SQL语句，建立项目信息管理数据库；

3. 从开始菜单启动CMD；
   执行EXP HELP=Y，查看EXP帮助文档，了解各个参数的作用。

执行IMP HELP=Y，查看IMP帮助文档，了解各个参数的作用。

4. 执行命令，用DBLESSON用户导出DBLESSON的TEACHER、TM、MYPROJECT表；

C:\>EXP USERID=DBLESSON/DBLESSON@ORCL TABLES=(TEACHER,TM,MYPROJECT) FILE=E:\DBLESSON.DMP

注：如果网络服务名配置有误，则采用下面的命令（即网络服务名用“IP地址:端口号/SID”代替），后同。

C:\>EXP USERID=DBLESSON/DBLESSON@127.0.0.1:1521/orcl TABLES=(TEACHER,TM,MYPROJECT) FILE=E:\DBLESSON.DMP

如果Oracle SID为XE，则用以下语句，后同。

C:\>EXP USERID=DBLESSON/DBLESSON@127.0.0.1:1521/XE TABLES=(TEACHER,TM,MYPROJECT) FILE=E:\DBLESSON.DMP

5. 在SQLPLUS环境下，用户DBLESSON删除表格TM；

DROP TABLE TM；

6. 在CMD下，用E:\DBLESSON.DMP还原数据库中的TM表，并查看结果；

IMP USERID=DBLESSON/DBLESSON@ORCL FILE=E:\DBLESSON.DMP TABLES=(TM) IGNORE=Y

注：如果网络服务名配置有误，请参照第4步，后同。

7. 在CMD下，用DBLESSON导出方案DBLESSON；

EXP USERID=DBLESSON/DBLESSON@ORCL OWNER=DBLESSON FILE=E:\SCHEMA.DMP

8. 在SQLPLUS环境下，用户DBLESSON删除DBLESSON方案下的所有表、视图等对象，执行以下IMP命令恢复。查看删除和恢复前后的结果；

C:\>IMP USERID=DBLESSON/DBLESSON FROMUSER=DBLESSON TOUSER=DBLESSON FILE=E:\SCHEMA.DMP

9. 在CMD下，用SYSTEM用户导出整个数据库；

EXP USERID=SYSTEM/ORACLE FILE=E:\DB.DMP FULL=Y

10. 在SQLPLUS环境下，用SYSTEM用户删除DBLESSON用户，重建一个用户DB1并授予RESOURCE、CONNECT角色权限；

CREATE USER DB1 IDENFIFIED BY DB1；GRANT RESOURCE，CONNECT TO DB1；

11. 在CMD下，把导出的数据导入到用户DB1；

IMP USERID=SYSTEM/ORACLE FROMUSER=DBLESSON TOUSER=DB1 FILE=E:\DB.DMP

实验总结
总结实验过程中涉及到的知识点、实验过程中遇到的问题及解决方法。
通过这次实验，对ORACLE数据库系统逻辑备份和恢复的方法有了初步的了解，学习了在CMD中对数据库的一些基本操作；使用EXP导出用户创建的表或者整个方案，在原本的用户内容出现丢失的时候，可以使用IMP使用原本的备份数据回复丢失的数据；不能直接删除正在连接的用户，需要断开连接才能删除。