📋

Description

This error indicates that the Oracle database user lacks the necessary privileges to access a specific table or column within a non-Oracle system accessed through a database link. It occurs during query execution when the target table or column requires elevated permissions.

💬

Error Message

ORA-28556: authorization insufficient to access table

🔍

Known Causes

4 known causes

⚠️

Privileged Table Access

The user lacks SELECT or EXECUTE privileges on the table within the non-Oracle system. The table is protected and requires specific grants.

⚠️

Privileged Column Access

The user is attempting to access a column within the table that requires specific privileges. Column-level security is enforced in the external system.

⚠️

Incorrect Database Link

The database link is configured with a user that doesn't have the required privileges on the remote system. The connection user lacks sufficient permissions.

⚠️

Missing Grants

The necessary grants have not been propagated correctly from the non-Oracle system to the Oracle database link user. Permissions haven't been synced.

🛠️

Solutions

4 solutions available

1. Grant Necessary Privileges to the User easy

The most common cause is the user lacking select, insert, update, or delete privileges on the target table.

1

Connect to the Oracle database as a user with sufficient privileges (e.g., SYS, SYSTEM, or a DBA user).

2

Grant the required privileges (e.g., SELECT, INSERT, UPDATE, DELETE) on the specific table to the user experiencing the ORA-28556 error.

GRANT SELECT ON schema_name.table_name TO user_name;
GRANT INSERT ON schema_name.table_name TO user_name;
GRANT UPDATE ON schema_name.table_name TO user_name;
GRANT DELETE ON schema_name.table_name TO user_name;

3

If the user needs to access multiple tables within a schema, consider granting broader privileges (use with caution).

GRANT SELECT ANY TABLE TO user_name;
GRANT INSERT ANY TABLE TO user_name;
GRANT UPDATE ANY TABLE TO user_name;
GRANT DELETE ANY TABLE TO user_name;

4

Alternatively, if the user is part of a role that should have access, ensure the role has the necessary privileges and that the user is granted that role.

GRANT role_name TO user_name;

2. Verify Table Ownership and Schema easy

Ensure the user is attempting to access the table in the correct schema and that the table actually exists.

1

Connect to the Oracle database as the user experiencing the error.

2

Check if the table exists and in which schema it resides. If the table is not in the current user's schema, you must qualify the table name with the schema name.

SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME_IN_UPPERCASE';
SELECT owner, table_name FROM all_tables WHERE table_name = 'TABLE_NAME_IN_UPPERCASE';

3

If the table exists in another schema, adjust your SQL query to include the schema name.

SELECT * FROM schema_name.table_name;

4

If the table does not exist or is misspelled, correct the table name in your query.

3. Check for Public Synonyms medium

If a public synonym points to a table the user doesn't have direct access to, ORA-28556 can occur.

1

Connect to the Oracle database as a DBA user.

2

Identify public synonyms that might be causing the issue. Look for synonyms that point to tables for which the user lacks privileges.

SELECT synonym_name, table_owner, table_name FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'SYNONYM_NAME_IN_UPPERCASE';

3

If a public synonym is the culprit, you have a few options:
1. Grant the user privileges on the underlying table (preferred if the user should have direct access).
2. Create a private synonym for the user pointing to the table and grant privileges on that.
3. Drop the public synonym (if it's no longer needed or causing widespread issues) and create a private synonym for the user.

GRANT SELECT ON schema_name.table_name TO user_name; -- Option 1
CREATE SYNONYM user_name.synonym_name FOR schema_name.table_name; -- Option 2 (requires privileges on the table)
GRANT SELECT ON user_name.synonym_name TO user_name; -- Granting access to the private synonym if needed

4. Review Row Level Security (RLS) Policies advanced

Row Level Security can restrict access to specific rows, and if misconfigured, can lead to authorization errors.

1

Connect to the Oracle database as a DBA user.

2

Identify if Row Level Security policies are applied to the table in question.

SELECT policy_name, policy_schema, policy_object_name FROM ALL_RLS WHERE policy_object_name = 'TABLE_NAME_IN_UPPERCASE' AND policy_schema = 'SCHEMA_NAME';

3

If RLS policies are present, examine the security predicates associated with them. The user might not meet the criteria defined in the policy.

SELECT policy_name, policy_schema, policy_object_name, policy_text FROM ALL_RLS WHERE policy_object_name = 'TABLE_NAME_IN_UPPERCASE' AND policy_schema = 'SCHEMA_NAME';

4

Adjust the RLS policy or grant the user the necessary attributes/roles that satisfy the policy's conditions to allow access.

Oracle ORA-28556: Insufficient Authorization