Re: Role/Privilege and remote databases

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:iz_Ei.90302$Mu5.72412_at_newsfe15.phx...
> "Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in
> news:YEWEi.8761$924.1057_at_newssvr23.news.prodigy.net:
>
>> Oracle 10.2.0.3, Windows 2003 Server
>>
>> Below is the scenario. I'm confused about roles and permissions when
>> using more than just a local database. Is the question at the end of
>> the scenario correct?
>>
>> remote_server
>>
>> my_schema.my_table
>> - GRANT SELECT, INSERT, UPDATE ON my_schema.my_table TO MY_ROLE
>>
>> my_role
>> - user1
>> - user2
>>
>> local server
>>
>> CREATE PUBLIC SYNONYM my_table FOR my_schema.my_table_at_remote_server
>>
>> my_role
>> - user3
>>
>> Q: Is having the MY_ROLE role on both the remote and local server,
>> and
>> granting privileges
>> to the role sufficient to allow USER3 to access the MY_TABLE on
>> the
>> remote server?
>>
>>
>
> It depends upon how/when/where the access to MY_TABLE is made.
> From SQL*Plus, yes. From PL/SQL, no.

It would be through PL/SQL code contained in packages.

What would I need to do to permit access by a role to tables on a remote server? Is there a good example/description in the Oracle documentation somewhere? Received on Sun Sep 09 2007 - 18:50:26 CDT