数据库权限控制管理

--注意:通常如果我们需要进行授权,我们需要以System的身份登录数据库,
--进行权限的授予,以及权限的收回.
--但是进行测试的时候,是需要以测试的用户进行登录的


部门（部门号，名称，经理名，地址，电话号）
职工（职工号，姓名，年龄，职务，工资，部门号）  
一、在Hr用户模式下，创建职工和部门表，各插入2条测试数据。
--创建"部门表"基本表
--查询到当前数据库的字符集，
--如果value=ZHS16GBK，那么一个汉字占用2个字节,即一个汉字占用2个字符的大小;
--如果value=AL32UTF8，那么一个汉字占用3个字节,即一个汉字占用3个字符的大小;
select * 
from v$nls_parameters t
where t.PARAMETER='NLS_CHARACTERSET';

--创建"部门表"基本表
--部门（部门号，名称，经理名，地址，电话号）
create table Dept
(
      Deptno char(9) ,
      Deptname varchar2(8),
      Deptmanager varchar2(12),
      Deptaddress varchar2(20),
      Deptphone char(11),
      primary key(Deptno)
);

drop table Dept;
select * 
from Dept;

--创建"员工表"基本表 
--职工（职工号，姓名，年龄，职务，工资，部门号） 
create table Emp
(
      Empno char(9) ,
      Empname varchar2(8),
      Empage char(4),
      Empjob varchar2(10),
      Empsal smallint,
      Empdeptno char(9),
      primary key(Empno),
      foreign key(Empdeptno) references Dept(Deptno)
);

drop table Emp;
select * 
from Emp;

--各插入2条测试数据。
--向部门表中插入两条记录
--部门（部门号，名称，经理名，地址，电话号）
insert into Dept values('201400644','学习部','张三','湖北大学','12345678910');  
insert into Dept values('201400645','卫生部','李四','湖北大学','0987654321');  

--向职工表中插入两条记录
--职工（职工号，姓名，年龄，职务，工资，部门号）
insert into Emp values('201400789','王五',25,'销售人员',1200,'201400644'); 
insert into Emp values('201400790','李明',26,'监督人员',1200,'201400645'); 
select * 
from Emp;
select * 
from Dept;

二、定义用户，用SQL的GRANT和REVOKE语句完成以下授权定义或存取控制功能。
1、创建数据库本地用户test，密码为oracle，默认表空间为users，临时表空间为temp，同时具有create session系统权限。
--创建数据库本地用户test
create user test
--设置密码为oracle
identified by oracle
--设置默认表空间为users
default tablespace  users
--设置表空间为temp
temporary tablespace temp;

--给test用户授予create session的权限
--如果没有给test用户授予create session权限,使用用户和口令进行登录的时候会提示
--user test lacks create session privilege;logon  denied-->提示test用户没有create session的权限
grant create session to test;
--将授予用户test的create session权限收回
revoke create session from test;

2、用户王明对两个表有SELECT权力；
--将两个表的select权限授予给用户王明
--首先创建在管理员登录下,创建一个新用户
create user WangMing
--将用户WangMing用户的密码设置为oracle
identified by oracle;
--然后授予用户WangMing的create session的权限
grant create session to WangMing;
--回收用户WangMing的create session的权限
revoke create session from WangMing;

--授予用户WangMing查询Emp表的权限
--对指定的用户授予指定的数据库对象的指定操作权限时,可以多次连续授予用户改权限
grant select 
on Emp
to WangMing;
--收回用WangMing查询Emp表的权限
--注意在将指定的用户指定数据对象的指定操作权限进行收回的时候,只能回收一次
--如果已经回收的话,再次进行权限的收回,会提示-->无法对你未授权的权限进行收回
revoke select
on Emp
from WangMing;

--对用户WangMing授予查询Dept的权限
grant select
on Dept
to WangMing;

--将给WangMing用户授予的查询Dept的权限收回
revoke select
on Dept
from WangMing;

