Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in PL/SQL, works in SQL*Plus
(Doc ID 168168.1)
Last updated on OCTOBER 10, 2022
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
The purpose of the document is to show the limitations of privileges assigned to roles.
Due to these limitations, you may get errors in PL/SQL procedures/packages when accessing certain objects or packages, but the same code works from directly run SQL.
The flagged errors are ORA-00942 or ORA-01933 or ORA-01031 and ORA-06512 or PLS-00201 and ORA-06550 or ORA-28111 with Fine Grained Auditing (FGA). The generated errors may differ in each release.
This note is appropriate for DBAs and developers. It intends to focus on limitations of roles and privileges. It shows which errors are typically flagged and how to detect which privileges are granted directly and which privileges are granted via roles.
A. Environment users/roles/privileges used
B. Example: creating a view via a role generates ORA-00942 or ORA-01933 or ORA-1031
C. Example: creating a procedure via a role generates PLS-00201, ORA-06550
D. Example: creating a table via a role generates ORA-01031, ORA-06512
E. Example: select privilege via a role generates ORA-00942
F. Example: ORA-28111 :insufficient privilege to evaluate policy predicate in combination with FGA
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!