1.登录oracle数据库服务器，以管理员用户登录
sqlplus / as sysdba
切换容器等操作
show pdbs;
alter session set container = ORA19CPDB;
show con_name;
2.创建只读用户
create user cmsreadonly identified by cmsreadonly default tablespace CMSPROD_DATA;
3.授权，基本权限
grant connect to cmsreadonly ; --连接权限
grant CREATE SESSION to cmsreadonly ; --创建会话权限
grant CREATE SYNONYM to cmsreadonly ; --创建同义词权限
grant select any table to cmsreadonly ; --可以查询任何表
--revoke SELECT ANY TABLE from cmsreadonly ; --回收权限
还有一种授权方式：授予某个用户的某个表的 select/insert/update 权限
将用户userA的表的查询权限授权给userB
grant select on userA.t_bd_customer to userB;
grant insert on userA.t_bd_customer to userB;
grant update on userA.t_bd_customer to userB;
查看某个用户拥有哪个表的哪些权限：
记住这个表：all_tab_privs
select * from all_tab_privs where GRANTEE='BEDCPROD';
4.创建同义词，不创建就不能直接通过单独的名词来查询
CREATE SYNONYM bedc_bank FOR bedcprod.bedc_bank;
附：批量创建同义词的脚
select 'create or replace synonym 目的地用户.'|| table_name ||' for 源用户.'|| table_name ||';' from all_tables where owner = '源用户';--select 'create or replace synonym '||object_name||' for '||owner||'.'||object_name||';' from dba_objects where owner in ('USERA') and object_type='TABLE';
create or replace synonym cmsreadonly.PORTAL_USER_PROFILE for cmsprod.PORTAL_USER_PROFILE;
复制运行的sql，执行
给表同义词赋权，操作方式同上
select 'grant select, insert, update, delete on 源用户.'|| table_name ||' to 目的地用户;' from all_tables where owner = '源用户';