Oracle ORA-01031: Insufficient Privileges Fix for SELECT Queries

You're running a simple SELECT statement on a table you've accessed a hundred times. Today, Oracle hits you with ORA-01031: insufficient privileges. This usually happens in Oracle 19c or 21c after a schema migration, a role change, or when someone recreated the table. I've seen it most often when a stored procedure tries to query a table from another schema—like calling SELECT * FROM HR.EMPLOYEES from a custom schema called APP_USER. The trigger? Someone dropped and recreated the table, or the grant got lost during an export/import.

Root Cause

The error means your session doesn't have the right privilege to run the operation. In Oracle, there are two kinds of privileges: system privileges (like SELECT ANY TABLE) and object privileges (like SELECT on a specific table). If you're using a role—like READ_ONLY_ROLE—that role might grant SELECT on the table, but roles don't work inside stored procedures or views by default. That's the sneaky part. Your SQL*Plus session works, but your PL/SQL block fails. The real fix? Give the privilege directly to the user, not through a role.

Fix Steps

1. Check the current privileges. Run this SQL as a DBA:
   

   SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'YOUR_USER' AND TABLE_NAME = 'EMPLOYEES';

   If nothing shows, you need a direct grant.
2. Grant the object privilege directly. Connect as the table owner or use a DBA account. Run:
   

   GRANT SELECT ON HR.EMPLOYEES TO APP_USER;

   This gives APP_USER the right to SELECT from that table directly. No role needed.
3. If the table is in another schema and you want to access many tables, use the system privilege SELECT ANY TABLE. This is an all-or-nothing approach:
   

   GRANT SELECT ANY TABLE TO APP_USER;

   Be careful—this lets the user read every table in the database. Only do this for trusted accounts.
4. For stored procedures or functions, check invoker's rights vs. definer's rights. If the procedure uses definer's rights (default), the owner's privileges are used, not the caller's. You might need to grant the privilege to the procedure owner. Or, change the procedure to use invoker's rights with AUTHID CURRENT_USER in the CREATE statement.
5. Test the fix. Run your SELECT again. If it works, you're golden. If not, move to the next section.

What to Check If It Still Fails

• Check if the table exists. Sounds dumb, but I've had cases where the table was dropped and recreated with a different owner. Run SELECT owner, table_name FROM ALL_TABLES WHERE table_name = 'EMPLOYEES';
• Check for synonyms. Sometimes a public synonym hides the real object. Use SELECT * FROM DBA_SYNONYMS WHERE synonym_name = 'EMPLOYEES'; to see what it points to.
• Check if the privilege was granted through a role. Role-based privileges don't work inside PL/SQL without SET ROLE or dynamic SQL. The safest path is always a direct grant.
• On Oracle 21c, check the container database model. If you're in a pluggable database (PDB), you need to grant privileges inside that PDB, not the root container. Connect to the correct PDB first: ALTER SESSION SET CONTAINER = your_pdb;
• Try a different approach: Create a view in the target schema that selects from the table, then grant SELECT on the view. This avoids giving broad access and works with roles.

ORA-01031 is frustrating, but once you understand the role vs. direct grant distinction, it's easy to fix. I've spent hours on this myself—don't let a missing grant ruin your day.