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;
如何连接oracle数据库
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创建只读账号的资料请关注其它相关⽂章！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。