Troubleshooting Oracle permissions

Oracle privileges can be very complex.

When unexpected permissions issues arise, here are some troubleshooting actions that you can take.

Symptoms that can occur

Permissions issues can have several different symptoms. Here are the most common types of errors that you might see:

• ORA-01031: insufficient privileges

• ORA-01039: insufficient privileges on underlying objects of the view

• ORA-00942: table or view does not exist

• ORA-00904: string: invalid identifier

Gathering information to help resolve issues

Getting the current privileges

As the Structural user, issue the following commands:

• SELECT ROLE FROM SESSION_ROLES

• SELECT PRIVILEGE FROM SESSION_PRIVS

These reveal the system privileges that are currently in effect.

Getting the failing SQL statement

To capture the failing SQL statement, use the Structural diagnostic logging feature.

Get Oracle audit trail logs and identify related events

Depending on where the statement failed, you get the audit trail from the source or the destination Oracle server.

To get the audit trail logs and identify events that are related to the job:

SELECT action_name, object_schema, object_name, sql_text, event_timestamp
FROM UNIFIED_AUDIT_TRAIL
WHERE return_code != 0
AND DBUSERNAME = 'TONIC'
ORDER BY event_timestamp DESC;

Types of issues and their sources

Problems during job startup and schema gathering

These most often occur when Structural queries system views to obtain information about the source and destination databases.

Ensure that the Structural user has the SELECT_CATALOG_ROLE privilege or the SELECT ANY DICTIONARY privilege.

If the user does not have, and cannot get, either of those, then the user might need explicit privileges on objects in the source or destination schema.

The Oracle Autonomous Database SELECT ANY DICTIONARY privilege does not give access to objects in the SYS schema, which is where Structural does most of its querying.

On views in SYS that Structural uses, use the SELECT_CATALOG_ROLE role or grant the SELECT object privilege.

Insufficient privileges during BulkCopy

The BulkCopy tool requires the INSERT ANY TABLE privilege or INSERT privilege on the destination table.

Insufficient privileges when creating a view or materialized view

The destination schema does not have privileges on objects that are referenced in the view declaration.