Re: Foreign Key Validation after Reference Table Ownership Change

On 03/21/2018 10:48 AM, Louis Battuello wrote:
> 
>>
>> The point is you can't resolve a name like "schema_1.something" unless
>> you have USAGE on schema_1.  So the RI-checking query, which is run as
>> the owner of the table, fails at parse time.
> 
> That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the
referencetable’s schema.
 
> 
> But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is
user_1’sinsert on the referencing table failing? Is the validation of the FK no longer done as user_1?
 
> 

 From Tom's post:
"
The point is you can't resolve a name like "schema_1.something" unless
you have USAGE on schema_1.  So the RI-checking query, which is run as
                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

the owner of the table, fails at parse time."
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It is not the user that is doing the INSERT that matters it is the user 
that owns the table that matters.

-- 
Adrian Klaver
adrian.klaver@aklaver.com