實驗：用檢視加同義詞實現資料安全

 

實驗：用檢視加同義詞實現資料安全

需求：test01使用者需要訪問test02使用者的a表中的userid和password列：

1，不使用檢視

conn / as sysdba;
drop user test01 cascade;
drop user test02 cascade;

create user test01 account unlock identified by test01;
grant create session,create synonym to test01;
create user test02 account unlock identified by test02 quota 100m on users;
grant create session,create table to test02;

conn test02/test02
create table a(userid number,password varchar2(20),salary varchar2(20));
insert into a values(1,'abc','3000');
insert into a values(2,'edf','6000');
commit;
select * from a;
grant select on a to test01;

conn test01/test01
create synonym a for test02.a;
select userid,password from a;
select * from a;
結果 select * from a;使用者看到了不該看到的其他列，這對DBA來說是一個重大失誤。

2，使用檢視

conn / as sysdba;
drop user test01 cascade;
drop user test02 cascade;

create user test01 account unlock identified by test01;
grant create session,create synonym to test01;
create user test02 account unlock identified by test02 quota 100m on users;
grant create session,create table,create view to test02;

conn test02/test02
create table a(userid number,password varchar2(20),salary varchar2(20));
insert into a values(1,'abc','3000');
insert into a values(2,'edf','6000');
commit;
select * from a;
create view a_vw as select userid,password from a;
grant select on a_vw to test01;

conn test01/test01
create synonym a for test02.a_vw;
select * from a;
結果：使用者只能看到檢視中指定的兩列，既滿足了需求同時也實現了資料安全性。