Hi,
Oracle Version: 12c (12.1.0.2.0)
We have 3 schemas: A, B and C
Schema A has tables A.A1 and A.A2
Schema B has tables B.B1 and B.B2
Schema B also has a view V1 (Which uses tables - A.A1, A.A2, B.B1 and B.B2) and a procedure P1 - This procedure uses the view V1
Schema C is an user and doesn't have any objects
Now, all the 3 schemas have SELECT GRANTs on the other schema tables. And also SELECT on the view and EXECUTE privilege on the procedure P1. Example Grants:
--Select on tables
GRANT SELECT ON A.A1 TO B, C;
GRANT SELECT ON A.A2 TO B, C;
GRANT SELECT ON B.B1 TO A, C;
GRANT SELECT ON B.B2 TO A, C;
--Select on view
GRANT SELECT ON B.V1 TO A, C;
--Execute on procedure
GRANT EXECUTE ON B.P1 TO A, C;
Now, when C executes this procedure B.P1 - We get the following error: ORA-01720: grant option does not exist for A.A1...
This can be resolved by using the GRANT option:
GRANT SELECT ON A.A1 TO B WITH GRANT OPTION;
But I am trying to understand why exactly we need this "WITH GRANT OPTION" when schema C already has explicit SELECT privileges on the underlying tables - A.A1 and A.A2 and also SELECT on the view B.V1 itself?
Please advise.
Thank you!