ORA-01720: Can't grant privileges on a view

Question

I have an user with some tables and I granted privileges to three of them to another user. Now I need to create a view and grant select to another user, like this:

User A

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE_A TO USER_B WITH GRANT OPTION;

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE_B TO USER_B WITH GRANT OPTION;

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE_C TO USER_B WITH GRANT OPTION;

User B

CREATE OR REPLACE VIEW V_XYZ AS SELECT * FROM TABLE_A, TABLE_B, TABLE_C;

GRANT ALL ON V_XYZ to USER_C;

This give the folowing error:

ORA-01720: "grant option does not exist for '%s.%s'"

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

I want help. I gave grant option from USER_A to USER_B and now in USER_B I can't grant privileges to another user. Maybe that's something easy that I'm not looking. — Silva_PT_SCP, Oct 25 '16 at 12:43
Follow the instructions in the error message. It literally tells you exactly what to do. — RBarryYoung, Oct 25 '16 at 12:45
In my case the issue was due to fact that underlying table in the select statment of the view was not partitioned. — Viktor Reinok, Aug 28 '20 at 08:43

score 0 · Accepted Answer · answered Oct 25 '16 at 14:08

0

You ask ALL privileges to be given to USER_C, but only 4 have been granted to you to the underlying tables. Try either to grant ALL PRIVILEGES from A to B, or to restrict the grant from B to C on SELECT, UPDATE, INSERT, DELETE.

answered Oct 25 '16 at 14:08

Thinkeye

888
12
22

score -1 · Answer 2 · answered Oct 25 '16 at 12:58

-1

Try giving:

GRANT select ON V_XYZ to USER_C;

answered Oct 25 '16 at 12:58

Nitin

290
2
12

ORA-01720: Can't grant privileges on a view

2 Answers2