My Oracle Support Banner

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 DECEMBER 09, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to 12.1.0.2 [Release 9.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata 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.
Checked for relevance on 08-MAR-2013


Purpose

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.

Note: The limitations affect both regular roles and global roles


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.

Scope

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

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.