Oracle DB 18c：如何撤销用户访问其他用户模式的权限？

我使用以下脚本在Oracle DB 18c（版本18.0.0.0.0）中创建数据库用户：

CREATE USER username IDENTIFIED BY username;
-- ROLES
GRANT "CONNECT" TO username WITH ADMIN OPTION;
ALTER USER username DEFAULT ROLE "CONNECT";
-- SYSTEM PRIVILEGES
GRANT DROP ANY TRIGGER TO username WITH ADMIN OPTION;
GRANT DROP ANY SEQUENCE TO username WITH ADMIN OPTION;
GRANT CREATE TRIGGER TO username WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO username WITH ADMIN OPTION;
GRANT ADMINISTER DATABASE TRIGGER TO username WITH ADMIN OPTION;
GRANT CREATE ANY PROCEDURE TO username WITH ADMIN OPTION;
GRANT EXECUTE ANY INDEXTYPE TO username WITH ADMIN OPTION;
GRANT DEBUG CONNECT SESSION TO username WITH ADMIN OPTION;
GRANT ALTER SESSION TO username WITH ADMIN OPTION;
GRANT CREATE MATERIALIZED VIEW TO username WITH ADMIN OPTION;
GRANT MERGE ANY VIEW TO username WITH ADMIN OPTION;
GRANT CREATE ANY INDEX TO username WITH ADMIN OPTION;
GRANT ALTER ANY MATERIALIZED VIEW TO username WITH ADMIN OPTION;
GRANT CREATE INDEXTYPE TO username WITH ADMIN OPTION;
GRANT DEBUG ANY PROCEDURE TO username WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO username WITH ADMIN OPTION;
GRANT CREATE VIEW TO username WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO username WITH ADMIN OPTION;
GRANT CREATE SESSION TO username WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO username WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO username WITH ADMIN OPTION;
GRANT ALTER ANY SEQUENCE TO username WITH ADMIN OPTION;
GRANT DROP ANY MATERIALIZED VIEW TO username WITH ADMIN OPTION;
GRANT CREATE TABLE TO username WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO username WITH ADMIN OPTION;
GRANT SELECT ANY SEQUENCE TO username WITH ADMIN OPTION;
GRANT MANAGE TABLESPACE TO username WITH ADMIN OPTION;
GRANT CREATE TYPE TO username WITH ADMIN OPTION;
GRANT CREATE TABLESPACE TO username WITH ADMIN OPTION;
GRANT SELECT ANY TRANSACTION TO username WITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO username WITH ADMIN OPTION;
GRANT DROP ANY TYPE TO username WITH ADMIN OPTION;
GRANT QUERY REWRITE TO username WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO username WITH ADMIN OPTION;
GRANT CREATE SYNONYM TO username WITH ADMIN OPTION;
GRANT DEBUG CONNECT ANY TO username WITH ADMIN OPTION;
GRANT CREATE SEQUENCE TO username WITH ADMIN OPTION;
GRANT CREATE ANY INDEXTYPE TO username WITH ADMIN OPTION;
GRANT DROP ANY INDEX TO username WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO username WITH ADMIN OPTION;
GRANT DROP ANY VIEW TO username WITH ADMIN OPTION;
GRANT ALTER TABLESPACE TO username WITH ADMIN OPTION;
GRANT ALTER ANY TRIGGER TO username WITH ADMIN OPTION;
GRANT CREATE ANY VIEW TO username WITH ADMIN OPTION;
GRANT IMPORT FULL DATABASE TO username WITH ADMIN OPTION;
GRANT READ ANY TABLE TO username WITH ADMIN OPTION;
GRANT INSERT ANY TABLE TO username WITH ADMIN OPTION;
GRANT CREATE ANY MATERIALIZED VIEW TO username WITH ADMIN OPTION;
GRANT ALTER ANY TYPE TO username WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO username WITH ADMIN OPTION;
GRANT DROP ANY INDEXTYPE TO username WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO username WITH ADMIN OPTION;
GRANT CREATE ANY TRIGGER TO username WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO username WITH ADMIN OPTION;
GRANT CREATE ANY TYPE TO username WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO username WITH ADMIN OPTION;
GRANT ALTER DATABASE TO username WITH ADMIN OPTION;
GRANT DROP TABLESPACE TO username WITH ADMIN OPTION;