在WangMing登录之后:进行测试
==================

查询system数据库中创建的基本表Emp的全部信息
--如果在WangMing用户的登录下,是不存在Emp表的
--这时候如果直接执行下面的语句就会出现视图或表不存在
select * 
from Emp;

--因为Emp表我们是在system表中的创建的,这时候我们应该在引用
--Emp表的时候,需要在Emp表前面添加上用户的名字
select * 
from system.Emp;

--如果在用户未被对数据库对象的指定的操作权限的授予的时候使用权限
--这时候数据库管理系统会报错,提示视图或者表不存在
--如果将授予WangMing的查询Dept表的权限收回,再进行查询的时候会提示视图不存在
--这时候说明授予WangMing
select *
from system.Dept;



3、用户李勇对两个表有INSERT和DELETE权力；
--创建用户李勇
create user LiYong
identified by oracle;

--给用户李勇授予创建回话的权限create session
grant create session
to LiYong;

--给用户李勇授予查询基本表Emp的权限
grant select
on Emp
to LiYong;

--将授予给李勇的查询基本表Emp的权限收回
revoke select
on Emp
from LiYong;

--给用户LiYong授予查询基本表Dept的权限
grant select
on Dept
to LiYong;

--将授予给LiYong的查询基本表Dept的权限收回
revoke select
on Dept
from LiYong;

在用户LiYong登录之后进行测试:
------------------


select * from system.Emp;
select * from system.Dept;


4、用户刘星对职工表有SELECT权力，对工资字段具有更新权力；
--创建用户刘星
create user LiuXing
--设置用户刘星的密码
identified by oracle;

--授予用户刘星创建会话的权限
grant create session
to LiuXing;

--回收授予刘星创建回话的权限
revoke create session
from LiuXing;

--授予用户刘星对职工表查询的权限
grant select,update(Empsal)
on Emp
to LiuXing;

--回收授予用户刘星对职工表查询的权限
revoke select
on Emp
from LiuXing;

--回收授予用户刘星对职工表中工资字段更新的权限
--提示只能从整个表而不能按照列进行revoke
revoke update(Empsal)
on Emp
from LiuXing;

使用用户LiuXing登录之后进行测试:
====================

查询职工表中全部信息
select * 
from system.Emp;

--如果将用户的权限收回,将会提示权限不足;
--将职工表中员工工号为201400789的职工的工资更新为3000
update system.Emp
set Empsal=3000
where Empno='201400789';

5、用户周平具有对两个表所有权力（读、插、改、删数据），并具有给其他用户授权的权力；
提示：
--所有权力为ALL PRIVILEGE，
--在GRANT语句中使用WITH GRANT OPTION选项，被授权的用户就具有了再次将对象权限授予其他用户的能力。
--创建用户周平
create user ZhouPing
--将用户周平的密码设置为oracle
identified by oracle

--创建用户时对于同一个用户名的用户只能创建一次
--如果多次重复创建会出现与另外一个用户名或者角色发生冲突的现象
--授予用户周平创建回话的权限
grant create session
to ZhouPing;

--收回授予给用户ZhouPing创建回话的权限
revoke create session
from ZhouPing;

--授予用户ZhouPing对基本表Emp表增,删,改,查的权限,并且具有给其他用户授权的权限
grant insert,delete,update,select
on Emp
to ZhouPing
with grant option;

--授予用户ZhouPing对基本表Emp表增,删,改,查的权限,并且具有给其他用户授权的权限
grant all privileges
on Emp
to ZhouPing
with grant option;

--授予用户ZhouPing对基本表Emp表增,删,改,查的权限,并且具有给其他用户授权的权限
grant all
on Emp
to ZhouPing
with grant option;

--使用ZhouPing登录之后进行测试:
--对基本表Emp的查询
--在进行授权的时候经常会出现,授权之后查询出错,但是一会又查询出来了结果的情况
select * 
from system.Emp;

