Re: Role/Privilege and remote databases

>
> The whole Oracle online doc. set can be found at http://tahiti.oracle.com
>
> The general rule is that "privs acquired via ROLEs do NOT apply within
> PL/SQL procedures".
>
> There is nothing unique or different regarding how permissions are handled
> when comparing "local" access to "remote" access.
>
> Keep in mind that WRT to "remote" access, a session is "logged into the
> (remote) database" using a username & password. Which objects & what type
> of access are governed by the same rules as apply as to the local DB.
>
> GRANTs on the objects must be issued directly to the schema/username.

local_database

stg
 stg.table01 .. stg.table99

 stg.load_package

 GRANT EXECUTE ON stg.load_package TO load_role

user1
 GRANT load_role TO user1

remote_database

 CREATE PUBLIC SYNONYM load_package FOR stg.load_package_at_local_database

user2
 GRANT load_role TO user2

user1 and user 2 are granted the LOAD_ROLE which in turn is granted execute permission on STG.LOAD_PACKAGE which will perform the DML operations on the tables using the privileges of the owner (in this case STG which is the owner of all tables).

User2 **should** be able to perform operations on stg.table01 through the STG.LOAD_PACKAGE since it will be using permissions inherited through the package. Received on Sun Sep 09 2007 - 20:50:04 CDT