大型数据库技术实验教案网络Word文档格式.docx
《大型数据库技术实验教案网络Word文档格式.docx》由会员分享，可在线阅读，更多相关《大型数据库技术实验教案网络Word文档格式.docx（16页珍藏版）》请在冰豆网上搜索。
15
Oracle安全管理
实验一SQL*PLUS练习（2学时）
【实验目的】
（1）了解Oracle的工作环境和基本使用方法。
（2）练习标准SQL的数据操作，查询命令及其查询优化。
（3）学会使用高级SQL命令，排序、分组、自连接查询等。
（4）学会使用SQL*PLUS命令显示报表，存储到文件等。
【实验内容】
一、准备使用SQL*PLUS
1.进入SQL*PLUS
C:
\SQLPLUS
2.退出SQL*PLUS
SQL>
EXIT
3.显示表结构命令DESCRIBE
SQL>
DESCRIBEemp
使用DESCRIBE（缩写DESC）可以列出指定表的基本结构，包括各字段的字段名以及类型、长度、是否非空等信息。
4.使用SQL*PLUS显示数据库中EMP表的内容
输入下面的查询语句：
SELECT*FROMemp;
按下回车键执行查询
5.执行命令文件
START或@命令将指定文件调入SQL缓冲区中，并执行文件内容。
@文件名（文件后缀缺省为.SQL）或
START文件名
文件中每条SQL语句顺序装入缓冲区并执行。
6.创建用户并分配权限
CREATEUSEREMPidentifiedbyEMP;
GRANTCREATESESSION,RESOURCE,DBATOEMP;
7.用户登录
CONNEMP/EMP;
8.显示当前登录用户
SHOWUSER;
9.查看系统拥有哪些用户
select*fromall_users;
二、数据库命令——有关表、视图等的操作
1.创建表employee
例1定义一个人事信息管理系统中存放职工基本信息的一张表。
可输入如下命令：
CREATETABLEemployee
（empnonumber（6）PRIMARYKEY,/*职工编号
namevarchar2（10）NOTNULL,/*姓名
jobvarchar2（10）NOTNULL,/*职位
deptnonumber
（2）DEFAULT10,/*部门号
salarynumber（7,2）CHECK（salary<
100000）,/*工资
birth_datedate/*出生年月
）;
例2复制一个与emp表的表结构相同的新空表emp2.
createtableemp2as
select*fromemp
where1=2;
在命令的where子句中给出1=2，表示条件不可能成立，因而只能复制表结构，而不能复制任何数据到新表中去。
另外，还可以复制一个表的部分列定义或部分列定义及其数据。
三、实验内容
1、以cs+学号为用户名创建用户，并授予用户创建数据对象的权限。
2、使用新建用户登录，复制emp表，复制表名为emp_学号，然后将emp表中工资低于$2000的职工插入到复制的表中。
3、对复制的emp表插入一行只包含有职工号,职工名,工资与部门号四个数据
项值的记录。
4、在复制的emp表中将雇员ALLEN提升为经理，工资增至＄2500，
奖（佣）金增加40%。
5、删除复制的emp表中工资低于500的记录行。
6、列出10号部门中既不是经理，也不是秘书的职工的所有信息。
实验二Oracle数据库开发环境下PL/SQL编程（2学时）
（1）掌握PL/SQL的基本使用方法。
（2）在SQL*PLUS环境下运行PL/SQL的简单程序。
（3）应用PL/SQL解决实际问题
【实验内容与步骤】
PL/SQL块中的可执行部分是由一系列语句组成的（包括对数据库进行操作的SQL语句，PL/SQL语言的各种流程控制语句等）。
在块中对数据库查询，增、删、改等对数据的操作是由SQL命令完成的。
在PL／SQL块中，可以使用SQL的数据查询命令，数据操纵命令和事务控制命令。
可使用全部SQL函数。
PL／SQL中的SQL语句，可使用SQL的比较操作等运算符。
但不能使用数据定义语句。
在PL／SQL块中使用SELECT语句时注意几点：
（1）SELECT语句必须含有INTO子句。
（2）INTO子句后的变量个数和位置及数据类型必须和SELECT命令后的字段名表相同。
（3）INTO子句后可以是简单类型变量或组合类型变量。
（4）SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式，但变量名不要同数据库表列名相同。
（5）在未使用显式游标的情况下，使用SELECT语句必须保证只有一条记录返回，否则会产生异常情况。
实验内容：
1、用PL/SQL实现：
输入eno的值，显示emp表中对应记录的内容。
2、用PL/SQL完成：
读入三个数，计算并输出它们的平均值及三个数的乘积。
3、对职工表emp中的雇员SCOTT提高奖金，若工种为MANAGER,则奖金提高其原来的20%;
若工种为SALESMAN,则奖金提高其原来的15%;
若工种为ANALYST,则奖金提高其原来的10%，其它都按原来的7%提高。
4、用PL/SQL块实现下列操作
公司为每个职工增加奖金：
若职工属于30号部门，则增加＄150；
若职工属于20号部门，则增加＄250；
若职工属于10号部门，则增加＄350。
实验三PL/SQL触发器（2学时）
（1）了解触发器的类型。
（2）掌握PL/SQL触发器的使用方法。
触发器注意事项
（1）触发器可以声明为在对记录进行操作之前，在之前（检查约束之前和INSERT，UPDATE或DELETE执行前）或之后（在检查约束之后和完成INSERT，UPDATE或DELETE操作）触发.。
（2）一个FOREACHROW执行指定操作的触发器为操作修改的每一行都调用一次。
（3）SELECT并不更改任何行，因此不能创建SELECT触发器。
这种场合下规则和视图更适合。
（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。
（5）在一个表上的每一个动作只能有一个触发器与之关联。
（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。
1.实例讲解Oracle数据库自带的几个触发器
Oracle数据库自带的几个触发器（最简单触发器格式）示例如下：
--
createorreplacetriggerMDSYS.sdo_drop_user
afterdroponDATABASE
declare
stmtvarchar2（200）;
BEGIN
ifdictionary_obj_type='
USER'
THEN
stmt:
='
DELETEFROMSDO_GEOM_METADATA_TABLE'
||
'
WHERESDO_OWNER='
'
||dictionary_obj_name||'
;
EXECUTEIMMEDIATEstmt;
endif;
end;
---
createorreplacetriggerSYS.aurora$server$startupafterstartupondatabase
calldbms_java.server_startup
--
createorreplacetriggerSYS.JIS$ROLE_TRIGGER$afterdropondatabase
when（ora_dict_obj_type='
ROLE'
）
begin
sns_context.role_dropped（ora_dict_obj_name）;
http_security_cascade.principal_dropped（ora_dict_obj_name）;
end;
--删除前备份数据的器
CreateOrReplaceTriggerYSPJ.T_Bill_reMain_Del
BeforedeleteOnbill_remain
FOREACHROW
Begin
InsertintoBILL_REMAIN_TIGER
Values（:
old.BILL_REMAINID,:
old.BILL_TYPEID,:
old.REMAIN_NUM,:
old.ADD_TIME,:
old.ORG_ID,:
old.STATE,:
old.BILL_ID,'
删除记录'
Sysdate,user）;
End;
二、实验内容：
1、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。
（要求：
emp表、dept表均为复制后的表）
2、创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表。
--创建测试表
CREATETABLEtest
（
t_id
NUMBER（4）,
t_nameVARCHAR2（20）,
t_ageNUMBER
（2）,
t_sexCHAR
--创建记录测试表
CREATETABLEtest_log
l_user
VARCHAR2（15）,
l_type
l_date
VARCHAR2（30）
--创建触发器
CREATEORREPLACETRIGGERtest_trigger
AFTERDELETEORINSERTORUPDATEONtest
DECLARE
v_typetest_log.l_type%TYPE;
IFINSERTINGTHEN--INSERT触发
v_type:
INSERT'
DBMS_OUTPUT.PUT_LINE（'
记录已经成功插入，并已记录到日志'
ELSIFUPDATINGTHEN--UPDATE触发
UPDATE'
记录已经成功更新，并已记录到日志'
ELSIFDELETINGTHEN
DELETE'
记录已经成功删除，并已记录到日志'
ENDIF;
INSERTINTOtest_logVALUES（user,v_type,
TO_CHAR（sysdate,'
yyyy-mm-ddhh24:
mi:
ss'
））;
END;
/
--下面分别执行DML语句
INSERTINTOtestVALUES（101,'
zhao'
22,'
M'
UPDATEtestSETt_age=30WHEREt_id=101;
DELETEtestWHEREt_id=101;
--然后查看效果
SELECT*FROMtest;
SELECT*FROMtest_log;
3、创建触发器，它将映射emp表中每个部门的总人数和总工资。
--创建映射表
CREATETABLEdept_sal
AS
SELECTdeptno,COUNT（empno）AStotal_emp,SUM（sal）AStotal_salFROMempGROUPBYdeptno;
DESCdept_sal;
CREATEORREPLACETRIGGERemp_info
AFTERINSERTORUPDATEORDELETEONemp
CURSORcur_empIS
DELETEdept_sal;
--触发时首先删除映射表信息
FORv_empINcur_empLOOP
--DBMS_OUTPUT.PUT_LINE（v_emp.deptno||v_emp.total_emp||v_emp.total_sal）;
--插入数据
INSERTINTOdept_sal
VALUES（v_emp.deptno,v_emp.total_emp,v_emp.total_sal）;
ENDLOOP;
--对emp表进行DML操作
INSERTINTOemp（empno,deptno,sal）VALUES（'
123'
'
10'
10000）;
SELECT*FROMdept_sal;
DELETEEMPWHEREempno=123;
4、创建触发器，它记录表的删除数据
--创建表
id
VARCHAR2（4）NOTNULL,
nameVARCHAR2（15）NOTNULL,
ageNUMBER
（2）
NOTNULL,
sexCHAR
NOTNULL
DESCemployee;
--插入数据
INSERTINTOemployeeVALUES（'
e101'
23,'
e102'
jian'
21,'
F'
--创建记录表
CREATETABLEold_employeeAS
SELECT*FROMemployee;
DESCold_employee;
CREATEORREPLACETRIGGERtig_old_emp
AFTERDELETEONemployee--
FOREACHROW--语句级触发，即每一行触发一次
INSERTINTOold_employee
VALUES（:
old.id,:
old.name,:
old.age,:
old.sex）;
--:
old代表旧值
--进行测试
DELETEemployee;
SELECT*FROMold_employee;
5、创建触发器，比较emp表中更新的工资。
CREATEORREPLACETRIGGERsal_emp
BEFOREUPDATEONemp
IF:
OLD.sal>
:
NEW.salTHEN
工资减少'
ELSIF:
OLD.sal<
工资增加'
ELSE
工资未作任何变动'
ENDIF;
更新前工资：
||:
OLD.sal）;
更新后工资：
NEW.sal）;
--执行UPDATE查看效果
UPDATEempSETsal=3000WHEREempno='
7788'
6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。
Createtablefoo（anumber）;
Createtriggerbiud_foo
Beforeinsertorupdateordelete
Onfoo
Ifusernotin（‘DONNY’）then
Raise_application_error（-20001,‘Youdon’thaveaccesstomodifythistable.’）;
Endif;
即使SYS，SYSTEM用户也不能修改foo表。
实验四PL/SQL存储过程（2学时）
（1）了解存储过程的使用方法。
（2）掌握存储过程的使用方法。
1、写存储过程，显示所指定雇员名所在的部门名和位置。
CREATEORREPLACEPROCEDUREDeptMesg（penameemp.ename%TYPE,
pdnameOUTdept.dname%TYPE,plocOUTdept.loc%TYPE）AS
SELECTdname,locINTOpdname,ploc
FROMemp,dept
WHEREemp.deptno=dept.deptnoANDemp.ename=pename;
VARIABLEvdnameVARCHAR2（14）;
VARIABLEvlocVARCHAR2（13）;
EXECUTEDeptMesg（'
SMITH'
:
vdname，:
vloc）;
PRINTvdnamevloc;
2、定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；
否则若工种为MANAGER的，则工资加＄1000；
工种为SALESMAN，工资加＄500；
工种为ANALYST，工资加＄200，否则工资加＄100。
createorreplaceprocedurexggz（namevarchar2）is
k_jobemp.job%type;
addsalemp.sal%type;
selectjobintok_jobfromempwhereename=name;
ifk_job=’MANAGER’then
addsal:
=1000;
elsifk_job=’SALESMAN’then
addsal:
=500;
elsifk_job=’ANALYST’then
=200;
else
=100;
endif;
updateempsetsal=sal+addsalwhereename=name;
exception
whenno_data_foundthen
dbms_output.put_line（‘查无此人”）;
3、通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。
但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。
并统计显示各部门人数及工资调整后的总和。
实验五Oracle安全管理（2学时）
（1）了解Oracle的安全保障机制。
（2）掌握Oracle权限、角色的概念。
（3）学会创建用户、给用户授予权限或角色。
一、建立用户
用户是定义在数据库中的一个名称，它是Oracle数据库的基本访问机制。
当于数据库连接时，必须提供有效的用户名及口令。
建立数据库用户test。
CREATEUSERtestIDENTIFIEDBYtest
DEFAULTTABLESPACEusers
TEMPORARYTABLESPACEtemp
QUOTA3MONusers;
其中：
IDENTIFIEDBY用于指定用户口令。
DEFAULTTABLESPACE用于指定用户的默认表空间。
建立数据库对象（表、索引和簇）时，如果不指定TABLESPACE子句，Oracle会自动在默认表空间上为这些对象分配空间。
TEMPORARYTABLESPACE用于指定用户的临时表空间；
当用户执行排序操作时，若临时数据尺寸超过PGA工作区，即在该表空间上建立临时段。
QUOTA用于指定表空间的配额，即用户对象在表空间上可占用的最大空间。
需要注意以下事项：
⏹如果建立用户时，没有为表空间指定QUOTA子句，那么用户在特定的表空间上
的配额为0，用户将不能在相应的表空间上建立数据对象。
⏹初始建立的数据库用户没有任何权限，不能执行任何数据库操作。
为了使用户可以连接到数据库，必须授予其CREATESESSION权限。
二、给用户授予权限、角色
权限是指执行特定类型SQL命令的权利。
它用于控制用户可以执行的一个或一组数据库操作。
GRANTCREATESESSION,CREATETABLEtotest;
角色是相关权限的命名集合，使用角色可以简化权限管理。
CONNECT,RESOURCE角色是建立数据库时，Oracle执行脚本SQL.BSQ自动建立的角色。
该角色具有应用开发人员所需要的许多权限。
GRANTCONNECT,RESOURCEtotestWITHADMINOPTION;
在授予系统权限时带有WITHADMINOPTION选项，被授权的用户、角色还可以将相应系统权限授予其他用户、角色。
三、修改用户口令：
ALTERUSERtestIDENTIFIEDBYtest1;
CONNtest/test1@orcl_server2;
四、给用
升级会员 