--对基本表Emp表的插入一条新的记录
--如果直接在ZhouPing用户中使用Emp表需要指明system.Emp,这样才能成功插入
--提示表或者视图不存在;
insert into Emp 
values('201400792','小赵',27,'监督人员',200,'201400645');

--如果之前已经插入了该条记录,再次插入该条记录的时候会提示违反唯一约束条件
insert into system.Emp 
values('201400191','小王',30,'销售人员',3000,'201400644'); 
insert into system.Emp 
values('201400792','小赵',27,'监督人员',200,'201400645');

--对Emp表中职工号为201400792的学生工资进行更新
update system.Emp
set Empsal=2000
where Empno='201400792';

--将姓名为小王的学生的学号更新为201400791;
update system.Emp
set Empno='201400791'
where Empname='小王';

--将学号为201400792的学生信息删除
delete 
from system.Emp
where Empno='201400792';

6、用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。
--首先创建用户YangLan
create user YangLan
--设置用户密码为oracle
identified by oracle;

--授予用户YangLan创建会话的权限
grant create session
to YangLan;

--回收授予用户YangLan创建会话的权限
revoke create session
from YangLan

--首先创建视图查询每个部门职工中的最高工资，最低工资和平均工资，
--然后授予用户查询视图的权限。
create view Sallevel
(MaxSal,MinSal,AvgSal)
as
select max(Empsal),min(Empsal),avg(Empsal)
from system.Emp;

--授予用户YangLan查询视图的权限
grant select 
on Sallevel
to YangLan;

--回收用户YangLan查询视图的权限
revoke select
on Sallevel
from YangLan;

使用YangLan登录之后进行测试:
==================

查询视图的相关信息

select * 
from system.Sallevel;

--该用户不能查看每个人的工资
select *
from system.Emp;

三、设计安全机制使得用户“liming”只能查询年龄在40岁以上（包括）职工。
--首先创建视图查询年龄在40岁以上（包括）职工
--然后授予用户查询视图的权限。

--创建视图查询年龄在40岁以上的职工的所有信息
create view Oldemp
as
select *
from Emp
where Empage>=40;

--创建LiMing用户
create user LiMing
identified by oracle;

--给LiMing用户授予创建回话的权限
grant create session
to LiMing;

--收回授权给LiMing用户的创建回话的权限
revoke create session
from LiMing;

--授予用户LiMing查询视图的权限
grant select 
on Oldemp
to LiMing;

--收回授予LiMing查询视图的权限
revoke select
on Oldemp
from LiMing;

使用LiMing登录之后进行测试:
=================

查询40岁以上年龄的员工的所有信息
select * 
from system.Oldemp;

四、设计安全机制使得用户“liming”只能访问“职工”的职工号、姓名。
提示：用视图实现

--首先创建视图查询“职工”的职工号、姓名
--然后授予用户查询视图的权限。
create view Maininfor
as
select Empno,Empname
from Emp;

--给用户LingMing授予查询视图的权限
grant select
on Maininfor
to LiMing;

--回收授予用户LiMing查询视图的权限
revoke select
on Maininfor
from LiMing;

使用LiMing登录之后进行测试:
=================

查询"职工”的职工号、姓名。
select * 
from system.Maininfor;


五、设计角色“student”，可以查看“职工”的职工号、姓名、年龄。将用户“liming”添加到角色“student”中。
提示：用视图实现
--首先创建视图查询“职工”的职工号、姓名、年龄;
--然后将查询该视图的权限授予给创建的角色
--然后该角色授予用户LiMing

--创建视图查询“职工”的职工号、姓名、年龄;
create view Moreinfor
as
select Empno,Empname,Empage
from Emp;

--创建角色student
create role student;

--给学生student角色授予查询视图的权限
grant select
on Moreinfor
to student;

--将角色授予给用户
grant student
to LiMing;

select * from Moreinfor;

使用LiMing登录之后进行测试:
=================

查看“职工”的职工号、姓名、年龄
select *
from system.Moreinfor;