模式对象和用户权限管理
一.实验目标
1. 掌握Oracle常用对象的操作方法。会使用常用对象解决一些实际问题。
2. 掌握Oracle的安全管理方法。
二.实验项目
1.索引,写出相应SQL命令。
创建索引
(1) 创建student1表,并为student1表建立主键。(“student1”的结构与“a_db.学生”表一样)
create table student1
(学号 varchar2(4)not null,
姓名 varchar2(4 char) not null,
性别 varchar2(1 char) not null,
专业 varchar2(10 char not null,
注册日期 date,
状态 varchar2(8),
Primary key(学号));
(2) 为student1表的“姓名”创建索引,索引名为“obj1_1”。
create index obj1_1 on student1(姓名);
(3) 为student1表的“注册日期”创建索引,并以降序排列,索引名为“obj1_2”。
create index obj1_2 on student1(注册日期 desc);
(4) 为student1表的“性别”创建位图索引,索引名为“student_gender_index”。
Create bitmap index student_gender_index on student1(性别);
(5) 为student1表的“性别”与“姓名”创建组合索引,索引名为“obj1_3”。
create index obj1_3 on student1(性别,姓名);
查看索引
利用数据字典视图USER_INDEXES、USER_IND_COLUMNS查看索引信息。
select * from USER_INDEXES where table_name='student1';
select * from USER_IND_COLUMNS where table_name='student1';
删除索引
删除索引“student_ gender _index”。
drop index student_gender_index;
2.同义词,写出相应SQL命令。
创建同义词
(1) 创建student1表的同义词,名为“obj1_4”。
Create synonym obj1_4 from student;
(2) 创建obj1_3的同义词,名为“obj1_5”。
Create synonym obj1_5 from obj1_3;
(3) 创建同义词obj1_5的同义词,名为“st1”。
craete st1 for obj1_5
(4) 创建某Oracle对象的公共同义词,公共同义词的命名规则:字符串“obj1_”后面跟上你的学号。(Oracle对象任意,在自己的计算机上完成)
create public synonym obj1_201705961429 for scott.emp;
查看同义词
利用数据字典视图user_synonyms、all_synonyms查看同义词信息。
select synonym_name,table_name,table_owner from user_synonyms;
删除同义词
删除同义词st1。
drop synonym st1;
3.序列,写出相应SQL命令。
创建序列
(1) 创建序列,该序列起始值50,步长为10,不缓冲,序列名为“obj1_6”。
create sequence obj1_6 start with 50 increment by 10 nocache;
(2) 创建序列,该序列最大值无限制,最小值为1,步长为1,序列名为“obj1_7”。
create sequence obj1_7 start with 1 nomaxvalue increment by 1;
(3) 创建序列,该序列起始值为1000,步长为2,最大值为10000,不可循环,序列名为“seq_1”。
create sequence seq_1 start with 1000 increment by 2 maxvalue 10000 nocycle;
查看序列
利用数据字典视图user_SEQUENCES、all_SEQUENCES查看序列信息。
select * from all_SEQUENCES where sequence_owner='A201705961429';
select * from user_SEQUENCES;
引用序列
向DEPT表中插入三条记录,利用序列obj1_6生成部门编号。
insert into dept values(obj1_6.nextval,null,null,null,null,null);
insert into dept values(obj1_6.nextval,null,null,null,null,null);
insert into dept values(obj1_6.nextval,null,null,null,null,null);
修改序列
(1) 修改序列“obj1_6”,将该序列最大值设为“82000”,最小值设为“10”,步长设为“5”。
alter sequence obj1_6
maxvalue 82000
minvalue 10
increment by 5;
(2) 修改序列“obj1_7”,将该序列最大值设为“1000”。
alter sequence obj1_7
maxvalue 1000;
删除序列
删除序列seq_1。
drop sequence seq_1;
4.用户,写出相应SQL命令(在自己的计算机上完成)。
创建用户
(1) 创建一个用户。注意:如果用户名的命名违背了公俗良序原则,将给予某些处罚。
create user a
identified by 123456;
(2) 该用户授予一定的权限
grant connect, resource to a;
grant create session to a;
授予用户权限
新开一个会话,以新创建的用户身份连接并对scott中的emp表进行查询、删除操作。如果权限不够,则授予相应权限。最后,回滚事务。
grant select,delete on scott.emp to a;
select * from scott.emp where empno=7369;
delete from scott.emp where empno=7369;
select * from scott.emp where empno=7369;
rollback;
select * from scott.emp where empno=7369;
修改用户信息
(1) 修改该用户的口令,然后用新口令重新连接。
alter user a identified by 123;
(2) 封锁该用户的账号,然后解锁。
alter user a account lock;
alter user a account unlock;
查询用户与权限信息
(1) 利用数据字典视图dba_users、user_sys_privs、user_tab_privs、user_role_privs、session_privs查询用户与权限信息。
select * from dba_users;
select * from user_sys_privs;
select * from user_tab_privs;
select * from user_role_privs;
select * from session_privs;
删除用户
drop user a201705961429;
5.视图,针对a_db模式中的表:学生、课程、成绩、emp等,创建视图,视图名依次命名为obj1_10、obj1_11、… 、obj1_14
· 查询每门课程考试成绩第一名(可以并列)的学生的学号与课程号,按课程号升序排列。
create view obj1_10 as
select 学号,rank() over(partition by 课程号
order by 分数 asc nulls last)
名次 from a_db.成绩 where 课程号='C13';
· 查询每门课程考试成绩第一名(可以并列)的学生的学号与课程号,按课程号升序排列。
create view obj1_11 as
select 学号,课程号 from (select 学号,课程号,rank()
over(partition by 课程号 order by 分数 asc nulls last名次
from a_db.成绩 )where 名次=1;
select 学号,课程号 from ( select 学号,课程号,rank()
over(partition by 课程号 order by 分数 asc nulls last名次
from a_db.成绩 )where 名次=1;
· 按平均分数对课程连续排名(可以并列),显示名次、课程号与平均分数(保留2位小数),按名次、课程号升序排列。
create view obj1_12 as
select rank()
over(order by avg(分数) desc) 名次,课程号,round(avg(分数),2)as 平均分数
from a_db.成绩 group by 课程号 order by 名次,课程号;
select rank()
over(order by avg(分数) desc) 名次,课程号,round(avg(分数),2)as 平均分数
from a_db.成绩 group by 课程号 order by 名次,课程号;
· 查询每门课程的课程号,以及每门课程考试成绩第一名(可以并列)的学生的姓名,按课程号与姓名升序排列。
create view v100 as
select distinct 课程号,姓名
FROM (select 成绩.*,rank()over(PARTITION by 课程号
order by 分数 DESC NULLS LAST)RK
from a_db.成绩)R left join a_db.学生 on R.学号=学生.学号 where rk=1
order by 课程号,姓名 asc;
select distinct 课程号,姓名
FROM (select 成绩.*,rank()over(PARTITION by 课程号
order by 分数 DESC NULLS LAST)RK
from a_db.成绩)R left join a_db.学生 on R.学号=学生.学号 where rk=1
order by 课程号,姓名 asc;
· 列出工资在3500到5000之间的员工的姓名,但只取姓名的前5个字符,不足5个则以*补足,按姓名升序排列。
create view obj1_14 as
select rpad(ename,5,'*')as 姓名
from a_db.emp where sal between 3500 and 5000
order by ename asc;
select rpad(ename,5,'*')as 姓名
from a_db.emp where sal between 3500 and 5000
order by ename asc;
三.实验错误解决方案
问题一: ORA-01045问题
1、问题的出现
在实验项目4(a)中创建“创建用户”,SQL语句如下:
create user a identified by 123456;
出现错误:
ORA-01045:user A lacks CREATE SESSION privilege;
Logon denied;
2、问题分析
-
掌握Oracle创建用户的操作方法。会使用常用对象解决一些实际问题。
-
掌握Oracle的安全管理方法。
错误出现在第1行,表明新建的“用户”的权限不够。新建用户至少需要会话的权利,否则连接也不成功;用户在会话的权利上,应该有其他操作的权利。但在第1行中没有给新建用户会话的权限,所以出错。
3、解决方案
解决方案如下:
grant connect, resource to a;
grant create session to a;
执行该语句后,用户已连接,问题解决。
问题二: ORA-00933问题
1、问题的出现
在实验项目5(3)中创建“obj1_11”表,SQL语句如下:
create table 专业
(ID varchar(5),
course_id varchar(8),
foreign key (ID) references)
;
select 成绩.课程号,姓名 from a_db.学生,a_db.成绩(
select 课程号,max(分数)m
from a_db.成绩 group by 课程号)f0
Where 学生.学号=成绩.学号 and 成绩.课程号=f0.课程号
and f0.m=成绩.分数 order by 成绩.课程号,姓名asc;
出现错误:
ORA-00933: SQL命令未正确结束
2、问题分析
-
掌握Oracle常用对象的操作方法。会使用常用对象解决一些实际问题。
-
掌握Oracle的安全管理方法。
错误出现在第2行,表明外码定义错误中的括号没有用英文状态下的括号。sql编程都需要在英文状态下编程,出现中文字符则会被误以为是符号。但在第2行中采用中文括号且selecte语句应用不正确不符合题意,所以出错。
3、解决方案
解决方案如下:
create view obj1_11 as
select 学号,课程号 from (select 学号,课程号,rank()
over(partition by 课程号 order by 分数 asc nulls last名次
from a_db.成绩 )where 名次=1;
select 学号,课程号 from ( select 学号,课程号,rank()
over(partition by 课程号 order by 分数 asc nulls last名次
from a_db.成绩 )where 名次=1;
执行该语句后,视图已创建,问题解决。