Object type "nested" implicit grants

A colleague got an "ORA-01720: grant option does not exist" error and couldn't understand why. So together we researched a bit and learned some things about how object type grants across schemas works - including a small surprise that was the reason for our puzzlement.

To demonstrate, I create three users with just the necessary privileges:

SQL> connect system@orcl
Enter password:
Connected.
SQL> grant connect, create type to user1 identified by user1;

Grant succeeded.

SQL> grant connect, create type to user2 identified by user2;

Grant succeeded.

SQL> grant connect to user3 identified by user3;

Grant succeeded.

Then USER1 creates an object type TYPE1 and grants EXECUTE privilege to USER2:

SQL> connect user1/user1@orcl
Connected.
SQL> create type type1 as object (
  2     attr1    varchar2(10)
  3  );
  4  /

Type created.

SQL> grant execute on type1 to user2;

Grant succeeded.

USER2 creates an object type TYPE2 that has an attribute of type USER1.TYPE1:

SQL> connect user2/user2@orcl
Connected.
SQL> create type type2 as object (
  2     attr2    varchar2(10)
  3   , attr3    user1.type1
  4  )
  5  /

Type created.

Then USER2 tries to grant EXECUTE privilege on TYPE2 to USER3:

SQL> grant execute on type2 to user3;
grant execute on type2 to user3
                 *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER1.TYPE1'

Okay, after thinking a bit of course I understand why I get this error. The documentation also states it quite clearly:

To grant access to your new type or table to other users, you must have either the required EXECUTE object privileges with the GRANT option or the EXECUTE ANY TYPE system privilege with the option WITH ADMIN OPTION. You must have been granted these privileges explicitly, not through a role.

In order for USER3 to be able to use TYPE2, USER3 has to be able to write a constructor call like this:

user2.type2(
   'VALUE2'
 , user1.type1('VALUE1')
)

Which means that USER3 also needs privileges on USER1.TYPE1 - it does not make sense for USER2 to be able to grant privileges on his TYPE2 without also being able to implicitly also grant privileges on USER1.TYPE1. To do that USER2 would need (like the docs write) that USER1 had granted the privilege WITH GRANT OPTION, which he didn't.

But then comes the slight surprise:

SQL> grant execute on type2 to user1;
grant execute on type2 to user1
                 *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER1.TYPE1'

Letting USER2 grant privilege on TYPE2 to USER1 shouldn't be a problem, since USER1 of course has the privilege already to use his own TYPE1, so USER1 can use the constructor call above without needing extra privileges.

But it appears that the check for WITH GRANT OPTION is performed without testing whether it really is needed or not - the documented specification states that USER2 must have been granted WITH GRANT OPTION in order to perform the GRANT on TYPE2, so that's just it. That it in this one case really is unnecessary does not matter.

So we fix the problem by letting USER1 perform a grant WITH GRANT OPTION - then USER2 is able to do his GRANTs without errors:

SQL> connect user1/user1@orcl
Connected.
SQL> grant execute on type1 to user2 with grant option;

Grant succeeded.

SQL> connect user2/user2@orcl
Connected.
SQL> grant execute on type2 to user3;

Grant succeeded.

SQL> grant execute on type2 to user1;

Grant succeeded.

We found that the error normally makes sense - the grants on TYPE2 performed by USER2 has to implicitly also perform grants on TYPE1 in the USER1 schema. The surprise was that this also is the case when the "nested" implicit grant really is not necessary.