仅授予connect、resource两个角色却能访问其它用户的表

文档课题：Oracle中在表上创建公共同义词，且将相关对象权限授予Public的测试.
实际场景：此前客户反应新建的用户只授予connect、resource两个角色，却能访问其它用户的表.以下便是探究此问题的演练过程.
1、建用户及授权
sys@ORCL 2022-10-10 17:41:39> create user leo identified by leo;

User created.

sys@ORCL 2022-10-10 17:42:20> grant connect,resource to leo;

Grant succeeded.

sys@ORCL 2022-10-10 17:43:15> select * from dba_role_privs where grantee='LEO';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LEO                            RESOURCE                       NO  YES
LEO                            CONNECT                        NO  YES

sys@ORCL 2022-10-10 17:44:29> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

sys@ORCL 2022-10-10 17:46:00> select * from dba_tab_privs where grantee='CONNECT';

no rows selected

sys@ORCL 2022-10-10 17:47:10> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.

sys@ORCL 2022-10-10 17:46:00> select * from dba_tab_privs where grantee=' RESOURCE';

no rows selected
2、scott用户
sys@ORCL 2022-10-10 17:47:26> alter user scott account unlock;

User altered.

sys@ORCL 2022-10-10 17:50:27> alter user scott identified by tiger;

User altered.

sys@ORCL 2022-10-10 17:50:42> conn scott/tiger;
Connected.

scott@ORCL 2022-10-10 17:51:37> select table_name from user_tables;

TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT

scott@ORCL 2022-10-10 17:51:51> select count(*) from emp;

  COUNT(*)
----------
        14
3、第一次查询
scott@ORCL 2022-10-10 17:52:02> conn leo/leo;
Connected.
leo@ORCL 2022-10-10 17:53:00> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

4、创建Public Synonym
leo@ORCL 2022-10-10 17:53:10> conn scott/tiger
Connected.
scott@ORCL 2022-10-10 17:53:23> create public synonym emp_syno for emp;
create public synonym emp_syno for emp
*
ERROR at line 1:
ORA-01031: insufficient privileges


scott@ORCL 2022-10-10 17:54:10> conn sys/oracle_4U as sysdba
Connected.
sys@ORCL 2022-10-10 17:54:23> grant create public synonym to scott;

Grant succeeded.

sys@ORCL 2022-10-10 17:54:41> conn scott/tiger
Connected.
scott@ORCL 2022-10-10 17:54:48> create public synonym emp_syno for emp;

Synonym created.

scott@ORCL 2022-10-10 17:55:01> grant select on emp_syno to public;

Grant succeeded.
5、第二次查询
scott@ORCL 2022-10-10 17:55:45> conn leo/leo;
Connected.
leo@ORCL 2022-10-10 17:55:56> select count(*) from emp_syno;

  COUNT(*)
----------
        14
leo@ORCL 2022-10-10 17:56:46> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.
6、其它dml测试
leo@ORCL 2022-10-10 17:56:56> delete from scott.emp where empno=7369;
delete from scott.emp where empno=7369
                  *
ERROR at line 1:
ORA-01031: insufficient privileges


leo@ORCL 2022-10-10 17:58:00> conn scott/tiger;
Connected.
scott@ORCL 2022-10-10 17:58:11> grant delete on emp_syno to public;

Grant succeeded.

scott@ORCL 2022-10-10 17:58:30> conn leo/leo;
Connected.
leo@ORCL 2022-10-10 17:58:33> delete from scott.emp where empno=7369;

1 row deleted.

结论：Oracle中在表上创建公共同义词后且将相关对象权限授予Public，此后其它用户也具有授予的对象权限.