1

When I open a QGIS (3.10) connection to an Oracle (12c) database using a logon which owns a spatial table, and add the table as a layer, the layer can be edited as normal.
If I open the Oracle connection using a different logon, the same table cannot be edited (toggle editing is inactive).

I've tried a user with the DBA role, and can edit the layer as that user via SQL update statements (from SQL*Plus), but not from within QGIS. I've tried creating a user synonym and ensuring there's an entry in USER_SDO_GEOM_METADATA for the table and user.
I've also granted the user the SELECT/INSERT/UPDATE/DELETE ANY TABLE system privileges in case the DBA role isn't enough (some privileges only work when granted directly, not via a role).
The database connection was created via the QGIS browser with no credentials stored, allowing QGIS to prompt for them as the connection is opened.

What is it that determines whether an Oracle Spatial table is editable in QGIS?

I'm reluctant to give out the SYSTEM owner password to all and sundry, or to have it unencrypted in a QGIS layer file.

Surely I can't be the only person who uses Oracle in a multi-user environment?

Improve this question
5
  • 2
    You haven't mentioned anything about the way of how you've organized ROLEs and GRANTs.
    – Vince
    Jun 3, 2020 at 12:59
  • The user I'm currently trying has the DBA role, which encompasses most system privileges, and I've just added the general table update privileges, to no effect.
    – Andrew_S
    Jun 3, 2020 at 13:33
  • SDO_COORD_REF_SYS is there, with the 27700 projection of the data in it, there's a publis synonym, and the user has select any table and can select it
    – Andrew_S
    Jun 3, 2020 at 14:22
  • You can see the SQL selects used in the source code, for example github.com/qgis/QGIS/blob/master/src/providers/oracle/…. And if you don't get an answer from here soon, write mail to qgis-users mailing list.
    – user30184
    Jun 3, 2020 at 14:25
  • I'd recommend using specific GRANTs, and leave the ANY and DBA out of any security model design. Sometime apps query for access, and the deific privileges aren't accounted for properly.
    – Vince
    Jun 3, 2020 at 18:01

1 Answer 1

Reset to default
1

Resolved

Only direct table grants are recognized.
Grants via a user role are not checked on table load.

A specific UPDATE grant on the geometry column is required for vertex edit.
"grant update("SHAPE") on [table] to [user]"

A valid primary key is required for vertex edit. QGIS didn't like an ArcGIS OBJECTID column (number(38,0), unique index) that was subsequently designated as a primary key, and I ended up recreating the table with a PK, and reloading it.
(without vertex edit, you can create new features but not change existing ones)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.