oracle数据库添加只读账号并授权

第一步：创建只读账号

sql
create user app_zijin_reader 
identified by 123456 --初始口令
default tablespace ZIJIN; --初始口令过期，会提示用户重置口令

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

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

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

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

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

js

$ sqlplus / as sysdba > ./app_zijin_reader.sql <<sqleof
select 'grant select on '||owner||'.'||object_name||' to app_zijin_reader;' from dba_objects where owner in ('APP_ZIJIN')  and object_type='TABLE';
sqleof

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

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

js
$ sqlplus / as sysdba > ./app_zijin_reader_synonym.sql <<sqleof
select 'create or replace SYNONYM app_zijin_reader.'||object_name||' for ' ||owner||'.'||object_name||';' from dba_objects where owner in ('APP_ZIJIN')  and object_type='TABLE';
sqleof

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

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

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

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

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

新账号进行删除操作时，会提示报错，无权删除

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

js
附录：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;