ORACLE开发知识：Oracle创建只读账号的详细步骤

–创建只读账号 第一步
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;
grant 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;

–在原账号HEPSUSR端执行，获取需要显示的表名称
select ‘create or replace SYNONYM htreader.’||object_name|| ‘ for ‘ ||owner|| ‘.’||object_name||’;’
from dba_objects
where owner in (‘HEPSUSR’)
and 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;

1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

5.查看所有角色：
select * from dba_roles;

6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;