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;