Oracle创建只读账号的详细步骤

目录

• 需求说明
• 第一步：创建只读账号
• 第二步：赋予账号连接数据库等基本权限
• 第三步：获取原账号的查询权限
• 第四步：将原账号权限赋值为新账号
• 第五步：在新账号端创建同位显示表
• 第六步：查询结果在新账号端执行
• 第七步：执行完成之后 登录新账号，查看结果
• 第八步：执行删除、修改sql语句测试
• 附录：oracle查询账号及权限详细语句

需求说明

现有数据库账号：hepsusr：具有完整权限，增删改查。

需要创建一个数据库账号：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;
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;

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

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

--在原账号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;

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

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

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

附录：oracle查询账号及权限详细语句

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;

以上就是oracle创建只读账号的详细步骤的详细内容，更多关于oracle创建只读账号的资料请关注www.887551.com其它相关文章！