CODEWITHSUNDEEP
oracle-databasesql-grant
Jürgen Zornig
Jürgen Zornig

Reputation: 1244

GRANT INSERT on tables participating in an updateable view

The given database contains a masterdata (MD) Schema and an application specific Schema (APP). In the APP Schema we have a view which provides the applications data from one table in the scheme joined with data from the MD Schema.

Example: Think of an address book application, which holds an address table, but cities and ZIP codes are joined from a masterdata table in another Schema which is maintained centrally.

CREATE VIEW view_adress AS
SELECT app.ID, app.Street, app.ZIP, zip.CITYNAME
FROM APP.adress app 
LEFT OUTER JOIN MD.zipcodes zip
  ON app.ZIP = zip.ZIP

This is very simplified. The actual view I use is a lot more complicated like that and therefore I implemented an INSTEAD OF INSERT, UPDATE Trigger to map INSERTs on the view to the correct base table in my APP Schema.

The application users (role) is granted SELECT,INSERT,UPDATE,DELETE on all tables inside this APP Schema. They are also granted SELECT on that zipcode table in the master data Schema.

When I insert on that view, I get an "ORA-01720: Grant Option Does Not Exist"... I don't know the exact cause of this error, but it can be assumed that the INSTEAD-OF Trigger never INSERTS on the ZIP Code Table, only on the address table.

I understand, that granting the application users INSERT privilege on the zipcode table would probably resolve this issue, but I am feeling uncomfortable granting INSERTs on tables to users which they never should edit in any way, because these are only lookups.

Is there another, possibly "the correct way" to solve this?

Upvotes: 0

Views: 9172

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

By "insufficient permissions error" do you mean this?

ORA-01720: grant option does not exist for 'MD.ZIPCODES'

*Cause: A grant was being performed on a view or a view was being replaced and the grant option was not present for an underlying object.

*Action: Obtain the grant option on all underlying objects of the view or revoke existing grants on the view.

If so, the solution is that you need to grant the relevant permissions to the schema owning the view - not to the roles that use the view:

grant insert on md.zipcodes to app with grant option;

It's true that you are still having to grant a permission that is logically not required, but you are not granting it to users, only the app schema.

Upvotes: 2

Related Questions