Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0
CREATE TABLESPACE A_DATA DATAFILE '+DATA' SIZE 30G UNIFORM SIZE 1M;CREATE USER A  IDENTIFIED BY oracle DEFAULT TABLESPACE A_DATA  QUOTA unlimited ON A_DATATEMPORARY TABLESPACE temp;
--授权GRANT "CONNECT" TO "A";GRANT "RESOURCE" TO "A";GRANT "PLUSTRACE" TO "A";GRANT DEBUG CONNECT SESSION TO "A";GRANT CREATE SEQUENCE TO "A";GRANT CREATE VIEW TO "A";GRANT CREATE SYNONYM TO "A";

create user B identified by oracle;create role R_SELECT_A;grant connect,R_SELECT_A to B;
--然后我们登录 A 用户，将 A 用户下的表对象只读权限给与角色 R_SELECT_A，--将执行结果再次执行一遍。
SQL> select 'grant select on '||table_name||' to r_select_A;' from user_tables where rownum<=3;
'GRANTSELECTON'||TABLE_NAME||'TOR_SELECT_A;'---------------------------------------------------------------------grant select on DEPT to r_select_A;grant select on EMP to r_select_A;grant select on BONUS to r_select_A;

create T_NEWTABLE(id int,name varchar2(20));grant select on T_NEWTABLE to R_SELECT_A；

vim grantToRole.sh
#!/bin/bash
if [ $# != 1 ] ; then        echo "USAGE: $0 user_a|user_b|user_c|all"        exit 1;fi
task=$1source /home/oracle/.bash_profile
#Write Logfiletime=`date +%Y%m%d%H%M%S`basedir=/home/oracle/tmplogfile=/home/oracle/tmp/grantToRole_${time}.log
#Write and Execute SQL ScriptsgrantToRole(){echo "==============Start to execute batch sql at `date`================" >> ${logfile}username=$1password=$2sqlplus -S /nolog >> ${logfile} << EOFconn ${username}/${password}spool ${basedir}/grantToRole_${username}_${time}.sqlset head offset feedback offset timing offset verify offset pagesize 0set linesize 200select 'grant select on '||table_name||' to r_select_${username};' from user_tables;select 'grant select on '||sequence_name||' to r_select_${username};' from user_sequences;select 'grant insert,delete,update on '||table_name||' to r_update_${username};' from user_tables;spool off@${basedir}/grantToRole_${username}_${time}.sqlEOFecho "==============End up of executing batch sql at `date`===============" >> ${logfile}}
#############################################################             Main                                         #############################################################
case "${task}" in
"user_a" )grantToRole user_a passwd_oracle;;
"user_b" )grantToRole user_b passwd_oracle;;
"user_c" )grantToRole user_c passwd_oracle;;
"all" )grantToRole user_a passwd_oraclegrantToRole user_b passwd_oraclegrantToRole user_c passwd_oracle;;esac
-- rm -f ${basedir}/grantToRole_*.sql-- crontab-- 0 14 * * * /home/oracle/tmp/grantToRole.sh all

grant select any table on schema USER_A to USER_B;

cd $ORACLE_HOME/network/admin
[oracle@jiekexu admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora# Generated by Oracle configuration tools.
FREE =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = FREE)    )  )
LISTENER_FREE =  (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))
--写入如下信息[oracle@jiekexu admin]$ vim  tnsnames.oraFREEPDB1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = FREEPDB1)    )  )

conn jiekexu/Oracle_21c@FREEPDB1create table test(id int,name varchar2(20));insert into test values(1,'jiekexu'),(2,'freepdb');  --注意：如上的新特性，在 values 后面可以跟多个值了，以前 21c 之前只能跟一个。commit;

SQL> alter session set container=FREEPDB1;
Session altered.
SQL> grant select any table on schema jiekexu to jiekexu_sel;
Grant succeeded.
SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1Connected.SQL> select * from jiekexu.test;
        ID NAME---------- --------------------         1 jiekexu         2 freepdb

SQL> create table t_new(id number);SQL> insert into t_new values(1),(2),(3),(4);4 rows created.SQL> commit;
--切到查询用户，则可以查询到新建的表 t_new 表的数据。SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1Connected.SQL> select * from jiekexu.test;
        ID NAME---------- --------------------         1 jiekexu         2 freepdb
SQL> select * from jiekexu.t_new;
        ID----------         1         2         3         4
SQL> show user;USER is "JIEKEXU_SEL"SQL> col OWNER for a15SQL> col TABLE_NAME for a10SQL> select owner,table_name from all_tables where owner not in ('SYS','MDSYS','SYSTEM','XDB','CTXSYS');
OWNER           TABLE_NAME--------------- ----------JIEKEXU         TESTJIEKEXU         T_NEW

SQL> select *  from dba_role_privs where grantee='JIEKEXU_SEL';
no rows selected
SQL> select  *  from dba_sys_privs where  grantee='JIEKEXU_SEL';
GRANTEE     PRIVILEGE                                ADM COM INH----------- ---------------------------------------- --- --- ---JIEKEXU_SEL CREATE SESSION                           NO  NO  NO
SQL> conn / as sysdbaSQL> set long 99999 pages 9999 LONGCHUNKSIZE 99999SQL> select dbms_metadata.get_ddl('VIEW','ALL_TABLES','SYS') from dual;

$ sqlplus / as sysdba 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 31 16:13:59 2023Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0
SQL> create user READONLY identified by READONLY;SQL> grant connect,R_SELECT_PROD to READONLY;
SQL> create or replace trigger tri_default_schema_prodafter logon on READONLY.schemabeginexecute immediate 'alter session set current_schema=PROD';end;/
--登录只读用户查询业务用户下的表数据CONN READONLY/READONLYSELECT COUNT(*) FROM PROD.TEST;SELECT COUNT(*) FROM TEST;       --均是访问 PROD.TEST 的表