I just demonstrated something to myself which took me by surprise and am wondering if I am perhaps missing something. Running Oracle 18.104.22.168.0 EE on RHEL 64-bit. Here is the scenario...
schema X has a view, v1 which selects from table t1 owned by schema Y.
schema Y has granted select on t1 to schema X.
schema X has in turn granted select on view v1 to user Z.
When user Z issues the command
SQL> select * from X.v1;
ORA-01031, insufficient privileges is returned. In order to resolve this schema Y had to
SQL> grant select on v1 to X with grant option;
My question. I didn't expect it would be necessary to issue the grant with grant option. Am I missing something or is there another way to handle this? I don't want to issue the grant to public and I am not particularly happy about user X being able to grant select on view v1 to others. If this is the way it must be so be it but it took me by surprise.
Univ. of California at Davis
IET Campus Data Center