如何获取Oracle中仅分配给物化视图的权限？

直接通过关联DBA_TAB_PRIVS和DBA_OBJECTS视图，筛选出对象类型为MATERIALIZED VIEW的权限记录，即可排除物化视图底层表的权限：

SELECT grantee, privilege, table_name, owner
FROM dba_tab_privs tp
INNER JOIN dba_objects o 
    ON tp.table_name = o.object_name 
    AND tp.owner = o.owner
WHERE o.object_type = 'MATERIALIZED VIEW';

如果需要排除系统自动生成的物化视图相关辅助表（比如物化日志表MLOG$_xxx、快速刷新表RUPD$_xxx），可以添加额外过滤条件：

SELECT grantee, privilege, table_name, owner
FROM dba_tab_privs tp
INNER JOIN dba_objects o 
    ON tp.table_name = o.object_name 
    AND tp.owner = o.owner
WHERE o.object_type = 'MATERIALIZED VIEW'
  AND tp.table_name NOT LIKE 'MLOG$_%'
  AND tp.table_name NOT LIKE 'RUPD$_%';

说明 #

• DBA_OBJECTS中的OBJECT_TYPE = 'MATERIALIZED VIEW'确保只针对物化视图对象筛选权限
• 关联条件tp.table_name = o.object_name AND tp.owner = o.owner保证权限记录和物化视图对象一一对应
• 第二个查询的额外条件用于排除系统生成的辅助表，这些表不属于用户手动创建的物化视图

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