databaseoracle-databaserights-management

Why Oracle type created by one user is not visible by another user?


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

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!


Solution

  • 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.