oracle 创建用户只有查看另一个用户的视图的权限

1、创建用户yyy

-- Create the user 

create user YYY
  identified by password
  default tablespace SYSTEM
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to YYY;
grant dba to YYY;

grant resource to YYY;

2、创建表UIMAGE_DICITEM和表UIMAGE_DICINDEX

-- Create table
create table UIMAGE_DICITEM
(
  DICID   VARCHAR2(10) not null,
  ITEMID  VARCHAR2(10) not null,
  ITEMVAL VARCHAR2(100) not null,
  ITEMDES VARCHAR2(100)
)

-- Create table
create table UIMAGE_DICINDEX
(
  DICID   VARCHAR2(10) not null,
  DICNAME VARCHAR2(20) not null,
  DICDES  VARCHAR2(100)
)

3、创建视图view_person_permission 

create or replace view view_person_permission as
  select p.dicid,p.dicname,c.itemid,c.itemval from uimage_dicindex p,uimage_dicitem c where p.dicid=c.dicid

4、创建用户yyyy

-- Create the user 
create user YYYY
  identified by ""
  default tablespace SYSTEM
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke object privileges 
grant select on VIEW_PERSON_PERMISSION to YYYY;
-- Grant/Revoke role privileges 
grant connect to YYYY;
-- Grant/Revoke system privileges 
grant create synonym to YYYY;

5、yyyy用户登录创建同义词

CREATE SYNONYM  PERSON_PERMISSION FOR  yyy.VIEW_PERSON_PERMISSION ;

6、设置yyyy的session访问数

conn / as sysdba

alter system set resource_limite=true scope=both sid='*';

create profile third_user limit SESSIONS_PER_USER 1 FAILED_LOGIN_ATTEMPTS unlimited;

alter user ryd_interface profile third_user;

将会话数调整到30

alter profile third_user limit SESSIONS_PER_USER 30;