需求说明

现有数据库账号：HEPSUSER:具有完整权限，增删改查。需要创建一个数据库账号：HTREADER,对HEPSUSR账号下所有的表具有只读权限。

第一步、创建只读账号

--创建只读账号 第一步
CREATE USER htreader identified by 123456

第二步、赋予账号连接数据库等基本权限

--赋予htreader连接等常规权限
grant connect to htreader;
grant create view to htreader;
grant create session to htreader;
grant create synonym to htreader;

第三步、获取原账号的查询权限

--获取原账号HEPSUSR用户的所有查询表权限
select 'grant select on '|| owner || '.'||object_name||'to htreader;'
from dba_objects
where owner in ('HEPSUSR')
and object_type='TABLE';

查询结果为新账号的赋值语句，如下图:

第四步：将原账号权限赋值为新账号

在原账号HEPSUSR下执行，将原账号的查询权限赋值给新账号

grant select on  HEPSUSR.ENTRY_CERT  to htreader;
grant select on  HEPSUSR.SUB_MESSAGE_INFO  to htreader;
grant select on HEPSUSR.ENTRY_CERT_RELATION to htreader;
rant select on HEPSUSR.ENTRY_CERT_RELATION to htreader;
grant select on HEPSUSR.ENTRY_DECL_TAX to htreader;
grant select on HEPSUSR.ENTRY_DOCU to htreader;
grant select on HEPSUSR.ENTRY_FEES to htreader;
grant select on HEPSUSR.ENTRY_GOODS_TAX to htreader;
grant select on HEPSUSR.ENTRY_HEAD to htreader;
grant select on HEPSUSR.ENTRY_LIST to htreader;
grant select on HEPSUSR.ENTRY_WORKFLOW to htreader;
grant select on HEPSUSR.IQ_APPEND to htreader;
grant select on HEPSUSR.IQ_CERT to htreader;
grant select on HEPSUSR.SUB_SWAP to htreader;
grant select on HEPSUSR.VIN_LIST to htreader;

第五步：在新账号端创建同位显示表

因为新创建的只读账号，Tables栏中显示为空，我们需要在PL/SQL显示栏中为新账号登录界面添加显示同位元素，如下：

--在原账号HEPSUSR端执行，获取需要显示的表名称
select 'create or replace SYNONYM htreader.'|| 
 object_name||' for '||owner|| '.'|| object_name||';' 
 from dba_objects 
 where owner in ('HEPSUSR') a
 nd object_type='TABLE'

第六步：查询结果在新账号端执行

--在只读账号HTREADER端执行：添加显示各个表信息；在SYSNONYM目录下，tables目录下无显示
create or replace SYNONYM htreader.VIN_LIST for HEPSUSR.VIN_LIST;
create or replace SYNONYM htreader.SUB_SWAP for HEPSUSR.SUB_SWAP;
create or replace SYNONYM htreader.SUB_MESSAGE_INFO for HEPSUSR.SUB_MESSAGE_INFO;
create or replace SYNONYM htreader.IQ_CERT for HEPSUSR.IQ_CERT;
create or replace SYNONYM htreader.IQ_APPEND for HEPSUSR.IQ_APPEND;
create or replace SYNONYM htreader.ENTRY_WORKFLOW for HEPSUSR.ENTRY_WORKFLOW;
create or replace SYNONYM htreader.ENTRY_LIST for HEPSUSR.ENTRY_LIST;
create or replace SYNONYM htreader.ENTRY_HEAD for HEPSUSR.ENTRY_HEAD;
create or replace SYNONYM htreader.ENTRY_GOODS_TAX for HEPSUSR.ENTRY_GOODS_TAX;
create or replace SYNONYM htreader.ENTRY_FEES for HEPSUSR.ENTRY_FEES;
create or replace SYNONYM htreader.ENTRY_DOCU for HEPSUSR.ENTRY_DOCU;
create or replace SYNONYM htreader.ENTRY_DECL_TAX for HEPSUSR.ENTRY_DECL_TAX;
create or replace SYNONYM htreader.ENTRY_CONTAINER for HEPSUSR.ENTRY_CONTAINER;
create or replace SYNONYM htreader.ENTRY_CERT_RELATION for HEPSUSR.ENTRY_CERT_RELATION;
create or replace SYNONYM htreader.ENTRY_CERT for HEPSUSR.ENTRY_CERT;

第七步：执行完成之后登录新账号，查看结果

新账号可以查询原账号的所有表结构，但是无法执行增删改相关操作

第八步：执行删除、修改sql语句测试

转载自：https://www.cnblogs.com/yaoyangding/p/15205570.html

Oracle创建只读账户(转载)