Receiving ORA-01950 when attempting to insert records.

Products

• Data Loss Prevention Enforce

Issue/Introduction

This issue is generalized to the database and may appear for ANY table where records are trying to be inserted:

When attempting to insert records into a table you receive an RSOD and a message in the localhost.log very similar to the following:

* SQLException during execution of sql-statement:
* sql statement was 'INSERT INTO InformationMonitor (informationMonitorID,monitorName,uniqueName,host,port,isDeleted) VALUES (?,?,?,?,?,?) '
* Exception message is [ORA-01950: no privileges on tablespace 'USERS']

Resolution

Occasionally a DBA will grant DBA role or RESOURCE to protect (or whatever the schema-owner happens to be named).  Unlimited privs tends to "grease the rails" during an install.

DBAs typically don't like to leave these privileges in place.  In fact, it is rather dangerous to do so. So they get revoked after completing whatever they want to accomplish.

Problem is that by deleting DBA or RESOURCE role from a user also revokes that user's access to UNLIMITED tablespace, a privilege that is granted at the time that protect is created.  The end result is the error experienced:

ORA-01950: no privileges on tablespace 'USERS'

The cure is to have the DBA grant the privilege again from SQLPlus as sys as sysdba:

GRANT unlimited tablespace TO protect;