大型数据库技术实验教案网络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;
四、给用