grant select on view

Question

somewhere along the way something is going wrong, and I can't seem to find out why. If this is already asked apologies.

I have 3 schema's in my database: COLLDESK LOCAL_IT GERBEN

COLLDESK is our main schema, LOCAL_IT is where we do our local development, and GERBEN is the end user (should only have select options).

Now I have a table in my COLLDESK schema called GESTIONES. In LOCAL_IT I have a view called ACTIONS. This is holding 4 columns from the table GESTIONES in the COLLDESK schema. So far so good!

Now I want to give the GERBEN schema select privileges in the view ACTIONS. But when I say

grant select on LOCAL_IT.ACTIONS to GERBEN

I get the error ORA-01720: grant option does not exist for COLLDESK.GESTIONES

Now I tried giving GERBEN select privileges to GESTIONES, but I am still getting the error message

Any help would be greatly appreciated!

Kind regards

Gerben

Answer 1

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.

Solution:

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.