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;
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.