Oninit
Talk to an expert →

Informix Error -506

-506 Do not have permission to update all columns.

Your account has been granted the privilege of updating specific columns, but this UPDATE statement updates all columns or columns for which you do not have the privilege. Contact the owner of the table or someone with Database Administrator privilege on this database, and ask to be granted full UPDATE privilege. For a way to list table owners, see the discussion of error -313.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-506 is a column-level privilege error: the session's UPDATE grant on the table is restricted to specific columns, but the statement updates columns outside that grant (or all columns, via an unqualified SET that touches the whole row).

  1. Column-level GRANT UPDATE (col1, col2) ON table TO user issued instead of a full table-level GRANT UPDATE, and the statement then updates a column not in that list.
  2. An UPDATE statement written against the whole row by someone who only holds a column-restricted grant, without realizing the restriction exists.
  3. A privilege grant that predates a later schema change — a new column added to the table after the original column-level GRANT, so it was never included in anyone's column list.
Solutions / Resolution
  1. Contact the table's owner or a DBA-privileged user, per the official guidance, and request full UPDATE privilege (or an updated column list) rather than the restricted column-level grant.
  2. Identify the table's owner first, per the official guidance's pointer to -313's discussion, by querying systables:
    SELECT tabname, owner FROM systables WHERE tabname = 'orders';
  3. Once granted, retry the UPDATE, or rewrite it to touch only the columns already covered by the existing column-level grant if that's sufficient.
Examples
Hitting the restriction
-- Session holds only: GRANT UPDATE (status) ON orders TO app_user;
UPDATE orders SET status = 'shipped', ship_date = TODAY WHERE order_id = 42;
-- -506: ship_date isn't covered by the column-level grant
Requesting the needed privilege
-- Run by the table owner or a DBA-privileged user:
GRANT UPDATE (status, ship_date) ON orders TO app_user;
Diagnostic Checks
  1. Query systabauth/syscolauth for the session's actual UPDATE grant on the table, to confirm whether it's column-restricted and which columns it covers, before assuming the privilege is simply missing outright.
  2. Identify the table owner via systables when requesting a privilege change, per the official guidance's pointer to -313.
Related Errors / Related Topics
  • -313 — referenced directly by -506's official text as the discussion of how to find a table's owner when requesting privilege changes.

A column-scoped privilege gap, not a missing grant outright — check exactly which columns the existing grant covers before escalating to the table owner.