Background

Currently, utPLSQL treats all users the same way, regardless of their privileges.
This means, that we always need to check what objects are visible to user and we assume minimal privileges when scanning for annotations.
The only performance boost we get is when user has access to dba_objects and dba_source.
Framework however does not alter any logic based on user privileges to simplify scanning or boost performance by any means.

The slowest part of annotation parsing is retrieval of source code from data dictionary.
Despite cache introduced in version 3.0.4 (#492), we still need to check for cache validity and re-scan changed objects.
The current solution is better than no cache at all but still far from perfect.

Analysis

There are 3 possible scenarios:

1. User doesn't have any elevated privs on schema containing test pacakges
   • utPSLQL cannot cleanup cache for schema, as cache might contain objects not accessible to user and those objects would get deleted from cache
   • utPSLQL needs to join to all_source when retrieving suites (or skip suites not accessible to user), as cache might contain objects not accessible to user and those would fail to execute on missing privileges
2. User can execute all procedures in schema (owner or has execute any procedure)
   • utPLSQL can cleanup cache for schema, as user can see all packages
   • utPLSQL can retrieve all pacakges from cache and skip join to all_source when retrieving suites as user can see execute all packages in schema
3. User can access dba_source & dba_objects
   • utPLSQL can cleanup cache for schema, as user can see all packages
   • utPLSQL needs to join to all_source when retrieving suites (or skip suites not accessible to user), as cache might contain objects not accessible to user and those would fail to execute on missing privileges

To summarize
If user owns the schema, or user has execute any procedure privilege then.

1. When scanning - we see whole schema packages
2. When scanning - we can detect dropped packages safely (as we see all packages, not only those granted) and so we can remove those from cache
3. No need to join with all_objects - we can skip the join.

Cache solution

Cache will return only data for annotated objects / suites that are visible to user invoking utPLSQL.
Cache will be cleaned up from dropped packages when possible (when invoking user can see all packages in schema)

Details

• If user can access all code in schema:
  • Sources are scanned by last modified date > last full scan date
  • Cache is populated with scanned source
  • Cache is purged by deleting objects that no longer existing
  • Cache full scan date is updated for schema
  • Data from cache is retrieved without joining with all objects
• If user has access to DBA source and DBA objects but cannot access all code in schema
  • Sources are scanned by last modified date > last full scan date
  • Cache is populated with scanned source
  • Cache is purged by deleting objects that no longer existing
  • Cache full scan date is updated for schema
  • Data from cache is retrieved by joining with all objects
• If user cannot access DBA source and cannot access all code in schema
  • Sources are scanned by last modified date > last full scan date
  • Cache is populated with scanned source
  • Data from cache is retrieved by joining with all objects

Related to: #757