Oracle中如何为Amy授予指定schema下所有表的SELECT权限？

Oracle SQL语法不支持用schema.*这种通配符批量授权指定schema下所有表的SELECT权限，这就是执行grant select on schema.* to amy;触发ORA-00903: invalid table name错误的核心原因。

方法1：动态SQL批量生成授权语句 #

通过查询数据字典视图生成对应schema下所有表的授权语句，再批量执行。以目标schema名为target_schema为例：

生成单条授权语句（手动执行） #

SELECT 'GRANT SELECT ON target_schema.' || table_name || ' TO amy;'
FROM all_tables
WHERE owner = 'TARGET_SCHEMA'; -- Oracle对象名默认大写，需匹配实际存储的schema名

将查询结果中的每条语句复制出来执行即可。

PL/SQL批量自动执行 #

BEGIN
  FOR table_rec IN (
    SELECT table_name 
    FROM all_tables 
    WHERE owner = 'TARGET_SCHEMA'
  ) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON target_schema.' || table_rec.table_name || ' TO amy';
  END LOOP;
END;
/

方法2：授予系统权限（谨慎使用） #

若允许Amy查询数据库中所有可访问的表，可直接授予SELECT ANY TABLE系统权限：

GRANT SELECT ANY TABLE TO amy;

注意：该权限范围极广，会赋予Amy访问所有schema下可查询表的权限，非必要场景不建议使用。

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