SQL Dev extension: No filter on owner of a synonym

[rvo-cs]

Case 1: for objects of type synonym, the following disjunction voids the filter on owner :

where (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')

The effect can be demonstrated easily by creating a synonym with the same name in two distinct schemas:

alter session set plscope_settings = "IDENTIFIERS:ALL, STATEMENTS:ALL";

alter session set current_schema = SCOTT";
create or replace synonym s_emp for emp;

alter session set current_schema = "SCOTT2";
create or replace synonym s_emp for emp;

Then, after navigating to the PL/Scope -> Synonyms -> S_EMP node of either schema. both synonyms will be shown at once, e.g. in the "Identifiers" tab, if using a privileged account (e.g. if SELECT_CATALOG_ROLE is enabled in the session).

Affected tabs:

• Identifiers
• Uses
• Used By

$ git checkout v1.0.0
$ git grep -in 'object_owner or'
sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml:51:       where (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')
sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml:72:       where (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')
sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml:584:       where (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')
sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml:1173:         and (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')
sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml:1285:         and (owner = :OBJECT_OWNER or upper(replace(:OBJECT_TYPE, 'plscope-utils-')) = 'SYNONYM')

---

Case 2: omitted filters

For consistency reasons, in the sql_ids CTE of the "Table Usages" tab, the following:

 from sys.all_statements -- NOSONAR: avoid public synonym
where owner like nvl(:OBJECT_OWNER, user)

should read as:

 from sys.all_statements -- NOSONAR: avoid public synonym
where owner like nvl(:OBJECT_OWNER, user)
  and object_type in (
         upper(replace(:OBJECT_TYPE, 'plscope-utils-')), upper(replace(:OBJECT_TYPE, 'plscope-utils-')) || ' BODY'
      )
  and object_name = :OBJECT_NAME

I'm not aware of any consequence beside pulling unnecessary rows from all_statements.

[rvo-cs]

Correction: in case 2 above, the "should read as" part should actually read as:

 from sys.all_statements -- NOSONAR: avoid public synonym
where owner = :OBJECT_OWNER
  and object_type in (
         upper(replace(:OBJECT_TYPE, 'plscope-utils-')), upper(replace(:OBJECT_TYPE, 'plscope-utils-')) || ' BODY'
      )
  and object_name = :OBJECT_NAME

That's an equality on owner, not a LIKE predicate.

[rvo-cs]

Regarding case 1: wasn't the original intent to handle public and private synonyms together?

Well, that works, from a purely technical viewpoint. End users may find the result confusing, though.

UT3 has many instances of public synonyms with names identical to matching private synonyms. This is not a made-up example.

Why not add a separate object type (e.g. plscope-utils-pubsyn) in the tree to distinguish between private and public synonyms?

[PhilippSalvisberg]

Case 1: this looks like a a bug. Thanks for reporting it. Case 2: I do not understand this one. Could you please explain the problem or better create a dedicated issue for this case.

I change the title of the case from "wrong filters in CTEs of editors" to "No filter on owner of a synonym".

[rvo-cs]

Regarding Case 2: simply put, the filters in the sql_ids CTE of the "Table Usages" display are wrong: there's no reason for using a LIKE predicate on owner, nor for that nvl(:OBJECT_OWNER, user) expression.

That looks like an erroneous cut-and-paste from the plscope-utils-report.xml file.

[rvo-cs]

Just to clarify the point about Case 2: as of v1.0.0 the sql_ids CTE in the query of the "Table Usages" display looks like:

sql_ids as (
      select /*+ materialize */
             owner,
             nvl(sql_id, type) as name,
             signature,
             [...]
             origin_con_id
        from sys.all_statements -- NOSONAR: avoid public synonym
       where owner like nvl(:OBJECT_OWNER, user)
   ),

And that's obviously too broad: there should be conditions on object_type and object_name here; as a consequence, that CTE will select all rows from dba_statements for the considered schema.

In practice this is not an issue at all, because the extra rows will be "out-of-context", so to speak, as they don't bear the same values of object_type and/or object_name as rows from the pls_ids CTE. Therefore, they end up with sane_fk = 'NO' and their (fixed) usage_context_id is set to NULL, which eliminates them from the tree CTE due to conditions on usage_context_id.

[EDIT] So it's really just a matter of making the filters consistent with those of src here, for clarity and in order to avoid getting unnecessary rows from dba_statements.