PL/SQL 培训项目实践与练习(一) 整理笔记

题目：

控制客户端应用程序对同一组数据库的访问权限。

需求：

业务数据是存储在数据库中一个SCOTT帐户下的一个表EMP。

要求设计一种方法，客户端在登陆到数据库的时候，除了提供帐户名以及口令之外，还要提供一些其他的一些信息。系统可以根据这些信息，决定这个客户端能看到的EMP表中的特定部门工作的职员。

方式：

首先分析这个需求，简化这个需求

利用已经学习到解决如何让一个帐户只看到一个表中一部分行，而其他部分看不到。这个时候要用到视图，多个帐户和表上的授权三个内容。
之后，分析这个解决方法的不足。

然后进一步满足需求，如何让所有用户都登陆到同一个帐户上，然后还能看到SCOTT帐户下EMP表的不同行的数据。这个时候可以利用现有的其他知识，如角色的生效失效等。

接下来，进一步提高要求，要求所有客户端都登陆到同一个帐户上，而且访问同一个视图，但这个视图可以根据客户端登陆时提供的信息，决定当前会话可以查看的数据。

最后再讨论将这个例子放大到全体业务数据的方法，以及应用Oracle提供的其他功能（如VPD，Lavel Security等），进行其他改进的可能。

目的：综合利用Oracle权限管理，帐户管理，视图，临时表，过程/函数的创建等技术，解决访问控制问题。

相关知识理解笔记：

临时表：
create global temporary table table_name(
  username varchar2(10)
  ......
);
客户端访问服务器端时，服务器端会自动给每个客户端分配一个setion,用以区别不用的客户端，道理就象这个样子，临时表的用处就在此，就象它会自动设置不同的客户端的权限一样，比如：
A在主机上建立一个临时表TEMP，B可以访问到这个表，A在TEMP上插入数据a，对于这个数据a，A可以SELECT到，但是B却不能。同样，B也在这个表上插入一个数据b，对于这个数据b，B可以SELECT到，而A却不能.

PS：根据这个知识，我建立一个临时表，输入一些信息，根据这些信息的不同，我所建立的视图就不同，这样就可以完成本题关键部分。

视图
 create view view_name as select * from table_name where ...
视图是建立在表的基础上的，它只是完成表中一些数据的集体的体现.对于表的本身并不做任何修改。比如：
我有两个表：
create table talbe_person(id number,name varchar(20));
insert into table_person values(...,...);
create table table_name (name,varchar(20));
insert into table_name values(...);
现在我想查看table_person中名字符合table_name里的名字的人的相关信息，我可以以视图的方式实现：
create view view_person as select * from table_person where name in(select * from table_name);

具体操作：

现系统已经存在表EMP。（其中有empno字段）

--建立权限数据表，用来存储对每个客户端分配的不同权限
create table test_privileges (
  id       number(4),
  username varchar2(10),
  empno    number(4)
);

--建立自增序列
create sequence seq_test_id start with 1 increment by 1;

--插入数据，每一行都有一个username字段，用来区别不同的客户端..
insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'HR',7369);

insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'HR',7499);

insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'TEST03',7521);

insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'TEST05',7566);
insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'TEST05',7900);
insert into test_privileges (id,username,empno)
values (seq_test_id.nextval,'TEST05',7902);
commit;

--建立临时表，用来存储用户名，用来区别不同的客户端
create global temporary table t_test_info(
  username varchar2(10)
);

--这里是关键，建立视图。
create view v_emp as
  select * from emp
  where empno in
  (select empno
   from test_privileges s,t_test_info d
   where s.username = d.username);

--这样我每输入不同的用户名，之后
insert into t_test_info values('HR');
--所看到的内容也就不同了
select * from v_emp;
insert into t_test_info values('TEST05');
 select * from v_emp;
insert into t_test_info values('TEST03');
select * from v_emp;