Is there a SQL query to determine exactly which users are able to execute a specific database method (like creating new parts via PART_CATALOG_API.New__), accounting for both grants and any specific revocations?

Q: Is there a SQL query to determine exactly which users are able to execute a specific database method (like creating new parts via PART_CATALOG_API.New__), accounting for both grants and any specific revocations?

Yes — combine three views: FND_USER_ROLE_RUNTIME (which roles/permission sets a user effectively has), SECURITY_SYS_PRIVS_TAB (package-level grants), and SECURITY_SYS_TAB (method-level revocations). For example, to find users who can execute PART_CATALOG_API.New__: select identities from FND_USER_ROLE_RUNTIME where their role is among those granted the package in SECURITY_SYS_PRIVS_TAB for that table/package name, MINUS any roles that have that specific method explicitly revoked in SECURITY_SYS_TAB. This accounts for the fact that a role might have package-level access granted but a specific method revoked, which a simple grant-only check would miss.

Answer

Yes — combine three views: FND_USER_ROLE_RUNTIME (which roles/permission sets a user effectively has), SECURITY_SYS_PRIVS_TAB (package-level grants), and SECURITY_SYS_TAB (method-level revocations). For example, to find users who can execute PART_CATALOG_API.New__: select identities from FND_USER_ROLE_RUNTIME where their role is among those granted the package in SECURITY_SYS_PRIVS_TAB for that table/package name, MINUS any roles that have that specific method explicitly revoked in SECURITY_SYS_TAB. This accounts for the fact that a role might have package-level access granted but a specific method revoked, which a simple grant-only check would miss.

← Browse Security and access questions