0

I have 2 users in OracleXE db: ALICE and BOB. Following sequence of steps is successful at my local machine:

  • connect as ALICE
  • create top-level type: create or replace type testtype as object(x number(16)); (EDIT: correction from wrong name foo which misleaded Matthew McPeak)
  • connect as BOB
  • BOB sees the ALICE's type:
    • select * from all_types returns row
    • declare x ALICE.testtype; begin null; end; compiles

Same steps applied to our development database yield in situation where type is invisible to BOB, i.e. select doesn't return row for the type, PLSQL block compilation fails with PLS-00201: identifier 'ALICE.TESTTYPE' must be declared error.

I need to make type visible, so I ask: What may cause this difference? (I guessed for some need of grants on types or public synonyms, however this minimalistic example proves the opposite.) Some property of user/session? I did my best in googling and am stuck now though I hope the solution will be trivial. Both databases are Oracle 11g (11.2.0.1.0 dev, 11.2.0.2.0 local).

Thanks!

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64

1 Answers1

0

I'm assuming it's more than just the fact that you named your type foo but tried to use it as testtype.

If foo is the correct name, then, as ALICE:

grant execute on foo to bob;

Bob probably has EXECUTE ANY TYPE or similar system privilege in the 1st database.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Sorry, foo was wrong, I overlooked this typo when rewriting from mail. Thnks, I will try it on my work computer soon. – Tomáš Záluský Oct 10 '15 at 12:10
  • Solved. At my local machine, Bob has been created by system user and initialized by script which comes from this query: `select 'grant ' || privilege || ' to BOB' from session_privs`. Hence he really had `EXECUTE ANY TYPE` privilege. After restricting privileges, everything went back to normal and `grant execute on TESTTYPE to BOB`/`revoke execute on TESTTYPE from BOB` enables/disables access, respectively. – Tomáš Záluský Oct 13 '15 at 07:03