grant select on view

The error message should be written like this:

ORA-01720: “grant option” does not exist for COLLDESK.GESTIONES.

Here’s how it works:

You have 3 schemas:

  • Schema1 – Holder of a table named “table1”
  • Schema2 – Holder of a view “view1” selecting from schema1.table1
  • Schema3 – User, selecting from schema2.view1 – has no select granted on schema1.table1.

You probably have done the following:

grant select on schema1.table1 (COLLDESK.GESTIONES) to schema2;

Note that since you’re creating a view, the select must granted directly to schema2, your view won’t work if the select is granted through a role.

Now you are trying to allow a 3rd schema (schema3) to use the view read data from schema1. For security purposes, a schema2 will never be allowed to access data from schema1 to a 3rd schema even if it can view the data. This would be a huge security hole otherwise.

So the solution is to make it explicit that schema2 will be able to grant that select privilege, indirectly, when a 3rd party is granted the select privilege on the view.


grant select on schema1.table1 to schema2 with grant option;

Now schema2, is allowed to grant select on its view to 3rd parties:

grant select on schema2.view1 to schema3;

For you:

grant select on COLLDESK.GESTIONED to LOCAL_IT with grant option;
grant select on LOCAL_IT.ACTIONS to GERBEN;

Also: Avoid using public views and public db links unless absolutely necessary. Deplete every other options first.

Leave a Comment