ORA-06598 on rebuild_annotation_cache from SYS

[Pazus]

Describe the bug
If you have utPLSQL installed in a schema and try to rebuild cache from SYS user you face ORA-06598 (insufficient INHERIT PRIVILEGES privilege error).

begin
    ut3.ut_annotation_manager.purge_cache('APP','PACKAGE');
    ut3.ut_annotation_manager.rebuild_annotation_cache('APP','PACKAGE');
end;
Error report -
ORA-06598: недостаточные полномочия INHERIT PRIVILEGES
ORA-06512: на  "UT3.UT_ANNOTATION_MANAGER", line 247
ORA-06512: на  line 2
06598. 00000 -  "insufficient INHERIT PRIVILEGES privilege"
*Cause:    An attempt was made to run an AUTHID CURRENT_USER function or
           procedure, or to reference a BEQUEATH CURRENT_USER view, and the
           owner of that function, procedure, or view lacks INHERIT PRIVILEGES
           privilege on the calling user.
*Action:   Either do not call the function or procedure or reference the view,
           or grant the owner of the function, procedure, or view
           INHERIT PRIVILEGES privilege on the calling user.

Provide version info
utPLSQL v3.1.6 and older I suppose.

To Reproduce
Execute the following code from SYS. Instead of APP you may provide any other schema except SYS and schema where utplsql is installed.

begin
    ut3.ut_annotation_manager.purge_cache('APP','PACKAGE');
    ut3.ut_annotation_manager.rebuild_annotation_cache('APP','PACKAGE');
end;

Expected behavior
Cache has to be rebuilt correctly.

Additional context
As problem is connected to INHERIT PRIVILEGES it affects 12c+ only.

[PhilippSalvisberg]

Why do you need to run that as SYS?

[jgebal]

This is standard behavior.
Annotation cache is running with invokers rights AUTHID CURRENT_USER, as most objects in utPLSQL.
It needs to be running with invokers rights to be able to see objects that a the invoking user can see.
Without that utPLSQL schema would need to have elevated privileges to be able to see all objects.
That could be considered serious security issue for some users.

[PhilippSalvisberg]

I agree with @jgebal. If you really need to run it from SYS, then there are other was to do that. Nonetheless it would be interesting to know what that is needed in the first place.

[Pazus]

My fried who reported the issue is a DBA. They use CloneDB technology to prepare test/dev environments. They prepare base image which is used for further clones when they obfuscate personal data and do some other tasks. Now they want to install utplsql and build cache at that point so that all CloneDB images already have it installed and cache built.
So DBA runs sys, creates a schema, runs headless installation and tryes to rebuild cache in the next step.

Here is a gread example and description of what's happening:
https://oracle-base.com/articles/12c/control-invoker-rights-privileges-for-plsql-code-12cr1

The problem is solved by executing

GRANT INHERIT PRIVILEGES ON user sys TO ut3;

So the question is do we want to add this to the headless installation (probably not) or may be we should mention it in doc as a tip & tricks.

[jgebal]

We could add it in documentation next to with link to oracle doc.

[PhilippSalvisberg]

I think the use case described by @Pazus is valid. Refresh utPLSQL application caches at the end of a database cloning process to speed up initial test runs.

However, the ut_annotation_manager is not part of the public API. I do not think it is a good idea to start describing how to use internal API.

Instead I suggest to add a rebuild_cache procedure to the public API**. Similar as we have done it for ut_runner.get_suites_info.

[pesse]

I agree that having a rebuild_cache procedure in the public API would be very beneficial in several circumstances. It would also keep the internals closed and give us control to decide how the use case is done.

[jgebal]

Two API's are available in ut_runner since 3.1.0:

  /**
  * Rebuilds annotation cache for a specified schema and object type.
  *  It can be used to speedup execution of utPLSQL on a given schema
  *   if it is executed before initial call made to `ut.run` or `ut_runner.run` procedure.
  *
  * @param a_object_owner owner of objects to get annotations for
  * @param a_object_type  optional type of objects to get annotations for (defaults to 'PACKAGE')
  */
  procedure rebuild_annotation_cache(a_object_owner varchar2, a_object_type varchar2 := null);

  /**
  * Removes cached information about annotations for objects of specified type and specified owner
  *
  * @param a_object_owner  optional - owner of objects to purge annotations for. If null (default) then all schemas are purged
  * @param a_object_type   optional - type of objects to purge annotations for. If null (default) then cache for all object types is purged
  */
  procedure purge_cache(a_object_owner varchar2 := null, a_object_type varchar2 := null);