如何列出Oracle中角色关联的所有对象及权限（含执行权限赋权场景）

Alright, let's break down these Oracle role-related queries clearly—they're common tasks when managing database permissions, so I’ve got you covered:

To get a clean list of unique objects that a role has permissions on, use the DBA_TAB_PRIVS data dictionary view. This view tracks all object-level privilege grants in the database, including those given to roles.

Here’s the query:

SELECT DISTINCT owner, table_name
FROM dba_tab_privs
WHERE grantee = 'YOUR_TARGET_ROLE'
ORDER BY owner, table_name;

• Replace YOUR_TARGET_ROLE with the actual name of your role (remember Oracle is case-sensitive here, so use uppercase unless you created the role with quoted identifiers).
• The DISTINCT keyword ensures you don’t get duplicate entries for objects that have multiple permissions granted to the role.

If you need to see exactly what permissions the role has on each object, just remove the DISTINCT and include the privilege column:

SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'YOUR_TARGET_ROLE'
ORDER BY owner, table_name, privilege;

This will show every individual permission granted to the role for each object—for example, if the role has both SELECT and INSERT on a table, both will appear as separate rows.

Since you mentioned granting EXECUTE to a role and needing to query its associated objects, you can filter the first query to only show objects with that specific permission:

SELECT DISTINCT owner, table_name
FROM dba_tab_privs
WHERE grantee = 'YOUR_TARGET_ROLE'
AND privilege = 'EXECUTE'
ORDER BY owner, table_name;

Note: You’ll need the SELECT_CATALOG_ROLE or direct access to DBA_TAB_PRIVS to run these queries. If you don’t have that level of access, use ALL_TAB_PRIVS instead—it will only show objects you have permission to view.

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