AWS Oracle RDS 19c数据库用户转只读权限异常问题咨询

你此前回收的RESOURCE、CONNECT角色仅包含系统级权限，Oracle中对象所有者对自身schema下的所有对象拥有不可被直接回收的固有全部权限，这是回收系统权限后用户仍可修改自有表的核心原因。此外你未回收用户的表空间配额，用户仍有存储空间可创建新对象，存在权限溢出风险。

所有操作需使用持有DBA权限的账号执行。

第一步：清理残留权限 #

先补全遗漏的权限回收操作，避免用户再创建新对象：

-- 回收USERS表空间配额
ALTER USER xyz QUOTA 0 ON "USERS";
-- 回收所有额外系统权限，避免遗漏
REVOKE ALL PRIVILEGES, GRANT ANY PRIVILEGE FROM xyz;
-- 保留基础连接权限
GRANT CREATE SESSION TO xyz;

第二步：处理自有对象权限问题 #

根据业务场景选择以下两种方案之一即可： #

方案1：迁移对象所有权（推荐，安全性最高） #

将xyz用户下的所有业务对象迁移到专用的只读所有者schema，从根源上取消xyz的对象所有者权限：

1. 新建专用对象存储账号，该账号不对外提供登录权限：

CREATE USER app_owner IDENTIFIED BY 自定义强密码 
DEFAULT TABLESPACE "USERS" 
QUOTA UNLIMITED ON "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT CREATE TABLE, CREATE INDEX, CREATE SEQUENCE TO app_owner;

2. 批量迁移xyz下的对象到app_owner schema，以下为生成迁移语句的脚本，执行输出结果即可完成迁移：

-- 生成表迁移语句
SELECT 'RENAME xyz.' || table_name || ' TO app_owner.' || table_name || ';' 
FROM dba_tables WHERE owner='XYZ';
-- 生成索引迁移语句
SELECT 'ALTER INDEX xyz.' || index_name || ' REBUILD OWNER TO app_owner;' 
FROM dba_indexes WHERE owner='XYZ';

3. 给xyz用户授予只读访问权限，可批量生成授权语句：

-- 生成对象查询授权语句
SELECT 'GRANT SELECT ON app_owner.' || table_name || ' TO xyz;' 
FROM dba_tables WHERE owner='APP_OWNER';
-- 可选：生成公共同义词，让xyz用户无需加schema前缀即可访问表
SELECT 'CREATE PUBLIC SYNONYM ' || table_name || ' FOR app_owner.' || table_name || ';' 
FROM dba_tables WHERE owner='APP_OWNER';

迁移完成后xyz用户下无任何自有对象，仅拥有连接权限和指定对象的查询权限，完全无法执行写操作。 #

方案2：保留对象在xyz schema下，用触发器拦截写操作（适合无需迁移对象的场景） #

因为所有者固有权限无法直接回收，可创建schema级触发器拦截xyz用户的所有写操作：

CREATE OR REPLACE TRIGGER block_xyz_write
BEFORE INSERT OR UPDATE OR DELETE OR CREATE OR ALTER OR DROP OR TRUNCATE ON xyz.SCHEMA
BEGIN
  RAISE_APPLICATION_ERROR(-20001, '该用户仅允许只读访问，禁止执行写操作');
END;
/

触发器创建完成后，xyz用户执行任何写操作都会直接报错，仅允许执行SELECT查询。

使用xyz用户登录数据库，执行以下操作确认权限符合要求：

• 执行SELECT * FROM 业务表名可正常返回结果
• 执行INSERT/UPDATE/DELETE/DROP/CREATE等写操作直接报错无权限

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