8

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

Gerben
  • 117
  • 1
  • 2
  • 10

4 Answers4

22

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.

Nicolas de Fontenay
  • 2,170
  • 5
  • 26
  • 51
1

create a public synonym for the view and try again with your grants

create public synonym ACTIONS for LOCAL_IT.ACTIONS;

grant select on ACTIONS to GERBEN;
Srini V
  • 11,045
  • 14
  • 66
  • 89
  • I am getting the Missing keyword error when I am trying to run your first query. – Gerben Jun 19 '14 at 13:55
  • 1
    Had to change some other things. I had to run the query grant select on gestiones to local_it with grant option before I could grant the select to GERBEN. Thanks for your help anyway, much appreciated! – Gerben Jun 20 '14 at 08:34
0

For the end user without having access to the view structure

grant select on COLLDESK.GESTIONED to LOCAL_IT with grant option;
create synonym LOCAL_IT.VWACTIONS for LOCAL_IT.ACTIONS;

grant select on LOCAL_IT.VWACTIONS to GERBEN;

It's works

Jzb10n
  • 1
  • 1
0

For the end user without having access to the view structure

create public synonym  V_ALERT_001 for usr_sms.V_ALERT_01 (lacal view );
grant select on V_ALERT_001 to USER_ALERT;
lemon
  • 14,875
  • 6
  • 18
  • 38
ALEJANDRO LOPEZ
  • 1