问题 #

这些用户可以通过Schema Browser浏览其他用户的模式，还能对其他Schema的表执行SELECT、UPDATE等操作。我尝试执行REVOKE select on SYS.ALL_USERS from PUBLIC但未生效，请问需要撤销哪些权限来解决该问题？

解决方案 #

核心原因 #

问题出在授予的大量带ANY后缀的系统权限，这类权限允许用户操作所有Schema下的对象，包括浏览、修改其他用户的数据和对象。

需要撤销的权限 #

1. 跨Schema数据操作权限

这些权限直接允许对任意Schema的表执行增删改查，必须撤销：

REVOKE SELECT ANY TABLE FROM username;
REVOKE UPDATE ANY TABLE FROM username;
REVOKE DELETE ANY TABLE FROM username;
REVOKE INSERT ANY TABLE FROM username;
REVOKE READ ANY TABLE FROM username;

2. 跨Schema对象访问权限

这些权限允许查看或操作任意Schema的序列、事务等对象：

REVOKE SELECT ANY SEQUENCE FROM username;
REVOKE SELECT ANY TRANSACTION FROM username;
REVOKE LOCK ANY TABLE FROM username;

3. 跨Schema对象管理权限

如果不需要让用户修改、创建或删除其他Schema的对象，需撤销以下权限：

REVOKE ALTER ANY TABLE FROM username;
REVOKE ALTER ANY SEQUENCE FROM username;
REVOKE ALTER ANY PROCEDURE FROM username;
REVOKE ALTER ANY TRIGGER FROM username;
REVOKE ALTER ANY MATERIALIZED VIEW FROM username;
REVOKE ALTER ANY TYPE FROM username;
REVOKE CREATE ANY TABLE FROM username;
REVOKE CREATE ANY SEQUENCE FROM username;
REVOKE CREATE ANY VIEW FROM username;
REVOKE CREATE ANY PROCEDURE FROM username;
REVOKE CREATE ANY TRIGGER FROM username;
REVOKE CREATE ANY MATERIALIZED VIEW FROM username;
REVOKE CREATE ANY INDEX FROM username;
REVOKE CREATE ANY INDEXTYPE FROM username;
REVOKE CREATE ANY TYPE FROM username;
REVOKE DROP ANY TABLE FROM username;
REVOKE DROP ANY SEQUENCE FROM username;
REVOKE DROP ANY VIEW FROM username;
REVOKE DROP ANY PROCEDURE FROM username;
REVOKE DROP ANY TRIGGER FROM username;
REVOKE DROP ANY MATERIALIZED VIEW FROM username;
REVOKE DROP ANY INDEX FROM username;
REVOKE DROP ANY INDEXTYPE FROM username;
REVOKE DROP ANY TYPE FROM username;
REVOKE EXECUTE ANY PROCEDURE FROM username;
REVOKE EXECUTE ANY INDEXTYPE FROM username;
REVOKE MERGE ANY VIEW FROM username;

4. 不必要的超级管理员权限

原脚本中还授予了远超普通业务用户需求的权限，如数据库级、表空间级操作权限，建议一并撤销：

REVOKE ALTER DATABASE FROM username;
REVOKE CREATE TABLESPACE FROM username;
REVOKE DROP TABLESPACE FROM username;
REVOKE MANAGE TABLESPACE FROM username;
REVOKE IMPORT FULL DATABASE FROM username;
REVOKE ADMINISTER DATABASE TRIGGER FROM username;

关于之前的尝试无效的说明 #

REVOKE select on SYS.ALL_USERS from PUBLIC无法解决问题，因为用户能看到其他Schema，主要是因为拥有SELECT ANY TABLE等权限，这些权限允许用户访问数据字典中的ALL_OBJECTS等视图，从而获取其他Schema的对象信息。撤销上述ANY类权限后，用户仅能看到自己Schema下的对象和PUBLIC对象。

内容的提问来源于stack exchange，提问作者user2829319